It’s 2 AM, and your monitoring system sends an alert:
“Archive log gap detected between primary and standby database.”
You log in and realize your Oracle Data Guard setup isn’t shipping logs anymore — or the standby is lagging hours behind.
Relax 😌 — this is one of the most common Oracle Data Guard issues, and with the right troubleshooting sequence, you can fix it fast.
In this guide, we’ll walk through how to diagnose and fix broken Data Guard log shipping, using real commands and scenarios from production environments.
What Is Data Guard Log Shipping?
In an Oracle Data Guard environment, the primary database continuously sends redo logs to the standby database.
This process ensures that the standby remains a near-real-time copy of production — essential for disaster recovery.
When log shipping breaks, it means:
- Redo logs are not being transferred, or
- They’re transferred but not being applied on standby.
Either way, your standby is no longer synchronized — which means no failover safety until you fix it.
Common Causes of Broken Log Shipping
- Network issues between primary and standby (port blocked, latency, firewall).
- Archive destination full or invalid.
- Incorrect permissions on standby archive directories.
- Redo transport configuration mismatch (
LOG_ARCHIVE_DEST_n). - Standby or listener not running.
- Archive gaps caused by standby being down for a while.
- Log corruption or failed transfer due to disk I/O issues.
Step-by-Step: How to Diagnose the Problem
Step 1: Check Data Guard Configuration
On the primary database:
DGMGRL> SHOW CONFIGURATION;
If you see:
Configuration Status: ERROR
then something is broken between primary and standby.
Next, check:
DGMGRL> SHOW DATABASE VERBOSE 'standby';
Look for:
- Transport Lag – delay in sending logs.
- Apply Lag – delay in applying logs.
- Error Messages – failed transport or apply status.
Step 2: Verify Archive Log Destination
Run on primary:
SHOW PARAMETER log_archive_dest;
You should see something like:
log_archive_dest_2 SERVICE=STBYDB ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stbydb
✅ Ensure:
- The service name matches your standby TNS entry.
- The destination is enabled and valid.
Step 3: Check Network Connectivity
From the primary host:
tnsping stbydb
If this fails — check listener and network connectivity.
On standby, verify listener status:
lsnrctl status
If it’s not running:
lsnrctl start
Step 4: Check Archive Log Gaps
Run on standby:
SELECT * FROM v$archive_gap;
If it returns rows — there are missing archive logs.
On primary, manually ship the missing logs:
ARCHIVE LOG LIST;
Then:
ALTER SYSTEM ARCHIVE LOG CURRENT;
Copy the missing archive logs manually via SCP or NFS:
scp /u01/app/oracle/arch/1_10543_111.log oracle@standby:/u01/app/oracle/arch/
Then register them on the standby:
ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/arch/1_10543_111.log';
Step 5: Restart Managed Recovery
Sometimes MRP (Managed Recovery Process) stops automatically.
On standby, check:
SELECT process, status, sequence# FROM v$managed_standby;
If MRP0 isn’t listed or status shows “WAITING FOR ARCHIVED LOG”, start recovery manually:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
Step 6: Check for Archive Destination Errors
On the primary, run:
SELECT error FROM v$archive_dest WHERE target='STANDBY';
If you see something like:
ORA-12514: TNS listener does not currently know of service requested
👉 The service name or listener entry is wrong.
Fix your TNS or listener configuration and restart the Data Guard processes.
Step 7: Verify Real-Time Apply
Enable real-time apply to reduce future delays:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
Check again:
SELECT recovery_mode FROM v$archive_dest_status;
Should show:
Managed Real Time Apply
Real Production Example
During a Data Guard switchover test at GTN Technologies, one standby lagged 3 hours behind.
The error:
ORA-12545: Connect failed because target host or object does not exist
We discovered the standby hostname in tnsnames.ora had been changed during a network reconfiguration.
Fix:
vi $ORACLE_HOME/network/admin/tnsnames.ora
Updated the host and restarted the listener:
lsnrctl stop
lsnrctl start
Log shipping resumed immediately, and lag dropped to zero.
Prevention Tips for DBAs
- Monitor log gaps daily with AWR or custom scripts.
- Enable real-time apply on standby to minimize delays.
- Automate cleanup of archive destinations to prevent space issues.
- Test Data Guard connections after any patch, restart, or network change.
- Use Data Guard Broker (DGMGRL) for easier management and health checks.
- Integrate monitoring with tools like Datadog, Prometheus, or OEM.
Related Articles
- ORA-04063 and ORA-00904 on “SYS.DBA_REGISTRY” Has Errors
- ORA-04031: Unable to Allocate Shared Memory – Causes, Fixes, and Prevention (Oracle DBA Guide)
- How to Fix ORA-39181: Only Partial Table Data May Be Exported Due to Fine Grain Access Control in Oracle
Final Thoughts
When Oracle Data Guard log shipping breaks, it’s usually not a disaster — just a signal to check your connections, archive destinations, and recovery processes.
With a disciplined step-by-step approach — and a few quick commands — you can restore synchronization in minutes.
Proactive monitoring and automation will ensure you catch these issues before they cause downtime.
Comments 2