Skip to main content

Standby Database Setup Using RMAN Backup and Restore

Oracle Data Guard standby database setup overview

Introduction

Oracle Data Guard (ODG) is a high-availability, disaster recovery, and data protection feature 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. In this guide, we'll set up a physical standby database step by step.

Prerequisites

  • Primary server with Oracle Database software installed and a running database.
  • Standby server with Oracle Database software installed.
  • Network connectivity between the primary and standby servers.

Environment Used in This Guide

These are the primary and standby server details used throughout this walkthrough. Swap them for your own hostnames, IPs, and SIDs, just keep the values consistent across every step below.

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

Step 1: Verify Connectivity Between Primary and Standby

Data Guard relies on continuous network communication between both servers for redo log shipping, so we confirm both directions work before configuring anything else. If ping fails here, nothing downstream will work either.


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

Step 2: Primary Database Setup

Check the Current Database State

Before making any changes, confirm the primary database's current role, open mode, and log mode. Data Guard requires the primary to be running in ARCHIVELOG mode, since redo needs to be archived and shipped to the standby.


[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
SQL> col open_mode for a15
SQL> select name,database_role,open_mode,log_mode from v$database;

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 your database shows NOARCHIVELOG mode instead, switch it to ARCHIVELOG first using the commands below before continuing.


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;

Configure the Primary Database for Data Guard

These parameter changes tell the primary database how to communicate with the standby, where to ship redo, and how to behave if the standby becomes unavailable temporarily. Each one plays a specific role, so it's worth understanding what you're setting rather than just copy-pasting.


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.

Two parameters deserve special attention: DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT. These tell Oracle how to automatically translate datafile and logfile paths between the primary and standby, which matters since the two servers use different directory structures for their respective database names in this setup.


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.

Several of the parameters above were set with SCOPE=SPFILE, meaning they only take effect after a restart. Bounce the database now so all the changes apply.


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

Finally, add standby redo logfiles. These are used to receive and apply redo whenever this database's role switches to standby (during a switchover or failover), so they need to exist even though this server is currently the primary. The number of standby redo log groups should be one more than the number of online redo log groups.


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.

Step 3: Primary Service Setup

Both servers need TNS entries for each other's database service so Data Guard can establish connections in both directions. Confirm the entries exist in tnsnames.ora on the primary.


[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)
    )
  )

Step 4: Back Up the Primary Database and Supporting Files

The standby database gets built from a backup of the primary, so we need a pfile, the password file, and a full RMAN backup with a control file created specifically for standby use.

Create the pfile:


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

Copy the password file: the standby needs an identical password file so RMAN and Data Guard connections can authenticate correctly.


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

Take a full database backup using RMAN: note the backup current controlfile for standby line, this produces a control file specifically formatted for a standby database, which is different from a regular backup control file.


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

Step 5: Transfer the Backup and Files to the Standby Server

Copy everything we just created (the backups, archive logs, standby control file, pfile, and password file) across to the standby server. RMAN will restore from these files locally rather than pulling them over the network.


[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

Step 6: Standby Database Setup

The standby needs its own pfile, derived from the primary's pfile but with several parameters changed to reflect the standby's own paths and unique name. Here are the key parameters that differ:


*.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'

Here's the full initorcldr.ora file with these changes applied:


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 the required directories: the standby needs its own audit, data, and fast recovery area directories before startup.


[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 the password file into $ORACLE_HOME/dbs: note it's renamed to match the standby's SID.


[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

Next, create a listener using NETCA, start it, and add TNS entries for both the primary and standby services in tnsnames.ora. If you haven't set up a listener before, see our separate guide: Listener Creation 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)
    )
  )

Step 7: Start the Standby Instance and Restore the Backup

Start the instance in nomount mode using the standby's pfile, since it has no control file yet.


[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 control file from the standby-formatted backup, then mount the database: using the standby control file here (rather than a regular one) is what tells Oracle this instance should behave as a standby.


[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>

Log in and verify the role: confirm the database now identifies itself as a physical standby.


[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

Catalog the backup files and restore the database: since we're restoring from local backup pieces rather than a live network connection to the primary, RMAN first needs to know these files exist.


[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

Step 8: Start Real-Time Apply

With the standby restored, we now enable log shipping on the primary and start managed recovery on the standby, so redo starts flowing and applying continuously between the two.

Enable log shipment on the 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
SQL> col DEST_NAME for a20
SQL> col DESTINATION for a20
SQL> 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 managed recovery (apply) on the standby: the MRP0 background process shown below is what continuously applies incoming redo to keep the standby in sync.


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

Database altered.
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 switch logs a few times on the primary and check that the standby is catching up.


On Primary:

SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> set lines 200 pages 300
SQL> alter session set nls_date_format= 'DD-MON-YYYY HH24:MI:SS';
SQL> 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

The ARC_DIFF value of 0 on the standby confirms it has fully caught up with the primary, applied sequence matches the last sequence generated.

Step 9: Verify Sync With a Real Change

As a final check, create a tablespace on the primary and confirm it automatically appears on the standby, proving the whole pipeline works end to end.


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.

The test01.dbf datafile and TEST tablespace appearing on the standby, without any manual intervention, confirms Data Guard is correctly shipping and applying redo from the primary in real time.

Thank you for reading!

I hope this content has been helpful to you. Your feedback and suggestions are always welcome. Feel free to leave a comment or reach out with any queries.

Abhishek Shrivastava

Oracle DBA with hands-on experience managing production Data Guard, RAC, GoldenGate, and APEX environments. I write practical, tested installation and troubleshooting guides based on real deployment work.

📧 Email: oraeasyy@gmail.com
🌐 Website: www.oraeasy.com

Follow Us

Location: Noida, Uttar Pradesh, India

Comments

Post a Comment

↑