Oracle Database: Physical Standby Configuration and Active Data Guard Setup

Oracle DBA Data Guard Physical Standby Active Data Guard Oracle 19c Disaster Recovery

Oracle Data Guard provides a mechanism for maintaining one or more standby databases for disaster recovery and high availability. A physical standby database is maintained by applying redo generated by the primary database to a corresponding physical copy of the primary database.

A physical standby can normally run Redo Apply while mounted. It can also be opened read-only for reporting purposes. When the physical standby is opened read-only while Redo Apply continues to run, the database operates in the Active Data Guard real-time query configuration.

Important: Active Data Guard is not a separate database role. The database continues to report PHYSICAL STANDBY as its database role. The defining state for real-time query is READ ONLY WITH APPLY.

Environment Used in This Guide

The procedure below follows the actual Oracle 19c lab configuration used for this implementation. The primary database is CDB and the physical standby is CDB_DR.

Component	Primary	Standby
DB_NAME	CDB	CDB
DB_UNIQUE_NAME	CDB	CDB_DR
Host	192.168.15.58	192.168.15.59
Listener Port	1522	1521
Service	CDB	CDB_DR

DB_NAME vs DB_UNIQUE_NAME: Both databases use the same DB_NAME, CDB, because the standby represents the same database. The DB_UNIQUE_NAME is different so that Oracle can uniquely identify each Data Guard member: CDB for the primary and CDB_DR for the standby.

Physical Standby and Active Data Guard

Configuration	Database Role	Open Mode	Redo Apply
Normal physical standby	PHYSICAL STANDBY	MOUNTED	Running
Read-only standby	PHYSICAL STANDBY	READ ONLY	Stopped
Active Data Guard	PHYSICAL STANDBY	READ ONLY WITH APPLY	Running

The conversion to Active Data Guard therefore does not change the DATABASE_ROLE. It changes the operating state of the physical standby so that it remains open for read-only access while Redo Apply continues.

ASYNC vs SYNC Redo Transport

Redo transport determines how redo generated by the primary is transmitted to the standby. The example in this guide uses ASYNC.

ASYNC

With ASYNC, the primary does not wait for the standby to acknowledge receipt of the redo before continuing the primary transaction processing. This reduces the effect of network latency on primary database performance.

SYNC

With SYNC, the primary uses synchronous redo transport and waits for the required acknowledgement from the standby as part of the transaction processing path. This can provide stronger data protection but introduces network and standby I/O latency into the primary workload.

Attribute	Meaning
ASYNC	Redo is transported asynchronously. The primary does not wait for standby acknowledgement for each transaction.
SYNC	Redo transport is synchronous and can introduce standby/network latency into the primary transaction path.

Important: ASYNC does not mean that the standby cannot apply redo in real time. Transport mode and apply mode are separate concepts. A physical standby using asynchronous transport can still use standby redo logs and continuously apply received redo.

Prerequisites

• Standby database server with the same Oracle Database version and patch level as the primary database.
• Primary database must be in ARCHIVELOG mode.
• Network connectivity must exist between the primary and standby servers.
• The required listener port must be open and accessible on the standby database server.
• Force Logging should be enabled on the primary database.
• Standby storage must have sufficient space for database files, archived redo logs, and the recovery area.
• STANDBY_FILE_MANAGEMENT=AUTO should be configured on the standby database.

Step 1: Verify the Primary Database

Before creating the standby, verify the current primary database configuration. The first check establishes the database name, unique name, database role, open mode, archive mode and force logging state.

SQL> archive log list

Output

Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /u01/app/oracle/product/19c/dbhome_1/dbs/arch
Oldest online log sequence     6
Current log sequence           8

The database is initially running in NO ARCHIVELOG mode. A physical standby requires archived redo, so ARCHIVELOG mode must be enabled.

2: Enable ARCHIVELOG Mode

The database is restarted in the mounted state because ALTER DATABASE ARCHIVELOG must be performed while the database is mounted.

SQL> startup mount

Output

ORACLE instance started.

Total System Global Area  788526672 bytes
Fixed Size                  9139792 bytes
Variable Size             297795584 bytes
Database Buffers          473956352 bytes
Redo Buffers                7634944 bytes
Database mounted.

SQL> alter database archivelog;

Output

Database altered.

SQL> alter database open;

Output

Database altered.

SQL> SELECT LOG_MODE FROM V$DATABASE;

Output

LOG_MODE
------------
ARCHIVELOG

The primary is now capable of generating archived redo that can be transported to the standby.

Step 3: Enable FORCE LOGGING

Force Logging ensures that database operations generate redo even when an operation could otherwise use minimal logging. This helps ensure that the physical standby receives the redo necessary to maintain consistency with the primary.

SQL> SELECT FORCE_LOGGING FROM V$DATABASE;

Output

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

SQL> alter database force logging;

Output

Database altered.

SQL> SELECT FORCE_LOGGING FROM V$DATABASE;

Output

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

Step 4: Verify Database Identity

The physical standby will use the same DB_NAME as the primary but a different DB_UNIQUE_NAME.

SQL> SHOW PARAMETER DB_NAME;

Output

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      CDB

SQL> SHOW PARAMETER DB_UNIQUE_NAME;

Output

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      CDB

Step 5: Configure Data Guard Members

LOG_ARCHIVE_CONFIG identifies the databases participating in the Data Guard configuration. The values correspond to the DB_UNIQUE_NAME values.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(CDB,CDB_DR)' SCOPE=BOTH;

Output

System altered.

SQL> show parameter LOG_ARCHIVE_CONFIG

Output

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_config                   string      DG_CONFIG=(CDB,CDB_DR)

Step 6: Configure the Fast Recovery Area

The Fast Recovery Area (FRA) is used to manage recovery-related files such as archived redo logs and other recovery files. In this configuration, the FRA size is set to 10G.

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=10G SCOPE=BOTH;

Output

System altered.

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST=
'/u01/app/oracle/fast_recovery_area' SCOPE=BOTH;

Output

System altered.

SQL> SHOW PARAMETER DB_RECOVERY

Output

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/app/oracle/fast_recovery_area
db_recovery_file_dest_size           big integer 10G

db_recovery_file_dest_size In a production environment, the FRA size should be determined based on redo generation, backup retention, Flashback Database requirements, and recovery requirements.

Step 7: Configure the Primary Archive Destinations

The primary uses one local destination for archived redo and one remote destination for transporting redo to the physical standby.

Local Archive Destination

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1=
'LOCATION=USE_DB_RECOVERY_FILE_DEST
 VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
 DB_UNIQUE_NAME=CDB' SCOPE=BOTH;

Output

System altered.

SQL> show parameter LOG_ARCHIVE_DEST_1

Output

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1                   string      LOCATION=USE_DB_RECOVERY_FILE_
                                                 DEST VALID_FOR=(ALL_LOGFILES,A
                                                 LL_ROLES) DB_UNIQUE_NAME=CDB

LOCATION=USE_DB_RECOVERY_FILE_DEST places local archived redo in the FRA. VALID_FOR=(ALL_LOGFILES,ALL_ROLES) makes the destination valid for all logfile types and database roles.

Remote Standby Destination

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2=
'SERVICE=CDB_DR ASYNC
 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
 DB_UNIQUE_NAME=CDB_DR' SCOPE=BOTH;

Output

System altered.

The important attributes are:

Value	Meaning
SERVICE=CDB_DR	Uses the Oracle Net service CDB_DR to reach the standby.
ASYNC	Uses asynchronous redo transport. The primary does not wait for standby acknowledgement for each transaction.
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)	The destination transports online redo while this database is operating in the primary role.
DB_UNIQUE_NAME=CDB_DR	Identifies the remote standby database.

Step 8: Configure Automatic Standby File Management

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO SCOPE=BOTH;

Output

System altered.

With AUTO, Oracle automatically manages corresponding standby datafiles when datafiles are created on the primary, reducing manual file administration.

Step 9: Check Primary Online Redo Logs

The number and size of standby redo logs are based on the primary online redo log configuration.

SQL> SELECT
       GROUP#,
       THREAD#,
       BYTES/1024/1024 AS SIZE_MB
FROM V$LOG
ORDER BY THREAD#, GROUP#;

Output

GROUP#    THREAD#    SIZE_MB
---------- ---------- ----------
         1          1        200
         2          1        200
         3          1        200

The primary has three online redo log groups, each with a size of 200M. This implementation therefore creates four standby redo log groups of the same size.

Step 10: Create Standby Redo Logs

Standby Redo Logs receive redo transported from the primary. They are especially important for real-time apply because the standby can apply redo from the standby redo logs without waiting for the redo to be archived.

SQL> ALTER DATABASE ADD STANDBY LOGFILE
('/u01/app/oracle/oradata/CDB_DR/srl01.log') SIZE 200M;

Output

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE
('/u01/app/oracle/oradata/CDB_DR/srl02.log') SIZE 200M;

Output

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE
('/u01/app/oracle/oradata/CDB_DR/srl03.log') SIZE 200M;

Output

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE
('/u01/app/oracle/oradata/CDB_DR/srl04.log') SIZE 200M;

Output

Database altered.

Verify Standby Redo Logs

SQL> SELECT
       GROUP#,
       THREAD#,
       BYTES/1024/1024 AS SIZE_MB,
       STATUS
FROM V$STANDBY_LOG
ORDER BY THREAD#, GROUP#;

Output

GROUP#    THREAD#    SIZE_MB STATUS
---------- ---------- ---------- ----------
         4          0        200 UNASSIGNED
         5          0        200 UNASSIGNED
         6          0        200 UNASSIGNED
         7          0        200 UNASSIGNED

Why four groups? This configuration has three primary online redo log groups and creates four standby redo log groups. The additional group allows another standby redo log to be available while other groups are being used.

Step 11: Verify the Password File

The standby requires the password file used for Oracle administrative authentication.

SQL> !ls -l $ORACLE_HOME/dbs/orapw$ORACLE_SID

Output

-rw-r-----. 1 oracle oinstall 2048 Aug 10 20:27
/u01/app/oracle/product/19c/dbhome_1/dbs/orapwCDB

Step 12: Configure Oracle Net on the Primary

The primary must be able to resolve the standby service CDB_DR.

[oracle@e1vsr058 ~]$ cat $ORACLE_HOME/network/admin/tnsnames.ora

Output

LISTENER_CDB =
  (ADDRESS = (PROTOCOL = TCP)(HOST = e1vsr058)(PORT = 1522))

CDB_DR =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.15.59)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = CDB_DR)
    )
  )

CDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = e1vsr058)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = CDB)
    )
  )

Step 13: Configure Listener and TNS on the Standby

Configure the Oracle Net listener on the standby server using netca or manually. The standby listener uses TCP port 1521 and provides the CDB_DR service required for Data Guard connectivity.

The listener.ora configuration should look as shown below depending on your environment:

[oracle@e1vsr059 ~]$ cat $ORACLE_HOME/network/admin/listener.ora

Configuration

# listener.ora Network Configuration File: /u01/app/oracle/product/19c/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = e1vsr059)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )


SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = CDB_DR)
      (ORACLE_HOME = /u01/app/oracle/product/19c/dbhome_1)
      (SID_NAME = CDB_DR)
    )
  )

The LISTENER section defines the TCP endpoint on host e1vsr059 using port 1521. The SID_LIST_LISTENER section statically associates the CDB_DR service with the standby Oracle Home and CDB_DR instance.

The TNS aliases should be created as shown below for Standby DB and Primary DB:

[oracle@e1vsr059 ~]$ cat $ORACLE_HOME/network/admin/tnsnames.ora

Configuration

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19c/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.




CDB_DR =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.15.59)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = CDB_DR)
    )
  )

CDB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.15.58)(PORT = 1522))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = CDB)
    )
  )

The CDB_DR alias points to the standby server 192.168.15.59 on port 1521, while the CDB alias points to the primary server 192.168.15.58 on port 1522. These aliases provide the Oracle Net service names required for communication between the primary and standby databases.

Step 14: Test Primary to Standby Connectivity

[oracle@e1vsr058 ~]$ tnsping CDB_DR

Output

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production
on 11-AUG-2026 09:38:38

Used parameter files:
/u01/app/oracle/product/19c/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.15.59)
      (PORT = 1521)))
  (CONNECT_DATA =
    (SERVICE_NAME = CDB_DR)))

OK (0 msec)

The successful response confirms that the primary can resolve the CDB_DR service and reach the standby listener.

Step 15: Test Standby to Primary Connectivity

[oracle@e1vsr059 ~]$ tnsping CDB

Output

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production
on 11-AUG-2026 09:38:31

Used parameter files:
/u01/app/oracle/product/19c/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.15.58)
      (PORT = 1522)))
  (CONNECT_DATA =
    (SERVICE_NAME = CDB)))

OK (0 msec)

Step 16: Transfer the Password File to the Standby

[oracle@e1vsr058 ~]$ scp
/u01/app/oracle/product/19c/dbhome_1/dbs/orapwCDB
oracle@192.168.15.59:
/u01/app/oracle/product/19c/dbhome_1/dbs/orapwCDB_DR

Output

oracle@192.168.15.59's password:
orapwCDB                                      100% 2048  1.0MB/s  00:00

Step 17: Create and Transfer the Primary PFILE

A PFILE is created from the primary SPFILE so that the standby can use the primary configuration as a starting point.

SQL> create pfile='/home/oracle/pfile_11082026.ora'
     from spfile;

Output

File created.

[oracle@e1vsr058 ~]$ scp
/home/oracle/pfile_11082026.ora
oracle@192.168.15.59:/home/oracle/pfile_standby.ora

Output

oracle@192.168.15.59's password:
pfile_11082026.ora                         100% 1376  657.9KB/s  00:00

Step 18: Make changes to Standby PFILE

The standby PFILE uses the same DB_NAME but changes DB_UNIQUE_NAME to CDB_DR. It also contains the standby-specific Data Guard destinations.

[oracle@e1vsr059 ~]$ cat pfile_standby.ora

Output

*.audit_file_dest='/u01/app/oracle/admin/CDB_DR/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/u01/app/oracle/oradata/control_file/control01.ctl',
'/u01/app/oracle/fast_recovery_area/control_file/control02.ctl'
*.db_block_size=8192
*.db_name='CDB'
*.db_unique_name='CDB_DR'
*.db_recovery_file_dest_size=10737418240
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=CDBXDB)'
*.enable_pluggable_database=true
*.local_listener='LISTENER'
*.log_archive_config='DG_CONFIG=(CDB,CDB_DR)'
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST
VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=CDB_DR'
*.log_archive_dest_2='SERVICE=CDB ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=CDB'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=250m
*.processes=320
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=750m
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'

Step 19: Start the Standby in NOMOUNT

Set the ORACLE_SID that needs to be set for Standby Database.
The standby must be started in NOMOUNT before the standby control file can be restored.

[oracle@e1vsr059 ~]$ . oraenv
ORACLE_SID = [CDB] ? CDB_DR
ORACLE_HOME = [/home/oracle] ?
/u01/app/oracle/product/19c/dbhome_1

[oracle@e1vsr059 ~]$ sqlplus / as sysdba

SQL> startup nomount pfile=/home/oracle/pfile_standby.ora

Output

SQL> startup nomount pfile=/home/oracle/pfile_standby.ora
ORACLE instance started.

Total System Global Area  788526672 bytes
Fixed Size                  9139792 bytes
Variable Size             213909504 bytes
Database Buffers          557842432 bytes
Redo Buffers                7634944 bytes

At NOMOUNT, the Oracle instance is started but the control file is not yet mounted.

Step 20: Restore the Standby Control File

RMAN restores the standby control file directly from the primary database service.

rman target /
RMAN> restore standby controlfile from service 'CDB';

Output

Starting restore at 11-AUG-26
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=261 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service CDB
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02

output file name=/u01/app/oracle/oradata/control_file/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/control_file/control02.ctl

Finished restore at 11-AUG-26

The standby now has the control file required to mount the database and restore the database files.

Step 21: Mount the Standby

RMAN> alter database mount;

Output

Statement processed

Step 22: Restore the Database from the Primary

RMAN can restore the primary database files directly over the Oracle Net service using FROM SERVICE.

RMAN> run
{
    restore database from service 'CDB';
}

Output

Starting restore at 11-AUG-26
Starting implicit crosscheck backup at 11-AUG-26
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=136 device type=DISK
Crosschecked 1 objects
Finished implicit crosscheck backup at 11-AUG-26

Starting implicit crosscheck copy at 11-AUG-26
using channel ORA_DISK_1
Finished implicit crosscheck copy at 11-AUG-26

searching for all files in the recovery area
cataloging files...
no files cataloged

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service CDB
channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00001 to /u01/CDB/system01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25

channel ORA_DISK_1: restoring datafile 00003 to /u01/CDB/sysaux01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15

channel ORA_DISK_1: restoring datafile 00004 to /u01/CDB/undotbs01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07

channel ORA_DISK_1: restoring datafile 00005 to /u01/CDB/pdbseed/system01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07

channel ORA_DISK_1: restoring datafile 00006 to /u01/CDB/pdbseed/sysaux01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07

channel ORA_DISK_1: restoring datafile 00007 to /u01/CDB/users01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

channel ORA_DISK_1: restoring datafile 00008 to /u01/CDB/pdbseed/undotbs01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03

channel ORA_DISK_1: restoring datafile 00009 to /u01/CDB/PDB1/system01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07

channel ORA_DISK_1: restoring datafile 00010 to /u01/CDB/PDB1/sysaux01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:08

channel ORA_DISK_1: restoring datafile 00011 to /u01/CDB/PDB1/undotbs01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03

channel ORA_DISK_1: restoring datafile 00012 to /u01/CDB/PDB1/users01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

channel ORA_DISK_1: restoring datafile 00013 to /u01/CDB/PDB2/system01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07

channel ORA_DISK_1: restoring datafile 00014 to /u01/CDB/PDB2/sysaux01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07

channel ORA_DISK_1: restoring datafile 00015 to /u01/CDB/PDB2/undotbs01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03

channel ORA_DISK_1: restoring datafile 00016 to /u01/CDB/PDB2/users01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

Finished restore at 11-AUG-26

Step 23: Start Redo Apply

Once the standby database files have been restored, Redo Apply is started. The command runs the managed recovery process in the background.

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

Output

Database altered.

Step 24: Verify Redo Apply

SQL> select process,
            client_process,
            sequence#,
            status
     from v$managed_standby;

Output

PROCESS   CLIENT_P  SEQUENCE# STATUS
--------  --------  --------- ------------
ARCH      ARCH              0 CONNECTED
DGRD      N/A               0 ALLOCATED
DGRD      N/A               0 ALLOCATED
ARCH      ARCH              0 CONNECTED
ARCH      ARCH              0 CONNECTED
ARCH      ARCH              0 CONNECTED
MRP0      N/A               9 WAIT_FOR_LOG

The MRP0 process represents the Managed Recovery Process. It is responsible for applying redo to the physical standby.

The subsequent check showed the standby receiving redo through RFS processes.

SQL> select process,
            client_process,
            sequence#,
            status
     from v$managed_standby;

Output

PROCESS   CLIENT_P  SEQUENCE# STATUS
--------  --------  --------- ------------
ARCH      ARCH              0 CONNECTED
DGRD      N/A               0 ALLOCATED
DGRD      N/A               0 ALLOCATED
ARCH      ARCH              0 CONNECTED
ARCH      ARCH              0 CONNECTED
ARCH      ARCH              0 CONNECTED
MRP0      N/A              16 WAIT_FOR_LOG
RFS       Archival          0 IDLE
RFS       LGWR             16 IDLE
RFS       UNKNOWN           0 IDLE
RFS       UNKNOWN           0 IDLE
RFS       UNKNOWN           0 IDLE

Step 25: Check Data Guard Lag

The standby should be checked for transport and apply lag before converting it to the Active Data Guard read-only state.

SQL> SELECT NAME,
            VALUE,
            UNIT
     FROM V$DATAGUARD_STATS
     WHERE NAME IN
     ('transport lag',
      'apply lag',
      'apply finish time');

Output

NAME
--------------------------------
transport lag

VALUE
--------------------------------
+00 00:06:07

UNIT
--------------------------------
day(2) to second(0) interval


NAME
--------------------------------
apply lag

VALUE
--------------------------------
+00 00:06:07

UNIT
--------------------------------
day(2) to second(0) interval


NAME
--------------------------------
apply finish time

VALUE
--------------------------------
 
UNIT
--------------------------------
day(2) to second(3) interval

Transport lag represents the delay between redo generation at the primary and its availability at the standby.

Apply lag represents the delay between the primary and the point to which redo has been applied on the standby.

Step 26: Configure ARCHIVE_LAG_TARGET

The ARCHIVE_LAG_TARGET parameter specifies the maximum amount of time, in seconds, that can elapse before Oracle forces an online redo log switch. It can be used to limit the time interval between archived redo logs, which is useful in a Data Guard environment where regular redo generation and log switching are important for standby recovery.

In this configuration, ARCHIVE_LAG_TARGET is set to 900 seconds, which is equivalent to 15 minutes.

SQL> ALTER SYSTEM SET ARCHIVE_LAG_TARGET=900 SCOPE=BOTH;

Output

System altered.

The value 900 represents 900 seconds:

900 seconds ÷ 60 = 15 minutes

SQL> SHOW PARAMETER ARCHIVE_LAG_TARGET;

Output

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target                   integer     900

Why 900? The value 900 represents a 15-minute interval. This configuration uses the value to encourage regular redo log switches so that redo is archived and made available to the standby at regular intervals. The value should be selected according to the database workload and Data Guard recovery requirements.

Step 27: Confirm the Physical Standby State

Before enabling Active Data Guard real-time query, verify that the standby is still mounted and operating as a physical standby.

SQL> SELECT DATABASE_ROLE, OPEN_MODE
     FROM V$DATABASE;

Output

DATABASE_ROLE    OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY MOUNTED

This is the normal physical standby state with Redo Apply available.

Step 28: Stop Redo Apply

The standby must stop its current managed recovery process before it is opened read-only.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Output

Database altered.

Step 29: Open the Standby Read Only

The standby is now opened read-only. At this point it can service read-only queries, but Redo Apply has not yet been restarted.

SQL> ALTER DATABASE OPEN READ ONLY;

Output

Database altered.

SQL> SELECT DATABASE_ROLE, OPEN_MODE
     FROM V$DATABASE;

Output

DATABASE_ROLE    OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY READ ONLY

The database is now a read-only physical standby, but it is not yet in the Active Data Guard state because Redo Apply is stopped.

Step 30: Start Redo Apply While Read Only

This is the key step. Redo Apply is started again while the physical standby remains open read-only.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
     USING CURRENT LOGFILE
     DISCONNECT FROM SESSION;

Output

Database altered.

Step 31: Verify Active Data Guard

SQL> SELECT NAME,
            DATABASE_ROLE,
            OPEN_MODE
     FROM V$DATABASE;

Output

NAME      DATABASE_ROLE    OPEN_MODE
--------- ---------------- --------------------
CDB       PHYSICAL STANDBY READ ONLY WITH APPLY

Active Data Guard is now running.

The database role is still PHYSICAL STANDBY, but the open mode is READ ONLY WITH APPLY. The standby is therefore open for read-only queries while Redo Apply continues.

Step 32: Verify Redo Apply and RFS

SQL> SELECT PROCESS,
            STATUS,
            THREAD#,
            SEQUENCE#
     FROM V$MANAGED_STANDBY
     ORDER BY PROCESS;

Output

PROCESS   STATUS          THREAD# SEQUENCE#
--------- --------------- ------- ---------
ARCH      CONNECTED             0         0
ARCH      CONNECTED             0         0
ARCH      CONNECTED             0         0
ARCH      CONNECTED             0         0
DGRD      ALLOCATED             0         0
DGRD      ALLOCATED             0         0
MRP0      WAIT_FOR_LOG          1        16
RFS       IDLE                  0         0
RFS       IDLE                  0         0
RFS       IDLE                  1         0
RFS       IDLE                  1        16
RFS       IDLE                  0         0

The important processes are:

Process	Meaning
MRP0	Managed Recovery Process that applies redo to the physical standby.
RFS	Remote File Server process that receives redo from the primary.
ARCH	Archive-related processes handling redo/archive operations.
DGRD	Data Guard-related background processes.

Step 33: Verify DC-DR Synchronization Status

After Redo Apply has been enabled, the synchronization status between the primary database and the physical standby should be verified. The following queries compare the latest archived redo sequence with the latest applied redo sequence and show the time at which redo was last applied.

Check DC-DR Synchronization Status from DC

Run the following query on the primary database. It compares the latest archived sequence with the latest sequence applied on the standby and calculates the difference as ARC_DIFF.

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,
            d.database_role,
            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,
             database_role
      from v$database) d
     where a.thread#=b.thread#;

Output

DB_UNIQUE_NAME    DATABASE_ROLE    THREAD#    LAST_SEQ    APPLIED_SEQ    LAST_APP_TIMESTAMP       ARC_DIFF
----------------  ---------------  --------  ---------    -----------    -------------------      --------
CDB               PRIMARY                  1          16            16    11-AUG-2026 09:42:18           0

The ARC_DIFF column represents the difference between the latest archived redo sequence and the latest applied redo sequence for the thread. A value of 0 indicates that there is no sequence difference in this check.

Check DC-DR Synchronization Status from DR

Run the following query on the physical standby. This version specifically checks redo registered through RFS and calculates both the sequence difference and the elapsed time since the last applied redo.

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,
            d.database_role,
            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 gv$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,
             database_role
      from v$database) d
     where a.thread#=b.thread#;

Output

DB_UNIQUE_NAME    DATABASE_ROLE    THREAD#    LAST_SEQ    APPLIED_SEQ    ARC_DIFF    LAST_APP_TIMESTAMP       GAP_IN_MINS    GAP_IN_SECONDS
----------------  ---------------  --------  ---------    -----------    --------    -------------------      ------------   --------------
CDB_DR            PHYSICAL STANDBY        1          16            16           0    11-AUG-2026 09:42:18            0.18           10.80

The DR-side query provides three useful indicators:

Column	Meaning
LAST_SEQ	Latest archived redo sequence identified by the query.
APPLIED_SEQ	Latest redo sequence marked as applied.
ARC_DIFF	Difference between the latest archived sequence and the latest applied sequence.
LAST_APP_TIMESTAMP	Timestamp associated with the latest applied redo.
GAP_IN_MINS	Elapsed time, in minutes, since the latest applied redo timestamp.
GAP_IN_SECONDS	Elapsed time, in seconds, since the latest applied redo timestamp.

Synchronization Status: The DC and DR checks show ARC_DIFF = 0, indicating that the latest archived sequence and latest applied sequence are aligned for the checked redo thread.

Why perform the check from both sides? The DC-side query provides the primary perspective, while the DR-side query confirms what has actually been received through RFS and applied on the standby. Checking both perspectives provides a clearer picture of the DC-DR synchronization status.

Understanding the Complete Data Flow

1. The primary generates redo from database transactions.
2. LOG_ARCHIVE_DEST_2 transports redo to CDB_DR.
3. The standby receives the redo through RFS.
4. Standby Redo Logs receive the transported redo.
5. MRP0 applies the redo to the physical standby.
6. The standby is opened read-only.
7. Redo Apply is started again while the standby remains open.
8. The final state becomes READ ONLY WITH APPLY.
9. Users can execute read-only queries while redo continues to be applied.

Parameter Values Used in This Configuration

Parameter	Value	Purpose
DB_NAME	CDB	Same database name on primary and physical standby.
DB_UNIQUE_NAME Primary	CDB	Uniquely identifies the primary.
DB_UNIQUE_NAME Standby	CDB_DR	Uniquely identifies the physical standby.
LOG_ARCHIVE_CONFIG	DG_CONFIG=(CDB,CDB_DR)	Defines the Data Guard members.
LOG_ARCHIVE_DEST_2	SERVICE=CDB_DR ASYNC	Transports redo from primary to standby asynchronously.
VALID_FOR	(ONLINE_LOGFILES,PRIMARY_ROLE)	Uses online redo while the database is primary.
STANDBY_FILE_MANAGEMENT	AUTO	Automatically manages corresponding standby datafiles.
DB_RECOVERY_FILE_DEST_SIZE	10G	FRA size used in this lab.
Primary redo log size	200M	Size used for each primary online redo group.
Standby redo log size	200M	Matches the primary online redo log size.
Standby redo log groups	4	Four SRLs were created for three primary redo groups.

Why Standby Redo Logs Were Created

The primary has three online redo log groups:

GROUP#    THREAD#    SIZE_MB
---------- ---------- ----------
         1          1        200
         2          1        200
         3          1        200

Four standby redo log groups of 200M were therefore created. The standby redo logs provide a receiving location for redo transported from the primary and allow Redo Apply to process redo without waiting for an archived redo log to become available.

Normal Physical Standby in Mount Mode vs Active Data Guard in Read Only

Item	Physical Standby (Mounted)	Active Data Guard (Read Only)
Database Role	PHYSICAL STANDBY	PHYSICAL STANDBY
Open Mode	MOUNTED	READ ONLY WITH APPLY
Redo Apply	Running	Running
Read-only queries	Not while mounted	Available
Primary redo transport	Can be ASYNC or SYNC	Can be ASYNC or SYNC
Standby Redo Logs	Recommended/required for real-time apply	Required for the real-time apply configuration

Summary

This guide demonstrates the complete process of creating a physical standby database and converting it into an Active Data Guard environment. The primary database is first prepared by enabling ARCHIVELOG and FORCE LOGGING, defining the Data Guard members, configuring the Fast Recovery Area, and establishing Oracle Net connectivity between the primary and standby.

Redo transport is configured using LOG_ARCHIVE_DEST_2 with ASYNC transport. Standby Redo Logs are created to receive transported redo and support continuous redo application. The ARCHIVE_LAG_TARGET parameter is also configured to encourage regular redo log switches and make archived redo available to the standby at regular intervals.

The physical standby is then initialized using the standby control file and RMAN's RESTORE DATABASE FROM SERVICE. After the database files are restored, Redo Apply is started and the standby is verified using V$MANAGED_STANDBY. Transport and apply lag are checked before the standby is opened for read-only access.

The standby is then moved through the following states:

PHYSICAL STANDBY / MOUNTED
        |
        v
PHYSICAL STANDBY / READ ONLY
        |
        v
PHYSICAL STANDBY / READ ONLY WITH APPLY

The final state, READ ONLY WITH APPLY, keeps the database in the PHYSICAL STANDBY role while allowing users to execute read-only queries and Redo Apply to continue in the background. This is the Active Data Guard real-time query configuration demonstrated in this guide.

The final DC-DR verification compares the latest archived and applied redo sequences and also measures the time-based apply gap. A zero ARC_DIFF indicates that the checked archived and applied redo sequences are aligned, while GAP_IN_MINS and GAP_IN_SECONDS show the current time difference since the latest applied redo.

Final Result: The physical standby is operating as an Active Data Guard database in READ ONLY WITH APPLY mode, with redo continuously received and applied while the standby remains available for read-only workloads.