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.
| DETAILS | PRIMARY DATABASE | STANDBY DATABASE |
| Server IP | 192.168.0.118 | 192.168.0.119 |
| Server Name | DBsGuruN1.localdomain | DBsGuruN2.localdomain |
| DB Unique Name | suncdb | suncdb_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/ |
| Backup | Not Require | RMAN 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.
