Oracle 11g new feature of
active database duplication could also be used for creating standby databases in data guard configurations. This blog uses the
same RAC configuration used for active database duplication to create a single instance physical standby database.
RAC instances are
olsnodes -n
rac4 1
rac5 2
Data files and logfiles are in two ASM diskgroups called +DATA and +FLASH.
Physical standby database will have it's files in the local file system and will be referred to as stdby through out the blog.
1. Instance Oracle Enterprise Edition software on the host where physical standby will reside. In addition to this also create the necessary directory structures such as adump ($ORACLE_BASE/admin/
sid name/adump) and directories for controlfiles, datafiles and onlinelogs, though the configuration uses OMF once the setup is completed, these directories are required in the beginning to complete the setup. (not required if instead of OMF, some other directory path is referenced). For this configuration following directories were created
cd /data/oradata
mkdir STDBY
cd STDBY
mkdir controlfile datafile onlinelog
cd cd /data/flash_recovery
mkdir STDBY
cd STDBY
mkdir onlinelog
2. Create TNS entries on both RAC node's tnsnames.ora file
STDBYTNS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby-host)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = stdby)
)
)
3. Create TNS entry on the standby's tnsnames.ora file
PRIMARYTNS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-host)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = rac11g2)
)
)
In this case only one instance will be used to fetch archive log gaps and to send redo when the switch over happens. In a RAC standby multiple instances can receive but there can only be one applier.
4. Add a static listener entry to listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = stdby)
(SID_NAME = stdby)
(ORACLE_HOME = /opt/app/oracle/product/11.2.0/ent)
)
)
5. Enable force logging on the primary
SQL> alter database force logging;
6. Create standby log files for each thread on primary. These should be same size as the online redo log files
SQL> alter database add standby logfile thread 1 size 52428800;
or
SQL> alter database add standby logfile thread 1;
SQL> alter database add standby logfile thread 2;
There should be at least one more redo log group per thread than the online redo logs.
6. Add Data Guard related initialization parameters to primary. These include, information about the instances involved in the data guard configuration, redo transport parameters such SYNC,ASYNC, AFFIRM, NOAFFIRM, fetch archive log client and server values and datafile/logfile name conversions.
alter system set log_archive_config='dg_config=(rac11g2,stdby)' scope=both ;
alter system set log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=rac11g2' scope=both;
alter system set log_archive_dest_2='service=STDBYTNS LGWR ASYNC NOAFFIRM max_failure=10 max_connections=5 reopen=180 valid_for=(online_logfiles,primary_role) db_unique_name=stdby' scope=both;
alter system set log_archive_dest_state_1='enable' scope=both;
alter system set log_archive_dest_state_2='enable' scope=both;
alter system set fal_server='STDBYTNS' scope=both;
alter system set fal_client='PRIMARYTNS' scope=both;
alter system set log_archive_max_processes=10 scope=both;
alter system set db_file_name_convert='/data/oradata/STDBY','+DATA/rac11g2' scope=spfile;
alter system set log_file_name_convert='/data/flash_recovery/STDBY','+FLASH/rac11g2' scope=spfile;
alter system set standby_file_management='AUTO' scope=both;
7. Copy the password file to standby host's ORACLE_HOME/dbs and rename the file. Assuming password file is copied to the standby host
mv orapwrac11g22 orapwstdby
8. create a pfile with db_name as the only entry.
*.db_name='stdby'
9. Start the standby instance using the above mentioned pfile in nomount mode
startup nomount pfile=initstdby.ora
10. On the primary using rman connect to primary db as target and standby as the auxiliary and run the active duplication command to create the standby. Some of the RAC only parameters has been reset while others have been set to reflect the standby database after switch over.
rman target / auxiliary sys/rac11g2db@stdbytns
duplicate target database for standby from active database
spfile
parameter_value_convert 'rac11g2','stdby','RAC11G2','stdby'
set db_unique_name='stdby'
set db_file_name_convert='+DATA/rac11g2','/data/oradata/STDBY','+DATA/rac11g2/tempfile','/data/oradata/STDBY'
set log_file_name_convert='+FLASH/rac11g2','/data/flash_recovery/STDBY','+DATA/rac11g2','/data/flash_recovery/STDBY'
set control_files='/data/oradata/stdby/controlfile/control01.ctl'
set log_archive_max_processes='5'
set fal_client='STDBYTNS'
set fal_server='PRIMARYTNS'
SET cluster_database='false'
reset REMOTE_LISTENER
reset local_listener
set db_create_file_dest = '/data/oradata'
set db_recovery_file_dest = '/data/flash_recovery'
set standby_file_management='AUTO'
set log_archive_config='dg_config=(rac11g2,stdby)'
set log_archive_dest_2='service=PRIMARYTNS LGWR ASYNC NOAFFIRM max_failure=10 max_connections=5 reopen=180 valid_for=(online_logfiles,primary_role) db_unique_name=rac11g2'
set log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=stdby';
This will start the duplication and creation of the physical standby
Starting Duplicate Db at 30-NOV-10
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=10 device type=DISK
contents of Memory Script:
{
backup as copy reuse
targetfile '/opt/app/oracle/product/11.2.0/db_1/dbs/orapwrac11g22' auxiliary format
'/opt/app/oracle/product/11.2.0/ent/dbs/orapwstdby' ;
}
executing Memory Script
...
...
...
Finished Duplicate Db at 30-NOV-10If any parameter setting has some configuration mismatches and still referrers to ASM for files then the duplication process will terminate with
ERROR: slave communication error with ASM; terminating process 21416
Errors in file /opt/app/oracle/diag/rdbms/stdby/stdby/trace/stdby_lgwr_21416.trc:
Mon Nov 29 17:40:49 2010
PMON (ospid: 21396): terminating the instance due to error 470
Instance terminated by PMON, pid = 21396
11. Once successfully completed start redo apply on the standby with
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT
12. Force a log switch on the primary
alter system switch logfile;
or
alter database archive log current;
13. Check if the logs are applied on the standby with
select thread#,sequence#,applied from v$archived_log;
THREAD# SEQUENCE# APPLIED
---------- ---------- ---------
1 108 YES
1 109 YES
1 110 YES
1 111 YES
2 98 YES
2 99 YES
2 100 YES
1 112 IN-MEMORY
To further checks could be performed by creating a tablespace on primary and checking whether the changes get reflected appropriately only the standby.
The spfile created in this scenario will have the RAC instance level values similar to
active duplication.
Alternately a pfile for the standby could be created with all the necessary parameter entries and use that to start the duplicate in nomount state. Then when running the duplicate command and omit the spfile clause.
In this scenario pfile created on step 8 would be
*.audit_file_dest='/opt/app/oracle/admin/stdby/adump'
*.audit_trail='OS'
*.compatible='11.2.0.0.0'
*.control_files='/data/oradata/STDBY/controlfile/control01.ctl'
*.db_block_size=8192
*.db_create_file_dest='/data/oradata'
*.db_domain='domain.net'
*.db_name='rac11g2'
*.db_unique_name='stdby'
*.db_file_name_convert='+DATA/rac11g2','/data/oradata/STDBY','+DATA/rac11g2/tempfile','/data/oradata/STDBY'
*.log_file_name_convert='+FLASH/rac11g2','/data/flash_recovery/STDBY','+DATA/rac11g2','/data/flash_recovery/STDBY'
*.db_recovery_file_dest='/data/flash_recovery'
*.db_recovery_file_dest_size=40705720320
*.diagnostic_dest='/opt/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=stdbyXDB)'
*.log_archive_format='%t_%s_%r.dbf'
*.log_archive_config='dg_config=(rac11g2,stdby)'
*.log_archive_dest_2='service=PRIMARYTNS LGWR ASYNC NOAFFIRM max_failure=10 max_connections=5 reopen=180 valid_for=(online_logfiles,primary_role) db_unique_name=rac11g2'
*.standby_file_management='AUTO'
*.log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=stdby'
*.open_cursors=300
*.pga_aggregate_target=1326448640
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=3707764736
*.undo_tablespace='UNDOTBS1'
*.fal_client='STDBYTNS'
*.fal_server='PRIMARYTNS'
Duplication command on step 10 would be
duplicate target database for standby from active database;
This will not create a spfile for the standby and at the end of the duplication command a spfile should be created explicitly. Complete output for this last scenario is given below
rman target / auxiliary sys/rac11g2db@stdbytns
Recovery Manager: Release 11.2.0.1.0 - Production on Tue Nov 30 13:02:16 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: RAC11G2 (DBID=371695083)
connected to auxiliary database: RAC11G2 (not mounted)
RMAN> duplicate target database for standby from active database;
Starting Duplicate Db at 30-NOV-10
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=10 device type=DISK
contents of Memory Script:
{
backup as copy reuse
targetfile '/opt/app/oracle/product/11.2.0/db_1/dbs/orapwrac11g22' auxiliary format
'/opt/app/oracle/product/11.2.0/ent/dbs/orapwstdby' ;
}
executing Memory Script
Starting backup at 30-NOV-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=152 instance=rac11g22 device type=DISK
Finished backup at 30-NOV-10
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/data/oradata/stdby/control01.ctl';
}
executing Memory Script
Starting backup at 30-NOV-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/opt/app/oracle/product/11.2.0/db_1/dbs/snapcf_rac11g22.f tag=TAG20101130T130231 RECID=33 STAMP=736434151
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 30-NOV-10
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 tempfile 1 to
"/data/oradata/stdby/tempfile/temp.263.732796409";
switch clone tempfile all;
set newname for datafile 1 to
"/data/oradata/stdby/datafile/system.256.732796287";
set newname for datafile 2 to
"/data/oradata/stdby/datafile/sysaux.257.732796289";
set newname for datafile 3 to
"/data/oradata/stdby/datafile/undotbs1.258.732796289";
set newname for datafile 4 to
"/data/oradata/stdby/datafile/users.259.732796291";
set newname for datafile 5 to
"/data/oradata/stdby/datafile/undotbs2.264.732796603";
backup as copy reuse
datafile 1 auxiliary format
"/data/oradata/stdby/datafile/system.256.732796287" datafile
2 auxiliary format
"/data/oradata/stdby/datafile/sysaux.257.732796289" datafile
3 auxiliary format
"/data/oradata/stdby/datafile/undotbs1.258.732796289" datafile
4 auxiliary format
"/data/oradata/stdby/datafile/users.259.732796291" datafile
5 auxiliary format
"/data/oradata/stdby/datafile/undotbs2.264.732796603" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /data/oradata/stdby/tempfile/temp.263.732796409 in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 30-NOV-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+DATA/rac11g2/datafile/system.256.732796287
output file name=/data/oradata/stdby/datafile/system.256.732796287 tag=TAG20101130T130241
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:05
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=+DATA/rac11g2/datafile/sysaux.257.732796289
output file name=/data/oradata/stdby/datafile/sysaux.257.732796289 tag=TAG20101130T130241
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:55
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=+DATA/rac11g2/datafile/undotbs1.258.732796289
output file name=/data/oradata/stdby/datafile/undotbs1.258.732796289 tag=TAG20101130T130241
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=+DATA/rac11g2/datafile/undotbs2.264.732796603
output file name=/data/oradata/stdby/datafile/undotbs2.264.732796603 tag=TAG20101130T130241
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/rac11g2/datafile/users.259.732796291
output file name=/data/oradata/stdby/datafile/users.259.732796291 tag=TAG20101130T130241
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 30-NOV-10
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=33 STAMP=736434316 file name=/data/oradata/stdby/datafile/system.256.732796287
datafile 2 switched to datafile copy
input datafile copy RECID=34 STAMP=736434316 file name=/data/oradata/stdby/datafile/sysaux.257.732796289
datafile 3 switched to datafile copy
input datafile copy RECID=35 STAMP=736434316 file name=/data/oradata/stdby/datafile/undotbs1.258.732796289
datafile 4 switched to datafile copy
input datafile copy RECID=36 STAMP=736434316 file name=/data/oradata/stdby/datafile/users.259.732796291
datafile 5 switched to datafile copy
input datafile copy RECID=37 STAMP=736434316 file name=/data/oradata/stdby/datafile/undotbs2.264.732796603
Finished Duplicate Db at 30-NOV-10
Not related to the title but for single-single physical standby through active database duplication, assuming primary is ent11g2, on primary
alter system set log_archive_config='dg_config=(ent11g2,stdby)' scope=both;
alter system set log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=ent11g2' scope=both;
alter system set log_archive_dest_2='service=STDBYTNS LGWR ASYNC NOAFFIRM max_failure=10 max_connections=5 reopen=180 valid_for=(online_logfiles,primary_role) db_unique_name=stdby' scope=both;
alter system set log_archive_dest_state_1='enable' scope=both;
alter system set log_archive_dest_state_2='enable' scope=both;
alter system set fal_server='STDBYTNS' scope=both;
alter system set fal_client='PRIMARYTNS' scope=both;
alter system set log_archive_max_processes=10 scope=both;
alter system set db_file_name_convert='/data/oradata/stdby/','/data/oradata/ENT11G2' scope=spfile;
alter system set log_file_name_convert='/data/flash_recovery/STDBY','/data/flash_recovery/ENT11G2' scope=spfile;
alter system set standby_file_management='AUTO' scope=both;
RMAN command for duplication for standby
duplicate target database for standby from active database
spfile
parameter_value_convert 'ent11g2','stdby','ENT11G2','stdby'
set db_unique_name='stdby'
set db_file_name_convert='/ENT11G2','/stdby'
set log_file_name_convert='/ENT11G2','/stdby'
set control_files='/data/oradata/stdby/controlfile/control01.ctl'
set log_archive_max_processes='5'
set fal_client='STDBYTNS'
set fal_server='PRIMARYTNS'
set standby_file_management='AUTO'
set log_archive_config='dg_config=(ent11g2,stdby)'
set log_archive_dest_2='service=PRIMARYTNS LGWR ASYNC NOAFFIRM max_failure=10 max_connections=5 reopen=180 valid_for=(online_logfiles,primary_role) db_unique_name=ent11g2';
