Create Physical Standby Database Using RMAN Backup and Restore -

Step by step guide on how to create a physical standby database using RMAN without using duplicate command.

Create a Physical Standby Database Using the RMAN Backup and Restore process.: In this content, you will see and learn how we can Create a Physical Standby Database Using RMAN Backup and Restore. We will performed here this activity in Oracle 19c Database.

Environment Details for both (Primary and Standby):

Primary:

	Platform	: Red Hat Enterprise Linux Server release 7.8
	Server Name	: primary.localdomain, IP: 192.168.0.113
	DB Version	: Oracle 19.0.0.0.0
	File system     : Normal
	Database Name	: ORCL
        DB_UNIQUE_NAME  : ORCL
	Oracle Home Path: /u01/app/oracle/product/19.0.0/dbhome_1/
		
Standby:
		
	Platform	: Red Hat Enterprise Linux Server release 7.8
	Server Name	: standby.localdomain, IP: 192.168.0.114
	DB Version	: Oracle 19.0.0.0.0
	File system     : Normal
	Database Name	: ORCL
        DB_UNIQUE_NAME  : ORCLDR
	Oracle Home Path: /u01/app/oracle/product/19.0.0/dbhome_1/

Primary Server side Configurations:-

Make sure primary is in archivelog mode:

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/archive
Oldest online log sequence     4
Next log sequence to archive   6
Current log sequence           6
SQL>

*** If Archive Mode is Disabled then click here to visualize however we will change it.

Check FORCE LOGGING is enabled:

SQL> select force_logging from v$database;

FORCE_LOGGING
---------------------------------------
NO

SQL> alter database force logging;

Database altered.

SQL> select force_logging from v$database;

FORCE_LOGGING
---------------------------------------
YES

SQL>

Set Primary Database Initialization Parameters:

SQL> alter system set log_archive_config='dg_config=(ORCL,ORCLDR)' scope=both;

System altered.

SQL> alter system set log_archive_dest_1='location=/u01/archive valid_for=(all_logfiles,all_roles) db_unique_name=ORCL' scope=both;

System altered.

SQL> alter system set log_archive_dest_2='service=ORCLDR lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=ORCLDR' scope=both;

System altered.

SQL> alter system set log_archive_dest_state_1=enable;

System altered.

SQL> alter system set log_archive_dest_state_2=enable;

System altered.

SQL> alter system set log_archive_max_processes=10;

System altered.

SQL> alter system set remote_login_passwordfile=exclusive scope=spfile;

System altered.

SQL> alter system set fal_client=ORCL;

System altered.

SQL> alter system set fal_server=ORCLDR;

System altered.

SQL> alter system set db_unique_name=ORCL scope=spfile;

System altered.

SQL> alter system set standby_file_management=auto;

System altered.

SQL>

Configure Standby Redo Log on Primary:

SQL> col member for a50
SQL> set lines 400 pages 400
SQL> select group#, type, member from v$logfile order by group#, member;

    GROUP# TYPE    MEMBER
---------- ------- --------------------------------------------------
         1 ONLINE  /u01/app/oracle/oradata/ORCL/redo01.log
         2 ONLINE  /u01/app/oracle/oradata/ORCL/redo02.log
         3 ONLINE  /u01/app/oracle/oradata/ORCL/redo03.log

3 rows selected.

SQL> alter database add standby logfile thread 1 group 4 ('/u01/app/oracle/oradata/ORCL/stdy_redo04.log') size 200M;

Database altered.

SQL> alter database add standby logfile thread 1 group 5 ('/u01/app/oracle/oradata/ORCL/stdy_redo05.log') size 200M;

Database altered.

SQL> alter database add standby logfile thread 1 group 6 ('/u01/app/oracle/oradata/ORCL/stdy_redo06.log') size 200M;

Database altered.

SQL> alter database add standby logfile thread 1 group 7 ('/u01/app/oracle/oradata/ORCL/stdy_redo07.log') size 200M;

Database altered.

SQL> select group#, type, member from v$logfile order by group#, member;

    GROUP# TYPE    MEMBER
---------- ------- --------------------------------------------------
         1 ONLINE  /u01/app/oracle/oradata/ORCL/redo01.log
         2 ONLINE  /u01/app/oracle/oradata/ORCL/redo02.log
         3 ONLINE  /u01/app/oracle/oradata/ORCL/redo03.log
         4 STANDBY /u01/app/oracle/oradata/ORCL/stdy_redo04.log
         5 STANDBY /u01/app/oracle/oradata/ORCL/stdy_redo05.log
         6 STANDBY /u01/app/oracle/oradata/ORCL/stdy_redo06.log
         7 STANDBY /u01/app/oracle/oradata/ORCL/stdy_redo07.log

7 rows selected.

Take RMAN backup plus archivelog and controlfile for standby format:

[oracle@primary ~]$ mkdir -p /u02/backup/rman/
[oracle@primary ~]$
[oracle@primary ~]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Sun Mar 28 15:58:06 2021
Version 19.3.0.0.0

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

connected to target database: ORCL (DBID=1595561719)

RMAN> run
2> {
allocate channel ch1 device type disk;
crosscheck archivelog all;
3> delete expired archivelog all;
crosscheck archivelog all;
backup as compressed backupset database format '/u02/backup/rman/Fullback_%T_%U'
plus archivelog format '/u02/backup/rman/Archive_%T_%U';
BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT '/u02/backup/rman/Controlback_%T_%U';
release channel ch1;
}
4> 5> 6> exit;
7> 8> 9> 10> 11>
using target database control file instead of recovery catalog
allocated channel: ch1
channel ch1: SID=458 device type=DISK

validation succeeded for archived log
archived log file name=/u01/archive/1_5_1067780986.dbf RECID=1 STAMP=1068388010
validation succeeded for archived log
archived log file name=/u01/archive/1_6_1067780986.dbf RECID=2 STAMP=1068388667
validation succeeded for archived log
archived log file name=/u01/archive/1_7_1067780986.dbf RECID=3 STAMP=1068392317
Crosschecked 3 objects


specification does not match any archived log in the repository

validation succeeded for archived log
archived log file name=/u01/archive/1_5_1067780986.dbf RECID=1 STAMP=1068388010
validation succeeded for archived log
archived log file name=/u01/archive/1_6_1067780986.dbf RECID=2 STAMP=1068388667
validation succeeded for archived log
archived log file name=/u01/archive/1_7_1067780986.dbf RECID=3 STAMP=1068392317
Crosschecked 3 objects



Starting backup at 28-MAR-21
current log archived
channel ch1: starting compressed archived log backup set
channel ch1: specifying archived log(s) in backup set
input archived log thread=1 sequence=5 RECID=1 STAMP=1068388010
input archived log thread=1 sequence=6 RECID=2 STAMP=1068388667
input archived log thread=1 sequence=7 RECID=3 STAMP=1068392317
input archived log thread=1 sequence=8 RECID=4 STAMP=1068393627
channel ch1: starting piece 1 at 28-MAR-21
channel ch1: finished piece 1 at 28-MAR-21
piece handle=/u02/backup/rman/Archive_20210328_02vqsp4s_1_1 tag=TAG20210328T160028 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:55
Finished backup at 28-MAR-21

Starting backup at 28-MAR-21
channel ch1: starting compressed full datafile backup set
channel ch1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/ORCL/system01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/ORCL/sysaux01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/ORCL/undotbs01.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/ORCL/users01.dbf
channel ch1: starting piece 1 at 28-MAR-21
channel ch1: finished piece 1 at 28-MAR-21
piece handle=/u02/backup/rman/Fullback_20210328_03vqsp6l_1_1 tag=TAG20210328T160124 comment=NONE
channel ch1: backup set complete, elapsed time: 00:03:56
Finished backup at 28-MAR-21

Starting backup at 28-MAR-21
current log archived
channel ch1: starting compressed archived log backup set
channel ch1: specifying archived log(s) in backup set
input archived log thread=1 sequence=9 RECID=5 STAMP=1068393922
channel ch1: starting piece 1 at 28-MAR-21
channel ch1: finished piece 1 at 28-MAR-21
piece handle=/u02/backup/rman/Archive_20210328_04vqspe3_1_1 tag=TAG20210328T160523 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:01
Finished backup at 28-MAR-21

Starting backup at 28-MAR-21
channel ch1: starting full datafile backup set
channel ch1: specifying datafile(s) in backup set
including standby control file in backup set
channel ch1: starting piece 1 at 28-MAR-21
channel ch1: finished piece 1 at 28-MAR-21
piece handle=/u02/backup/rman/Controlback_20210328_05vqspe5_1_1 tag=TAG20210328T160524 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:01
Finished backup at 28-MAR-21

Starting Control File and SPFILE Autobackup at 28-MAR-21
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/autobackup/2021_03_28/o1_mf_s_1068393927_j60pzjfm_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 28-MAR-21

released channel: ch1

RMAN>

Recovery Manager complete.

Configure network files for Primary:

[oracle@primary admin]$ pwd
/u01/app/oracle/product/19.0.0/dbhome_1/network/admin
[oracle@primary admin]$
[oracle@primary admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ORCL)
      (ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
      (SID_NAME = ORCL)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = ORCLDR)
      (ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
      (SID_NAME = ORCL)
    )
  )

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.113)(PORT = 1521))
  )

ADR_BASE_LISTENER = /u01/app/oracle

[oracle@primary admin]$
[oracle@primary admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.113)(PORT = 1521))


ORCLDR =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.114)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ORCLDR)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.113)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ORCL)
    )
  )

[oracle@primary admin]$
[oracle@primary admin]$ lsnrctl start LISTENER

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 28-MAR-2021 16:08:31

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

Starting /u01/app/oracle/product/19.0.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 19.0.0.0.0 - Production
System parameter file is /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/primary/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.113)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.113)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                28-MAR-2021 16:08:31
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/primary/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.113)(PORT=1521)))
Services Summary...
Service "ORCL" has 1 instance(s).
  Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...
Service "ORCLDR" has 1 instance(s).
  Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@primary admin]$
[oracle@primary admin]$ tnsping ORCL

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 28-MAR-2021 16:10:48

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

Used parameter files:
/u01/app/oracle/product/19.0.0/dbhome_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.113)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ORCL)))
OK (10 msec)
[oracle@primary admin]$
[oracle@primary admin]$ tnsping ORCLDR

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 28-MAR-2021 16:10:51

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

Used parameter files:
/u01/app/oracle/product/19.0.0/dbhome_1/network/admin/sqlnet.ora


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

Verify connection “as sysdba” from Primary:

[oracle@primary ~]$ sqlplus sys/Welcome123@ORCL as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Mar 28 16:40:01 2021
Version 19.3.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> def
DEFINE _DATE           = "28-MAR-21" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "ORCL" (CHAR)
DEFINE _USER           = "SYS" (CHAR)
DEFINE _PRIVILEGE      = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1903000000" (CHAR)
DEFINE _EDITOR         = "vi" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0" (CHAR)
DEFINE _O_RELEASE      = "1903000000" (CHAR)
SQL>

Copy password file and pfile from primary to standby server:

[oracle@primary ~]$ cd /u01/app/oracle/product/19.0.0/dbhome_1/dbs/
[oracle@primary dbs]$ ls -ltrh
total 11M
-rw-r--r--. 1 oracle oinstall 3.1K May 14  2015 init.ora
-rw-r-----. 1 oracle oinstall   24 Mar 21 13:44 lkORCL
-rw-r-----. 1 oracle oinstall 2.0K Mar 21 13:50 orapwORCL
-rw-rw----. 1 oracle oinstall 1.6K Mar 28 14:27 hc_ORCL.dat
-rw-r-----. 1 oracle oinstall 4.5K Mar 28 15:37 spfileORCL.ora
-rw-r-----. 1 oracle oinstall  11M Mar 28 15:47 snapcf_ORCL.f
-rw-r--r--. 1 oracle oinstall 1.6K Mar 28 15:53 initORCL.ora
[oracle@primary dbs]$
[oracle@primary dbs]$ scp orapwORCL initORCL.ora oracle@192.168.0.114:/u01/app/oracle/product/19.0.0/dbhome_1/dbs/
The authenticity of host '192.168.0.114 (192.168.0.114)' can't be established.
ECDSA key fingerprint is SHA256:KMocJJVCjmc/7q+zR4GCXMem7J1es/f1BZc80N2BUz8.
ECDSA key fingerprint is MD5:6b:e7:43:9f:06:0a:74:16:20:a3:05:b1:23:84:bb:60.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.0.114' (ECDSA) to the list of known hosts.
oracle@192.168.0.114's password:
orapwORCL                                                                                           100% 2048   124.2KB/s   00:00
initORCL.ora                                                                                        100% 1604   397.1KB/s   00:00
[oracle@primary dbs]$

Copy backup pieces from primary to standby server:

[oracle@primary ~]$ cd /u02/backup/rman/
[oracle@primary rman]$ ls -ltrh
total 380M
-rw-r-----. 1 oracle oinstall  83M Mar 28 16:01 Archive_20210328_02vqsp4s_1_1
-rw-r-----. 1 oracle oinstall 288M Mar 28 16:05 Fullback_20210328_03vqsp6l_1_1
-rw-r-----. 1 oracle oinstall 274K Mar 28 16:05 Archive_20210328_04vqspe3_1_1
-rw-r-----. 1 oracle oinstall  11M Mar 28 16:05 Controlback_20210328_05vqspe5_1_1
[oracle@primary rman]$
[oracle@primary rman]$ scp * oracle@192.168.0.114:/u02/backups/
oracle@192.168.0.114's password:
Archive_20210328_02vqsp4s_1_1                                                                       100%   82MB  16.5MB/s   00:05
Archive_20210328_04vqspe3_1_1                                                                       100%  274KB   7.7MB/s   00:00
Controlback_20210328_05vqspe5_1_1                                                                   100%   10MB  12.4MB/s   00:00
Fullback_20210328_03vqsp6l_1_1                                                                      100%  287MB  15.9MB/s   00:18
[oracle@primary rman]$

Standby Server side Configurations:-

Configure network files for standby:

[oracle@standby admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ORCL)
      (ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
      (SID_NAME = ORCL)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = ORCLDR)
      (ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
      (SID_NAME = ORCL)
    )
  )

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.114)(PORT = 1521))
  )

ADR_BASE_LISTENER = /u01/app/oracle

[oracle@standby admin]$
[oracle@standby admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

ORCLDR =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.114)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ORCLDR)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.113)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ORCL)
    )
  )

[oracle@standby admin]$
[oracle@standby admin]$ lsnrctl start LISTENER

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 28-MAR-2021 16:10:38

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

Starting /u01/app/oracle/product/19.0.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 19.0.0.0.0 - Production
System parameter file is /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/standby/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.114)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.114)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                28-MAR-2021 16:10:39
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/standby/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.114)(PORT=1521)))
Services Summary...
Service "ORCL" has 1 instance(s).
  Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...
Service "ORCLDR" has 1 instance(s).
  Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@standby admin]$
[oracle@standby admin]$
[oracle@standby admin]$ tnsping ORCL

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 28-MAR-2021 16:11:01

Copyright (c) 1997, 2019, 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.113)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ORCL)))
OK (10 msec)
[oracle@standby admin]$
[oracle@standby admin]$ tnsping ORCLDR

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 28-MAR-2021 16:11:04

Copyright (c) 1997, 2019, 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.114)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ORCLDR)))
OK (10 msec)

Configure init parameter for standby:

[oracle@standby dbs]$ cat initORCL.ora
ORCL.__data_transfer_cache_size=0
ORCL.__db_cache_size=478150656
ORCL.__inmemory_ext_roarea=0
ORCL.__inmemory_ext_rwarea=0
ORCL.__java_pool_size=0
ORCL.__large_pool_size=4194304
ORCL.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
ORCL.__pga_aggregate_target=314572800
ORCL.__sga_target=759169024
ORCL.__shared_io_pool_size=33554432
ORCL.__shared_pool_size=230686720
ORCL.__streams_pool_size=0
ORCL.__unified_pga_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/ORCLDR/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/u01/app/oracle/oradata/ORCLDR/control01.ctl','/u01/app/oracle/fast_recovery_area/ORCLDR/control02.ctl'
*.db_block_size=8192
*.db_name='ORCL'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=8256m
*.db_unique_name='ORCLDR'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
*.fal_client='ORCLDR'
*.fal_server='ORCL'
*.local_listener='LISTENER_ORCL'
*.log_archive_config='dg_config=(ORCL,ORCLDR)'
*.log_archive_dest_1='location=/u01/archive valid_for=(all_logfiles,all_roles) db_unique_name=ORCLDR'
*.log_archive_dest_2='service=ORCL lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=ORCL'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.dbf'
*.log_archive_max_processes=10
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=300m
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=724m
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.db_file_name_convert='/u01/app/oracle/oradata/ORCL/','/u01/app/oracle/oradata/ORCLDR/'
*.log_file_name_convert='/u01/app/oracle/oradata/ORCL/','/u01/app/oracle/oradata/ORCLDR/'

Create required directories and Add /etc/oratab entry:

[oracle@standby ~]$ mkdir -p /u01/archive
[oracle@standby ~]$ mkdir -p /u01/app/oracle/oradata/ORCLDR/
[oracle@standby ~]$ mkdir -p /u01/app/oracle/admin/ORCLDR/adump/
[oracle@standby ~]$ mkdir -p /u01/app/oracle/fast_recovery_area/ORCLDR/
[oracle@standby ~]$ vi /etc/oratab
[oracle@standby ~]$
[oracle@standby ~]$ cat /etc/oratab
#



# This file is used by ORACLE utilities.  It is created by root.sh
# and updated by either Database Configuration Assistant while creating
# a database or ASM Configuration Assistant while creating ASM instance.

# A colon, ':', is used as the field terminator.  A new line terminates
# the entry.  Lines beginning with a pound sign, '#', are comments.
#
# Entries are of the form:
#   $ORACLE_SID:$ORACLE_HOME:<N|Y>:
#
# The first and second fields are the system identifier and home
# directory of the database respectively.  The third field indicates
# to the dbstart utility that the database should , "Y", or should not,
# "N", be brought up at system boot time.
#
# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
ORCL:/u01/app/oracle/product/19.0.0/dbhome_1:N

Verify connection “as sysdba” from Standby:

[oracle@standby ~]$ . oraenv
ORACLE_SID = [ORCL] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@standby ~]$
[oracle@standby ~]$ sqlplus sys/Welcome123@ORCLDR as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Mar 28 16:43:26 2021
Version 19.3.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> def
DEFINE _DATE           = "28-MAR-21" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "ORCLDR" (CHAR)
DEFINE _USER           = "SYS" (CHAR)
DEFINE _PRIVILEGE      = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1903000000" (CHAR)
DEFINE _EDITOR         = "vi" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0" (CHAR)
DEFINE _O_RELEASE      = "1903000000" (CHAR)
SQL>

Startup standby DB in Nomount stage using edited pfile and create spfile:

[oracle@standby ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Mar 28 16:29:21 2021
Version 19.3.0.0.0

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

Connected to an idle instance.

SQL> startup nomount pfile='/u01/app/oracle/product/19.0.0/dbhome_1/dbs/initORCL.ora';
ORACLE instance started.

Total System Global Area  759165480 bytes
Fixed Size                  8901160 bytes
Variable Size             234881024 bytes
Database Buffers          511705088 bytes
Redo Buffers                3678208 bytes
SQL> 
SQL> create spfile from pfile='/u01/app/oracle/product/19.0.0/dbhome_1/dbs/initORCL.ora';

File created.

SQL> shut immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL>
SQL> startup nomount;
ORACLE instance started.

Total System Global Area  759165480 bytes
Fixed Size                  8901160 bytes
Variable Size             234881024 bytes
Database Buffers          511705088 bytes
Redo Buffers                3678208 bytes
SQL>

Restore the Standby Controlfile and Mount the Database:

[oracle@standby ~]$ cd /u02/backups/
[oracle@standby backups]$ ls -ltrh
total 380M
-rw-r-----. 1 oracle oinstall  83M Mar 28 16:15 Archive_20210328_02vqsp4s_1_1
-rw-r-----. 1 oracle oinstall 274K Mar 28 16:15 Archive_20210328_04vqspe3_1_1
-rw-r-----. 1 oracle oinstall  11M Mar 28 16:15 Controlback_20210328_05vqspe5_1_1
-rw-r-----. 1 oracle oinstall 288M Mar 28 16:15 Fullback_20210328_03vqsp6l_1_1
[oracle@standby backups]$
[oracle@standby backups]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Sun Mar 28 16:47:31 2021
Version 19.3.0.0.0

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

connected to target database: ORCL (not mounted)

RMAN> restore standby controlfile from '/u02/backups/rman/Controlback_20210328_05vqspe5_1_1';

Starting restore at 28-MAR-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=423 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/u01/app/oracle/oradata/ORCLDR/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/ORCLDR/control02.ctl
Finished restore at 28-MAR-21

RMAN>

RMAN> sql 'alter database mount standby database';

sql statement: alter database mount standby database
released channel: ORA_DISK_1

RMAN>

Catalog all the backup pieces and run report schema:

RMAN> catalog start with '/u02/backups/';

using target database control file instead of recovery catalog
searching for all files that match the pattern /u02/backups/

List of Files Unknown to the Database
=====================================
File Name: /u02/backups/Archive_20210328_02vqsp4s_1_1
File Name: /u02/backups/Archive_20210328_04vqspe3_1_1
File Name: /u02/backups/Controlback_20210328_05vqspe5_1_1
File Name: /u02/backups/Fullback_20210328_03vqsp6l_1_1

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

List of Cataloged Files
=======================
File Name: /u02/backups/Archive_20210328_02vqsp4s_1_1
File Name: /u02/backups/Archive_20210328_04vqspe3_1_1
File Name: /u02/backups/Controlback_20210328_05vqspe5_1_1
File Name: /u02/backups/Fullback_20210328_03vqsp6l_1_1

RMAN> report schema;

RMAN-06139: warning: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name ORCLDR

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    0        SYSTEM               ***     /u01/app/oracle/oradata/ORCLDR/system01.dbf
3    0        SYSAUX               ***     /u01/app/oracle/oradata/ORCLDR/sysaux01.dbf
4    0        UNDOTBS1             ***     /u01/app/oracle/oradata/ORCLDR/undotbs01.dbf
7    0        USERS                ***     /u01/app/oracle/oradata/ORCLDR/users01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767       /u01/app/oracle/oradata/ORCLDR/temp01.dbf

RMAN>

Restore and Recover Database:

RMAN> list backup of archivelog all;

using target database control file instead of recovery catalog

List of Backup Sets
===================


BS Key  Size
------- ----------
2       82.42M

  List of Archived Logs in backup set 2
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    5       2018505    21-MAR-21 2142446    28-MAR-21
  1    6       2142446    28-MAR-21 2167825    28-MAR-21
  1    7       2167825    28-MAR-21 2197426    28-MAR-21
  1    8       2197426    28-MAR-21 2200441    28-MAR-21

  Backup Set Copy #1 of backup set 2
  Device Type Elapsed Time Completion Time Compressed Tag
  ----------- ------------ --------------- ---------- ---
  DISK        00:00:51     28-MAR-21       YES        TAG20210328T160028

    List of Backup Pieces for backup set 2 Copy #1
    BP Key  Pc# Status      Piece Name
    ------- --- ----------- ----------
    2       1   AVAILABLE   /u02/backup/rman/Archive_20210328_02vqsp4s_1_1

  Backup Set Copy #2 of backup set 2
  Device Type Elapsed Time Completion Time Compressed Tag
  ----------- ------------ --------------- ---------- ---
  DISK        00:00:51     28-MAR-21       YES        TAG20210328T160028

    List of Backup Pieces for backup set 2 Copy #2
    BP Key  Pc# Status      Piece Name
    ------- --- ----------- ----------
    5       1   AVAILABLE   /u02/backups/Archive_20210328_02vqsp4s_1_1

BS Key  Size
------- ----------
4       273.50K

  List of Archived Logs in backup set 4
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    9       2200441    28-MAR-21 2201297    28-MAR-21

  Backup Set Copy #1 of backup set 4
  Device Type Elapsed Time Completion Time Compressed Tag
  ----------- ------------ --------------- ---------- ---
  DISK        00:00:00     28-MAR-21       YES        TAG20210328T160523

    List of Backup Pieces for backup set 4 Copy #1
    BP Key  Pc# Status      Piece Name
    ------- --- ----------- ----------
    4       1   AVAILABLE   /u02/backup/rman/Archive_20210328_04vqspe3_1_1

  Backup Set Copy #2 of backup set 4
  Device Type Elapsed Time Completion Time Compressed Tag
  ----------- ------------ --------------- ---------- ---
  DISK        00:00:00     28-MAR-21       YES        TAG20210328T160523

    List of Backup Pieces for backup set 4 Copy #2
    BP Key  Pc# Status      Piece Name
    ------- --- ----------- ----------
    6       1   AVAILABLE   /u02/backups/Archive_20210328_04vqspe3_1_1

RMAN>

RMAN> run
{
ALLOCATE CHANNEL c1 DEVICE TYPE DISK;
restore database;
switch datafile all;
recover database;
}
2> 3> 4> 5> 6> 7>
allocated channel: c1
channel c1: SID=440 device type=DISK

Starting restore at 28-MAR-21

channel c1: starting datafile backup set restore
channel c1: specifying datafile(s) to restore from backup set
channel c1: restoring datafile 00001 to /u01/app/oracle/oradata/ORCLDR/system01.dbf
channel c1: restoring datafile 00003 to /u01/app/oracle/oradata/ORCLDR/sysaux01.dbf
channel c1: restoring datafile 00004 to /u01/app/oracle/oradata/ORCLDR/undotbs01.dbf
channel c1: restoring datafile 00007 to /u01/app/oracle/oradata/ORCLDR/users01.dbf
channel c1: reading from backup piece /u02/backup/rman/Fullback_20210328_03vqsp6l_1_1
channel c1: errors found reading piece handle=/u02/backup/rman/Fullback_20210328_03vqsp6l_1_1
channel c1: failover to piece handle=/u02/backups/Fullback_20210328_03vqsp6l_1_1 tag=TAG20210328T160124
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:03:06
Finished restore at 28-MAR-21


Starting recover at 28-MAR-21

starting media recovery

channel c1: starting archived log restore to default destination
channel c1: restoring archived log
archived log thread=1 sequence=9
channel c1: reading from backup piece /u02/backup/rman/Archive_20210328_04vqspe3_1_1
channel c1: errors found reading piece handle=/u02/backup/rman/Archive_20210328_04vqspe3_1_1
channel c1: failover to piece handle=/u02/backups/Archive_20210328_04vqspe3_1_1 tag=TAG20210328T160523
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch1_9_1067780986.dbf thread=1 sequence=9
media recovery complete, elapsed time: 00:00:02
Finished recover at 28-MAR-21
released channel: c1

RMAN>

Check & Verify Standby redo logs:

SQL> set lines 400 pages 4000
SQL> SELECT NAME,OPEN_MODE,DB_UNIQUE_NAME,DATABASE_ROLE,PROTECTION_MODE FROM V$DATABASE;

NAME      OPEN_MODE            DB_UNIQUE_NAME                 DATABASE_ROLE    PROTECTION_MODE
--------- -------------------- ------------------------------ ---------------- --------------------
ORCL      MOUNTED              ORCLDR                         PHYSICAL STANDBY MAXIMUM PERFORMANCE

SQL> col member for a50
SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                             IS_     CON_ID
---------- ------- ------- -------------------------------------------------- --- ----------
         3         ONLINE  /u01/app/oracle/oradata/ORCLDR/redo03.log          NO           0
         2         ONLINE  /u01/app/oracle/oradata/ORCLDR/redo02.log          NO           0
         1         ONLINE  /u01/app/oracle/oradata/ORCLDR/redo01.log          NO           0
         4         STANDBY /u01/app/oracle/oradata/ORCLDR/stdy_redo04.log     NO           0
         5         STANDBY /u01/app/oracle/oradata/ORCLDR/stdy_redo05.log     NO           0
         6         STANDBY /u01/app/oracle/oradata/ORCLDR/stdy_redo06.log     NO           0
         7         STANDBY /u01/app/oracle/oradata/ORCLDR/stdy_redo07.log     NO           0

7 rows selected.

SQL> select a.group#, a.member, b.bytes/1024/1024 "Size In MB" FROM v$logfile a, v$standby_log b WHERE a.group# = b.group#;

    GROUP# MEMBER                                             Size In MB
---------- -------------------------------------------------- ----------
         4 /u01/app/oracle/oradata/ORCLDR/stdy_redo04.log            200
         5 /u01/app/oracle/oradata/ORCLDR/stdy_redo05.log            200
         6 /u01/app/oracle/oradata/ORCLDR/stdy_redo06.log            200
         7 /u01/app/oracle/oradata/ORCLDR/stdy_redo07.log            200

SQL>

Enable MRP on standby and check MRP status:

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

Database altered.

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

PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH      CONNECTED             0          0          0          0
DGRD      ALLOCATED             0          0          0          0
DGRD      ALLOCATED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
RFS       IDLE                  1          0          0          0
RFS       IDLE                  1         53         27          1
MRP0      WAIT_FOR_LOG          1         53          0          0
RFS       IDLE                  0          0          0          0
RFS       IDLE                  0          0          0          0
RFS       IDLE                  0          0          0          0
RFS       IDLE                  0          0          0          0
RFS       IDLE                  0          0          0          0
RFS       IDLE                  0          0          0          0
RFS       IDLE                  0          0          0          0

22 rows selected.

SQL>

Check & Verify sync status:

On Primary Database :

SQL> set lines 400 pages 400
SQL> SELECT NAME,OPEN_MODE,DB_UNIQUE_NAME,DATABASE_ROLE,PROTECTION_MODE, INSTANCE_NAME FROM V$DATABASE,v$INSTANCE;

NAME      OPEN_MODE            DB_UNIQUE_NAME                 DATABASE_ROLE    PROTECTION_MODE      INSTANCE_NAME
--------- -------------------- ------------------------------ ---------------- -------------------- ----------------
ORCL      READ WRITE           ORCL                           PRIMARY          MAXIMUM PERFORMANCE  ORCL

SQL>
SQL> select max(sequence#) from v$archived_log where applied='YES';

MAX(SEQUENCE#)
--------------
            52

SQL>

On Standby Database :

SQL> set lines 400 pages 400
SQL> SELECT NAME,OPEN_MODE,DB_UNIQUE_NAME,DATABASE_ROLE,PROTECTION_MODE, INSTANCE_NAME FROM V$DATABASE,v$INSTANCE;

NAME      OPEN_MODE            DB_UNIQUE_NAME                 DATABASE_ROLE    PROTECTION_MODE      INSTANCE_NAME
--------- -------------------- ------------------------------ ---------------- -------------------- ----------------
ORCL      MOUNTED              ORCLDR                         PHYSICAL STANDBY MAXIMUM PERFORMANCE  ORCL

SQL>
SQL> select max(sequence#) from v$archived_log where applied='YES';

MAX(SEQUENCE#)
--------------
            52

SQL>

Let’s test for Active Dataguard :

Active Data guard: The main feature of Oracle Active Data Guard that it permits read-only access to a physical standby database for queries, sorting, reporting, web-based access, etc., whereas endlessly applying changes received from the production/primary database.