The following demonstration is a step by step procedure to create a hybrid Disaster Recovery environment :
Primary database is 2 node RAC and Standby database is Standalone. This procedure gives a general idea and there are some steps that
can be optimized.
Environment
Primary Database 2 Node RAC
Node Names: node1, node2
DB Name: ORCL
DB Unique Name: ORCL
DB Version: 11.2.0.1
Grid Infrastructure (CRS + ASM).
SCAN settings in /etc/hosts file, so SCAN listener only running on one node (Node2 for this demo)
ASM Diskgroups: +DATA, +FRA
Standby Database Single Instance:
ASM Diskgroup : +DG_DATA (For datafiles and FRA)
DB Unique Name: STDBY
DB Name: ORCL
Preparations at the Primary Site
GI_HOME=/u02/app/oracle/11.2.0/grid
ORACLE_HOME=/u02/app/oracle/product/11.1.0/db_1
Ensure force logging is enabled. Then following steps are needed:
a.) Dedicated Listener for duplication on Primary Site
b.) Prepare initialization Parameters
c.) Add Standby redo logs.
Why Dedicated listener for Duplicate database command ?
When duplicate database command is issued from RMAN, it copies the password file from Primary database host to Standby database host. If this operation is performed from remote client (with tnsnames.ora having SCAN name), it would cause issues when password file gets copied from the Primary host to Standby host. This is because SCAN directs connection to a node based on load. Here is the error:
RMAN> duplicate target database for standby from active database;
Starting Duplicate Db at 12-AUG-11
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=26 device type=DISK
contents of Memory Script:
{
backup as copy reuse
targetfile ‘/u02/app/oracle/product/11.1.0/db_1/dbs/orapwORCL1’ auxiliary format
‘/u02/app/oracle/product/11.1.0/db_1/dbs/orapwSTDBY’ ;
}
executing Memory Script
Starting backup at 12-AUG-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=33 instance=ORCL2 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 08/12/2011 14:38:59
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 08/12/2011 14:38:59
ORA-19505: failed to identify file “/u02/app/oracle/product/11.1.0/db_1/dbs/orapwORCL1”
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
To Avoid this, create a dedicated Listener on a source node [node1] for this operation.
Here are the Settings from both nodes:
Node 1
[[email protected] bin]# ./srvctl config scan_listener
SCAN Listener LISTENER_SCAN1 exists. Port: TCP:1530
[[email protected] bin]# ./srvctl config scan
SCAN name: cluster1, Network: 1/192.168.10.0/255.255.255.0/
SCAN VIP name: scan1, IP: /cluster1.home.com/192.168.10.30 SQL> show parameter listener NAME TYPE VALUE listener_networks string
local_listener string (DESCRIPTION=(ADDRESS_LIST=(AD
DRESS=(PROTOCOL=TCP)(HOST=192.
168.10.9)(PORT=1521))))
remote_listener string cluster1.home.com:1530 [[email protected] admin]$ ps -ef |grep tns
oracle 7072 1 0 10:19 ? 00:00:00 /u02/app/11.2.0/grid/bin/tnslsnr LISTENER -inherit
oracle 12670 8486 0 10:51 pts/1 00:00:00 grep tns
[[email protected] admin]$ lsnrctl status listener LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 12-AUG-2011 10:52:04 Copyright (c) 1991, 2009, Oracle. All rights reserved. Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) STATUS of the LISTENER Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 12-AUG-2011 10:19:01
Uptime 0 days 0 hr. 33 min. 3 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u02/app/11.2.0/grid/network/admin/listener.ora
Listener Log File /u02/app/oracle/diag/tnslsnr/node1/listener/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.10.2)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.10.8)(PORT=1521)))
Services Summary…
Service "+ASM" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service…
Service "ORCL" has 1 instance(s).
Instance "ORCL1", status READY, has 1 handler(s) for this service…
The command completed successfully Node 2 [[email protected] ~]$ ps -ef |grep tns
oracle 5401 1 0 10:18 ? 00:00:00 /u02/app/11.2.0/grid/bin/tnslsnr LISTENER -inherit
oracle 5403 1 0 10:18 ? 00:00:00 /u02/app/11.2.0/grid/bin/tnslsnr LISTENER_SCAN1 -inherit
oracle 7684 7135 0 10:52 pts/1 00:00:00 grep tns
Local Listener: on port 1521
SCAN Listener: on port 1530 With SCAN listener in place, clientís tnsnames.ora file looks like: orcl=
(description=
(address=(port=1530)(host=cluster1.home.com)(protocol=tcp))
(connect_data=
(service_name=ORCL))
) As mentioned above, duplicate database command fails in case the command is issued from a client with tnsnames.ora file entry as above. To workaround this, create a dedicated Listener on a source node [node1] for this operation. Lets configure ORCL1 that runs from DB_HOME. [[email protected] admin]$ lsnrctl start ORCL1 LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 12-AUG-2011 14:47:54 Copyright (c) 1991, 2009, Oracle. All rights reserved. Starting /u02/app/oracle/product/11.1.0/db_1/bin/tnslsnr: please wait… TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /u02/app/oracle/product/11.1.0/db_1/network/admin/listener.ora
Log messages written to /u02/app/oracle/product/11.1.0/db_1/log/diag/tnslsnr/node1/orcl1/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=node1.home.com)(PORT=1523))) So, the remote clientís tnsnames.ora file would look like: orcl=
(description=
(address=(port=1530)(host=cluster1.home.com)(protocol=tcp))
(connect_data=
(service_name=ORCL))
) STDBY=
(description=
(address=(port=1522)(host=node1.home.com)(protocol=tcp))
(connect_data=
(service_name=STDBY))
)
ORCL1=
(description=
(address=(port=1523)(host=node1.home.com)(protocol=tcp))
(connect_data=
(service_name=ORCL1))
) Initialization Parameters On Primary Set Archiving Destinations and defer 2nd destination for the time being to avoid any errors. SQL> alter system set log_archive_dest_1='LOCATION=+FRA valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=ORCL'; System altered.
SQL> alter system set
2 log_archive_dest_2='service=stdby lgwr sync Valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=STDBY'; System altered.
SQL> alter system set log_archive_dest_state_2=defer; System altered.
SQL> alter system set standby_file_management=auto; System altered. Add Standby Redo Logs on the Primary SQL> alter database add standby logfile thread 1 '+FRA'; Database altered. SQL> alter database add standby logfile thread 1 '+FRA'; Database altered. SQL> alter database add standby logfile thread 2 '+FRA'; Database altered. SQL> alter database add standby logfile thread 2 '+FRA'; Database altered. Preparations at the Standby Site a.) Configure Listener b.) Initialization parameters Listener for Standby database has to be configured. For this Demo, following has been configured: [[email protected] admin]$ cat listener.ora
STDBY=
(description=
(address=(protocol=tcp)(host=node1.home.com)(port=1522))
)
SID_LIST_STDBY=
(SID_LIST=
(SID_DESC=
(SID_NAME=STDBY)
(ORACLE_HOME=/u02/app/oracle/product/11.1.0/db_1))
) Initialization Parameters On Standby db_name=ORCL
db_unique_name=STDBY
cluster_database=false
fal_server=orcl
fal_client=stdby
log_archive_dest_1='location=+DG_DATA valid_for=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=STDBY'
log_archive_dest_2='service=orcl valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL'
compatible='11.1.0.0.0'
db_create_file_dest='+DG_DATA'
db_create_online_log_dest_1='+DG_DATA' Since OMF is in use, we just Specify DB_CREATE_FILE_DEST and DB_CREATE_ONLINE_LOG_DEST_n parameters. There is no need to specify DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT parameters. Also note that COMPATIBLE parameter has to be set explicitly in order to avoid errors during Duplicate database command.
Duplication a.) Copy password file from primary host to Standby host and then rename it on standby host. This is just to ensure that duplication can work from a remote client. Otherwise, password file is copied as a part of duplication process. $ scp orapwORCL1 node1:$ORACLE_HOME/dbs $ mv orapwORCL1 orapwSTDBY Beware: If you are explicitly creating password file on standby site, it would give errors while shipping the logs. Only copying of password file would work. Run the duplicate command:
[[email protected] dbs]$ rman target sys/[email protected] auxiliary sys/[email protected] Recovery Manager: Release 11.2.0.1.0 - Production on Fri Aug 12 15:29:11 2011 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. connected to target database: ORCL (DBID=1286546160)
connected to auxiliary database: ORCL (not mounted) RMAN> duplicate target database for standby from active database; Starting Duplicate Db at 12-AUG-11
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=25 device type=DISK contents of Memory Script:
{
backup as copy reuse
targetfile '/u02/app/oracle/product/11.1.0/db_1/dbs/orapwORCL1' auxiliary format
'/u02/app/oracle/product/11.1.0/db_1/dbs/orapwSTDBY' ;
}
executing Memory Script Starting backup at 12-AUG-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 instance=ORCL1 device type=DISK
Finished backup at 12-AUG-11 contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '+DG_DATA/stdby/controlfile/current.256.758993371';
sql clone "create spfile from memory";
shutdown clone immediate;
startup clone nomount;
sql clone "alter system set control_files = ''+DG_DATA/stdby/controlfile/current.256.758993371'' comment=
''Set by RMAN'' scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script Starting backup at 12-AUG-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u02/app/oracle/product/11.1.0/db_1/dbs/snapcf_ORCL1.f tag=TAG20110812T152936 RECID=6 STAMP=758993377
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:04
Finished backup at 12-AUG-11 sql statement: create spfile from memory Oracle instance shut down connected to auxiliary database (not started)
Oracle instance started Total System Global Area 146472960 bytes Fixed Size 1335080 bytes
Variable Size 92274904 bytes
Database Buffers 50331648 bytes
Redo Buffers 2531328 bytes sql statement: alter system set control_files = ''+DG_DATA/stdby/controlfile/current.256.758993371''
comment= ''Set by RMAN'' scope=spfile Oracle instance shut down connected to auxiliary database (not started)
Oracle instance started Total System Global Area 146472960 bytes Fixed Size 1335080 bytes
Variable Size 92274904 bytes
Database Buffers 50331648 bytes
Redo Buffers 2531328 bytes 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 +DG_DATA in control file executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME Starting backup at 12-AUG-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+DATA/orcl/datafile/system.256.758765445
output file name=+DG_DATA/stdby/datafile/system.257.758993419 tag=TAG20110812T153017
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:26
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=+DATA/orcl/datafile/sysaux.284.758765459
output file name=+DG_DATA/stdby/datafile/sysaux.258.758993507 tag=TAG20110812T153017
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=+DATA/orcl/datafile/undotbs1.285.758765475
output file name=+DG_DATA/stdby/datafile/undotbs1.259.758993583 tag=TAG20110812T153017
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=+DATA/orcl/datafile/undotbs2.287.758768259
output file name=+DG_DATA/stdby/datafile/undotbs2.260.758993599 tag=TAG20110812T153017
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 12-AUG-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=6 STAMP=758993609 file name=+DG_DATA/stdby/datafile/system.257.758993419
datafile 2 switched to datafile copy
input datafile copy RECID=7 STAMP=758993609 file name=+DG_DATA/stdby/datafile/sysaux.258.758993507
datafile 3 switched to datafile copy
input datafile copy RECID=8 STAMP=758993609 file name=+DG_DATA/stdby/datafile/undotbs1.259.758993583
datafile 4 switched to datafile copy
input datafile copy RECID=9 STAMP=758993609 file name=+DG_DATA/stdby/datafile/undotbs2.260.758993599
Finished Duplicate Db at 12-AUG-11 Now enable the managed recovery on Standby host: SQL> recover managed standby database disconnect from session;
Media recovery complete. And we are done. Few checks done to check the setup: On Primary Site: SQL> -- edited later to get output from GV$ view SQL> select INST_ID,DEST_NAME,STATUS,RECOVERY_MODE,ARCHIVED_THREAD#,ARCHIVED_SEQ#,APPLIED_THREAD#,APPLIED_SEQ#,DB_UNIQUE_NAME
2 from GV$ARCHIVE_DEST_STATUS
3 where DEST_NAME in ('LOG_ARCHIVE_DEST_2'); INST_ID DEST_NAME STATUS RECOVERY ARCHIVED_THREAD# ARCHIVED_SEQ# APPLIED_THREAD# APPLIED_SEQ# DB_UNIQUE 1 LOG_ARCHIVE_DEST_2 VALID MANAGED 1 25 2 24 STDBY
2 LOG_ARCHIVE_DEST_2 VALID MANAGED 1 25 2 24 STDBY On the Standby Site: SQL> select thread#,sequence#,applied from v$archived_log order by sequence#; THREAD# SEQUENCE# APPLIED2 7 YES 2 8 YES 2 9 YES 2 10 YES 2 11 YES 2 12 YES 2 13 YES 2 14 YES 2 15 YES 2 16 YES 1 20 YES 1 21 YES 1 22 YES 1 23 YES 1 24 YES…
SQL> -- Output cut short for readability.
