Home / OCI

OCITroubleshootingAdvanced

Data Guard on OCI Base Database: transport lag and apply lag growing - finding the bottleneck between primary and standby

The standby falls minutes or hours behind. Separate transport lag from apply lag, check archive gaps, redo rate versus network bandwidth, and standby I/O, then fix each with Data Guard Broker and OCI settings.

Deepak Bhardwaj5 min read

Applies toOracle Base Database Service (VM DB systems) and Exadata Database Service with Data Guard associations, Oracle Database 19c and 23ai, DGMGRL

Symptoms

  • The OCI Console shows Data Guard apply lag of many minutes, or an alarm fires.
  • DGMGRL show configuration reports warnings such as ORA-16853: apply lag has exceeded specified threshold or ORA-16855: transport lag has exceeded specified threshold.
  • A planned switchover is blocked or slow because the standby is behind.

Lag means your RPO and RTO are worse than you think. Two very different problems hide behind it:

Lag Meaning
Transport lag Redo generated on the primary hasn't reached the standby yet (network, archive gaps)
Apply lag Redo arrived but hasn't been applied yet (standby too slow, apply stopped)

Step 1: measure both

On the standby:

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

With the broker:

DGMGRL> SHOW CONFIGURATION LAG;
DGMGRL> SHOW DATABASE 'db_stby';
DGMGRL> SHOW DATABASE 'db_stby' 'RecvQEntries';
DGMGRL> SHOW DATABASE 'db_prim' 'SendQEntries';

SendQEntries on the primary lists redo not yet shipped; RecvQEntries on the standby lists redo received but not applied.

Step 2: transport lag

Archive gap?

-- On the standby
SELECT * FROM v$archive_gap;

-- On the primary: destination status and errors
SELECT dest_id, status, error, gap_status, db_unique_name
FROM   v$archive_dest_status WHERE dest_id <= 4;

A gap usually follows a network outage or a primary archived log deleted before shipping. Data Guard fetches missing logs automatically (FAL) if they still exist on the primary. If they were deleted, restore them from backup on the primary or roll the standby forward with an incremental backup (RECOVER STANDBY DATABASE FROM SERVICE).

-- 19c+: roll forward a standby that's far behind, from the primary over the network
RMAN> RECOVER STANDBY DATABASE FROM SERVICE db_prim_tns;

Redo rate vs bandwidth

-- Primary: redo generated per hour, last 24 hours
SELECT TRUNC(first_time, 'HH') AS hour, ROUND(SUM(blocks * block_size)/1024/1024) AS redo_mb
FROM   v$archived_log
WHERE  dest_id = 1 AND first_time > SYSDATE - 1
GROUP  BY TRUNC(first_time, 'HH') ORDER BY 1;

Peak redo MB per second must fit comfortably within the network between primary and standby. Cross-region standbys on OCI use the backbone via remote peering or DRG; bandwidth is shared with everything else on that path.

Fixes:

  • Network tuning: set larger TCP socket buffers and SDU for redo transport in sqlnet.ora/tnsnames.ora (SEND_BUF_SIZE, RECV_BUF_SIZE, SDU), sized to the bandwidth-delay product. Oracle's Data Guard redo transport best-practice paper gives the formula.
  • Redo transport compression (COMPRESSION=ENABLE on the destination) reduces bytes over the wire at a CPU cost; it requires the Advanced Compression option, so check licensing.
  • Reduce redo: batch jobs that update the same rows repeatedly, unneeded NOLOGGING reversals, indexes rebuilt online during peak.

Transport mode

DGMGRL> SHOW DATABASE 'db_stby' LogXptMode;

ASYNC is normal for cross-region. SYNC across regions adds commit latency on the primary; FASTSYNC is a middle ground for nearby sites.

Step 3: apply lag

Is apply running?

SELECT process, status, thread#, sequence#, block#
FROM   v$managed_standby WHERE process LIKE 'MRP%';      -- or v$dataguard_process in newer releases

DGMGRL> SHOW DATABASE 'db_stby' 'StatusReport';
DGMGRL> EDIT DATABASE 'db_stby' SET STATE='APPLY-ON';

If apply runs but lags:

Cause Check Fix
Standby I/O slower than primary AWR on standby (Active Data Guard) or OS iostat; block volume performance Match storage performance (same VPU/shape class)
Smaller standby shape Compare OCPUs/memory Same shape as primary
Parallel apply limited APPLY_PARALLEL / recovery parallelism Let it default to automatic; avoid forcing low parallelism
Heavy read workload on Active Data Guard standby Read queries competing for I/O Move reports off peak or scale the standby
Apply stopped after an error (e.g. datafile added on primary, standby_file_management=MANUAL) Alert log on standby Set STANDBY_FILE_MANAGEMENT=AUTO, fix the file, restart apply

Warning

Don't "fix" a lagging standby by recreating it during an incident window unless you have to. While it rebuilds, you have no DR at all. Roll it forward first.

Step 4: thresholds and monitoring

Set broker thresholds so you hear about lag early:

DGMGRL> EDIT DATABASE 'db_stby' SET PROPERTY ApplyLagThreshold = 300;
DGMGRL> EDIT DATABASE 'db_stby' SET PROPERTY TransportLagThreshold = 120;

On OCI, create a Monitoring alarm on the Data Guard lag metrics published for the DB system (namespace and metric names are listed in the Base Database metrics documentation), and send it to a Notifications topic.

Prevent it next time

  • Size standby compute and storage identically to the primary.
  • Measure peak redo rate quarterly and compare with available bandwidth.
  • Keep STANDBY_FILE_MANAGEMENT=AUTO and archived log deletion policy APPLIED ON ALL STANDBY.
  • Practise switchover every quarter; a switchover exposes lag problems on your schedule, not during a disaster.

Related fixes