'oracle data guard'에 해당되는 글 2건

localhost@orcl : /home/oracle> dgmgrl sys/oracle

DGMGRL for Linux: Version 11.2.0.3.0 - Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.

Connected.

DGMGRL>

DGMGRL>

DGMGRL> help

The following commands are available:

add Adds a standby database to the broker configuration

connect Connects to an Oracle database instance

convert Converts a database from one type to another

create Creates a broker configuration

disable Disables a configuration, a database, or fast-start failover

edit Edits a configuration, database, or instance

enable Enables a configuration, a database, or fast-start failover

exit Exits the program

failover Changes a standby database to be the primary database

help Displays description and syntax for a command

quit Exits the program

reinstate Changes a database marked for reinstatement into a viable standby

rem Comment to be ignored by DGMGRL

remove Removes a configuration, database, or instance

show Displays information about a configuration, database, or instance

shutdown Shuts down a currently running Oracle database instance

sql Executes a SQL statement

start Starts the fast-start failover observer

startup Starts an Oracle database instance

stop Stops the fast-start failover observer

switchover Switches roles between a primary and standby database

Use "help <command>" to see syntax for individual commands

DGMGRL>

[Primary DB]

ocp@orcl : /home/oracle> dgmgrl sys/oracle

DGMGRL for Linux: Version 11.2.0.3.0 - Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.

Connected.

DGMGRL>

create configuration 'test_DG' as

primary database is 'orcl'

connect identifier is 'orcl';

Error:

ORA-16525: the Data Guard broker is not yet available

ORA-06512: at "SYS.DBMS_DRS", line 157

ORA-06512: at line 1

[해결방안]

Database dg_broker_start가 모두 false이므로 위와 같은 Error이 발생함.

Primary, standby 모두 true로 변경(DG Broker start)

Alter system set dg_broker_start=true;

DGMGRL> show configuration;

Configuration - test_DG

Protection Mode: MaxPerformance

Databases:

orcl - Primary database

Fast-Start Failover: DISABLED

Configuration Status:

DISABLED

[Primary DB] - standby DB 추가

DGMGRL>

add database 'stby' as

connect identifier is 'stby';

Database "stby" added

DGMGRL> show configuration;

Configuration - test_DG

Protection Mode: MaxPerformance

Databases:

orcl - Primary database

stby - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

DISABLED

DGMGRL> enable configuration;

Enabled.

DGMGRL> show configuration;

Configuration - test_DG

Protection Mode: MaxPerformance

Databases:

orcl- Primary database

stby- Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

DGMGRL> switchover to stby;

Performing switchover NOW, please wait...

New primary database "stby" is opening...

Operation requires shutdown of instance "orcl" on database "orcl"

Shutting down instance "orcl"...

ORACLE instance shut down.

Operation requires startup of instance "orcl" on database "orcl"

Starting instance "orcl"...

ORACLE instance started.

Database mounted.

Database opened.

Switchover succeeded, new primary is "stby"

구분

Host IP

OS Version

Oracle SID

DB Version

Priamry

192.168.22.3

GNU / LINUX

orcl

Oracle 11.2.0.3

Standby

192.168.22.10

GNU / LINUX

Stby

Oracle 11.2.0.3

Priamry

ORCL =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = ocp.mycorpdomain.com)(PORT = 1521))

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

)

)

SID_LIST_ORCL =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = orcl)

(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)

(SID_NAME = orcl)

)

)

orcl =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.22.3)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl)

)

)

Standby

STBY =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))

# (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

)

)

SID_LIST_STBY =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = stby)

(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)

(SID_NAME = stby)

)

)

stby =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.22.10)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = stby)

)

)

[Oracle 유저]

$Mkdir -p /home/oracle/arch

$Mkdir -p u01/app/oracle/admin/stby/adump

$Mkdir -p u01/app/oracle/admin/stby/dpdump

$Mkdir -p u01/app/oracle/admin/stby/pfile

$mkdir -p /u01/app/oracle/diag/rdbms/stby/stby/Trace

$Mkdir -p /u01/app/oracle/oradata/stby

alter database add standby logfile '/u01/app/oracle/oradata/stby/stby01.log' size 50m;

alter database add standby logfile '/u01/app/oracle/oradata/stby/stby02.log' size 50m;

alter database add standby logfile '/u01/app/oracle/oradata/stby/stby03.log' size 50m;

alter database add standby logfile '/u01/app/oracle/oradata/stby/stby04.log' size 50m;

SQL> alter system set log_archive_dest_1='location= /home/oracle/arch mandatory' scope=both;

SQL> alter system set fal_client=orcl scope=both;

SQL> alter system set fal_server=stby scope=both;

SQL> alter system set standby_file_management = auto scope=both;

SQL> alter system set log_archive_config='dg_config=(orcl,stby)' scope=both;

SQL> alter system set log_archive_dest_2='service=stby async valid_for=(online_logfile,primary_role) db_unique_name=stby' scope=both;

SQL> ALTER DATABASE FORCE LOGGING;

shutdown immediate;

startup mount;

alter database archivelog;

alter database open;

ORCL-> STBY

$ cp /u01/app/oracle/product/11.2.0/db_1/dbs/orapworcl

$ cd /u01/app/oracle/product/11.2.0/db_1/dbs/orapwstby

$ vi /u01/app/oracle/product/11.2.0/db_1/dbs/initstby.ora

db_name=orcl

db_unique_name=stby

localhost@stby : /u01/app/oracle/product/11.2.0/db_1/dbs> vi initstby.ora

orcl.__db_cache_size=121634816

stby.__db_cache_size=125829120

orcl.__java_pool_size=4194304

stby.__java_pool_size=4194304

orcl.__large_pool_size=4194304

stby.__large_pool_size=4194304

orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

stby.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

orcl.__pga_aggregate_target=79691776

stby.__pga_aggregate_target=79691776

orcl.__sga_target=234881024

stby.__sga_target=234881024

orcl.__shared_io_pool_size=0

stby.__shared_io_pool_size=0

orcl.__shared_pool_size=100663296

stby.__shared_pool_size=96468992

orcl.__streams_pool_size=0

stby.__streams_pool_size=0

*.archive_lag_target=0

*.audit_file_dest='/u01/app/oracle/admin/stby/adump'

*.audit_trail='db'

*.cluster_database=FALSE

*.compatible='11.2.0.0.0'

*.control_files='/u01/app/oracle/oradata/stby/control01.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_file_name_convert='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/stby'

*.db_name='orcl'

*.db_recovery_file_dest_size=10737418240

*.db_recovery_file_dest='/home/oracle/arch'

*.db_unique_name='stby'

*.dg_broker_start=TRUE

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=stbyXDB)'

*.fal_client='stby'

*.fal_server='orcl'

*.log_archive_config='dg_config=(orcl,stby)'

*.log_archive_dest_1='location=/home/oracle/arch'

*.log_archive_dest_2='service=stby lgwr async valid_for=(online_logfile,primary_role) db_unique_name=stby'

*.log_archive_dest_state_2='ENABLE'

run {

duplicate target database for standby from active database

dorecover

spfile

parameter_value_convert 'orcl','stby'

set db_unique_name='stby'

set control_files='/u01/app/oracle/oradata/stby/control01.ctl'

set db_file_name_convert='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/stby'

set log_file_name_convert='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/stby'

set cluster_database='FALSE'

set fal_client='stby'

set fal_server='orcl'

set log_archive_dest_1='location=/home/oracle/arch'

set log_archive_dest_2='service=orcl LGWR ASYNC valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=orcl'

set log_archive_config='dg_config=(orcl,stby)'

set service_names='stby';

sql "alter system archive log current";

CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;

}