Step by Step Physical Standby Database Creation in Oracle

March 12, 2021

Step by Step Physical Standby Database Creation in Oracle

Data Guard is used for disaster recovery and high availability. Due to any disaster, if our PRIMARY database got crash so, in that case, our STANDBY may become our PRIMARY database.

Here, our client requirement, we need to create a STANDBY database using RMAN backup. Below are details:-

*COMPONENTS*	*PRIMARY DATABASE*	*STANDBY DATABASE*
Server IP	192.168.0.118	192.168.0.119
Server Name	dbsgurun1.localdomain	dbsgurun2.localdomain
DB Unique Name	suncdb	sunscd_s

REMEMBER:-

Oracle version on Primary and secondary should be 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.

ON PRIMARY DATABASE/SERVER:

Step 1. Create Directory Structure.

To hold the database data files, redo log files, and control files. We will first create directories. Here our Primary and standby directory structures are the same. Need to create the same on the STANDBY server as well.

*COMPONENTS*	*PRIMARY DATABASE*	*STANDBY DATABASE*
*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*	/u01/app/oracle/oradata/suncdb/backup	/u01/app/oracle/oradata/suncdb/backup

[oracle@DBsGuruN1 ~]$ mkdir -p  /u01/app/oracle/oradata/suncdb
[oracle@DBsGuruN1 ~]$ ls -ld /u01/app/oracle/oradata/suncdb
drwxrwxr-x 7 oracle oinstall 4096 Mar 11 18:17 /u01/app/oracle/oradata/suncdb
[oracle@DBsGuruN1 ~]$ mkdir -p  /u01/app/oracle/oradata/suncdb/backup
[oracle@DBsGuruN1 ~]$ ls -ld /u01/app/oracle/oradata/suncdb/backup
drwxr-xr-x 2 oracle oinstall 4096 Mar 11 17:38 /u01/app/oracle/oradata/suncdb/backup
[oracle@DBsGuruN1 ~]$ mkdir -p  /u01/app/oracle/redolog/suncdb/
[oracle@DBsGuruN1 ~]$ ls -ld /u01/app/oracle/redolog/suncdb
drwxrwxr-x 7 oracle oinstall 4096 Mar 11 17:39 /u01/app/oracle/redolog/suncdb/

[oracle@DBsGuruN2 ~]$ mkdir -p  /u01/app/oracle/oradata/suncdb
[oracle@DBsGuruN2 ~]$ ls -ld /u01/app/oracle/oradata/suncdb
drwxrwxr-x 7 oracle oinstall 4096 Mar 11 18:17 /u01/app/oracle/oradata/suncdb
[oracle@DBsGuruN2 ~]$ mkdir -p  /u01/app/oracle/oradata/suncdb/backup
[oracle@DBsGuruN2 ~]$ ls -ld /u01/app/oracle/oradata/suncdb/backup
drwxr-xr-x 2 oracle oinstall 4096 Mar 11 17:38 /u01/app/oracle/oradata/suncdb/backup
[oracle@DBsGuruN2 ~]$ mkdir -p  /u01/app/oracle/redolog/suncdb/
[oracle@DBsGuruN2 ~]$ ls -ld /u01/app/oracle/redolog/suncdb
drwxrwxr-x 7 oracle oinstall 4096 Mar 11 17:39 /u01/app/oracle/redolog/suncdb/

NOTE: If you want to use a different directory structure for datafiles and redo log files between PRIMARY & STANDBY, use the below parameters:

db_file_name_convert
log_file_name_convert

Step 2. Alter required parameters on the PRIMARY database.

Enable Redo log Destinations.

PRIMARY> alter system set log_archive_config='dg_config=(suncdb,suncdb_s)' scope=both;

System altered.

PRIMARY> show parameter log_archive_config

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
log_archive_config                   string                           dg_config=(suncdb,suncdb_s)

Set Archive log destinations:-

PRIMARY> alter system set log_archive_dest_1= 'LOCATION=/u01/app/oracle/oradata/suncdb/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=suncdb' scope=both;  ==> For PRIMARY DB Archive Location

System altered.

PRIMARY> alter system set log_archive_dest_2='SERVICE=suncdb_s LGWR ASYNC valid_for=(online_logfiles,primary_role) DB_UNIQUE_NAME=suncdb_s' scope=both;  ==> For STANDBY DB Archive Location

System altered.

PRIMARY> show parameter log_archive_dest_1

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
log_archive_dest_1                   string                           LOCATION=/u01/app/oracle/orada
                                                                      ta/suncdb/arch VALID_FOR=(ALL_
                                                                      LOGFILES,ALL_ROLES) DB_UNIQUE_
                                                                      NAME=suncdb
PRIMARY> show parameter log_archive_dest_2

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
log_archive_dest_2                   string                           SERVICE=suncdb_s LGWR ASYNC va
                                                                      lid_for=(online_logfiles,prima
                                                                      ry_role) DB_UNIQUE_NAME=suncdb
                                                                      _s

Temporary, you may disable (DEFER) STANDBY destination to suppress any warning/error in DB alert log and enabled after set up of STANDBY database.

PRIMARY> alter system set log_archive_dest_state_1='ENABLE' scope=both ;

System altered.

PRIMARY> alter system set log_archive_dest_state_2='ENABLE' scope=both ;

System altered.

PRIMARY> show parameter log_archive_dest_state_1

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
log_archive_dest_state_1             string                           ENABLE

PRIMARY> show parameter log_archive_dest_state_2

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
log_archive_dest_state_2             string                           ENABLE

Set Remote login password to exclusive.

PRIMARY> alter system set remote_login_passwordfile='EXCLUSIVE' scope=spfile;

System altered.

PRIMARY> show parameter remote_login_passwordfile

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
remote_login_passwordfile            string                           EXCLUSIVE

Set Parameters for GAP resolution: and file transfer.

PRIMARY> alter system set fal_client='suncdb' scope=both;

System altered.

PRIMARY> alter system set fal_server='suncdb_s' scope=both;

System altered.

PRIMARY> alter system set standby_file_management= 'AUTO' scope=both;

System altered.

PRIMARY> show parameter fal_client

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
fal_client                           string                           suncdb

PRIMARY> show parameter fal_server

fal_server                           string                           suncdb_s

PRIMARY> show parameter standby_file_management

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
standby_file_management              string                           AUTO

Set File name convert parameter in case if the directory structure is different in primary and standby database. In our case, the PRIMARY and STANDBY database structures are the same. (We are mentioning here just for demonstration purpose only).

PRIMARY> alter system set db_file_name_convert='/oracle/dbguru/','/oracle/dbguru/','/u02/dbguru/','/u02/dbguru/' scope=spfile;
PRIMARY> alter system set log_file_name_convert='/oracle/dbguru/redo/','/oracle/dbguru/redo/' scope=spfile;

Reboot PRIMARY database. Remember, some of the parameters are not modifiable on run time, so the database will need to be restarted before they take effect of new values.

PRIMARY> shutdown immediate;
PRIMARY> startup

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.

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’

Step 3. Create a pfile for the STANDBY database.

PRIMARY> create pfile ='/u01/app/oracle/oradata/suncdb/backup/initsuncdb.ora' from spfile;

File created.

[oracle@DBsGuruN1 ~]$ ls -lrt /u01/app/oracle/oradata/suncdb/backup/initsuncdb.ora
-rwxr-xr-x 1 oracle oinstall 1641 Mar 11 17:05 /u01/app/oracle/oradata/suncdb/backup/initsuncdb.ora

Step 4. Create a control file for the STANDBY database.

PRIMARY> alter database create standby controlfile as '/u01/app/oracle/oradata/suncdb/backup/control_std.ctl';

Database altered.

[oracle@DBsGuruN1 ~]$ ls -lrt /u01/app/oracle/oradata/suncdb/backup/control_std.ctl
-rw-r----- 1 oracle oinstall 18202624 Mar 11 17:07 /u01/app/oracle/oradata/suncdb/backup/control_std.ctl

Step 5. Create a Password File.
Here we create a password file using command line utility orapwd for STANDBY. We are passing the same sys password which is in our PRIMARY database.

[oracle@DBsGuruN1 ~]$ $ORACLE_HOME/dbs/orapwd file=orapw$ORACLE_SID  password=DBsGuru123  entries=10 force=y ignorecase=y

Step 6. Configure Network.

tnsnames.ora: The “tnsnames.ora” file contains client-side network configuration parameters. It can be found in the “$ORACLE_HOME/network/admin” directory on the client. This file will also be present on the server if client-style connections are used on the server itself.

listener.ora: The “listerner.ora” file contains server-side network configuration parameters. It can be found in the “$ORACLE_HOME/network/admin” directory on the server. The listener has the default name of “LISTENER” and is listening for TCP connections on port 1521.

Below are the entries of tnsnames.ora files for PRIMARY and STANDBY.

PRIMARY TNSNAMES.ORA
Add below entries in TNS.

vi $ORACLE_HOME/network/admin/tnsnames.ora

SUNCDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.118)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = suncdb)
)
)

SUNCDB_S =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.119)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = suncdb_s)
)
)

Validate tnsping PRIMARY to STANDBY and STANDBY to PRIMARY vice versa once start the database in MOUNT state.

Step 7. Create a PRIMARY database full backup using RMAN.

[oracle@DBsGuruN1 ~]$ rman target / nocatalog

RMAN> run {
allocate channel ch11 type disk ;
backup FORMAT '/u01/app/oracle/oradata/suncdb/backup/df_%d_%s_%p_%T.bkp' database include current controlfile for standby;
release channel ch11;
}

Step 8. Copy files to the STANDBY database server.

In this step, we will copy the pfile, standby control file, full database backup set, tnsnames.ora file (preferably edit tnsnames.ora file), and the password file to the STANDBY database server. Follow the below commands.

[oracle@DBsGuruN1 ~]$ scp /u01/app/oracle/oradata/suncdb/backup/* [email protected]:/u01/app/oracle/oradata/suncdb/backup/

[oracle@DBsGuruN1 ~]$ scp /u01/app/oracle/product/12.1.0/db_1/dbs/orapwsuncdb [email protected]:/u01/app/oracle/product/12.1.0/db_1/dbs/orapwsuncdb_s

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 11 17:38 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 11  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 11 18:09 /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 11 18:25 /u01/app/oracle/oradata/suncdb/arch
[oracle@DBsGuruN2 backup]$ ls -ld /u01/app/oracle/redolog/suncdb
drwxr-xr-x 2 oracle oinstall 12288 Mar 11 18:26 /u01/app/oracle/redolog/suncdb

Step 5. Start STANDBY database in nomount 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. Copy STANDBY control file to desire locations according to values of parameter’s CONTROL_FILES.

[oracle@DBsGuruN2 backup]$ cp control_std.ctl /u01/app/oracle/oradata/suncdb/control01.ctl
[oracle@DBsGuruN2 backup]$ ls -lrt /u01/app/oracle/oradata/suncdb/control01.ctl
-rw-r----- 1 oracle oinstall 18202624 Mar 11 18:04 /u01/app/oracle/oradata/suncdb/control01.ctl
[oracle@DBsGuruN2 backup]$ cp control_std.ctl /u01/app/oracle/fast_recovery_area/suncdb/control02.ctl
[oracle@DBsGuruN2 backup]$ ls -lrt /u01/app/oracle/fast_recovery_area/suncdb/control02.ctl
-rw-r----- 1 oracle oinstall 18202624 Mar 11 18:05 /u01/app/oracle/fast_recovery_area/suncdb/control02.ctl

Step 7. Alter database in mount state.

STANDBY> alter database mount standby database;
Database altered.

Step 8. Restore backup.
After successfully starting the STANDBY database in mount state. Restore the backup, follow the below steps.

Catalog backup

[oracle@DBsGuruN2 backup]$ rman target /
RMAN> catalog start with '/u01/app/oracle/oradata/suncdb/backup';

Starting implicit crosscheck backup at 11-MAR-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
Crosschecked 12 objects
Finished implicit crosscheck backup at 11-MAR-21

Starting implicit crosscheck copy at 11-MAR-21
using channel ORA_DISK_1
Crosschecked 2 objects
Finished implicit crosscheck copy at 11-MAR-21

searching for all files in the recovery area
cataloging files...
cataloging done

searching for all files that match the pattern /u01/app/oracle/oradata/suncdb/backup

List of Files Unknown to the Database
=====================================
File Name: /u01/app/oracle/oradata/suncdb/backup/initsuncdb.ora
File Name: /u01/app/oracle/oradata/suncdb/backup/df_SUNCDB_444_1_20210311.bkp
File Name: /u01/app/oracle/oradata/suncdb/backup/df_SUNCDB_445_1_20210311.bkp
File Name: /u01/app/oracle/oradata/suncdb/backup/initstd.ora
File Name: /u01/app/oracle/oradata/suncdb/backup/df_SUNCDB_442_1_20210311.bkp
File Name: /u01/app/oracle/oradata/suncdb/backup/df_SUNCDB_443_1_20210311.bkp

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/app/oracle/oradata/suncdb/backup/df_SUNCDB_444_1_20210311.bkp
File Name: /u01/app/oracle/oradata/suncdb/backup/df_SUNCDB_445_1_20210311.bkp
File Name: /u01/app/oracle/oradata/suncdb/backup/df_SUNCDB_442_1_20210311.bkp
File Name: /u01/app/oracle/oradata/suncdb/backup/df_SUNCDB_443_1_20210311.bkp

List of Files Which Were Not Cataloged
=======================================
File Name: /u01/app/oracle/oradata/suncdb/backup/initsuncdb.ora
  RMAN-07517: Reason: The file header is corrupted
File Name: /u01/app/oracle/oradata/suncdb/backup/initstd.ora
  RMAN-07517: Reason: The file header is corrupted

Crosscheck backups
Delete expired backups (Applicable only if any old backup piece/archives available on the STANDBY server in the case already performed earlier restore or copy/move backup piece/archives from the PRIMARY database server.)
Crosscheck backups (Make sure cataloged backup piece should be visible.)

RMAN> crosscheck backup;
RMAN> report obsolete;
RMAN> delete obsolete;
RMAN> delete expired backup;
RMAN> crosscheck backup;

using channel ORA_DISK_1
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/fast_recovery_area/SUNCDB_S/autobackup/2020_10_11/o1_mf_s_1053546789_hr6jppmj_.bkp RECID=344 STAMP=1066932569
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/oradata/suncdb/backup/df_SUNCDB_444_1_20210311.bkp RECID=345 STAMP=1066932589
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/oradata/suncdb/backup/df_SUNCDB_445_1_20210311.bkp RECID=346 STAMP=1066932589
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/oradata/suncdb/backup/df_SUNCDB_442_1_20210311.bkp RECID=347 STAMP=1066932589
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/oradata/suncdb/backup/df_SUNCDB_443_1_20210311.bkp RECID=348 STAMP=1066932589
Crosschecked 5 objects

Rman restore

RMAN> restore database;

Starting restore at 11-MAR-21
using channel ORA_DISK_1

skipping datafile 2; already restored to file /u01/app/oracle/oradata/suncdb/pdbseed/system01.dbf
skipping datafile 4; already restored to file /u01/app/oracle/oradata/suncdb/pdbseed/sysaux01.dbf
skipping datafile 7; already restored to file /u01/app/oracle/oradata/suncdb/pdbseed/users01.dbf
skipping datafile 8; already restored to file /u01/app/oracle/oradata/suncdb/cdb_user01.dbf
skipping datafile 9; already restored to file /u01/app/oracle/oradata/suncdb/sunpdb_01/SUNCDB/2B7D6CB0D25809B7E0537600A8C0A639/datafile/o1_mf_system_ccrt9wv5_.dbf
skipping datafile 10; already restored to file /u01/app/oracle/oradata/suncdb/sunpdb_01/SUNCDB/2B7D6CB0D25809B7E0537600A8C0A639/datafile/o1_mf_sysaux_ccrt9wvz_.dbf
skipping datafile 11; already restored to file /u01/app/oracle/oradata/suncdb/sunpdb_01/SUNCDB/2B7D6CB0D25809B7E0537600A8C0A639/datafile/o1_mf_users_ccrt9ww2_.dbf
skipping datafile 12; already restored to file /u01/app/oracle/oradata/suncdb/sunpdb_01/SUNCDB/2B7D6CB0D25809B7E0537600A8C0A639/datafile/o1_mf_cdb_user_ccrt9ww3_.dbf
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/suncdb/system01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/suncdb/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/suncdb/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/suncdb/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/oradata/suncdb/backup/df_SUNCDB_442_1_20210311.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/oradata/suncdb/backup/df_SUNCDB_442_1_20210311.bkp tag=TAG20210311T171856
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:15
Finished restore at 11-MAR-21

Step9. 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_j4n4cd75_.log           ONLINE
/u01/app/oracle/fast_recovery_area/SUNCDB_S/onlinelog/o1_mf_1_j4n4cb6n_.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                                            STANDBY

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                                            STANDBY

Step 10. Start standby Managed service.
Start MRP service and validate MRP & RFS services on the STANDBY database.

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.

STANDBY> alter database recover managed standby database disconnect from session;

Database altered.
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      CONNECTED             0          0          0          0
MRP0      WAIT_FOR_LOG          1         69          0          0
RFS       IDLE                  0          0          0          0
RFS       IDLE                  1         69      26064          1
RFS       IDLE                  0          0          0          0
RFS       IDLE                  0          0          0          0

Step 11. 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
---------- -------------------- --------------------
        68 12-MAR-2021 16:37:04 12-MAR-2021 16:38:25
        68 12-MAR-2021 16:37:04 12-MAR-2021 16:38:25
        67 12-MAR-2021 16:36:41 12-MAR-2021 16:37:04
        67 12-MAR-2021 16:36:41 12-MAR-2021 16:37:04
        66 11-MAR-2021 18:18:08 12-MAR-2021 16:36:41
        66 11-MAR-2021 18:18:08 12-MAR-2021 16:36:41
        65 11-MAR-2021 18:07:55 11-MAR-2021 18:18:08
        65 11-MAR-2021 18:07:55 11-MAR-2021 18:18:08
        64 11-MAR-2021 17:58:09 11-MAR-2021 18:07:55
        64 11-MAR-2021 17:58:09 11-MAR-2021 18:07:55
        63 11-MAR-2021 17:56:25 11-MAR-2021 17:58:09

On STANDBY DATABASE

Validate applied archive log sequence.

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

  SEQUENCE# APPLIED      THREAD#
---------- --------- ----------
        68 YES                1
        67 YES                1
        66 YES                1
        65 YES                1
        64 YES                1
        63 YES                1
        62 YES                1
        61 YES                1
        60 YES                1
        59 YES                1

Validate archive Gap if any.

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

no rows selected

Click here for Steps to Create an Active Physical Standby Dataguard Using RMAN Duplicate in Oracle.

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.

DATABASE, DATAGUARD, ORACLE

Step by Step Physical Standby Database Creation in Oracle

Related Articles