Why Data Guard Lag Happens in Production: Sync, I/O and Network Deep Dive

Six Root Causes of Transport and Apply Lag , With Diagnostic SQL to Prove Each One

30 March 2026

Chetan Yadav , Senior Oracle & Cloud DBA

⏱️ 14–16 min read

⏱️ Estimated Reading Time: 14–16 minutes

Transport Lag • Apply Lag • SYNC vs ASYNC • Network RTT • Standby I/O • MRP Apply Bottleneck

⚙️ Production Environment Referenced in This Article

Oracle Database: 19.18.0.0.0 Enterprise Edition  •  Primary: 2-Node RAC, 4.8 TB OLTP  •  Standby: Physical Standby (Active Data Guard)
Protection Mode: Maximum Availability (SYNC/AFFIRM)  •  Network: Dedicated 1 GbE WAN (120 km distance, RTT 1.8 ms)
Peak Load: 2,800 TPS, 180 MB/sec redo generation  •  Application: Core banking transaction processing

The alert arrives at 11:43 PM: "Data Guard apply lag exceeds 900 seconds." The DBA on call opens the monitoring dashboard. Transport lag is 180 seconds. Apply lag is 900 seconds. The standby is 15 minutes behind the primary. If the primary fails right now, 15 minutes of financial transactions could be at risk.

This scenario plays out in production Data Guard environments more often than most teams admit. Lag is not a single problem , it is six different problems that look identical from the outside. Transport lag and apply lag each have completely different root causes, different diagnostic queries, and completely different fixes. Treating them the same wastes hours of investigation time.

This guide covers every real cause of Data Guard lag I have diagnosed in production, the exact SQL to prove which one you are dealing with, and the specific fix for each. No guesswork. No generic advice about "check your network." Precise diagnosis first, then precise resolution.

RMAN ACTIVE DUPLICATE — RUNNER DOC

 

RMAN ACTIVE DUPLICATE — RUNNER DOC

Oracle 19c  |  Target → Auxiliary (DR Clone)

# Env: OL7.9 / 19.3.0.0.0

 * Note: This test is done in my lab machine, I will suggest first try in your lab for the test, then use it.

Environment reference

 

# SOURCE (TARGET)

ORACLE_SID=FINPRD

DB_NAME=FINPRD

ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1

ORACLE_BASE=/u01/app/oracle

HOST=ora-prod-01.corp.internal

TNS_ALIAS=FINPRD

# DESTINATION (AUXILIARY / DR)

ORACLE_SID=FINDR

DB_NAME=FINDR

ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1

ORACLE_BASE=/u01/app/oracle

HOST=ora-dr-01.corp.internal

TNS_ALIAS=FINDR

# PATHS — auxiliary host

ORADATA=/data01/oradata/FINDR

FRA=/fra01/FINDR

ADMIN=/u01/app/oracle/admin/FINDR

# RMAN SYS password  →  do NOT hardcode in prod, use wallet

# SYS_PWD=<get from vault or /etc/oracle/.sys_cred>

 

 Pre Check – Source host

[01] Verify source DB — open mode, archivelog, force logging

sqlplus / as sysdba

SELECT name, open_mode, log_mode, force_logging FROM v$database;

 

→ expected output:

SQL> SELECT name, open_mode, log_mode, force_logging FROM v$database;

NAME      OPEN_MODE            LOG_MODE     FOR

--------- -------------------- ------------ ---

FINPRD    READ WRITE           ARCHIVELOG   YES

1 row selected.

 

# WARNING: If FOR=NO  →  ALTER DATABASE FORCE LOGGING;  then re-check.

 

[02] Check source listener — FINPRD must show READY

# On ora-prod-01.corp.internal

lsnrctl status LISTENER

tnsping FINPRD

 

→ expected (relevant lines only):

Service "FINPRD" has 1 instance(s).

Instance "FINPRD", status READY, has 1 handler(s) for this service...

TNS Ping Utility for Linux: Version 19.0.0.0.0

OK (10 msec)

 

# WARNING: If FINPRD shows BLOCKED or UNKNOWN — DB is not fully open. Do not proceed.

 

OS PREPARATION  —  AUXILIARY HOST

[03] Set env — run this first in every new shell session

# ssh oracle@ora-dr-01.corp.internal

export ORACLE_SID=FINDR

export ORACLE_BASE=/u01/app/oracle

export ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1

export PATH=$ORACLE_HOME/bin:$PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH

# quick sanity

echo $ORACLE_SID && which sqlplus && which rman

 

→ expected:

FINDR

/u01/app/oracle/product/19.3.0/dbhome_1/bin/sqlplus

/u01/app/oracle/product/19.3.0/dbhome_1/bin/rman

 

[04] Create directory structure on auxiliary

# WARNING: Run as root or oracle with sudo. RMAN will ORA-01119 if these don't exist.

 

mkdir -p /data01/oradata/FINDR

mkdir -p /fra01/FINDR

mkdir -p /u01/app/oracle/admin/FINDR/adump

mkdir -p /u01/app/oracle/admin/FINDR/dpdump

mkdir -p /u01/app/oracle/admin/FINDR/hdump

chown -R oracle:oinstall /data01/oradata /fra01

chmod -R 755 /data01/oradata /fra01 /u01/app/oracle/admin/FINDR

# verify

ls -ld /data01/oradata/FINDR /fra01/FINDR /u01/app/oracle/admin/FINDR/adump

 

→ all 3 lines should show oracle:oinstall ownership:

drwxr-xr-x 2 oracle oinstall 6 Nov 12 09:14 /data01/oradata/FINDR

drwxr-xr-x 2 oracle oinstall 6 Nov 12 09:14 /fra01/FINDR

drwxr-xr-x 2 oracle oinstall 6 Nov 12 09:14 /u01/app/oracle/admin/FINDR/adump

 

[05] Create password file — must match SYS password on source

# password must match ora-prod-01 SYS password exactly

orapwd file=$ORACLE_HOME/dbs/orapwFINDR \

password=<SYS_PASSWORD> \

entries=5 \

force=y \

format=12

ls -lh $ORACLE_HOME/dbs/orapwFINDR

 

→ expected:

-rw-r----- 1 oracle oinstall 2.0K Nov 12 09:16 /u01/app/oracle/product/19.3.0/dbhome_1/dbs/orapwFINDR

 

# WARNING: ORA-01017 during RMAN connect = password mismatch. Recreate orapwd with correct password.

 

[06] Create minimal pfile — just enough for NOMOUNT

# INFO: Only parameters needed for NOMOUNT. RMAN will set the rest from source spfile during duplicate. 

cat > $ORACLE_HOME/dbs/initFINDR.ora << 'EOF'

db_name=FINDR

db_unique_name=FINDR

sga_target=2G

pga_aggregate_target=512M

db_block_size=8192

enable_pluggable_database=false

audit_file_dest=/u01/app/oracle/admin/FINDR/adump

diagnostic_dest=/u01/app/oracle

EOF

cat $ORACLE_HOME/dbs/initFINDR.ora

 

# INFO: If source is CDB → set enable_pluggable_database=true

 

[07] Start auxiliary in NOMOUNT

sqlplus / as sysdba

STARTUP NOMOUNT PFILE='/u01/app/oracle/product/19.3.0/dbhome_1/dbs/initFINDR.ora';

 

→ expected:

SQL> STARTUP NOMOUNT PFILE='...';

ORACLE instance started.

Total System Global Area 2147483648 bytes

Fixed Size                  8900952 bytes

Variable Size             503316480 bytes

Database Buffers         1627389952 bytes

Redo Buffers                7876608 bytes

 

-- validate instance came up

SELECT instance_name, status FROM v$instance;

-- Expected: FINDR   STARTED

 

TNS + LISTENER  —  AUXILIARY HOST

[08] Add both aliases to tnsnames.ora on auxiliary

vi $ORACLE_HOME/network/admin/tnsnames.ora

# --- append these entries ---

 

FINPRD =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = ora-prod-01.corp.internal)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = FINPRD)

)

)

FINDR =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = ora-dr-01.corp.internal)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = FINDR)

)

)

 

[09] Configure STATIC listener entry — required while DB is NOMOUNT

# WARNING: Dynamic registration won't work in NOMOUNT. Static SID_LIST entry is mandatory or RMAN connection will fail.

 

vi $ORACLE_HOME/network/admin/listener.ora

 

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = ora-dr-01.corp.internal)(PORT = 1521))

)

)

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = FINDR)

(ORACLE_HOME = /u01/app/oracle/product/19.3.0/dbhome_1)

(SID_NAME = FINDR)

)

)

 

lsnrctl stop LISTENER

lsnrctl start LISTENER

lsnrctl status LISTENER | grep -A2 'Service\|SID'

 

→ FINDR will show UNKNOWN — that is normal for static entry in NOMOUNT:

Service "FINDR" has 1 instance(s).

Instance "FINDR", status UNKNOWN, has 1 handler(s) for this service...

 

[10] Validate TNS + actual login to source before running RMAN

tnsping FINPRD   # must be OK

tnsping FINDR    # must be OK

# test real connection to source

sqlplus sys/<SYS_PASSWORD>@FINPRD as sysdba

SELECT name, open_mode FROM v$database;

EXIT;

 

# WARNING: If tnsping OK but sqlplus fails → firewall blocking 1521, or wrong password. Fix before RMAN.

 

RMAN ACTIVE DUPLICATE

[11] Connect RMAN to both target and auxiliary

# run from ora-dr-01 (auxiliary host)

rman

CONNECT TARGET sys/<SYS_PASSWORD>@FINPRD;

CONNECT AUXILIARY sys/<SYS_PASSWORD>@FINDR;

 

→ expected:

Recovery Manager: Release 19.0.0.0.0 - Production

connected to target database: FINPRD (DBID=3421876501)

connected to auxiliary database: FINDR (not mounted)

 

# WARNING: ORA-12541 → listener not running or static entry missing (re-check step 09).

# WARNING: ORA-01017 → password file wrong or not created (re-check step 05).

 

[12] Execute DUPLICATE — monitor alert log in parallel session

# INFO: Open a second SSH session now and tail the alert log before hitting run.

 

# --- SECOND TERMINAL (monitor) ---

tail -100f /u01/app/oracle/diag/rdbms/findr/FINDR/trace/alert_FINDR.log

 

# --- RMAN SESSION (step 11 continued) ---

RUN {

ALLOCATE CHANNEL c1 TYPE DISK;

ALLOCATE CHANNEL c2 TYPE DISK;

ALLOCATE AUXILIARY CHANNEL a1 TYPE DISK;

ALLOCATE AUXILIARY CHANNEL a2 TYPE DISK;

DUPLICATE TARGET DATABASE TO FINDR

FROM ACTIVE DATABASE

SPFILE

PARAMETER_VALUE_CONVERT 'FINPRD','FINDR',

'/data01/oradata/FINPRD','/data01/oradata/FINDR',

'/fra01/FINPRD','/fra01/FINDR'

SET DB_UNIQUE_NAME='FINDR'

SET DB_NAME='FINDR'

SET INSTANCE_NAME='FINDR'

SET AUDIT_FILE_DEST='/u01/app/oracle/admin/FINDR/adump'

SET DIAGNOSTIC_DEST='/u01/app/oracle'

SET DB_FILE_NAME_CONVERT

'/data01/oradata/FINPRD','/data01/oradata/FINDR'

SET LOG_FILE_NAME_CONVERT

'/data01/oradata/FINPRD','/data01/oradata/FINDR'

SET LOG_ARCHIVE_DEST_1='LOCATION=/fra01/FINDR'

SET CONTROL_FILES='/data01/oradata/FINDR/control01.ctl',

'/fra01/FINDR/control02.ctl'

NOFILENAMECHECK;

}

 

→ last line on success:

Finished Duplicate Db at 12-NOV-2024 11:43:07

 

# ORA-19505 / ORA-17628  →  disk space issue on auxiliary paths

# ORA-01119              →  directory missing, re-check step 04

# Channel drops mid-run  →  network instability or NET_TIMEOUT too low

#   workaround: add to RMAN config →  CONFIGURE CHANNEL DEVICE TYPE DISK PARAMS 'NET_TIMEOUT=300';

 

POST-DUPLICATE STEPS

 

 

[13] Confirm DB opened — check role, mode, log_mode

sqlplus / as sysdba

SELECT name, db_unique_name, open_mode, log_mode, database_role

FROM   v$database;

 

→ expected:

NAME   DB_UNIQUE_NAME  OPEN_MODE   LOG_MODE   DATABASE_ROLE

------ --------------- ----------- ---------- ----------------

FINDR  FINDR           READ WRITE  ARCHIVELOG PRIMARY

 

[14] Confirm SPFILE is active — not running off pfile

SELECT value FROM v$parameter WHERE name = 'spfile';

-- must return non-null path

-- also check on OS

ls -lh $ORACLE_HOME/dbs/spfileFINDR.ora

 

# WARNING: If spfile value is NULL → RMAN didn't apply SPFILE clause. Check duplicate command, re-run.

 

[15] Verify all files are on auxiliary paths — no FINPRD leftover

-- must return ZERO rows

SELECT 'datafile' src, name FROM v$datafile  WHERE name LIKE '%FINPRD%'

UNION ALL

SELECT 'tempfile' src, name FROM v$tempfile   WHERE name LIKE '%FINPRD%'

UNION ALL

SELECT 'logfile'  src, member FROM v$logfile  WHERE member LIKE '%FINPRD%';

-- if rows returned → file rename needed before production use

 

→ expected:

no rows selected

 

[16] Check redo logs + tempfiles are online

SELECT group#, status, members FROM v$log ORDER BY 1;

-- one group should be CURRENT, others INACTIVE/UNUSED

SELECT file#, name, status FROM v$tempfile;

-- status should be ONLINE

 

[17] Check invalid objects + registry

SELECT count(*) invalid_obj FROM dba_objects WHERE status='INVALID';

-- compare vs source; minor count is acceptable if same on source

SELECT comp_name, version, status FROM dba_registry ORDER BY comp_name;

-- all components should be VALID

 

[18] Force listener re-registration + confirm READY

ALTER SYSTEM REGISTER;

-- wait 30 sec then:

-- (from OS)

lsnrctl status LISTENER | grep -A2 FINDR

 

→ after registration (was UNKNOWN in step 09, now should be READY):

Service "FINDR" has 1 instance(s).

Instance "FINDR", status READY, has 1 handler(s) for this service...

 

[19] Take immediate backup of controlfile + spfile

rman target /

BACKUP CURRENT CONTROLFILE FORMAT '/fra01/FINDR/cf_%d_%T_%s.bkp';

BACKUP SPFILE FORMAT '/fra01/FINDR/spfile_%d_%T_%s.bkp';

LIST BACKUP SUMMARY;

 

FINAL VALIDATION  —  PASTE & RUN

 

sqlplus / as sysdba << 'EOF'

set linesize 120 pagesize 50

prompt === 1. DB IDENTITY ===

SELECT name, db_unique_name, created FROM v$database;

prompt === 2. OPEN MODE + ROLE ===

SELECT open_mode, database_role, log_mode FROM v$database;

prompt === 3. INSTANCE STATUS ===

SELECT instance_name, status, database_status FROM v$instance;

prompt === 4. REDO LOG STATUS ===

SELECT group#, status, members, bytes/1048576 mb FROM v$log ORDER BY 1;

prompt === 5. TEMP TABLESPACE ===

SELECT tablespace_name, status FROM dba_tablespaces WHERE contents='TEMPORARY';

prompt === 6. CONTROLFILE STATUS ===

SELECT name, status FROM v$controlfile;

prompt === 7. SPFILE IN USE ===

SELECT value FROM v$parameter WHERE name='spfile';

prompt === 8. ARCHIVE DEST STATUS ===

SELECT dest_name, status, target FROM v$archive_dest WHERE status='VALID';

prompt === 9. INVALID OBJECTS COUNT ===

SELECT count(*) invalid_count FROM dba_objects WHERE status='INVALID';

prompt === 10. CURRENT SCN ===

SELECT current_scn FROM v$database;

EXIT;

EOF

 

# OS-level sanity

ps -ef | grep pmon | grep -i findr    # ora_pmon_FINDR must be running

lsnrctl status | grep FINDR           # status READY expected

 

# END OF RUNNER DOC # Rev: 1.0  |  Tested: 19.3.0.0.0 / OL7.9