Create Standby Database using duplicate database for standby RMAN command

We have seen preparing Primary Database for Dataguard and creating Oracle network service on both sides.

In this article, we will see Physical Standby creation using RMAN duplicate database for standby command.

Note: For this process, we need to copy password file from primary side to standby side under $ORACLE_HOME/dbs

Database Detail :

Connect to Primary Database :

Step 1: Take backup :

Connect to RMAN and take full database backup using rman backup database command.

[oracle@test1 oradata]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Wed Apr 18 17:54:27 2018

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: TESTDB (DBID=2756866105)

RMAN> backup database plus archivelog;

Starting backup at 18-APR-18
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=2 RECID=1 STAMP=973538201
input archived log thread=1 sequence=3 RECID=2 STAMP=973791335
input archived log thread=1 sequence=4 RECID=3 STAMP=973791681
input archived log thread=1 sequence=5 RECID=4 STAMP=973791684
input archived log thread=1 sequence=6 RECID=5 STAMP=973792480
channel ORA_DISK_1: starting piece 1 at 18-APR-18
channel ORA_DISK_1: finished piece 1 at 18-APR-18
piece handle=/u01/oracle/fast_recovery_area/TESTDB/backupset/2018_04_18/o1_mf_annnn_TAG20180418T175441_ffgg8b13_.bkp tag=TAG20180418T175441 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:08
Finished backup at 18-APR-18

Starting backup at 18-APR-18
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/oracle/oradata/testdb/system01.dbf
input datafile file number=00002 name=/u01/oracle/oradata/testdb/sysaux01.dbf
input datafile file number=00003 name=/u01/oracle/oradata/testdb/undotbs01.dbf
input datafile file number=00004 name=/u01/oracle/oradata/testdb/users01.dbf
channel ORA_DISK_1: starting piece 1 at 18-APR-18
channel ORA_DISK_1: finished piece 1 at 18-APR-18
piece handle=/u01/oracle/fast_recovery_area/TESTDB/backupset/2018_04_18/o1_mf_nnndf_TAG20180418T175449_ffgg8kob_.bkp tag=TAG20180418T175449 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:05
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 18-APR-18
channel ORA_DISK_1: finished piece 1 at 18-APR-18
piece handle=/u01/oracle/fast_recovery_area/TESTDB/backupset/2018_04_18/o1_mf_ncsnf_TAG20180418T175449_ffggbswc_.bkp tag=TAG20180418T175449 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 18-APR-18

Starting backup at 18-APR-18
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=7 RECID=6 STAMP=973792563
channel ORA_DISK_1: starting piece 1 at 18-APR-18
channel ORA_DISK_1: finished piece 1 at 18-APR-18
piece handle=/u01/oracle/fast_recovery_area/TESTDB/backupset/2018_04_18/o1_mf_annnn_TAG20180418T175603_ffggbvmn_.bkp tag=TAG20180418T175603 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 18-APR-18

RMAN>

Step 2: Transfer backup :

Connect to standby database and transfer backup from Primary to Standby server.

[oracle@localhost testdb]$ rsync -azvrh --progress 192.168.1.16:/u01/oracle/fast_recovery_area/TESTDB/ /u01/oracle/fast_recovery_area/TESTDB/
oracle@192.168.1.16's password: 
receiving incremental file list
./
backupset/
backupset/2018_04_18/
backupset/2018_04_18/o1_mf_annnn_TAG20180418T175441_ffgg8b13_.bkp
      90.85M 100%    9.48MB/s    0:00:09 (xfer#1, to-check=3/8)
backupset/2018_04_18/o1_mf_annnn_TAG20180418T175603_ffggbvmn_.bkp
     494.59K 100%    2.09MB/s    0:00:00 (xfer#2, to-check=2/8)
backupset/2018_04_18/o1_mf_ncsnf_TAG20180418T175449_ffggbswc_.bkp
       9.83M 100%   18.86MB/s    0:00:00 (xfer#3, to-check=1/8)
backupset/2018_04_18/o1_mf_nnndf_TAG20180418T175449_ffgg8kob_.bkp
       1.06G 100%    8.36MB/s    0:02:01 (xfer#4, to-check=0/8)
onlinelog/

sent 102 bytes  received 275.61M bytes  1.78M bytes/sec
total size is 1.17G  speedup is 4.23

Step 3 : Transfer pfile :

[oracle@localhost testdb]$ scp 192.168.1.16:$ORACLE_HOME/dbs/inittestdb.ora $ORACLE_HOME/dbs/
oracle@192.168.1.16's password: 
inittestdb.ora                                             100% 1291     1.3KB/s   00:00

Step 4 : Transfer password file.

[oracle@localhost testdb]$ scp 192.168.1.16:$ORACLE_HOME/dbs/orapwtestdb $ORACLE_HOME/dbs/
oracle@192.168.1.16's password: 
orapwtestdb                                                100% 1536     1.5KB/s   00:00

Step 5: Start database in nomount stage.

[oracle@localhost testdb]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Apr 19 12:43:32 2018

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area  663908352 bytes
Fixed Size		    2256192 bytes
Variable Size		  461374144 bytes
Database Buffers	  197132288 bytes
Redo Buffers		    3145728 bytes

Step 6: Connect to RMAN with a target and auxiliary database :

[oracle@localhost testdb]$ rman target sys/oracle@testdb auxiliary /

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Apr 19 12:43:43 2018

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: TESTDB (DBID=2756866105)
connected to auxiliary database: TESTDB (not mounted)

RMAN> duplicate target database for standby nofilenamecheck;

Starting Duplicate Db at 19-APR-18
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=1 device type=DISK

contents of Memory Script:
{
   restore clone standby controlfile;
}
executing Memory Script

Starting restore at 19-APR-18
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: copied control file copy
input file name=/u01/std_testdb.ctl
output file name=/u01/oracle/oradata/testdb/control01.ctl
output file name=/u01/oracle/oradata/testdb/control02.ctl
Finished restore at 19-APR-18

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 
 "/u01/oracle/oradata/testdb/temp01.dbf";
   switch clone tempfile all;
   set newname for datafile  1 to 
 "/u01/oracle/oradata/testdb/system01.dbf";
   set newname for datafile  2 to 
 "/u01/oracle/oradata/testdb/sysaux01.dbf";
   set newname for datafile  3 to 
 "/u01/oracle/oradata/testdb/undotbs01.dbf";
   set newname for datafile  4 to 
 "/u01/oracle/oradata/testdb/users01.dbf";
   restore
   clone database
   ;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/oracle/oradata/testdb/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 19-APR-18
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/oracle/oradata/testdb/system01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /u01/oracle/oradata/testdb/sysaux01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/oracle/oradata/testdb/undotbs01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/oracle/oradata/testdb/users01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/oracle/fast_recovery_area/TESTDB/backupset/2018_04_18/o1_mf_nnndf_TAG20180418T175449_ffgg8kob_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/oracle/fast_recovery_area/TESTDB/backupset/2018_04_18/o1_mf_nnndf_TAG20180418T175449_ffgg8kob_.bkp tag=TAG20180418T175449
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:55
Finished restore at 19-APR-18

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=973860316 file name=/u01/oracle/oradata/testdb/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=973860317 file name=/u01/oracle/oradata/testdb/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=973860317 file name=/u01/oracle/oradata/testdb/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=973860317 file name=/u01/oracle/oradata/testdb/users01.dbf
Finished Duplicate Db at 19-APR-18

RMAN>

Check Physical Standby Database Synchronization :

Step 7: Check role of primary database :

SQL> select name,open_mode,database_role from v$database;

NAME	  OPEN_MODE	       DATABASE_ROLE
--------- -------------------- ----------------
TESTDB	  READ WRITE	       PRIMARY

SQL>

Step 8: Check role of Standby database :

SQL> select name,open_mode ,database_role from v$database;

NAME	  OPEN_MODE	       DATABASE_ROLE
--------- -------------------- ----------------
TESTDB	  MOUNTED	       PHYSICAL STANDBY

Step 9: Check max archive log sequence from the Primary database :

SQL> select max(sequence#) from v$thread;

MAX(SEQUENCE#)
--------------
	    93

Step 10: Check max archive log sequence from Standby database :

SQL> select max(sequence#) from v$thread;

MAX(SEQUENCE#)
--------------
	    93

Step 11: Switch logfile in Primary database :

SQL> alter system switch logfile;

System altered.

Step 12: Check sequence from Standby database :

SQL> select max(sequence#) from v$thread;

MAX(SEQUENCE#)
--------------
	    94

Stay tuned for More articles on Oracle DataGuard

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Telegram Channel: https://t.me/helporacle

Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/

Joel Perez’s LinkedIn: Joel Perez’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp

 

 

Tagged:

About The Author

I am 11g OCP and OCE(RAC and GRID Infrastructure). Currently working as Oracle Database Administrator at _VOIS and Blogger (Freelancer) at Oracle-help . Previously I worked with Acute Informatics pvt ltd. and Mobile Tornado where I handled Banking Data Migration , created DR using log shipping , Golden Gate and Data Guard. I have experience of handling planned and unplanned DB role switchover and failovers and monitoring and troubleshooting Database Performance. Key skills : RMAN , GG , DG , RAC , Virtualization in OVMM.I have experience in Banking and Telecom domain. Reach me at : https://www.linkedin.com/in/jagruti-jasleniya

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.