Troubleshooting Oracle Data Guard

Oracle Data Guard — Troubleshooting Guide

🛠️ Oracle Data Guard — Troubleshooting Guide 🔍

Even with the most carefully planned Data Guard setups, issues can arise. A good DBA must be able to identify, troubleshoot, and fix problems quickly to ensure business continuity. Below is a colorful guide to mastering troubleshooting in Oracle Data Guard environments.

🚨 Common Issues in Data Guard

  • Redo Transport Errors – Problems with log shipping from primary to standby.
  • Apply Services Stuck – Redo logs not being applied on standby.
  • Gap Sequences – Missing archive logs causing standby lag.
  • Configuration Mismatches – Parameter or file structure inconsistencies.
  • Network Failures – Latency, packet drops, or disconnections between primary and standby.

🧾 Key Diagnostic Queries

Run these queries to check Data Guard health:

  • SELECT DEST_ID, STATUS, ERROR FROM V$ARCHIVE_DEST;
  • SELECT PROCESS, STATUS, THREAD#, SEQUENCE# FROM V$MANAGED_STANDBY;
  • SELECT SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
  • SELECT SWITCHOVER_STATUS FROM V$DATABASE;

🔧 Step-by-Step Troubleshooting Approach

  1. Check network connectivity between primary and standby.
  2. Review alert.log on both databases for error messages.
  3. Validate LOG_ARCHIVE_DEST settings on primary and standby.
  4. Resolve archive log gaps using FAL_SERVER and FAL_CLIENT.
  5. Restart managed recovery process if redo apply is stuck.
  6. Check disk space and permissions on standby file systems.

⚡ Fixing Archive Log Gaps

If logs are missing on standby:

  • Manually copy the missing logs from primary to standby.
  • Register logs on standby using:
    ALTER DATABASE REGISTER LOGFILE 'path_to_log';
  • Resume managed recovery:
    ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

🌀 Resolving Redo Transport Errors

When redo logs fail to ship:

  • Check listener status on standby (lsnrctl status).
  • Verify TNS entries in tnsnames.ora and listener.ora.
  • Confirm standby service is up and accepting connections.

💡 Pro Tips

  • Enable Data Guard Broker (DGMGRL) to simplify monitoring and troubleshooting.
  • Automate log gap detection with scripts and alerts.
  • Always test failover and switchover procedures in a lab environment.

✨ Final Brushstroke

Troubleshooting Oracle Data Guard is about being proactive and prepared. By mastering these checks and solutions, DBAs can keep their standby databases synchronized and their organizations safe from downtime and data loss.