Primary : Server : prim-server DB Name : DC DB_UNIQUE_NAME : DC Standby : Server : stand-server DB Name : DC DB_UNIQUE_NAME : DR Note: 1. Your primary database must be started with spfile and standby database must be started with pfile. 2. Your both primary and standby pfile, spfile, and password file name should be same on both primary and standby database. 3. Set below environment on standby server before starting it in nomount state. export ORACLE_SID=DC export ORACLE_HOME= export PATH=$ORACLE_HOME/bin:$PATH I faced multiple issues while configuring network between primary and standby servers and hence you use this document as it is. For DB_NAME also, there can be a problem. Use either capital letters or small letters for db_name on both primary and standby server. Check the standby database log file for any error. |
Step 1 : Enable the archivelog mode. SQL> shut immediate; SQL> startup mount; SQL> alter database archivelog; |
Step 2 : Enable force logging. SQL> select force_logging from v$database; SQL> alter database force logging; SQL> select force_logging from v$database; |
Step 3 : Set below parameters in the primary database and restart the database to take effect. SQL> alter system set log_archive_config='dg_config=(DC,DR)' scope=spfile; SQL> alter system set log_archive_dest_1='LOCATION=/home/oracle/u01/ valid_for=(all_logfiles,all_roles) db_unique_name=DC' scope=spfile; SQL> alter system set log_archive_dest_state_1=enable scope=spfile; SQL> alter system set log_archive_dest_2='service=DR async valid_for=(online_logfile,primary_role) db_unique_name=DR' scope=spfile; SQL> alter system set log_archive_dest_state_2=enable scope=spfile; SQL> alter system set standby_file_management=auto scope=spfile; SQL> alter system set log_archive_format='%t_%s_%r.arc' scope=spfile; SQL> alter system set fal_client='DC' scope=spfile; SQL> alter system set fal_server='DR' scope=spfile; SQL> shut immediate; SQL> startup; Ensure the above values has been reflected in the database. |
Step 4 : add tns and listener entries on both primary and standby server. Also start listeners on both servers. Primary: oracle@dc admin]$ cat tnsnames.ora DC = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = prim-server)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DC) ) ) DR = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = stand-server)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DC) ) ) [oracle@dc admin]$ cat listener.ora SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = dr.oracle.com) (ORACLE_HOME = /home/oracle/u01/product/11.2.0/dbhome_1) (SID_NAME = DC) ) ) Standby: oracle@dc admin]$ cat tnsnames.ora DC = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = prim-server)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DC) ) ) DR = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = stand-server)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DC) ) ) [oracle@dr admin]$ cat listener.ora SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = DC) (ORACLE_HOME = /home/oracle/u01/product/11.2.0/dbhome_1) ) ) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.43.92)(PORT = 1521)) ) ) ADR_BASE_LISTENER = /home/oracle/u01 |
Step 5 : Copy password file from primary server to standby server and ensure that password should be same as primary. Step 6 : Add below two entries in standby server pfile and we will set rest of the parameters using duplicate command. [oracle@dr dbs]$ cat initDC.ora DB_NAME='DC' DB_UNIQUE_NAME='DR' |
Step 7 : Create below script for duplicate command. [oracle@dr dbs]$ cat stand.cmd run { allocate channel ch1 type disk; allocate channel ch2 type disk; allocate channel ch3 type disk; allocate auxiliary channel st1 type disk; duplicate target database for standby from active database dorecover spfile set db_unique_name='DR' set control_files='/home/oracle/u01/control01.ctl','/home/oracle/u01/control02.ctl' set fal_client='DC' set fal_server='DR' set standby_file_management='AUTO' set log_archive_config='dg_config=(DC,DR)' set log_archive_dest_1='LOCATION=/home/oracle/u01/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=DR' set log_archive_dest_2='service=DC ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=DC' nofilenamecheck; } |
Step 8 : Now start the standby database in nomount mode using newly created pfile. SQL> startup nomount; ORACLE instance started. Total System Global Area 217157632 bytes Fixed Size 2251816 bytes Variable Size 159384536 bytes Database Buffers 50331648 bytes Redo Buffers 5189632 bytes SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options |
Step 9 : Now start the RMAN prompt and first connect target database DC and then auxiliary database DR using below commands. [oracle@dr dbs]$ ls -ltr -rw-r--r--. 1 oracle oinstall 2851 May 15 2009 init.ora -rw-r-----. 1 oracle oinstall 24 Jun 21 17:17 lkDC -rw-r--r--. 1 oracle oinstall 29 Jun 22 16:13 initDC.ora -rw-r-----. 1 oracle oinstall 1536 Jun 22 16:23 orapwDC -rw-r-----. 1 oracle oinstall 24 Jun 22 16:23 lkDR -rw-r--r--. 1 oracle oinstall 676 Jun 22 16:25 stand.cmd -rw-rw----. 1 oracle oinstall 1544 Jun 22 16:26 hc_DC.dat [oracle@dr dbs]$ rman Recovery Manager: Release 11.2.0.4.0 - Production on Thu Jun 22 16:26:24 2017 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. RMAN> connect target sys/oracle@DC connected to target database: DC (DBID=1661358039) RMAN> connect auxiliary sys/oracle@DR connected to auxiliary database: DC (not mounted) RMAN> @stand.cmd RMAN> run { 2> allocate channel ch1 type disk; 3> allocate channel ch2 type disk; 4> allocate channel ch3 type disk; 5> allocate auxiliary channel st1 type disk; 6> duplicate target database 7> for standby 8> from active database 9> dorecover 10> spfile 11> set db_unique_name='DR' 12> set control_files='/home/oracle/u01/control01.ctl','/home/oracle/u01/control02.ctl' 13> set fal_client='DC' 14> set fal_server='DR' 15> set standby_file_management='AUTO' 16> set log_archive_config='dg_config=(DC,DR)' 17> set log_archive_dest_1='LOCATION=/home/oracle/u01/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=DR' 18> set log_archive_dest_2='service=DC ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=DC' 19> nofilenamecheck; 20> } using target database control file instead of recovery catalog allocated channel: ch1 channel ch1: SID=41 device type=DISK allocated channel: ch2 channel ch2: SID=1 device type=DISK allocated channel: ch3 channel ch3: SID=42 device type=DISK allocated channel: st1 channel st1: SID=19 device type=DISK Starting Duplicate Db at 22-JUN-17 contents of Memory Script: { backup as copy reuse targetfile '/home/oracle/u01/product/11.2.0/dbhome_1/dbs/orapwDC' auxiliary format '/home/oracle/u01/product/11.2.0/dbhome_1/dbs/orapwDC' targetfile '/home/oracle/u01/product/11.2.0/dbhome_1/dbs/spfileDC.ora' auxiliary format '/home/oracle/u01/product/11.2.0/dbhome_1/dbs/spfileDC.ora' ; sql clone "alter system set spfile= ''/home/oracle/u01/product/11.2.0/dbhome_1/dbs/spfileDC.ora''"; } executing Memory Script Starting backup at 22-JUN-17 Finished backup at 22-JUN-17 sql statement: alter system set spfile= ''/home/oracle/u01/product/11.2.0/dbhome_1/dbs/spfileDC.ora'' contents of Memory Script: { sql clone "alter system set db_unique_name = ''DR'' comment= '''' scope=spfile"; sql clone "alter system set control_files = ''/home/oracle/u01/control01.ctl'', ''/home/oracle/u01/control02.ctl'' comment= '''' scope=spfile"; sql clone "alter system set fal_client = ''DC'' comment= '''' scope=spfile"; sql clone "alter system set fal_server = ''DR'' 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=(DC,DR)'' comment= '''' scope=spfile"; sql clone "alter system set log_archive_dest_1 = ''LOCATION=/home/oracle/u01/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=DR'' comment= '''' scope=spfile"; sql clone "alter system set log_archive_dest_2 = ''service=DC ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=DC'' comment= '''' scope=spfile"; shutdown clone immediate; startup clone nomount; } executing Memory Script sql statement: alter system set db_unique_name = ''DR'' comment= '''' scope=spfile sql statement: alter system set control_files = ''/home/oracle/u01/control01.ctl'', ''/home/oracle/u01/control02.ctl'' comment= '''' scope=spfile sql statement: alter system set fal_client = ''DC'' comment= '''' scope=spfile sql statement: alter system set fal_server = ''DR'' 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=(DC,DR)'' comment= '''' scope=spfile sql statement: alter system set log_archive_dest_1 = ''LOCATION=/home/oracle/u01/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=DR'' comment= '''' scope=spfile sql statement: alter system set log_archive_dest_2 = ''service=DC ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=DC'' comment= '''' scope=spfile Oracle instance shut down connected to auxiliary database (not started) Oracle instance started Total System Global Area 784998400 bytes Fixed Size 2257352 bytes Variable Size 515903032 bytes Database Buffers 264241152 bytes Redo Buffers 2596864 bytes allocated channel: st1 channel stby: SID=18 device type=DISK contents of Memory Script: { backup as copy current controlfile for standby auxiliary format '/home/oracle/u01/control01.ctl'; restore clone controlfile to '/home/oracle/u01/control02.ctl' from '/home/oracle/u01/control01.ctl'; } executing Memory Script Starting backup at 22-JUN-17 channel ch1: starting datafile copy copying standby control file output file name=/home/oracle/u01/product/11.2.0/dbhome_1/dbs/snapcf_DC.f tag=TAG20170622T162656 RECID=2 STAMP=947348816 channel ch1: datafile copy complete, elapsed time: 00:00:01 Finished backup at 22-JUN-17 Starting restore at 22-JUN-17 channel st1: copied control file copy Finished restore at 22-JUN-17 contents of Memory Script: { sql clone 'alter database mount standby database'; } executing Memory Script sql statement: alter database mount standby database RMAN-05538: WARNING: implicitly using DB_FILE_NAME_CONVERT contents of Memory Script: { set newname for tempfile 1 to "/home/oracle/datafile/DC/temp01.dbf"; switch clone tempfile all; set newname for datafile 1 to "/home/oracle/datafile/DC/system01.dbf"; set newname for datafile 2 to "/home/oracle/datafile/DC/sysaux01.dbf"; set newname for datafile 3 to "/home/oracle/datafile/DC/undotbs01.dbf"; set newname for datafile 4 to "/home/oracle/datafile/DC/users01.dbf"; backup as copy reuse datafile 1 auxiliary format "/home/oracle/datafile/DC/system01.dbf" datafile 2 auxiliary format "/home/oracle/datafile/DC/sysaux01.dbf" datafile 3 auxiliary format "/home/oracle/datafile/DC/undotbs01.dbf" datafile 4 auxiliary format "/home/oracle/datafile/DC/users01.dbf" ; sql 'alter system archive log current'; } executing Memory Script executing command: SET NEWNAME renamed tempfile 1 to /home/oracle/datafile/DC/temp01.dbf in control file executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME Starting backup at 22-JUN-17 channel ch1: starting datafile copy input datafile file number=00001 name=/home/oracle/datafile/DC/system01.dbf channel ch2: starting datafile copy input datafile file number=00002 name=/home/oracle/datafile/DC/sysaux01.dbf channel ch3: starting datafile copy input datafile file number=00003 name=/home/oracle/datafile/DC/undotbs01.dbf output file name=/home/oracle/datafile/DC/undotbs01.dbf tag=TAG20170622T162704 channel ch3: datafile copy complete, elapsed time: 00:00:03 channel ch3: starting datafile copy input datafile file number=00004 name=/home/oracle/datafile/DC/users01.dbf output file name=/home/oracle/datafile/DC/users01.dbf tag=TAG20170622T162704 channel ch3: datafile copy complete, elapsed time: 00:00:01 output file name=/home/oracle/datafile/DC/sysaux01.dbf tag=TAG20170622T162704 channel ch2: datafile copy complete, elapsed time: 00:00:19 output file name=/home/oracle/datafile/DC/system01.dbf tag=TAG20170622T162704 channel ch1: datafile copy complete, elapsed time: 00:00:29 Finished backup at 22-JUN-17 sql statement: alter system archive log current contents of Memory Script: { backup as copy reuse archivelog like "/home/oracle/u01/1_14_947265431.arc" auxiliary format "/home/oracle/u01/1_14_947265431.arc" ; catalog clone archivelog "/home/oracle/u01/1_14_947265431.arc"; switch clone datafile all; } executing Memory Script Starting backup at 22-JUN-17 channel ch1: starting archived log copy input archived log thread=1 sequence=14 RECID=13 STAMP=947348853 output file name=/home/oracle/u01/1_14_947265431.arc RECID=0 STAMP=0 channel ch1: archived log copy complete, elapsed time: 00:00:01 Finished backup at 22-JUN-17 cataloged archived log archived log file name=/home/oracle/u01/1_14_947265431.arc RECID=1 STAMP=947348854 datafile 1 switched to datafile copy input datafile copy RECID=2 STAMP=947348854 file name=/home/oracle/datafile/DC/system01.dbf datafile 2 switched to datafile copy input datafile copy RECID=3 STAMP=947348854 file name=/home/oracle/datafile/DC/sysaux01.dbf datafile 3 switched to datafile copy input datafile copy RECID=4 STAMP=947348854 file name=/home/oracle/datafile/DC/undotbs01.dbf datafile 4 switched to datafile copy input datafile copy RECID=5 STAMP=947348854 file name=/home/oracle/datafile/DC/users01.dbf contents of Memory Script: { set until scn 976400; recover standby clone database delete archivelog ; } executing Memory Script executing command: SET until clause Starting recover at 22-JUN-17 starting media recovery archived log for thread 1 with sequence 14 is already on disk as file /home/oracle/u01/1_14_947265431.arc archived log file name=/home/oracle/u01/1_14_947265431.arc thread=1 sequence=14 media recovery complete, elapsed time: 00:00:00 Finished recover at 22-JUN-17 Finished Duplicate Db at 22-JUN-17 released channel: ch1 released channel: ch2 released channel: ch3 released channel: st1 RMAN> RMAN> **end-of-file** RMAN> |
Step 10 : Start MRP process on standby server. SQL> alter database recover managed standby database disconnect from session; Database altered. Step 11 : Switch 2-3 logs on primary server and check standby database alert log file to see if logs are reflecting on standby. SQL> alter system switch logfile; SQL> alter system switch logfile; SQL> alter system switch logfile; |
Step 12 : DC-DR sync check Query. On Primary: SQL> select name,db_unique_name,database_role,open_mode,controlfile_type from v$database; SQL> select max(sequence#) from v$archived_log; On standby: SQL> select name,db_unique_name,database_role,open_mode,controlfile_type from v$database; SQL> select max(sequence#) from v$archived_log where applied='YES'; |
Thanks for reading this post ! Please comment if you like this post ! Click on FOLLOW to get future blog updates !
***************** END ****************
Great post Rupesh, It's a really nice work done by you, now no need to go through any other standby creation docs over the Internet. Thanks for sharing..
ReplyDelete