How to Verify Oracle Data Guard Sync Status

Verify whether your Primary Oracle Database is in sync with the Standby Database in a Data Guard configuration.

---

Solution:

To ensure synchronization, you need to check the Managed Recovery Process (MRP) and query relevant views that provide real-time status and logs. Below are the key SQL queries that help you confirm synchronization.

1. Check the Managed Standby Recovery Process (MRP)

Run the following query to check the status of the MRP process on the standby database:

SELECT thread#, sequence#, process, client_process, status, blocks 
FROM v$managed_standby;

• THREAD# – Identifies the thread number.
• SEQUENCE# – Displays the current sequence number being processed.
• PROCESS – Indicates the type of process running.
• CLIENT_PROCESS – Shows the client process (e.g., ARCH for archive or MRP for managed recovery).
• STATUS – Current status of the process (e.g., ACTIVE or WAITING).
• BLOCKS – Number of blocks being processed.

---

2. Check for Errors in Data Guard Status

Run this query to look for any critical errors (severity: Error or Fatal) in the Data Guard status log:

SELECT gvi.thread#, timestamp, message 
FROM gv$dataguard_status gvds, gv$instance gvi 
WHERE gvds.inst_id = gvi.inst_id 
AND severity IN ('Error', 'Fatal') 
ORDER BY timestamp, thread#;

This query will help you identify potential issues that could disrupt synchronization between the primary and standby databases.

---

3. Compare Last Received and Applied Archive Logs

Use this query to compare the last archive log received and applied on the standby:

SELECT ARCH.THREAD# "Thread", 
       ARCH.SEQUENCE# "Last Sequence Received", 
       APPL.SEQUENCE# "Last Sequence Applied", 
       (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" 
FROM (SELECT THREAD#, SEQUENCE# 
      FROM V$ARCHIVED_LOG 
      WHERE (THREAD#, FIRST_TIME) IN 
            (SELECT THREAD#, MAX(FIRST_TIME) 
             FROM V$ARCHIVED_LOG 
             GROUP BY THREAD#)) ARCH, 
     (SELECT THREAD#, SEQUENCE# 
      FROM V$LOG_HISTORY 
      WHERE (THREAD#, FIRST_TIME) IN 
            (SELECT THREAD#, MAX(FIRST_TIME) 
             FROM V$LOG_HISTORY 
             GROUP BY THREAD#)) APPL 
WHERE ARCH.THREAD# = APPL.THREAD#;

• Last Sequence Received – The most recent archive log received by the standby.
• Last Sequence Applied – The most recent archive log applied on the standby.
• Difference – Shows the gap (if any) between the received and applied sequence numbers.

If the Difference is 0 (zero), your standby database is fully synchronized with the primary. Any positive value indicates lag that may require attention.