Standby Database set up || Oracle Data Guard (ODG)

• Introduction: Oracle Data Guard (ODG) is a high-availability, disaster recovery, and data protection feature or solution provided by Oracle Database. It ensures the availability of critical data by maintaining one or more synchronized standby databases as replicas of a primary database. These standby databases can be used for disaster recovery, reporting, or backups, minimizing downtime and data loss in the event of unexpected failures. So here we will do Standby database set up in an easy and step by step manner.
  
  
• Prerequisites:

o Primary server with Oracle database Software installed and running database.
o Standby server with Oracle database Software installed.
o Connectivity between Primary and Standby server.


• Environment:
  

Server	Primary	Standby
Hostname	Source	Target
IP	192.168.80.51	192.168.80.111
OS	OEL 9	OEL 9
SID	ORCLDC	ORCLDR
Service Name	ORCLDC	ORCLDR



• Now start configuring the ODG:
  
  

1. Connectivity Test

==> From Source to Target:
[oracle@source ~]$ ping -c 3 target.localdomain
PING target.localdomain (192.168.80.111) 56(84) bytes of data.
64 bytes from target.localdomain (192.168.80.111): icmp_seq=1 ttl=64 time=0.678 ms
64 bytes from target.localdomain (192.168.80.111): icmp_seq=2 ttl=64 time=0.456 ms
64 bytes from target.localdomain (192.168.80.111): icmp_seq=3 ttl=64 time=0.924 ms
--- target.localdomain ping statistics ---
3 packets transmitted, 3 received, 0% packet loss, time 2107ms
rtt min/avg/max/mdev = 0.456/0.686/0.924/0.191 ms

==> From Target to Source:

[oracle@target ~]$ ping -c 3 source.localdomain
PING source.localdomain (192.168.80.51) 56(84) bytes of data.
64 bytes from source.localdomain (192.168.80.51): icmp_seq=1 ttl=64 time=0.690 ms
64 bytes from source.localdomain (192.168.80.51): icmp_seq=2 ttl=64 time=6.61 ms
64 bytes from source.localdomain (192.168.80.51): icmp_seq=3 ttl=64 time=0.315 ms
--- source.localdomain ping statistics ---
3 packets transmitted, 3 received, 0% packet loss, time 2002ms
rtt min/avg/max/mdev = 0.315/2.536/6.605/2.880 ms

2. Primary Database setup
First fetch some details about Primary Database:

[oracle@source ~]$ sqlplus / as sysdba

SQL> def
DEFINE _DATE              = "22-JAN-25" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "orcldc" (CHAR)
DEFINE _USER              = "SYS" (CHAR)
DEFINE _PRIVILEGE         = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE   = "1924000000" (CHAR)
DEFINE _EDITOR            = "vi" (CHAR)
DEFINE _O_VERSION         = "Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.24.0.0.0" (CHAR)
DEFINE _O_RELEASE         = "1924000000" (CHAR)

SQL> set lines 200 pages 1000
col open_mode for a15
col HOST_NAME for a15

NAME      DATABASE_ROLE    OPEN_MODE       LOG_MODE
--------  ---------------- --------------- ------------
ORCL      PRIMARY          READ WRITE      ARCHIVELOG

SQL> select banner, banner_full from v$version;

BANNER
--------------------------------------------------------------------------------
BANNER_FULL
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.24.0.0.0

SQL> col name for a70
SQL> select file#, name from v$datafile;

FILE# NAME
----- ----------------------------------------------------------------------
1     /u01/app/oracle/oradata/ORCL/system01.dbf
3     /u01/app/oracle/oradata/ORCL/sysaux01.dbf
4     /u01/app/oracle/oradata/ORCL/undotbs01.dbf
5     /u01/app/oracle/oradata/ORCL/pdbseed/system01.dbf
6     /u01/app/oracle/oradata/ORCL/pdbseed/sysaux01.dbf
7     /u01/app/oracle/oradata/ORCL/users01.dbf
8     /u01/app/oracle/oradata/ORCL/pdbseed/undotbs01.dbf
9     /u01/app/oracle/oradata/ORCL/orclpdb/system01.dbf
10    /u01/app/oracle/oradata/ORCL/orclpdb/sysaux01.dbf
11    /u01/app/oracle/oradata/ORCL/orclpdb/undotbs01.dbf
12    /u01/app/oracle/oradata/ORCL/orclpdb/users01.dbf

11 rows selected.

SQL> select name from v$controlfile;

NAME
----------------------------------------------------------------------
/u01/app/oracle/oradata/ORCL/control01.ctl
/u01/app/oracle/fast_recovery_area/ORCL/control02.ctl

SQL> col MEMBER for a40
SQL> select group#, type, MEMBER from v$logfile order by group#;

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

** If database is in NOARCHIVELOG mode then do below steps:

ALTER SYSTEM SET db_recovery_file_dest='/u01/app/oracle/fast_recovery_area' SCOPE=BOTH;
ALTER SYSTEM SET db_recovery_file_dest_size=50G SCOPE=BOTH;
SHUT IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

Now let's do changes in Primary Database for ODG:

SQL> SELECT name, force_logging, log_mode FROM v$database;
NAME    FORCE_LOGGING  LOG_MODE
------- -------------- ------------
ORCL    NO             ARCHIVELOG

SQL> ALTER DATABASE FORCE LOGGING;
Database altered.

SQL> SELECT name, force_logging, log_mode FROM v$database;
NAME    FORCE_LOGGING  LOG_MODE
------- -------------- ------------
ORCL    YES            ARCHIVELOG

SQL> SHOW PARAMETER db_name;
NAME        TYPE    VALUE
----------- ------- ------------------------------
db_name     string  orcl

SQL> SHOW PARAMETER db_unique_name;
NAME             TYPE    VALUE
---------------- ------- ------------------------------
db_unique_name   string  ORCLDC

SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORCLDC,ORCLDR)';
System altered.

SQL> SHOW PARAMETER LOG_ARCHIVE_CONFIG;
NAME                TYPE    VALUE
------------------- ------- ------------------------------
log_archive_config  string  DG_CONFIG=(ORCLDC,ORCLDR)

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=ORCLDR NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCLDR';
System altered.

SQL> SHOW PARAMETER LOG_ARCHIVE_DEST_2;
NAME                  TYPE    VALUE
--------------------- ------- -------------------------------------------------------
log_archive_dest_2    string  SERVICE=ORCLDR NOAFFIRM ASYNC
                               VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
                               DB_UNIQUE_NAME=ORCLDR

log_archive_dest_20   string
log_archive_dest_21   string
log_archive_dest_22   string
log_archive_dest_23   string
log_archive_dest_24   string
log_archive_dest_25   string
log_archive_dest_26   string
log_archive_dest_27   string
log_archive_dest_28   string
log_archive_dest_29   string

SQL> SHOW PARAMETER LOG_ARCHIVE_FORMAT;
NAME                  TYPE    VALUE
--------------------- ------- ------------------------------
log_archive_format    string  %t_%s_%r.dbf

SQL> ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE;
System altered.

SQL> SHOW PARAMETER LOG_ARCHIVE_FORMAT;
NAME                  TYPE    VALUE
--------------------- ------- ------------------------------
log_archive_format    string  %t_%s_%r.dbf

SQL> ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30 SCOPE=SPFILE;
System altered.

SQL> ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;
System altered.

SQL> ALTER SYSTEM SET FAL_SERVER=ORCLDR;
System altered.

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
System altered.

SQL> ALTER SYSTEM SET archive_lag_target=900;
System altered.

Now we need to set DB_FILE_NAME_CONVERT & LOG_FILE_NAME_CONVERT parameter so that datafile and logfile locations will be converted automatically from Primary to Standby and vice-versa.

SQL> ALTER SYSTEM SET DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/ORCLDR','/u01/app/oracle/oradata/ORCL' SCOPE=SPFILE;
System altered.

SQL> ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/ORCLDR','/u01/app/oracle/oradata/ORCL' SCOPE=SPFILE;
System altered.

Now just take a bounce of Database so that all parameter will get reflects.

SQL> SHUT IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> STARTUP;
ORACLE instance started.
Total System Global Area     1459616616 bytes
Fixed Size                      9177960 bytes
Variable Size                 905969664 bytes
Database Buffers              536870912 bytes
Redo Buffers                    7598080 bytes
Database mounted.
Database opened.

SQL> SHOW PDBS;
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORCLPDB                        READ WRITE NO

SQL> SHOW PARAMETER LOG_ARCHIVE_FORMAT;
NAME                  TYPE     VALUE
--------------------- -------- ------------------------------
log_archive_format    string   %t_%s_%r.arc

SQL> SHOW PARAMETER LOG_ARCHIVE_MAX_PROCESSES;
NAME                       TYPE     VALUE
-------------------------- -------- ------------------------------
log_archive_max_processes  integer  30

SQL> SHOW PARAMETER REMOTE_LOGIN_PASSWORDFILE;
NAME                        TYPE     VALUE
--------------------------- -------- ------------------------------
remote_login_passwordfile   string   EXCLUSIVE

Now add standby redo logfiles which will be used to apply the changes whenever primary becomes standby. Number of Standby logs should be one more than the number of Online logs.

SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/ORCL/standby_redo01.log') SIZE 200M;
Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/ORCL/standby_redo02.log') SIZE 200M;
Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/ORCL/standby_redo03.log') SIZE 200M;
Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/ORCL/standby_redo04.log') SIZE 200M;
Database altered.

SQL> SELECT group#, type, member FROM v$logfile ORDER BY group#;
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/standby_redo01.log
5       STANDBY  /u01/app/oracle/oradata/ORCL/standby_redo02.log
6       STANDBY  /u01/app/oracle/oradata/ORCL/standby_redo03.log
7       STANDBY  /u01/app/oracle/oradata/ORCL/standby_redo04.log

7 rows selected.

3. Primary Service setup

[oracle@source ~]$ cd $ORACLE_HOME/network/admin
[oracle@source 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.

ORCLDC =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = source.localdomain)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcldc)
    )
  )

ORCLDR =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = target.localdomain)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcldr)
    )
  )

4.Primary Database and files backup
Create pfile:

SQL> create pfile='/u01/app/oracle/rmandc/initorcldc.ora' from spfile;
  

Copy password file:

[oracle@source rmandc]$ cd $ORACLE_HOME/dbs
[oracle@source dbs]$ cp orapworcldc /u01/app/oracle/rmandc/
 
  

Take full database backup using RMAN:

RMAN> run
{
  allocate channel ch1 device type disk;
  allocate channel ch2 device type disk;
  backup as compressed backupset database format '/u01/app/oracle/rmandc/Fullback_%T_%U';
  backup as compressed backupset archivelog all format '/u01/app/oracle/rmandc/Archive_%T_%U';
  backup current controlfile for standby format '/u01/app/oracle/rmandc/StbyControlback_%T_%U';
  release channel ch1;
  release channel ch2;
}
using target database control file instead of recovery catalog
allocated channel: ch1
channel ch1: SID=299 device type=DISK
allocated channel: ch2
channel ch2: SID=55 device type=DISK
Starting backup at 22-JAN-25
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=00004 name=/u01/app/oracle/oradata/ORCL/undotbs01.dbf
channel ch1: starting piece 1 at 22-JAN-25
channel ch2: starting compressed full datafile backup set
channel ch2: specifying datafile(s) in backup set
input datafile file number=00003 name=/u01/app/oracle/oradata/ORCL/sysaux01.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/ORCL/users01.dbf
channel ch2: starting piece 1 at 22-JAN-25
channel ch2: finished piece 1 at 22-JAN-25
piece handle=/u01/app/oracle/rmandc/Fullback_20250122_353fsduj_101_1_1 tag=TAG20250122T113235 comment=NONE
channel ch2: backup set complete, elapsed time: 00:01:36
channel ch2: starting compressed full datafile backup set
channel ch2: specifying datafile(s) in backup set
input datafile file number=00009 name=/u01/app/oracle/oradata/ORCL/orclpdb/system01.dbf
input datafile file number=00011 name=/u01/app/oracle/oradata/ORCL/orclpdb/undotbs01.dbf
channel ch2: starting piece 1 at 22-JAN-25
channel ch1: finished piece 1 at 22-JAN-25
piece handle=/u01/app/oracle/rmandc/Fullback_20250122_343fsduj_100_1_1 tag=TAG20250122T113235 comment=NONE
channel ch1: backup set complete, elapsed time: 00:02:54
channel ch1: starting compressed full datafile backup set
channel ch1: specifying datafile(s) in backup set
input datafile file number=00006 name=/u01/app/oracle/oradata/ORCL/pdbseed/sysaux01.dbf
input datafile file number=00008 name=/u01/app/oracle/oradata/ORCL/pdbseed/undotbs01.dbf
channel ch1: starting piece 1 at 22-JAN-25
channel ch2: finished piece 1 at 22-JAN-25
piece handle=/u01/app/oracle/rmandc/Fullback_20250122_363fse1l_102_1_1 tag=TAG20250122T113235 comment=NONE
channel ch2: backup set complete, elapsed time: 00:01:17
channel ch2: starting compressed full datafile backup set
channel ch2: specifying datafile(s) in backup set
input datafile file number=00005 name=/u01/app/oracle/oradata/ORCL/pdbseed/system01.dbf
channel ch2: starting piece 1 at 22-JAN-25
channel ch1: finished piece 1 at 22-JAN-25
piece handle=/u01/app/oracle/rmandc/Fullback_20250122_373fse42_103_1_1 tag=TAG20250122T113235 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:45
channel ch1: starting compressed full datafile backup set
channel ch1: specifying datafile(s) in backup set
input datafile file number=00010 name=/u01/app/oracle/oradata/ORCL/orclpdb/sysaux01.dbf
input datafile file number=00012 name=/u01/app/oracle/oradata/ORCL/orclpdb/users01.dbf
channel ch1: starting piece 1 at 22-JAN-25
channel ch2: finished piece 1 at 22-JAN-25
piece handle=/u01/app/oracle/rmandc/Fullback_20250122_383fse42_104_1_1 tag=TAG20250122T113235 comment=NONE
channel ch2: backup set complete, elapsed time: 00:01:01
channel ch1: finished piece 1 at 22-JAN-25
piece handle=/u01/app/oracle/rmandc/Fullback_20250122_393fse5g_105_1_1 tag=TAG20250122T113235 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:25
Finished backup at 22-JAN-25
Starting backup at 22-JAN-25
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=14 RECID=1 STAMP=1191028331
input archived log thread=1 sequence=15 RECID=2 STAMP=1191064037
channel ch1: starting piece 1 at 22-JAN-25
channel ch2: starting compressed archived log backup set
channel ch2: specifying archived log(s) in backup set
input archived log thread=1 sequence=16 RECID=3 STAMP=1191064563
input archived log thread=1 sequence=17 RECID=4 STAMP=1191065457
input archived log thread=1 sequence=18 RECID=5 STAMP=1191065813
channel ch2: starting piece 1 at 22-JAN-25
channel ch2: finished piece 1 at 22-JAN-25
piece handle=/u01/app/oracle/rmandc/Archive_20250122_3b3fse6m_107_1_1 tag=TAG20250122T113653 comment=NONE
channel ch2: backup set complete, elapsed time: 00:00:15
channel ch1: finished piece 1 at 22-JAN-25
piece handle=/u01/app/oracle/rmandc/Archive_20250122_3a3fse6m_106_1_1 tag=TAG20250122T113653 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:25
Finished backup at 22-JAN-25
Starting backup at 22-JAN-25
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 22-JAN-25
channel ch1: finished piece 1 at 22-JAN-25
piece handle=/u01/app/oracle/rmandc/StbyControlback_20250122_3c3fse7h_108_1_1 tag=TAG20250122T113721 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:03
Finished backup at 22-JAN-25
Starting Control File and SPFILE Autobackup at 22-JAN-25
piece handle=/u01/app/oracle/fast_recovery_area/ORCLDC/autobackup/2025_01_22/o1_mf_s_1191065849_ms12s1os_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 22-JAN-25
released channel: ch1
released channel: ch2
RMAN> exit

5.Transfer backup and other files from Primary to Standby

[oracle@source dbs]$ cd /u01/app/oracle/rmandc/
[oracle@source rmandc]$ ls -lrth
total 1.3G
-rw-r--r--. 1 oracle oinstall 1.6K Jan 22 11:28 initorcldc.ora
-rw-r-----. 1 oracle oinstall  94M Jan 22 11:33 Fullback_20250122_353fsduj_101_1_1
-rw-r-----. 1 oracle oinstall 409M Jan 22 11:35 Fullback_20250122_343fsduj_100_1_1
-rw-r-----. 1 oracle oinstall 237M Jan 22 11:35 Fullback_20250122_363fse1l_102_1_1
-rw-r-----. 1 oracle oinstall 134M Jan 22 11:36 Fullback_20250122_373fse42_103_1_1
-rw-r-----. 1 oracle oinstall 227M Jan 22 11:36 Fullback_20250122_383fse42_104_1_1
-rw-r-----. 1 oracle oinstall  64M Jan 22 11:36 Fullback_20250122_393fse5g_105_1_1
-rw-r-----. 1 oracle oinstall  49M Jan 22 11:37 Archive_20250122_3b3fse6m_107_1_1
-rw-r-----. 1 oracle oinstall  69M Jan 22 11:37 Archive_20250122_3a3fse6m_106_1_1
-rw-r-----. 1 oracle oinstall  18M Jan 22 11:37 StbyControlback_20250122_3c3fse7h_108_1_1
-rw-r-----. 1 oracle oinstall 2.0K Jan 22 11:39 orapworcldc
[oracle@source rmandc]$ scp * oracle@target:/u01/app/oracle/rmandr
The authenticity of host 'target (192.168.80.111)' can't be established.
ED25519 key fingerprint is SHA256:u1O6svnS8kY1i6Mv88TDIlYsvqDyISi2Uz3ZBc8kKfY.
This key is not known by any other names
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added 'target' (ED25519) to the list of known hosts.
oracle@target's password:
Archive_20250122_3a3fse6m_106_1_1             100%   68MB    58.5MB/s   00:01
Archive_20250122_3b3fse6m_107_1_1             100%   49MB    52.0MB/s   00:00
Fullback_20250122_343fsduj_100_1_1            100%  409MB    24.7MB/s   00:16
Fullback_20250122_353fsduj_101_1_1            100%   93MB    20.9MB/s   00:04
Fullback_20250122_363fse1l_102_1_1            100%  237MB    25.4MB/s   00:09
Fullback_20250122_373fse42_103_1_1            100%  134MB    21.0MB/s   00:06
Fullback_20250122_383fse42_104_1_1            100%  226MB    25.7MB/s   00:08
Fullback_20250122_393fse5g_105_1_1            100%   63MB    33.5MB/s   00:01
initorcldc.ora                                100%   1632    52.9KB/s   00:00
orapworcldc                                   100%   2048   105.3KB/s   00:00
StbyControlback_20250122_3c3fse7h_108_1_1     100%   18MB    57.8MB/s   00:00
   

6.Standby Database setup
Do the changes in Primary pfile and create pfile initorcldr.ora for Standby. Below parameters have been modified. Sample file is shown further below:

*.audit_file_dest='/u01/app/oracle/admin/orcldr/adump'
*.control_files='/u01/app/oracle/oradata/ORCLDR/control01.ctl','/u01/app/oracle/fast_recovery_area/ORCLDR/control02.ctl'
*.db_file_name_convert='/u01/app/oracle/oradata/ORCL','/u01/app/oracle/oradata/ORCLDR'
*.db_unique_name='ORCLDR'
*.log_file_name_convert='/u01/app/oracle/oradata/ORCL','/u01/app/oracle/oradata/ORCLDR'
*.fal_server='ORCLDC'
*.log_archive_dest_2='SERVICE=ORCLDC NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCLDC'

orcldr.__data_transfer_cache_size=0
orcldr.__db_cache_size=486539264
orcldr.__inmemory_ext_roarea=0
orcldr.__inmemory_ext_rwarea=0
orcldr.__java_pool_size=16777216
orcldr.__large_pool_size=16777216
orcldr.__oracle_base='/u01/app/oracle'  # ORACLE_BASE set from environment
orcldr.__pga_aggregate_target=587202560
orcldr.__sga_target=872415232
orcldr.__shared_io_pool_size=50331648
orcldr.__shared_pool_size=285212672
orcldr.__streams_pool_size=0
orcldr.__unified_pga_pool_size=0
*.archive_lag_target=900
*.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_file_name_convert='/u01/app/oracle/oradata/ORCL','/u01/app/oracle/oradata/ORCLDR'
*.db_name='orcl'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=8192m
*.db_unique_name='ORCLDR'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orcldcXDB)'
*.enable_pluggable_database=true
*.fal_server='ORCLDC'
*.log_archive_config='DG_CONFIG=(ORCLDC,ORCLDR)'
*.log_archive_dest_2='SERVICE=ORCLDC NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCLDC'
*.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=30
*.log_file_name_convert='/u01/app/oracle/oradata/ORCL','/u01/app/oracle/oradata/ORCLDR'
*.memory_target=1384m
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'

Create required directories:

[oracle@target rmandr]$ mkdir -p /u01/app/oracle/admin/orcldr/adump
[oracle@target rmandr]$ mkdir -p /u01/app/oracle/oradata/ORCLDR/
[oracle@target rmandr]$ mkdir -p /u01/app/oracle/fast_recovery_area/ORCLDR/

Copy password file in $ORACLE_HOME/dbs :

[oracle@target rmandr]$ cp orapworcldc $ORACLE_HOME/dbs/orapworcldr
[oracle@target rmandr]$ ls -lrth $ORACLE_HOME/dbs/orapworcldr
-rw-r-----. 1 oracle oinstall 2.0K Jan 22 12:02 /u01/app/oracle/product/19.0.0/dbhome_1/dbs/orapworcldr

Create listener using NETCA. Start it. Also add TNS service entries for Primary & Standby in tnsnames.ora file.

Refer Create listener using NETCA

[oracle@target ~]$ lsnrctl start
Copyright (c) 1991, 2024, 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/target/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=target.localdomain)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=target.localdomain)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                22-JAN-2025 12:09:54
Uptime                    0 days 0 hr. 0 min. 2 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/target/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=target.localdomain)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully
[oracle@target ~]$
[oracle@target 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.

ORCLDC =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = source.localdomain)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcldc)
    )
  )

ORCLDR =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = target.localdomain)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcldr)
    )
  )

7.Startup Standby instance and restore the backup
Startup in nomount:

[oracle@target ~]$ . oraenv
ORACLE_SID = [orcl] ? orcldr
ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/product/19.0.0/dbhome_1
The Oracle base remains unchanged with value /u01/app/oracle

[oracle@target ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jan 22 12:13:03 2025
Version 19.24.0.0.0

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

Connected to an idle instance.
SQL> startup nomount pfile='/u01/app/oracle/rmandr/initorcldr.ora';
ORACLE instance started.

Total System Global Area 1459616616 bytes
Fixed Size                  9177960 bytes
Variable Size             905969664 bytes
Database Buffers          536870912 bytes
Redo Buffers                7598080 bytes
SQL>

SQL> def
DEFINE _DATE           = "22-JAN-25" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "orcldr" (CHAR)
DEFINE _USER           = "SYS" (CHAR)
DEFINE _PRIVILEGE      = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1924000000" (CHAR)
DEFINE _EDITOR         = "vi" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.24.0.0.0" (CHAR)
DEFINE _O_RELEASE      = "1924000000" (CHAR)
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.24.0.0.0
[oracle@target ~]$

Restore the controlfile from backup. Then mount the standby database instance:

[oracle@target ~]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Wed Jan 22 12:14:09 2025
Version 19.24.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (not mounted)

RMAN> restore controlfile from '/u01/app/oracle/rmandr/StbyControlback_20250122_3c3fse7h_108_1_1';
Starting restore at 22-JAN-25
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
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 22-JAN-25

RMAN> alter database mount;
released channel: ORA_DISK_1
Statement processed

RMAN>

Login into database and verify:

[oracle@target ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jan 22 12:15:19 2025
Version 19.24.0.0.0
Copyright (c) 1982, 2024, Oracle.  All rights reserved.

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

SQL> select NAME,DATABASE_ROLE,OPEN_MODE,LOG_MODE from v$database;

NAME      DATABASE_ROLE    OPEN_MODE            LOG_MODE
--------- ---------------- -------------------- ------------
ORCL      PHYSICAL STANDBY MOUNTED              ARCHIVELOG

SQL> show parameter db_unique_name

NAME            TYPE        VALUE
--------------- ----------- ------------
db_unique_name  string      ORCLDR

Now catalog the backup files and start the database restoration:

[oracle@target rmandr]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Wed Jan 22 12:18:34 2025
Version 19.24.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1718803653, not open)

RMAN> 
RMAN> catalog start with '/u01/app/oracle/rmandr';
using target database control file instead of recovery catalog
searching for all files that match the pattern /u01/app/oracle/rmandr

List of Files Unknown to the Database
=====================================
File Name: /u01/app/oracle/rmandr/Archive_20250122_3a3fse6m_106_1_1
File Name: /u01/app/oracle/rmandr/Archive_20250122_3b3fse6m_107_1_1
File Name: /u01/app/oracle/rmandr/Fullback_20250122_343fsduj_100_1_1
File Name: /u01/app/oracle/rmandr/Fullback_20250122_353fsduj_101_1_1
File Name: /u01/app/oracle/rmandr/Fullback_20250122_363fse1l_102_1_1
File Name: /u01/app/oracle/rmandr/Fullback_20250122_373fse42_103_1_1
File Name: /u01/app/oracle/rmandr/Fullback_20250122_383fse42_104_1_1
File Name: /u01/app/oracle/rmandr/Fullback_20250122_393fse5g_105_1_1
File Name: /u01/app/oracle/rmandr/StbyControlback_20250122_3c3fse7h_108_1_1
File Name: /u01/app/oracle/rmandr/initorcldr.ora

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/rmandr/Archive_20250122_3a3fse6m_106_1_1
File Name: /u01/app/oracle/rmandr/Archive_20250122_3b3fse6m_107_1_1
File Name: /u01/app/oracle/rmandr/Fullback_20250122_343fsduj_100_1_1
File Name: /u01/app/oracle/rmandr/Fullback_20250122_353fsduj_101_1_1
File Name: /u01/app/oracle/rmandr/Fullback_20250122_363fse1l_102_1_1
File Name: /u01/app/oracle/rmandr/Fullback_20250122_373fse42_103_1_1
File Name: /u01/app/oracle/rmandr/Fullback_20250122_383fse42_104_1_1
File Name: /u01/app/oracle/rmandr/Fullback_20250122_393fse5g_105_1_1
File Name: /u01/app/oracle/rmandr/StbyControlback_20250122_3c3fse7h_108_1_1

List of Files Which Were Not Cataloged
=======================================
File Name: /u01/app/oracle/rmandr/initorcldr.ora
RMAN-07517: Reason: The file header is corrupted

RMAN> run
{
allocate channel ch1 device type disk;
allocate channel ch2 device type disk;
restore database;
switch datafile all;
switch tempfile all;
recover database;
release channel ch1;
release channel ch2;
}

allocated channel: ch1
channel ch1: SID=41 device type=DISK
allocated channel: ch2
channel ch2: SID=276 device type=DISK

Starting restore at 22-JAN-25
channel ch1: starting datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
channel ch1: restoring datafile 00001 to /u01/app/oracle/oradata/ORCLDR/system01.dbf
channel ch1: restoring datafile 00004 to /u01/app/oracle/oradata/ORCLDR/undotbs01.dbf
channel ch1: reading from backup piece /u01/app/oracle/rmandr/Fullback_20250122_343fsduj_100_1_1

channel ch2: starting datafile backup set restore
channel ch2: specifying datafile(s) to restore from backup set
channel ch2: restoring datafile 00003 to /u01/app/oracle/oradata/ORCLDR/sysaux01.dbf
channel ch2: restoring datafile 00007 to /u01/app/oracle/oradata/ORCLDR/users01.dbf
channel ch2: reading from backup piece /u01/app/oracle/rmandr/Fullback_20250122_353fsduj_101_1_1

Finished restore at 22-JAN-25

Starting recover at 22-JAN-25
starting media recovery
archived log for thread 1 with sequence 19 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCLDR/archivelog/2025_01_22/o1_mf_1_19_ms14zky5_.arc
archived log for thread 1 with sequence 20 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCLDR/archivelog/2025_01_22/o1_mf_1_20_ms14zl0q_.arc
archived log for thread 1 with sequence 21 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCLDR/archivelog/2025_01_22/o1_mf_1_21_ms15d21o_.arc

channel ch1: starting archived log restore to default destination
channel ch1: restoring archived log
archived log thread=1 sequence=18
channel ch1: reading from backup piece /u01/app/oracle/rmandr/Archive_20250122_3b3fse6m_107_1_1
channel ch1: piece handle=/u01/app/oracle/rmandr/Archive_20250122_3b3fse6m_107_1_1 tag=TAG20250122T113653
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:15

archived log file name=/u01/app/oracle/fast_recovery_area/ORCLDR/archivelog/2025_01_22/o1_mf_1_18_ms160js6_.arc thread=1 sequence=18
media recovery complete, elapsed time: 00:00:05
Finished recover at 22-JAN-25

released channel: ch1
released channel: ch2

RMAN> 
RMAN> exit

8. Now start real time apply
Start log shipment on Primary:

SQL> alter system set log_archive_dest_state_2= ENABLE scope=both;

SQL> show parameter log_archive_dest_state_2;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_2             string      ENABLE
log_archive_dest_state_20            string      enable
log_archive_dest_state_21            string      enable
log_archive_dest_state_22            string      enable
log_archive_dest_state_23            string      enable
log_archive_dest_state_24            string      enable
log_archive_dest_state_25            string      enable
log_archive_dest_state_26            string      enable
log_archive_dest_state_27            string      enable
log_archive_dest_state_28            string      enable
log_archive_dest_state_29            string      enable

SQL> set lines 200 pages 1000
col DEST_NAME for a20
col DESTINATION for a20
col error for a30

SQL> select DEST_ID, DEST_NAME, STATUS, ERROR from v$archive_dest where DEST_NAME='LOG_ARCHIVE_DEST_2';

DEST_ID DEST_NAME            STATUS    ERROR
------- -------------------- --------- ------------------------------
      2 LOG_ARCHIVE_DEST_2   VALID     

SQL> select DEST_ID, DEST_NAME, DESTINATION, STATUS, ERROR 
     from v$archive_dest_status where status not in ('INVALID','INACTIVE');

DEST_ID DEST_NAME            DESTINATION          STATUS    ERROR
------- -------------------- -------------------- --------- ------------------------------
      1 LOG_ARCHIVE_DEST_1   /u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch VALID     
      2 LOG_ARCHIVE_DEST_2   ORCLDR               VALID     

Start Apply At Standby:

SQL> alter database recover managed standby database disconnect from session;
SQL> select process,status,thread#,sequence#,block# from v$managed_standby where process like '%MRP%';

PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#
--------- ------------ ---------- ---------- ----------
MRP0      APPLYING_LOG          1         26        903

Now do some log switches on Primary and check the sync.

==> On Primary:

SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> set lines 200 pages 300
alter session set nls_date_format= 'DD-MON-YYYY HH24:MI:SS';
select d.db_unique_name, a.thread#, b.last_seq, a.applied_seq, a.last_app_timestamp, b.last_seq - a.applied_seq ARC_DIFF
FROM
(select thread#, MAX(sequence#) applied_seq, MAX(next_time) last_app_timestamp from gv$archived_log where applied='YES' group by thread#) a,
(select thread#, MAX(sequence#) last_seq from gv$archived_log group by thread#) b,
(select db_unique_name from v$database) d where a.thread#=b.thread#;
Session altered.

DB_UNIQUE_NAME                    THREAD#   LAST_SEQ APPLIED_SEQ LAST_APP_TIMESTAMP     ARC_DIFF
------------------------------ ---------- ---------- ----------- -------------------- ----------
ORCLDC                                  1         29          27 22-JAN-2025 12:57:12          2

==> On Standby:

SQL> select d.db_unique_name, a.thread#, b.last_seq, a.applied_seq, b.last_seq - a.applied_seq ARC_DIFF, a.last_app_timestamp,
round((sysdate - a.last_app_timestamp)*24*60,2) Gap_in_Mins, round((sysdate - a.last_app_timestamp)*24*60*60,2) Gap_in_Seconds
FROM
(select thread#, MAX(sequence#) applied_seq, MAX(next_time) last_app_timestamp from v$archived_log where REGISTRAR='RFS' and applied='YES' group by thread#) a,
(select thread#, MAX(sequence#) last_seq from gv$archived_log group by thread#) b,
(select db_unique_name from v$database) d where a.thread#=b.thread#;

DB_UNIQUE_NAME                    THREAD#   LAST_SEQ APPLIED_SEQ   ARC_DIFF LAST_APP_TIMESTAMP   GAP_IN_MINS GAP_IN_SECONDS
------------------------------ ---------- ---------- ----------- ---------- -------------------- ----------- --------------
ORCLDR                                  1         29          29          0 22-JAN-2025 12:57:18        3.78            227

9.Create tablespace on Primary and check on Standby

==> On Primary:

SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORCLPDB                        READ WRITE NO

SQL> alter session set container=ORCLPDB;
Session altered.

SQL> create tablespace test datafile '/u01/app/oracle/oradata/ORCL/orclpdb/test01.dbf' size 1g;
Tablespace created.

==> On Standby:

SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       MOUNTED
         3 ORCLPDB                        MOUNTED

SQL> alter session set container=ORCLPDB;
Session altered.

SQL> select name from v$datafile;
NAME
----------------------------------------------------------------------
/u01/app/oracle/oradata/ORCLDR/orclpdb/system01.dbf
/u01/app/oracle/oradata/ORCLDR/orclpdb/sysaux01.dbf
/u01/app/oracle/oradata/ORCLDR/orclpdb/undotbs01.dbf
/u01/app/oracle/oradata/ORCLDR/orclpdb/users01.dbf
/u01/app/oracle/oradata/ORCLDR/orclpdb/test01.dbf

SQL> select name from v$tablespace;
NAME
----------------------------------------------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
TEST

6 rows selected.

Thanks for visiting!!