Steps to Create Active Physical Standby Dataguard Using RMAN Duplicate in Oracle

March 23, 2021
()

Steps to Create Active Physical Standby Dataguard Using RMAN Duplicate in Oracle

In the previous article, we have configured a Physical STANDBYdatabase where we started MRP in mount state. In this article, we are going to demonstrate Steps to Create Active Physical Standby Dataguard Using RMAN Active Duplicate in Oracle database. Active dataguard features introduced in 11g. Using active dataguard we can reduce the contention on the PRIMARY database.


Below are key features of Physical Standby Active Dataguard

 

  • Real-time query which enables to open the standby database in READ-ONLY mode.
  • Enable Block Change Tracking on the standby database.
  • Enable automatic block media recovery.
  • Schedule RMAN backup on standby.
  • Offload contention on the primary database.
  • Many more features depend on database versions like Far Sync, Real-Time cascade, etc.

Below are important details of the PRIMARY & STANDBY database which are going to be used in this demonstration.

DETAILSPRIMARY DATABASESTANDBY DATABASE
Server IP192.168.0.118192.168.0.119
Server NameDBsGuruN1.localdomainDBsGuruN2.localdomain
DB Unique Namesuncdbsuncdb_s
Control File/u01/app/oracle/oradata/suncdb, /u01/app/oracle/fast_recovery_area//u01/app/oracle/oradata/suncdb, /u01/app/oracle/fast_recovery_area/
Data File/u01/app/oracle/oradata/suncdb/u01/app/oracle/oradata/suncdb
Redo log File, Standby redo log file/u01/app/oracle/fast_recovery_area/, /u01/app/oracle/redolog/suncdb/ /u01/app/oracle/fast_recovery_area/, /u01/app/oracle/redolog/suncdb/
BackupNot RequireRMAN Active Duplicate

REMEMBER:-

  • Oracle version on Primary and secondary should be the same.
  • db_unique_name on standby and primary database should be different in order to used in DG_CONFIG setting of the LOG_ARCHIVE_CONFIG parameter.
  • PRIMARY and STANDBY (DR) server should ping each other by IP as well as its server name i.e resolved by both. In order to ping with its server name/IP, we need to add an entry in /etc/hosts file accordingly applicable to both sites.


PRIMARY DATABASE PARAMETERS:

*.db_file_name_convert=’/oracle/dbguru/’,’/oracle/dbguru/’,’/u02/dbguru/’,’/u02/dbguru/’ #optional in our setup

*.log_files_name_convert=’/oracle/dbguru/redo/’,’/oracle/dbguru/redo/’ #optional in our setup

*.db_name=’suncdb’
*.db_unique_name=’suncdb’

*.fal_client=’suncdb’
*.fal_server=’suncdb_s’

*.log_archive_config=’dg_config=(suncdb,suncdb_s)’

*.log_archive_dest_1=’LOCATION=/u01/app/oracle/oradata/suncdb/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=suncdb’
*.log_archive_dest_2=’SERVICE=suncdb_s LGWR ASYNC valid_for=(online_logfiles,primary_role) DB_UNIQUE_NAME=suncdb_s’
*.log_archive_dest_state_1=’ENABLE’
*.log_archive_dest_state_2=’ENABLE’

*.remote_login_passwordfile=’EXCLUSIVE’
*.standby_file_management=’AUTO’


STANDBY DATABASE PARAMETERS:

*.db_name=’suncdb’
*.db_unique_name=’suncdb_s’

*.fal_client=’suncdb_s’
*.fal_server=’suncdb’
*.log_archive_config=’dg_config=(suncdb,suncdb_s)’
*.log_archive_dest_1=’LOCATION=/u01/app/oracle/oradata/suncdb/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=suncdb_s’
*.log_archive_dest_2=’SERVICE=suncdb LGWR ASYNC valid_for=(online_logfiles,primary_role) DB_UNIQUE_NAME=suncdb’
*.log_archive_dest_state_1=’ENABLE’
*.log_archive_dest_state_2=’ENABLE’

*.remote_login_passwordfile=’EXCLUSIVE’
*.standby_file_management=’AUTO’


Overview of Required/Mandatory Parameters to set up PRIMARY & STANDBY DATABASE

  • DB_FILE_NAME_CONVERT, LOG_FILE_NAME_CONVERT: The purpose of the db_file_name_convert & log_file_name_convert parameters are for recovery purposes. It converts the filename of a new datafile, redo logfile on the primary database to a filename on the standby database.
  • FAL_CLIENT, FAL_SERVER: These parameters are init.ora parameters both are used for the gap resolution. When MRP finds that an archive log is missing during media recovery, it sends the fal_client information to the server identified by fal_server and requests fal_server to resend the file again.
  • LOG_ARCHIVE_CONFIG: It enables or disables the sending of redo logs to remote destinations and the receipt of remote redo logs, and specifies the unique db name (DB_UNIQUE_NAME) for each database in the Data Guard configuration.
  • LOG_ARCHIVE_DEST_n: Each LOG_ARCHIVE_DEST_n destination must contain either a LOCATION or SERVICE attribute to specify a local disk directory or a remotely accessed database, respectively.
  • REMOTE_LOGIN_PASSWORDFILE: It specifies whether Oracle checks for a password file and how many databases can use the password file.
  • STANDBY_FILE_MANAGEMENT: This initialization parameter plays an important role in the recovery process when the attributes of the data files are modified on the primary site. this parameter should be set to AUTO.


ON PRIMARY DATABASE/SERVER:

Click here to follow all steps to build a PRIMARY database of the section “ON PRIMARY DATABASE/SERVER” except Step 4, Step 7, and Step 8 (exclude RMAN backup piece and standby control file scp).

We are excluding Steps 4, 7 & 8 due to we will be using RMAN Active Duplicate on the STANDBY database so no need to backup the PRIMARY database along with the STANDBY control file.


ON STANDBY DATABASE/SERVER:

Step:1. Add entry in oratab and set an environment variable.

[oracle@DBsGuruN2 ~]$ vi /etc/oratab
suncdb_s:/u01/app/oracle/product/12.1.0/db_1:N
[oracle@DBsGuruN2 ~]$ . oraenv
ORACLE_SID = [labdb02] ? suncdb_s


Step 2. Edit listener.ora file and add TNS entries in tnsnames.ora file.

[oracle@DBsGuruN2 ~]$ cd $ORACLE_HOME/network/admin
[oracle@DBsGuruN2 admin]$ vi listener.ora
 
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dbsgurun2.localdomain)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
   
SID_LIST_LISTENER =
  (SID_LIST =
 (SID_DESC =
      (GLOBAL_DBNAME = suncdb_s)
      (ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1)
      (SID_NAME = suncdb_s)
    )
 )
 
[oracle@DBsGuruN2 admin]$ vi tnsname.ora 
SUNCDB_S =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.119)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = suncdb_s)
    )
  )
 
SUNCDB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.118)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = suncdb)
    )
  )

Step 3. Prepare pfile to start the STANDBY database.

Make a copy of pfile which has been generated from the PRIMARY database before modify.

[oracle@DBsGuruN2 backup]$ cd /u01/app/oracle/oradata/suncdb/backup/
[oracle@DBsGuruN2 backup]$ cp initsuncdb.ora initsuncdbstd.ora
[oracle@DBsGuruN2 backup]$ ls -lrt initsuncdbstd.ora
-rw-r--r-- 1 oracle oinstall 1261 Mar 15 11:34 initsuncdbstd.ora


Edit/append below parameters values in pfile initsuncdbstd.ora.

*.db_unique_name=’suncdb_s
*.log_archive_dest_1=’LOCATION=/u01/app/oracle/oradata/suncdb/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=suncdb_s’
*.log_archive_dest_2=’SERVICE=suncdb LGWR ASYNC valid_for=(online_logfiles,primary_role) DB_UNIQUE_NAME=suncdb’
*.fal_client=’suncdb_s’
*.fal_server=’suncdb’


Step 4, Create require directories to start a database.

[oracle@DBsGuruN2 backup]$ mkdir -p /u01/app/oracle/oradata/suncdb/adump
[oracle@DBsGuruN2 backup]$ mkdir -p /u01/app/oracle/fast_recovery_area
[oracle@DBsGuruN2 backup]$ mkdir -p /u01/app/oracle/oradata/suncdb/arch
[oracle@DBsGuruN2 backup]$ mkdir -p /u01/app/oracle/redolog/suncdb
[oracle@DBsGuruN2 backup]$ ls -ld /u01/app/oracle/fast_recovery_area/
drwxr-xr-x 5 oracle oinstall 4096 Mar 15  2021 /u01/app/oracle/fast_recovery_area/
[oracle@DBsGuruN2 backup]$ ls -ld /u01/app/oracle/oradata/suncdb/adump
drwxr-xr-x 2 oracle oinstall 114688 Mar 15 11:39 /u01/app/oracle/oradata/suncdb/adump/
[oracle@DBsGuruN2 backup]$ ls -ld /u01/app/oracle/oradata/suncdb/arch
drwxr-xr-x 2 oracle oinstall 12288 Mar 15 11:40 /u01/app/oracle/oradata/suncdb/arch/
[oracle@DBsGuruN2 backup]$ ls -ld /u01/app/oracle/redolog/suncdb
drwxr-xr-x 2 oracle oinstall 13137 Mar 15 11:41 /u01/app/oracle/redolog/suncdb/


Step 5. Start STANDBY database in mount state & create spfile from pfile.

[oracle@DBsGuruN2 backup]$ . oraenv
ORACLE_SID = [labdb02] ? suncdb_s
[oracle@DBsGuruN2 backup]$ sqlplus as sysdba
STANDBY> startup nomount pfile='/u01/app/oracle/oradata/suncdb/backup/initsuncdbstd.ora'
ORACLE instance started.
 
Total System Global Area 1073741824 bytes
Fixed Size                  2932632 bytes
Variable Size             713031784 bytes
Database Buffers          352321536 bytes
Redo Buffers                5455872 bytes
STANDBY> create spfile from pfile='/u01/app/oracle/oradata/suncdb/backup/initsuncdbstd.ora';
 
File created.


Step 6. Restore backup.
After successfully starting the STANDBY database in nomount state. Restore the backup available option RMAN Active Duplicate.

Connect to RMAN

[oracle@oDBsGuruN2 ~]$ rman target sys/DBsGuru123@suncdb AUXILIARY sys/DBsGuru123@suncdb_s

Recovery Manager: Release 12.1.0.2.0 - Production on Mon Mar 15 12:20:39 2021

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

connected to target database: SUNCDB (DBID=3062579685)
connected to auxiliary database: SUNCDB (not mounted)


Optionally you can execute the below command before executing active duplicate command if having any expired, obsolete backup, same expired, obsolete command can be executed from the PRIMARY database.

  • Crosscheck backups
  • Delete expired backups
  • Crosscheck backups 
RMAN> crosscheck backup;
RMAN> report obsolete;
RMAN> delete obsolete;
RMAN> delete expired backup;
RMAN> crosscheck backup;


Execute Active Duplicate Command

RMAN> DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE DORECOVER NOFILENAMECHECK;

Starting Duplicate Db at 15-MAR-21
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=249 device type=DISK
current log archived

contents of Memory Script:
{
   backup as copy reuse
   passwordfile auxiliary format  '/u01/app/oracle/product/12.1.0/db_1/dbs/orapwsuncdb_s'   ;
}
executing Memory Script

Starting backup at 15-MAR-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=25 device type=DISK
Finished backup at 15-MAR-21

contents of Memory Script:
{
   restore clone from service  'suncdb' standby controlfile;
}
executing Memory Script

Starting restore at 15-MAR-21
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service suncdb
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:04
output file name=/u01/app/oracle/oradata/suncdb/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/suncdb/control02.ctl
Finished restore at 15-MAR-21

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/app/oracle/oradata/suncdb/cdb_temp01.dbf";
   set newname for tempfile  2 to
 "/u01/app/oracle/oradata/suncdb/pdbseed/cdb_temp01.dbf";
   set newname for tempfile  3 to
 "/u01/app/oracle/oradata/suncdb/sunpdb_01/SUNCDB/2B7D6CB0D25809B7E0537600A8C0A639/datafile/o1_mf_cdb_temp_cf82hphc_.tmp";
   switch clone tempfile all;
   set newname for datafile  1 to
 "/u01/app/oracle/oradata/suncdb/system01.dbf";
   set newname for datafile  2 to
 "/u01/app/oracle/oradata/suncdb/pdbseed/system01.dbf";
   set newname for datafile  3 to
 "/u01/app/oracle/oradata/suncdb/sysaux01.dbf";
   set newname for datafile  4 to
 "/u01/app/oracle/oradata/suncdb/pdbseed/sysaux01.dbf";
   set newname for datafile  5 to
 "/u01/app/oracle/oradata/suncdb/undotbs01.dbf";
   set newname for datafile  6 to
 "/u01/app/oracle/oradata/suncdb/users01.dbf";
   set newname for datafile  7 to
 "/u01/app/oracle/oradata/suncdb/pdbseed/users01.dbf";
   set newname for datafile  8 to
 "/u01/app/oracle/oradata/suncdb/cdb_user01.dbf";
   set newname for datafile  9 to
 "/u01/app/oracle/oradata/suncdb/sunpdb_01/SUNCDB/2B7D6CB0D25809B7E0537600A8C0A639/datafile/o1_mf_system_ccrt9wv5_.dbf";
   set newname for datafile  10 to
 "/u01/app/oracle/oradata/suncdb/sunpdb_01/SUNCDB/2B7D6CB0D25809B7E0537600A8C0A639/datafile/o1_mf_sysaux_ccrt9wvz_.dbf";
   set newname for datafile  11 to
 "/u01/app/oracle/oradata/suncdb/sunpdb_01/SUNCDB/2B7D6CB0D25809B7E0537600A8C0A639/datafile/o1_mf_users_ccrt9ww2_.dbf";
   set newname for datafile  12 to
 "/u01/app/oracle/oradata/suncdb/sunpdb_01/SUNCDB/2B7D6CB0D25809B7E0537600A8C0A639/datafile/o1_mf_cdb_user_ccrt9ww3_.dbf";
   restore
   from service  'suncdb'   clone database
   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/suncdb/cdb_temp01.dbf in control file
renamed tempfile 2 to /u01/app/oracle/oradata/suncdb/pdbseed/cdb_temp01.dbf in control file
renamed tempfile 3 to /u01/app/oracle/oradata/suncdb/sunpdb_01/SUNCDB/2B7D6CB0D25809B7E0537600A8C0A639/datafile/o1_mf_cdb_temp_cf82hphc_.tmp in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 15-MAR-21
using channel ORA_AUX_DISK_1

skipping datafile 8; already restored to SCN 6726447
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service suncdb
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/suncdb/system01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:55
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service suncdb
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/suncdb/pdbseed/system01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:26
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service suncdb
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/suncdb/sysaux01.dbf

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:55
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service suncdb
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/suncdb/pdbseed/sysaux01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:35
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service suncdb
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/suncdb/undotbs01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:26
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service suncdb
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/suncdb/users01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service suncdb
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/suncdb/pdbseed/users01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service suncdb
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00009 to /u01/app/oracle/oradata/suncdb/sunpdb_01/SUNCDB/2B7D6CB0D25809B7E0537600A8C0A639/datafile/o1_mf_system_ccrt9wv5_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:26
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service suncdb
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00010 to /u01/app/oracle/oradata/suncdb/sunpdb_01/SUNCDB/2B7D6CB0D25809B7E0537600A8C0A639/datafile/o1_mf_sysaux_ccrt9wvz_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:45
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service suncdb
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00011 to /u01/app/oracle/oradata/suncdb/sunpdb_01/SUNCDB/2B7D6CB0D25809B7E0537600A8C0A639/datafile/o1_mf_users_ccrt9ww2_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:25
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service suncdb
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00012 to /u01/app/oracle/oradata/suncdb/sunpdb_01/SUNCDB/2B7D6CB0D25809B7E0537600A8C0A639/datafile/o1_mf_cdb_user_ccrt9ww3_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:08
Finished restore at 15-MAR-21

sql statement: alter system archive log current
current log archived

contents of Memory Script:
{
   restore clone force from service  'suncdb'
           archivelog from scn  7238146;
   switch clone datafile all;
}
executing Memory Script

Starting restore at 15-MAR-21
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: using network backup set from service suncdb
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=78
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: using network backup set from service suncdb
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=79
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: using network backup set from service suncdb
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=80
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 15-MAR-21

datafile 1 switched to datafile copy
input datafile copy RECID=63 STAMP=1067257655 file name=/u01/app/oracle/oradata/suncdb/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=64 STAMP=1067257655 file name=/u01/app/oracle/oradata/suncdb/pdbseed/system01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=65 STAMP=1067257655 file name=/u01/app/oracle/oradata/suncdb/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=66 STAMP=1067257655 file name=/u01/app/oracle/oradata/suncdb/pdbseed/sysaux01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=67 STAMP=1067257655 file name=/u01/app/oracle/oradata/suncdb/undotbs01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=68 STAMP=1067257655 file name=/u01/app/oracle/oradata/suncdb/users01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=69 STAMP=1067257655 file name=/u01/app/oracle/oradata/suncdb/pdbseed/users01.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=70 STAMP=1067257655 file name=/u01/app/oracle/oradata/suncdb/cdb_user01.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=71 STAMP=1067257655 file name=/u01/app/oracle/oradata/suncdb/sunpdb_01/SUNCDB/2B7D6CB0D25809B7E0537600A8C0A639/datafile/o1_mf_system_ccrt9wv5_.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=72 STAMP=1067257655 file name=/u01/app/oracle/oradata/suncdb/sunpdb_01/SUNCDB/2B7D6CB0D25809B7E0537600A8C0A639/datafile/o1_mf_sysaux_ccrt9wvz_.dbf
datafile 11 switched to datafile copy
input datafile copy RECID=73 STAMP=1067257655 file name=/u01/app/oracle/oradata/suncdb/sunpdb_01/SUNCDB/2B7D6CB0D25809B7E0537600A8C0A639/datafile/o1_mf_users_ccrt9ww2_.dbf
datafile 12 switched to datafile copy
input datafile copy RECID=74 STAMP=1067257655 file name=/u01/app/oracle/oradata/suncdb/sunpdb_01/SUNCDB/2B7D6CB0D25809B7E0537600A8C0A639/datafile/o1_mf_cdb_user_ccrt9ww3_.dbf

contents of Memory Script:
{
   set until scn  7238912;
   recover
   standby
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 15-MAR-21
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 78 is already on disk as file /u01/app/oracle/oradata/suncdb/arch/1_78_1053544965.arch
archived log for thread 1 with sequence 79 is already on disk as file /u01/app/oracle/oradata/suncdb/arch/1_79_1053544965.arch
archived log for thread 1 with sequence 80 is already on disk as file /u01/app/oracle/oradata/suncdb/arch/1_80_1053544965.arch
archived log file name=/u01/app/oracle/oradata/suncdb/arch/1_78_1053544965.arch thread=1 sequence=78
archived log file name=/u01/app/oracle/oradata/suncdb/arch/1_79_1053544965.arch thread=1 sequence=79
archived log file name=/u01/app/oracle/oradata/suncdb/arch/1_80_1053544965.arch thread=1 sequence=80
media recovery complete, elapsed time: 00:00:02
Finished recover at 15-MAR-21
Finished Duplicate Db at 15-MAR-21

RMAN>


Step7. Create STANDBY redo log files.
After finishing the restore database we need to create standby redo log files in the STANDBY database, and always recommended to create N+1 STANDBY redo log files.

STANDBY> alter database add standby logfile group 3 ('/u01/app/oracle/redolog/suncdb/redo03a.log') size 100m ;
 
STANDBY> alter database add standby logfile group 4 ('/u01/app/oracle/redolog/suncdb/redo04a.log') size 100m ;
 
STANDBY> alter database add standby logfile group 5 ('/u01/app/oracle/redolog/suncdb/redo05a.log') size 100m ;
 
STANDBY> set lines 333
STANDBY> col member for a85
STANDBY> select member,type from v$logfile order by type;
 
MEMBER                                                                                TYPE
------------------------------------------------------------------------------------- -------
/u01/app/oracle/fast_recovery_area/SUNCDB_S/onlinelog/o1_mf_2_j4y1c65h_.log           ONLINE
/u01/app/oracle/fast_recovery_area/SUNCDB_S/onlinelog/o1_mf_1_j4y1c4mv_.log           ONLINE
/u01/app/oracle/redolog/suncdb/redo04a.log                                            STANDBY
/u01/app/oracle/redolog/suncdb/redo05a.log                                            STANDBY
/u01/app/oracle/redolog/suncdb/redo03a.log


Note:- Now we need to create the same standby redo log files on the PRIMARY database also, in case we plan for the switchover in the future. So if our PRIMARY database becomes STANDBY then we must have STANDBY redo log files. Follow the same command as above.


PRIMARY DATABASE

PRIMARY> alter database add standby logfile group 3 ('/u01/app/oracle/redolog/suncdb/redo03a.log') size 100m ;
 
PRIMARY> alter database add standby logfile group 4 ('/u01/app/oracle/redolog/suncdb/redo04a.log') size 100m ;
 
PRIMARY> alter database add standby logfile group 5 ('/u01/app/oracle/redolog/suncdb/redo05a.log') size 100m ;
 
PRIMARY>  set lines 333
PRIMARY> col member for a85
PRIMARY> select member,type from v$logfile order by type;
 
MEMBER                                                                                TYPE
------------------------------------------------------------------------------------- -------
/u01/app/oracle/fast_recovery_area/SUNCDB/onlinelog/o1_mf_2_hr63kh8l_.log             ONLINE
/u01/app/oracle/fast_recovery_area/SUNCDB/onlinelog/o1_mf_1_hr63kfhg_.log             ONLINE
/u01/app/oracle/redolog/suncdb/redo04a.log                                            STANDBY
/u01/app/oracle/redolog/suncdb/redo05a.log                                            STANDBY
/u01/app/oracle/redolog/suncdb/redo03a.log


Step 8. Start STANDBY database in READ ONLY mode.

STANDBY>select name,db_unique_name,open_mode,database_role,switchover_status from v$database;

NAME      DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS
--------- ------------------------------ -------------------- ---------------- --------------------
SUNCDB    suncdb_s                       MOUNTED              PHYSICAL STANDBY NOT ALLOWED

STANDBY>alter database open read only;

Database altered.


Step 9. Start standby Managed service.

Before start MRP service makes sure, Listener is up and running on both SITE along with TNS ping & make a remote connection from PRIMARY to STANDBY using SYS user password vice versa.

On PRIMARY DATABASE
Listener & TNSPING status (Sample Output).

[oracle@DBsGuruN1 ~]$ lsnrctl status LISTENER

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 22-MAR-2021 23:42:06

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=DBsGuruN1.localdomain)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                22-MAR-2021 23:03:04
Uptime                    0 days 0 hr. 39 min. 2 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/DBsGuruN1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=DBsGuruN1.localdomain)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=DBsGuruN1.localdomain)(PORT=8080))(Security=(my_wallet_directory=/u01/app/oracle/admin/suncdb/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "suncdb" has 1 instance(s).
  Instance "suncdb", status READY, has 1 handler(s) for this service...
Service "suncdbXDB" has 1 instance(s).
  Instance "suncdb", status READY, has 1 handler(s) for this service...
Service "sunpdb_01" has 1 instance(s).
  Instance "suncdb", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@DBsGuruN1 ~]$ tnsping SUNCDB_S

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 22-MAR-2021 23:48:49

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.119)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = suncdb_s)))
OK (0 msec)


On STANDBY DATABASE
Listener & TNSPING status (Sample Output).

[oracle@DBsGuruN2 ~]$ lsnrctl status LISTENER

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 23-MAR-2021 00:01:32

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=DBsGuruN2.localdomain)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                22-MAR-2021 23:42:44
Uptime                    0 days 0 hr. 18 min. 48 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/DBsGuruN2/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=DBsGuruN2.localdomain)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=DBsGuruN2.localdomain)(PORT=8080))(Security=(my_wallet_directory=/u01/app/oracle/admin/suncdb_s/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "suncdbXDB" has 1 instance(s).
  Instance "suncdb_s", status READY, has 1 handler(s) for this service...
Service "suncdb_s" has 1 instance(s).
  Instance "suncdb_s", status READY, has 1 handler(s) for this service...
Service "sunpdb_01" has 1 instance(s).
  Instance "suncdb_s", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@DBsGuruN2 ~]$ tnsping SUNCDB

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 23-MAR-2021 00:09:53

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.118)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = suncdb)))
OK (0 msec)


Start MRP service and validate MRP & RFS services on the STANDBY database.

STANDBY>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

STANDBY>select name,db_unique_name,open_mode,database_role,switchover_status from v$database;

NAME      DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS
--------- ------------------------------ -------------------- ---------------- --------------------
SUNCDB    suncdb_s                       READ ONLY WITH APPLY PHYSICAL STANDBY NOT ALLOWED

STANDBY>select process, status, thread#, sequence#, block#, blocks from v$managed_standby;

PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
ARCH      CLOSING               1         81       4096       1177
RFS       IDLE                  1         82        173          1
RFS       IDLE                  0          0          0          0
MRP0      APPLYING_LOG          1         82        173     204800


Step 10. Validate archives apply.
This is the last & final step where we will validate archives are getting applied from PRIMARY to STANDBY database.


On PRIMARY DATABASE

Check log sequence details.

PRIMARY> select sequence#,first_time,next_time from v$archived_log order by sequence# desc;

 SEQUENCE# FIRST_TIME           NEXT_TIME
---------- -------------------- --------------------
        82 15-MAR-2021 12:34:46 15-MAR-2021 12:36:33
        82 15-MAR-2021 12:34:46 15-MAR-2021 12:36:33
        81 15-MAR-2021 12:27:32 15-MAR-2021 12:34:46
        81 15-MAR-2021 12:27:32 15-MAR-2021 12:34:46
        80 15-MAR-2021 12:27:29 15-MAR-2021 12:27:32
        79 15-MAR-2021 12:22:02 15-MAR-2021 12:27:29
        78 15-MAR-2021 12:21:44 15-MAR-2021 12:22:02
        77 15-MAR-2021 12:03:11 15-MAR-2021 12:21:44
        76 15-MAR-2021 11:14:16 15-MAR-2021 12:03:11
        75 15-MAR-2021 11:08:38 15-MAR-2021 11:14:16
        75 15-MAR-2021 11:08:38 15-MAR-2021 11:14:16


On STANDBY DATABASE

Validate applied archive log sequence .

STANDBY>select sequence#, applied, THREAD# from v$archived_log order by sequence# desc;

 SEQUENCE# APPLIED      THREAD#
---------- --------- ----------
        82 IN-MEMORY          1
        81 YES                1
        80 YES                1
        79 YES                1
        78 YES                1


Validate archive Gap if any.

STANDBY>select THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# From V$ARCHIVE_GAP;  
 
no rows selected

This document is only for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.


Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the most recent update. Click here to understand more about our pursuit.

Loading

How useful was this post?

Click on a star to rate it!

Average rating / 5. Vote count:

No votes so far! Be the first to rate this post.

As you found this post useful...

Follow us on social media!

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?

DBsGuru

🌐

<strong>Hello and welcome to DBsGuru,</strong>DBsGuru is a group of experienced DBA professionals and serves databases and their related community by providing technical blogs, projects, training. Technical blogs are the source of vast information not about databases but its related product like middleware, PL/SQL, replication methodology, and so on.Thanks for the visits!<strong>Share Learn Grow!</strong>

Leave a Reply

Your email address will not be published. Required fields are marked *