🚀 Keeping Your Oracle Data Guard Healthy! 🚀
If you’re managing an Oracle Data Guard setup, regular health checks are crucial to ensure seamless disaster recovery and high availability. Here are 10 essential SQL queries that help monitor and troubleshoot Data Guard performance. 📊🔍
1️.Check Data Guard Role & Database Mode
SELECT DATABASE_ROLE, OPEN_MODE FROM V$DATABASE;
✅ Ensures the database is in the correct role: PRIMARY or STANDBY.
2️.Verify Log Transport Status
SELECT DEST_ID, STATUS, DESTINATION, ERROR FROM V$ARCHIVE_DEST WHERE STATUS <> ‘VALID’;
🔄 Confirms if redo logs are successfully shipping to the standby.
3️.Check Standby Apply Lag
SELECT NAME, VALUE FROM V$DATAGUARD_STATS WHERE NAME LIKE ‘apply lag%’;
⏳ Helps identify any delay in applying redo logs on standby.
4️.Monitor Transport Lag
SELECT NAME, VALUE FROM V$DATAGUARD_STATS WHERE NAME LIKE ‘transport lag%’;
🚀 Ensures logs are being transported with minimal delay.
5️.Check Last Applied Log Sequence
SELECT THREAD#, SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG WHERE APPLIED=’YES’ ORDER BY SEQUENCE# DESC;
📌 Confirms the latest applied archive log on the standby.
6️.Find Missing Archive Logs
SELECT LOCAL.SEQUENCE#, STANDBY.SEQUENCE# FROM
(SELECT MAX(SEQUENCE#) SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=1) LOCAL,
(SELECT MAX(SEQUENCE#) SEQUENCE# FROM V$ARCHIVED_LOG WHERE APPLIED=’YES’) STANDBY;
⚠️ Helps identify gaps between primary and standby logs.
7️.Validate Standby File Management
SHOW PARAMETER STANDBY_FILE_MANAGEMENT;
🔄 Should be AUTO for automatic standby file management.
8️.Identify Standby Errors
SELECT MESSAGE FROM V$DATAGUARD_STATUS WHERE SEVERITY IN (‘Error’, ‘Fatal’) ORDER BY TIMESTAMP DESC;
🚨 Alerts you to any serious issues in Data Guard.
9️.Monitor Redo Apply Process
SELECT PROCESS, STATUS, THREAD#, SEQUENCE# FROM V$MANAGED_STANDBY WHERE PROCESS IN (‘MRP0’, ‘RFS’);
🔎 Ensures redo apply (MRP) and redo shipping (RFS) are active.
🔟 Check Primary-Standby Datafile Consistency
SELECT FILE#, STATUS, ERROR FROM V$DATAFILE_HEADER WHERE ERROR IS NOT NULL;
⚠️ Detects corrupt or missing datafiles on the standby.
🔹 Pro Tip: Automate these checks with a monitoring script or integrate with OEM Cloud Control for proactive alerts. 🚀