Create Standby Database DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE RMAN command.

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

In this article we will see RMAN duplicate target database for standby using active database command to prepare auxiliary database.

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

Database  Detail :

Step 1 : Connect to RMAN using netservice we have created at primary side :

[oracle@localhost ~]$ rman target sys/oracle@mgr auxiliary sys/oracle@std_mgr

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Apr 17 17:43:32 2018

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

connected to target database: MGR (DBID=1905869882)
connected to auxiliary database: MGR (not mounted)

RMAN>

Step 2 : Prepare a script to perform RMAN duplicate command :

run
{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
allocate auxiliary channel aux type disk;
duplicate target database for standby from active database nofilenamecheck spfile
set log_archive_max_processes='8'
set db_unique_name='std_mgr'
set standby_file_management='AUTO'
set log_archive_config='dg_config=(mgr,std_mgr)'
set log_archive_dest_1='LOCATION=/u01/arc/mgr VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES)'
set log_archive_dest_4='LOCATION=/u01/arc/mgr/stdby/ VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLES) 
DB_UNIQUE_NAME=std_mgr'
set DG_BROKER_START='TRUE'
set fal_client='std_mgr'
set fal_server='mgr'
set log_Archive_dest_2='service=mgr lgwr sync affirm  valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) 
db_unique_name=mgr';
}

We can see in above script, we have set all parameters which will be needed for Physical Standby database.

Step 3 : run above script to create standby database .

RMAN> run
2> {
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
allocate auxiliary channel aux type disk;
duplicate target database for standby from active database nofilenamecheck spfile
set log_archive_max_processes='8'
set db_unique_name='std_mgr'
set standby_file_management='AUTO'
set log_archive_config='dg_config=(mgr,std_mgr)'
set log_archive_dest_1='LOCATION=/u01/arc/mgr VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES)'
set log_archive_dest_4='LOCATION=/u01/arc/mgr/stdby/ VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLES) 
DB_UNIQUE_NAME=std_mgr'
set DG_BROKER_START='TRUE'
set fal_client='std_mgr'
set fal_server='mgr'
set log_Archive_dest_2='service=mgr lgwr sync affirm  valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) 
db_unique_name=mgr';
}3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17> 18> 19> 

using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=103 device type=DISK

allocated channel: c2
channel c2: SID=89 device type=DISK

allocated channel: c3
channel c3: SID=95 device type=DISK

allocated channel: c4
channel c4: SID=97 device type=DISK

allocated channel: aux
channel aux: SID=20 device type=DISK

Starting Duplicate Db at 17-APR-18

contents of Memory Script:
{
   backup as copy reuse
   targetfile  '/u01/oracle/product/11.2.0/db_1/dbs/orapwmgr' auxiliary format 
 '/u01/oracle/product/11.2.0/db_1/dbs/orapwmgr'   targetfile 
 '/u01/oracle/product/11.2.0/db_1/dbs/spfilemgr.ora' auxiliary format 
 '/u01/oracle/product/11.2.0/db_1/dbs/spfilemgr.ora'   ;
   sql clone "alter system set spfile= ''/u01/oracle/product/11.2.0/db_1/dbs/spfilemgr.ora''";
}
executing Memory Script

Starting backup at 17-APR-18
Finished backup at 17-APR-18

sql statement: alter system set spfile= ''/u01/oracle/product/11.2.0/db_1/dbs/spfilemgr.ora''

contents of Memory Script:
{
   sql clone "alter system set  log_archive_max_processes = 
 8 comment=
 '''' scope=spfile";
   sql clone "alter system set  db_unique_name = 
 ''std_mgr'' 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=(mgr,std_mgr)'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_archive_dest_1 = 
 ''LOCATION=/u01/arc/mgr VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES)'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_archive_dest_4 = 
 ''LOCATION=/u01/arc/mgr/stdby/ VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLES) DB_UNIQUE_NAME=std_mgr'' comment=
 '''' scope=spfile";
   sql clone "alter system set  DG_BROKER_START = 
 TRUE comment=
 '''' scope=spfile";
   sql clone "alter system set  fal_client = 
 ''std_mgr'' comment=
 '''' scope=spfile";
   sql clone "alter system set  fal_server = 
 ''mgr'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_Archive_dest_2 = 
 ''service=mgr lgwr sync affirm  valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=mgr'' comment=
 '''' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

sql statement: alter system set  log_archive_max_processes =  8 comment= '''' scope=spfile

sql statement: alter system set  db_unique_name =  ''std_mgr'' 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=(mgr,std_mgr)'' comment= '''' 
scope=spfile

sql statement: alter system set  log_archive_dest_1 =  ''LOCATION=/u01/arc/mgr VALID_FOR=(ONLINE_LOGFILES,
ALL_ROLES)'' comment= '''' scope=spfile

sql statement: alter system set  log_archive_dest_4 =  ''LOCATION=/u01/arc/mgr/stdby/ VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLES) DB_UNIQUE_NAME=std_mgr'' comment= '''' scope=spfile

sql statement: alter system set  DG_BROKER_START =  TRUE comment= '''' scope=spfile

sql statement: alter system set  fal_client =  ''std_mgr'' comment= '''' scope=spfile

sql statement: alter system set  fal_server =  ''mgr'' comment= '''' scope=spfile

sql statement: alter system set  log_Archive_dest_2 =  ''service=mgr lgwr sync affirm  valid_for=
(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=mgr'' comment= '''' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     392495104 bytes

Fixed Size                     2253584 bytes
Variable Size                176164080 bytes
Database Buffers             209715200 bytes
Redo Buffers                   4362240 bytes
allocated channel: aux
channel aux: SID=19 device type=DISK

contents of Memory Script:
{
   backup as copy current controlfile for standby auxiliary format  '/u01/oracle/oradata/mgr/control01.ctl';
   restore clone controlfile to  '/u01/oracle/oradata/mgr/control02.ctl' from 
 '/u01/oracle/oradata/mgr/control01.ctl';
}
executing Memory Script

Starting backup at 17-APR-18
channel c1: starting datafile copy
copying standby control file
output file name=/u01/oracle/product/11.2.0/db_1/dbs/snapcf_mgr.f tag=TAG20180417T174406 RECID=2 STAMP=973705448
channel c1: datafile copy complete, elapsed time: 00:00:04
Finished backup at 17-APR-18

Starting restore at 17-APR-18

channel aux: copied control file copy
Finished restore at 17-APR-18

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 
 "/u01/oracle/oradata/mgr/temp01.dbf";
   switch clone tempfile all;
   set newname for datafile  1 to 
 "/u01/oracle/oradata/mgr/system01.dbf";
   set newname for datafile  2 to 
 "/u01/oracle/oradata/mgr/sysaux01.dbf";
   set newname for datafile  3 to 
 "/u01/oracle/oradata/mgr/undotbs01.dbf";
   set newname for datafile  4 to 
 "/u01/oracle/oradata/mgr/users01.dbf";
   backup as copy reuse
   datafile  1 auxiliary format 
 "/u01/oracle/oradata/mgr/system01.dbf"   datafile 
 2 auxiliary format 
 "/u01/oracle/oradata/mgr/sysaux01.dbf"   datafile 
 3 auxiliary format 
 "/u01/oracle/oradata/mgr/undotbs01.dbf"   datafile 
 4 auxiliary format 
 "/u01/oracle/oradata/mgr/users01.dbf"   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

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

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 17-APR-18
channel c1: starting datafile copy
input datafile file number=00001 name=/u01/oracle/oradata/mgr/system01.dbf
channel c2: starting datafile copy
input datafile file number=00002 name=/u01/oracle/oradata/mgr/sysaux01.dbf
channel c3: starting datafile copy
input datafile file number=00003 name=/u01/oracle/oradata/mgr/undotbs01.dbf
channel c4: starting datafile copy
input datafile file number=00004 name=/u01/oracle/oradata/mgr/users01.dbf
output file name=/u01/oracle/oradata/mgr/users01.dbf tag=TAG20180417T174420
channel c4: datafile copy complete, elapsed time: 00:00:03
output file name=/u01/oracle/oradata/mgr/undotbs01.dbf tag=TAG20180417T174420
channel c3: datafile copy complete, elapsed time: 00:00:07
output file name=/u01/oracle/oradata/mgr/sysaux01.dbf tag=TAG20180417T174420
channel c2: datafile copy complete, elapsed time: 00:01:15
output file name=/u01/oracle/oradata/mgr/system01.dbf tag=TAG20180417T174420
channel c1: datafile copy complete, elapsed time: 00:01:35
Finished backup at 17-APR-18

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=2 STAMP=973705557 file name=/u01/oracle/oradata/mgr/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=3 STAMP=973705557 file name=/u01/oracle/oradata/mgr/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=4 STAMP=973705557 file name=/u01/oracle/oradata/mgr/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=5 STAMP=973705557 file name=/u01/oracle/oradata/mgr/users01.dbf
Finished Duplicate Db at 17-APR-18
released channel: c1
released channel: c2
released channel: c3
released channel: c4
released channel: aux

RMAN>

Now check physical standby database :

Step 4 : Check database role in Primary :

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

NAME	  OPEN_MODE	       DATABASE_ROLE
--------- -------------------- ----------------
MGR	  READ WRITE	       PRIMARY

SQL>

Step 5 : Check database role at standby :

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

NAME	  OPEN_MODE	       DATABASE_ROLE
--------- -------------------- ----------------
MGR	  MOUNTED	       PHYSICAL STANDBY

SQL>

Step 6 : Check max archive log sequence at primary :

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

MAX(SEQUENCE#)
--------------
	    35

SQL>

Step 7 : Check max archive log sequence at standby :

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

MAX(SEQUENCE#)
--------------
	    35

SQL>

Step 8 : Switch archive log at Primary database :

SQL> alter system switch logfile;

System altered.

Step 9 : Again check max archive log sequence at Standby database:

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

MAX(SEQUENCE#)
--------------
	    36

SQL>

We can see here archive log is transferred to standby database.

In next article, we will create standby with RMAN Restore

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.