Table of contents
Preamble
This article will show you few steps to create a physical standby database between two servers running Red Hat Enterprise Linux Server release 5.6 (Tikanga), with Oracle 11.2.0.2.0. Primary server is called server1.domain.com with ASM instance called +ASM, listener port is 1531 and database instance is called rac1, listener port is 1541. Standby server is called server2.domain.com with ASM instance called +ASM, listener port is 1531 and database instance is called rac2, listener port is 1541.
The method that will be used in this document is based on RMAN duplicate command that is most probably not the one you will use in real life but, anyway, described when following the Oracle official training.
Using Data Guard also implies to use spfile for initialization parameter file and create a password file for all your instances (REMOTE_LOGIN_PASSWORDFILE).
Prepare primary database
Enable FORCE LOGGING mode on your primary database, this avoids users creating objects in NOLOGGING mode:
SQL> ALTER DATABASE force logging; DATABASE altered. SQL> SELECT force_logging FROM v$database; FOR --- YES |
Create standby redo log files (one more than number of log files):
SQL> col member FOR a50 SQL> SET lines 200 SQL> SELECT * FROM v$logfile ORDER BY GROUP#; GROUP# STATUS TYPE MEMBER IS_ ---------- ------- ------- -------------------------------------------------- --- 1 ONLINE +DATA/rac1/onlinelog/group_1.264.751650297 YES 2 ONLINE +DATA/rac1/onlinelog/group_2.262.751650303 YES 3 ONLINE +DATA/rac1/onlinelog/group_3.260.751650309 YES SQL> SELECT * FROM v$log; GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------ ------------ ------------------ 1 1 16 52428800 512 1 YES ACTIVE 245025 24-MAY-11 249802 24-MAY-11 2 1 17 52428800 512 1 NO CURRENT 249802 24-MAY-11 2.8147E+14 3 1 15 52428800 512 1 YES INACTIVE 215947 20-MAY-11 245025 24-MAY-11 SQL> ALTER DATABASE ADD standby logfile '+DATA' SIZE 50m; DATABASE altered. SQL> ALTER DATABASE ADD standby logfile '+DATA' SIZE 50m; DATABASE altered. SQL> ALTER DATABASE ADD standby logfile '+DATA' SIZE 50m; DATABASE altered. SQL> ALTER DATABASE ADD standby logfile '+DATA' SIZE 50m; DATABASE altered. SQL> SELECT * FROM v$logfile ORDER BY GROUP#; GROUP# STATUS TYPE MEMBER IS_ ---------- ------- ------- -------------------------------------------------- --- 1 ONLINE +DATA/rac1/onlinelog/group_1.264.751650297 YES 2 ONLINE +DATA/rac1/onlinelog/group_2.262.751650303 YES 3 ONLINE +DATA/rac1/onlinelog/group_3.260.751650309 YES 4 STANDBY +DATA/rac1/onlinelog/group_4.265.751980833 NO 5 STANDBY +DATA/rac1/onlinelog/group_5.263.751980875 NO 6 STANDBY +DATA/rac1/onlinelog/group_6.284.751980881 NO 7 STANDBY +DATA/rac1/onlinelog/group_7.285.751980887 NO SQL> SELECT GROUP#, dbid, thread#, SEQUENCE#, status FROM v$standby_log; GROUP# DBID THREAD# SEQUENCE# STATUS ---------- ---------------------------------------- ---------- ---------- ---------- 4 UNASSIGNED 0 0 UNASSIGNED 5 UNASSIGNED 0 0 UNASSIGNED 6 UNASSIGNED 0 0 UNASSIGNED 7 UNASSIGNED 0 0 UNASSIGNED |
Specify the list of unique database names in your configuration (primary and all standby):
SQL> ALTER SYSTEM SET log_archive_config='dg_config=(rac1,rac2)'; SYSTEM altered. SQL> SELECT * FROM v$dataguard_config; DB_UNIQUE_NAME ------------------------------ rac1 rac2 |
Configure the redo transport server between your two databases:
SQL> ALTER SYSTEM SET log_archive_dest_2='service=rac2 valid_for=(online_logfile, primary_role) db_unique_name=rac2'; SYSTEM altered. SQL> ALTER SYSTEM SET log_archive_dest_state_2=enable; SYSTEM altered. |
As we are using ASM and OMF there is nothing to configure for datafiles naming convention, we can just add standby database datafile management in case of role change:
SQL> ALTER SYSTEM SET standby_file_management=auto; SYSTEM altered. |
To be able to connect to standby database you need to copy the Oracle password file and rename it to mach sid of your standby database:
[orarac@server1 /]$ scp /ora_rac/software/dbs/orapwrac1 server2:/ora_rac/software/dbs/orapwrac2 orarac@server2's password: orapwrac1 100% 1536 1.5KB/s 00:00 |
Prepare standby database
Create a standby init.ora file (initrac2.ora) containing only one line:
db_name=rac2 |
Start standby database in NOMOUNT mode:
[orarac@server2 /]$ SQL SQL*Plus: RELEASE 11.2.0.2.0 Production ON Tue May 24 16:55:08 2011 Copyright (c) 1982, 2010, Oracle. ALL rights reserved. Connected TO an idle instance. SQL> startup nomount pfile='/ora_rac/software/dbs/initrac2.ora'; ORACLE instance started. Total SYSTEM Global Area 217157632 bytes Fixed SIZE 2225064 bytes Variable SIZE 159386712 bytes DATABASE Buffers 50331648 bytes Redo Buffers 5214208 bytes |
SQL*Net configuration
Start by creating a crossover SQL*Net configuration to be able to access any instances of your Data Guard configuration from any servers. Which means following tnsnames.ora and sqlnet.ora files on all servers:
[orarac@server1 /]$ cat $TNS_ADMIN/tnsnames.ora +ASM.WORLD = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = server1.domain.com)(Port = 1531)) ) (CONNECT_DATA = (SID = +ASM) (SERVER = DEDICATED) ) ) rac1.WORLD = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = server1.domain.com)(Port = 1541)) ) (CONNECT_DATA = (SID = rac1) (SERVER = DEDICATED) ) ) rac2.WORLD = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = server2.domain.com)(Port = 1541)) ) (CONNECT_DATA = (SID = rac2) (SERVER = DEDICATED) ) ) |
[orarac@server1 /]$ cat $TNS_ADMIN/sqlnet.ora NAMES.DEFAULT_DOMAIN = world |
Here it is really a nightmare as it is impossible to use all new SQL*Net 11gR2 functionalities. So forget all those stuff about automatic registration of database to listener and use the old way and define list of listening SID in listener configuration (method described in Data Guard Oracle official training !!):
If you do insist to use those new functionalities your connect auxiliary sys/password@tnsentry will generate an error like the following:
RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of Duplicate Db command at 05/24/2011 16:16:17 RMAN-05501: aborting duplication of target database RMAN-03015: error occurred in stored script Memory Script RMAN-04006: error from auxiliary database: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor |
Simply because by specifying only db_name in your init.ora file, local_listener parameter is empty.
Oracle first suggestion is to launch the script from standby database and use connect auxiliary /, but this time error is:
RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of Duplicate Db command at 05/24/2011 16:40:04 RMAN-05501: aborting duplication of target database RMAN-06217: not connected to auxiliary database with a net service name |
If you do set value for local_listener (either in init.ora or with ALTER SYSTEM) you will get this time:
SQL> ALTER SYSTEM SET local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=server2.domain.com)(PORT=1541))))'; SYSTEM altered. |
RMAN> connect auxiliary sys/password@rac2 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-04006: error from auxiliary database: ORA-12528: TNS:listener: all appropriate instances are blocking new connections |
Database in NOMOUNT state are registered but in BLOCKED status:
[orarac@server2 /]$ lsnrctl status listener_rac2 LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 24-MAY-2011 15:42:51 Copyright (c) 1991, 2010, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_RAC2))) STATUS of the LISTENER ------------------------ Alias LISTENER_RAC2 Version TNSLSNR for Linux: Version 11.2.0.2.0 - Production Start Date 24-MAY-2011 14:33:55 Uptime 0 days 1 hr. 8 min. 56 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /ora_rac/software/network/admin/listener.ora Listener Log File /ora_rac/software/log/diag/tnslsnr/server2/listener_rac2/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_RAC2))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.102)(PORT=1541))) Services Summary... Service "rac2" has 1 instance(s). Instance "rac2", status BLOCKED, has 1 handler(s) for this service... The command completed successfully |
Oracle second suggestion is to add an option to your tnsnames entry (UR=A):
rac2.WORLD = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = server2.domain.com)(Port = 1541)) ) (CONNECT_DATA = (SERVICE_NAME = rac2.world)(UR=A) (SERVER = DEDICATED) ) ) |
But again not working so what’s remains is third Oracle option i.e. create a static entry in listener configuration for auxiliary database. So add the following in listener.ora file of your standby database:
SID_LIST_LISTENER_RAC2 = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = rac2.world) (ORACLE_HOME = /ora_rac/software) (SID_NAME = rac2) ) ) |
For failover and switchover it is strongly recommended to perform the same on your primary instance.
RMAN duplication
The script is something like:
[orarac@server1 ~]$ cat rman.txt connect target / connect auxiliary sys/password@rac2 run { allocate channel primary1 type disk; allocate channel primary2 type disk; allocate auxiliary channel standby1 type disk; duplicate target database for standby from active database spfile parameter_value_convert 'rac1', 'rac2' set db_unique_name='rac2' set db_create_file_dest='+DATA' set db_recovery_file_dest='+DATA' set db_recovery_file_dest_size='5g' set control_files='+DATA' set fal_client='rac2.world' set fal_server='rac1.world' set standby_file_management='auto' set log_archive_config='dg_config=(rac1, rac2)' set log_archive_dest_2='service=rac1 async valid_for=(online_logfile, primary_role) db_unique_name=rac1' set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=server2.domain.com)(PORT=1541))))'; } |
Please note spfile option that will copy spfile to standby instance and parameter_value_convert option that will mass replace instance names in spfile. Remains any few parameter that need to be manually modified with set option.
[orarac@server1 ~]$ rman Recovery Manager: Release 11.2.0.2.0 - Production on Thu May 24 15:44:28 2011 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. RMAN> @/home/orarac/rman.txt RMAN> connect target * connected to target database: RAC1 (DBID=656357419) RMAN> RMAN> connect auxiliary * connected to auxiliary database: RAC2 (not mounted) RMAN> RMAN> run { 2> allocate channel primary1 type disk; 3> allocate channel primary2 type disk; 4> allocate auxiliary channel standby1 type disk; 5> duplicate target database for standby from active database 6> spfile 7> parameter_value_convert 'rac1', 'rac2' 8> set db_unique_name='rac2' 9> set db_create_file_dest='+DATA' 10> set db_recovery_file_dest='+DATA' 11> set db_recovery_file_dest_size='5g' 12> set control_files='+DATA' 13> set fal_client='rac2.world' 14> set fal_server='rac1.world' 15> set standby_file_management='auto' 16> set log_archive_config='dg_config=(rac1, rac2)' 17> set log_archive_dest_2='service=rac1 async valid_for=(online_logfile, primary_role) db_unique_name=rac1' 18> set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=server2.domain.com)(PORT=1541))))'; 19> } using target database control file instead of recovery catalog allocated channel: primary1 channel primary1: SID=34 device type=DISK allocated channel: primary2 channel primary2: SID=35 device type=DISK allocated channel: standby1 channel standby1: SID=19 device type=DISK Starting Duplicate Db at 24-MAY-11 contents of Memory Script: { backup as copy reuse targetfile '/ora_rac/software/dbs/orapwrac1' auxiliary format '/ora_rac/software/dbs/orapwrac2' targetfile '+DATA/rac1/spfilerac1.ora' auxiliary format '/ora_rac/software/dbs/spfilerac2.ora' ; sql clone "alter system set spfile= ''/ora_rac/software/dbs/spfilerac2.ora''"; } executing Memory Script Starting backup at 24-MAY-11 Finished backup at 24-MAY-11 sql statement: alter system set spfile= ''/ora_rac/software/dbs/spfilerac2.ora'' contents of Memory Script: { sql clone "alter system set db_unique_name = ''rac2'' comment= '''' scope=spfile"; sql clone "alter system set db_create_file_dest = ''+DATA'' comment= '''' scope=spfile"; sql clone "alter system set db_recovery_file_dest = ''+DATA'' comment= '''' scope=spfile"; sql clone "alter system set db_recovery_file_dest_size = 5g comment= '''' scope=spfile"; sql clone "alter system set control_files = ''+DATA'' comment= '''' scope=spfile"; sql clone "alter system set fal_client = ''rac2.world'' comment= '''' scope=spfile"; sql clone "alter system set fal_server = ''rac1.world'' comment= '''' scope=spfile"; sql clone "alter system set standby_file_management = ''auto'' comment= '''' scope=spfile"; sql clone "alter system set log_archive_config = ''dg_config=(rac1, rac2)'' comment= '''' scope=spfile"; sql clone "alter system set log_archive_dest_2 = ''service=rac1 async valid_for=(online_logfile, primary_role) db_unique_name=rac1'' comment= '''' scope=spfile"; sql clone "alter system set local_listener = ''(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=server2.domain.com)(PORT=1541))))'' comment= '''' scope=spfile"; shutdown clone immediate; startup clone nomount; } executing Memory Script sql statement: alter system set db_unique_name = ''rac2'' comment= '''' scope=spfile sql statement: alter system set db_create_file_dest = ''+DATA'' comment= '''' scope=spfile sql statement: alter system set db_recovery_file_dest = ''+DATA'' comment= '''' scope=spfile sql statement: alter system set db_recovery_file_dest_size = 5g comment= '''' scope=spfile sql statement: alter system set control_files = ''+DATA'' comment= '''' scope=spfile sql statement: alter system set fal_client = ''rac2.world'' comment= '''' scope=spfile sql statement: alter system set fal_server = ''rac1.world'' comment= '''' scope=spfile sql statement: alter system set standby_file_management = ''auto'' comment= '''' scope=spfile sql statement: alter system set log_archive_config = ''dg_config=(rac1, rac2)'' comment= '''' scope=spfile sql statement: alter system set log_archive_dest_2 = ''service=rac1 async valid_for=(online_logfile, primary_role) db_unique_name=rac1'' comment= '''' scope=spfile sql statement: alter system set local_listener = ''(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=server2.domain.com)(PORT=1541))))'' comment= '''' scope=spfile Oracle instance shut down connected to auxiliary database (not started) Oracle instance started Total System Global Area 208769024 bytes Fixed Size 2225064 bytes Variable Size 142609496 bytes Database Buffers 58720256 bytes Redo Buffers 5214208 bytes allocated channel: standby1 channel standby1: SID=25 device type=DISK contents of Memory Script: { sql clone "alter system set control_files = ''+DATA/rac2/controlfile/current.290.752168699'' comment= ''Set by RMAN'' scope=spfile"; backup as copy current controlfile for standby auxiliary format '+DATA/rac2/controlfile/current.269.752168699'; sql clone "alter system set control_files = ''+DATA/rac2/controlfile/current.269.752168699'' comment= ''Set by RMAN'' scope=spfile"; shutdown clone immediate; startup clone nomount; } executing Memory Script sql statement: alter system set control_files = ''+DATA/rac2/controlfile/current.290.752168699'' comment= ''Set by RMAN'' scope=spfile Starting backup at 24-MAY-11 channel primary1: starting datafile copy copying standby control file output file name=/ora_rac/software/dbs/snapcf_rac1.f tag=TAG20110526T154509 RECID=3 STAMP=752168711 channel primary1: datafile copy complete, elapsed time: 00:00:07 Finished backup at 24-MAY-11 sql statement: alter system set control_files = ''+DATA/rac2/controlfile/current.269.752168699'' comment= ''Set by RMAN'' scope=spfile Oracle instance shut down connected to auxiliary database (not started) Oracle instance started Total System Global Area 208769024 bytes Fixed Size 2225064 bytes Variable Size 146803800 bytes Database Buffers 54525952 bytes Redo Buffers 5214208 bytes allocated channel: standby1 channel standby1: SID=25 device type=DISK contents of Memory Script: { sql clone 'alter database mount standby database'; } executing Memory Script sql statement: alter database mount standby database contents of Memory Script: { set newname for clone tempfile 1 to new; switch clone tempfile all; set newname for clone datafile 1 to new; set newname for clone datafile 2 to new; set newname for clone datafile 3 to new; set newname for clone datafile 4 to new; backup as copy reuse datafile 1 auxiliary format new datafile 2 auxiliary format new datafile 3 auxiliary format new datafile 4 auxiliary format new ; sql 'alter system archive log current'; } executing Memory Script executing command: SET NEWNAME renamed tempfile 1 to +DATA in control file executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME Starting backup at 24-MAY-11 channel primary1: starting datafile copy input datafile file number=00001 name=+DATA/rac1/datafile/system.259.751650313 channel primary2: starting datafile copy input datafile file number=00002 name=+DATA/rac1/datafile/sysaux.258.751650331 output file name=+DATA/rac2/datafile/sysaux.256.752168733 tag=TAG20110526T154537 channel primary2: datafile copy complete, elapsed time: 00:01:07 channel primary2: starting datafile copy input datafile file number=00003 name=+DATA/rac1/datafile/undotbs1.256.751650347 output file name=+DATA/rac2/datafile/system.257.752168731 tag=TAG20110526T154537 channel primary1: datafile copy complete, elapsed time: 00:01:09 channel primary1: starting datafile copy input datafile file number=00004 name=+DATA/rac1/datafile/users.269.751650369 output file name=+DATA/rac2/datafile/users.258.752168801 tag=TAG20110526T154537 channel primary1: datafile copy complete, elapsed time: 00:00:03 output file name=+DATA/rac2/datafile/undotbs1.259.752168801 tag=TAG20110526T154537 channel primary2: datafile copy complete, elapsed time: 00:00:16 Finished backup at 24-MAY-11 sql statement: alter system archive log current contents of Memory Script: { switch clone datafile all; } executing Memory Script datafile 1 switched to datafile copy input datafile copy RECID=3 STAMP=752168817 file name=+DATA/rac2/datafile/system.257.752168731 datafile 2 switched to datafile copy input datafile copy RECID=4 STAMP=752168817 file name=+DATA/rac2/datafile/sysaux.256.752168733 datafile 3 switched to datafile copy input datafile copy RECID=5 STAMP=752168817 file name=+DATA/rac2/datafile/undotbs1.259.752168801 datafile 4 switched to datafile copy input datafile copy RECID=6 STAMP=752168817 file name=+DATA/rac2/datafile/users.258.752168801 Finished Duplicate Db at 24-MAY-11 released channel: primary1 released channel: primary2 released channel: standby1 RMAN> **end-of-file** |
Start redo apply on standby database with:
SQL> ALTER DATABASE recover managed standby DATABASE USING CURRENT logfile disconnect; DATABASE altered. |
Start transmission of redo on primary with:
SQL> ALTER SYSTEM switch logfile; SYSTEM altered. |
Then check on standby status of redo apply with:
SQL> SELECT SEQUENCE#, first_time, next_time FROM v$archived_log ORDER BY SEQUENCE#; SEQUENCE# FIRST_TIME NEXT_TIME ---------- ------------------ ------------------ 19 24-MAY-11 24-MAY-11 20 24-MAY-11 24-MAY-11 21 24-MAY-11 24-MAY-11 |
To see it’s working issue few switch redo log file on primary with:
SQL> SELECT * FROM v$log ORDER BY SEQUENCE#; GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------ ------------ ------------------ 2 1 20 52428800 512 1 YES INACTIVE 266573 24-MAY-11 267204 24-MAY-11 3 1 21 52428800 512 1 YES ACTIVE 267204 24-MAY-11 268658 24-MAY-11 1 1 22 52428800 512 1 NO CURRENT 268658 24-MAY-11 2.8147E+14 SQL> ALTER SYSTEM switch logfile; SYSTEM altered. SQL> ALTER SYSTEM switch logfile; SYSTEM altered. SQL> ALTER SYSTEM switch logfile; SYSTEM altered. SQL> SELECT * FROM v$log ORDER BY SEQUENCE#; GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------ ------------ ------------------ 2 1 23 52428800 512 1 YES INACTIVE 268829 24-MAY-11 268840 24-MAY-11 3 1 24 52428800 512 1 YES INACTIVE 268840 24-MAY-11 268846 24-MAY-11 1 1 25 52428800 512 1 NO CURRENT 268846 24-MAY-11 2.8147E+14 |
Then check is done on standby with:
SQL> SELECT SEQUENCE#, first_time, next_time FROM v$archived_log ORDER BY SEQUENCE#; SEQUENCE# FIRST_TIME NEXT_TIME ---------- ------------------ ------------------ 19 24-MAY-11 24-MAY-11 20 24-MAY-11 24-MAY-11 21 24-MAY-11 24-MAY-11 22 24-MAY-11 24-MAY-11 23 24-MAY-11 24-MAY-11 24 24-MAY-11 24-MAY-11 6 ROWS selected. SQL> SELECT SEQUENCE#, applied FROM v$archived_log ORDER BY SEQUENCE#; SEQUENCE# APPLIED ---------- --------- 19 YES 20 YES 21 YES 22 YES 23 YES 24 IN-MEMORY 6 ROWS selected. |
References
- RMAN-04006 And ORA-12514 During RMAN Duplicate [ID 883490.1]
- Connection to Auxilary using connect string failed with ORA-12528 [ID 419440.1]
- How to resolve ORA-16792 [ID 966472.1]
