How to Verify Oracle Dataguard Standby Database Sync

2 Replies

Check physical Standby is in sync Dataguard in Oracle

The following command will help to check the Standby is synced with Primary Database in Oracle Dataguard Environment.

On Primary Server:

Check the current log sequence on the Primary database:

select thread#, max(sequence#) "Last Primary Seq Generated" from v$archived_log val, v$database vdb where val.resetlogs_change# = vdb.resetlogs_change# group by thread# order by 1;

OR 

Archive log list;

Check both primary and secondary archive in single command:

SELECT b.last_seq prmy_last_file,
      a.applied_seq stdby_last_file ,TO_CHAR (a.latest_apply_time, 'dd/mm/yyyy hh24:mi:ss') stdby_latest_time
 FROM (SELECT   resetlogs_id, thread#, MAX (sequence#) applied_seq, MAX (next_time) latest_apply_time
           FROM v$archived_log
          WHERE applied = 'YES'
       GROUP BY resetlogs_id, thread#) a,
      (SELECT   resetlogs_id, thread#, MAX (sequence#) last_seq
           FROM v$archived_log
       GROUP BY resetlogs_id, thread#) b
WHERE a.thread# = b.thread#
ORDER BY a.thread#;

PRMY_LAST_FILE STDBY_LAST_FILE STDBY_LATEST_TIME
-------------- --------------- ---------------------------
         63786           63786 16/10/2023 09:38:45

On Physical Standby Server:

Check the received log on the standby database

--Check received log on standby
select thread#, max(sequence#) "Last Standby Seq Received" from v$archived_log val, v$database vdb where val.resetlogs_change# = vdb.resetlogs_change# group by thread# order by 1;

Check the applied log on the Standby database:

--Check applied log on standby
select thread#, max(sequence#) "Last Standby Seq Applied" from v$archived_log val, v$database vdb where val.resetlogs_change# = vdb.resetlogs_change# and val.applied in ('YES','IN-MEMORY') group by thread# order by 1;


On Standby: Even we can check with the archive gap:

col thread# for a20
col low_sequence# for a20
col high_sequence# for a20
select * from v$archive_gap;

Get the list of archived applied:

select sequence#, first_time, next_time, applied from v$archived_log where applied='YES' order by sequence#;

2 thoughts on “How to Verify Oracle Dataguard Standby Database Sync”

    1. SandeepSingh's avatarSandeepSingh Post author

      SET LINESIZE 200
      COL “THREAD#” FORMAT 999
      COL “LAST_SEQ_RECEIVED” FORMAT 999999
      COL “LAST_SEQ_APPLIED” FORMAT 999999
      COL “APPLY_LAG(MIN)” FORMAT 9999
      COL “TRANSPORT_LAG(MIN)” FORMAT 9999

      SELECT
      a.thread# AS “THREAD#”,
      a.last_seq_received AS “LAST_SEQ_RECEIVED”,
      b.last_seq_applied AS “LAST_SEQ_APPLIED”,
      ROUND(EXTRACT(MINUTE FROM c.apply_lag) + EXTRACT(HOUR FROM c.apply_lag)*60) AS “APPLY_LAG(MIN)”,
      ROUND(EXTRACT(MINUTE FROM c.transport_lag) + EXTRACT(HOUR FROM c.transport_lag)*60) AS “TRANSPORT_LAG(MIN)”
      FROM
      (SELECT thread#, MAX(sequence#) AS last_seq_received
      FROM v$archived_log
      WHERE resetlogs_change# = (SELECT resetlogs_change# FROM v$database)
      GROUP BY thread#) a
      JOIN
      (SELECT thread#, MAX(sequence#) AS last_seq_applied
      FROM v$log_history
      GROUP BY thread#) b
      ON a.thread# = b.thread#
      JOIN
      v$dataguard_stats c
      ON 1=1
      ORDER BY a.thread#;

      Reply ↓

Leave a Reply