Oracle Data Guard Lag Troubleshooting: Diagnose Transport and Apply Lag

Introduction

Oracle Data Guard is widely used to maintain standby databases for disaster recovery and high availability. However, a standby database may fall behind its primary because of network problems, high redo generation, storage bottlenecks, or insufficient redo apply capacity.

When lag increases, DBAs must determine whether redo is failing to reach the standby or whether redo has arrived but is not being applied quickly enough.

This distinction is critical because transport lag and apply lag have different causes and require different troubleshooting approaches.

In this guide, we examine practical SQL queries and troubleshooting steps for Oracle Data Guard environments.

What Is Data Guard Transport Lag?

Transport lag measures how far the standby is behind in receiving redo generated by the source database.

Potential causes include:

• Network latency or packet loss.
• Insufficient network bandwidth.
• Archive destination errors.
• Connectivity problems.
• High redo generation on the primary.
• Transport configuration issues.

High transport lag can affect the recovery point objective (RPO), depending on the protection mode and configuration.

What Is Data Guard Apply Lag?

Apply lag indicates how far the standby’s applied data trails the corresponding data on the primary.

A standby can receive redo successfully but apply it more slowly than the primary generates it.

Potential causes include:

• High redo generation.
• CPU constraints.
• Storage read or write latency.
• Recovery process bottlenecks.
• Resource contention.
• Redo gaps.
• Workload patterns that increase recovery overhead.

Apply lag can affect recovery readiness and, in Active Data Guard configurations, the freshness of data used for standby queries.

Step 1: Check the Database Role

Run the following query on the database you are investigating:

SELECT
    name,
    open_mode,
    database_role
FROM v$database;

Confirm that you are connected to the intended standby database before running standby-specific diagnostic queries.

Step 2: Check Transport Lag and Apply Lag

Execute this query on the standby:

SET LINESIZE 200

COLUMN name FORMAT A20
COLUMN value FORMAT A30
COLUMN time_computed FORMAT A25
COLUMN datum_time FORMAT A25

SELECT
    name,
    value,
    time_computed,
    datum_time
FROM v$dataguard_stats
WHERE name IN ('transport lag', 'apply lag');

Example output might show:

Metric	Value
Transport lag	+00 00:00:16
Apply lag	+00 00:00:45

These are illustrative values, not live measurements.

In this example, the standby is behind in receiving redo and is further behind in applying changes.

Pay attention to DATUM_TIME. If it remains unchanged across repeated queries, the standby may not be receiving fresh source information.

Step 3: Check Data Guard Broker Status

If Data Guard Broker is configured, connect to DGMGRL and run:

DGMGRL> SHOW CONFIGURATION;

Then inspect the standby:

DGMGRL> SHOW DATABASE VERBOSE 'STANDBY_DB';

Replace STANDBY_DB with the actual database unique name.

Review:

• Transport lag.
• Apply lag.
• Database status.
• Intended state.
• Relevant broker warnings.

If the broker reports a warning or error, inspect the detailed status and related database alert logs.

Step 4: Check Redo Apply Status

For Oracle versions that provide V$MANAGED_STANDBY, the following query can help identify managed recovery processes:

SELECT
    process,
    status,
    thread#,
    sequence#,
    block#
FROM v$managed_standby
ORDER BY process;

On newer Oracle Database releases, use the appropriate supported views for monitoring recovery processes, such as V$DATAGUARD_PROCESS.

If redo apply is not running when it should be, investigate the database alert log and Data Guard status before attempting corrective action.

Do not restart managed recovery without first determining why it stopped.

Step 5: Check for Archive Log Gaps

Review the archived logs known to the standby:

SELECT
    thread#,
    MAX(sequence#) AS last_received_sequence
FROM v$archived_log
WHERE registrar = 'RFS'
GROUP BY thread#
ORDER BY thread#;

This can help establish the most recent received sequence for each redo thread.

However, the query alone does not prove that no gap exists. Review the sequence history, thread information, recovery state, and Data Guard diagnostics together.

For broker-managed configurations, use the broker status and documented gap-resolution procedures.

Step 6: Determine Whether the Network Is the Bottleneck

If transport lag is increasing, investigate the network path between the primary and standby.

Check:

• Network connectivity.
• Packet loss and latency.
• Available bandwidth.
• Network interface errors.
• Firewall or routing changes.
• Oracle Net connectivity.
• Archive destination errors.
• Redo compression and encryption settings.

Also check the primary database alert log for transport errors.

Do not change redo transport settings solely because lag exists. Establish which part of the transport path is responsible first.

Step 7: Investigate Slow Redo Apply

If transport lag is low but apply lag remains high, focus on the standby’s ability to process received redo.

Investigate:

1. CPU utilization.
2. Storage latency and throughput.
3. Memory pressure.
4. Redo generation on the primary.
5. Recovery process status.
6. Standby workload and concurrent queries.
7. Archive log gaps.
8. Recovery-related wait events and diagnostic reports.

Compare the periods when lag rises with batch processing, large data loads, maintenance operations, and peak transaction activity.

A high apply lag does not automatically mean that the standby needs more CPU. The bottleneck must be identified using database and operating-system evidence.

Step 8: Review Lag History

To examine the recorded lag histogram:

SELECT
    name,
    time,
    unit,
    count,
    last_time_updated
FROM v$standby_event_histogram
WHERE name LIKE '%lag'
  AND count > 0
ORDER BY last_time_updated;

This provides additional context about lag values observed since the standby instance was started.

For a useful comparison, capture snapshots at the beginning and end of the investigation period.

Combine these results with Data Guard statistics, alert logs, workload information, and operating-system metrics.

Step 9: Define Monitoring Thresholds

Monitoring should distinguish between a brief lag increase and a persistent problem.

Useful metrics include:

• Transport lag.
• Apply lag.
• Time since the last received datum.
• Redo apply rate.
• Archive destination errors.
• Recovery process status.
• Database and broker warnings.

Choose alert thresholds according to business RPO/RTO requirements and the architecture. A threshold suitable for one application may be unacceptable for another.

Practical Troubleshooting Decision Table

Observation	Investigation priority
Transport lag is high	Network, redo transport and archive destination
Transport lag is low but apply lag is high	Standby recovery performance
Both lag values are increasing	Transport and apply bottlenecks
DATUM_TIME is not advancing	Connectivity or redo transport
Redo apply is stopped unexpectedly	Alert log and recovery diagnostics
Lag increases during batch jobs	Redo generation and standby capacity

Conclusion

Successful Oracle Data Guard troubleshooting begins by distinguishing transport lag from apply lag.

Use V$DATAGUARD_STATS to establish the current lag, Data Guard Broker to inspect configuration health, and recovery diagnostics to investigate the underlying cause.

Avoid making configuration changes before identifying the bottleneck. A structured approach makes it easier to restore synchronization and maintain disaster recovery readiness.

Official references

• Oracle Database 19c Redo Apply Troubleshooting: https://docs.oracle.com/en/database/oracle/oracle-database/19/haovw/redo-apply-troubleshooting-and-tuning.html
• Oracle Data Guard Monitoring: https://docs.oracle.com/en/database/oracle/oracle-database/19/haovw/monitor-oracle-data-guard-configuration.html

Suggested internal links

• Oracle RMAN Backup Troubleshooting: Common Errors and Solutions
• Oracle Database Security Best Practices for DBAs
• Oracle 19.33 Database Release Update: Applying Patch 39915792