Data Guard hands-on with a physical standby database creation
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]


