Check Missing Archive Logs in Oracle Data Guard Standby Database

calendar Nov 26, 2025 / · 3 min read · Oracle Data Guard Database Administration Standby Database Archive Logs Replication Troubleshooting SQL Query  ·
Share on: linkedin copy

Check Missing Archive Logs in Oracle Data Guard Standby Database

Purpose

This SQL query helps database administrators identify missing archive logs on an Oracle Data Guard standby database. When archive logs fail to transfer from the primary to the standby database, it creates gaps that prevent proper synchronization. Running this query on the standby database reveals which log sequences are present locally but missing at the destination, allowing administrators to take corrective action before the gap becomes too large.

Original Code

 1select local.thread#
 2,      local.sequence# from 
 3       (select thread#
 4       ,       sequence# 
 5       from    v$archived_log 
 6       where dest_id=1) local 
 7where  local.sequence# not in 
 8       (select sequence#
 9       from v$archived_log
10       where dest_id=2 and 
11       thread# = local.thread#)
12/

Code Breakdown

The query works by comparing two sets of archive log records from the v$archived_log view.

Inner Query (local):

  • Selects thread# and sequence# from v$archived_log
  • Filters for dest_id=1, which represents the local archived logs on the standby database
  • Creates a subquery alias called local

Outer Query:

  • Retrieves thread number and sequence number from the local set
  • Uses NOT IN clause to exclude sequences that exist in dest_id=2
  • Filters by matching thread# to ensure comparison within the same redo thread

Result: The query returns all archive log sequences that exist locally (dest_id=1) but are missing from the standby destination (dest_id=2).

Key Points

  • Run on standby database only: This query must be executed on the standby database to detect gaps
  • dest_id values: dest_id=1 typically refers to local archived logs, while dest_id=2 refers to the standby destination
  • Thread awareness: The query accounts for RAC environments where multiple threads exist
  • Gap detection: Missing sequences indicate synchronization issues that require immediate attention

Insights and Best Practices

Oracle Data Guard relies on continuous archive log transfer to keep standby databases synchronized with the primary. When archive logs go missing, the standby database cannot apply changes, creating a recovery gap that increases over time. This query provides a quick diagnostic tool to identify specific missing sequences.

For small gaps (fewer than 15-20 logs), administrators can manually copy missing archive logs from the primary database and register them on the standby using ALTER DATABASE REGISTER LOGFILE. For larger gaps, Oracle recommends using incremental backup methods to resynchronize the standby database.

Alternative gap detection methods include querying v$archive_gap view or checking v$managed_standby to see where log apply processes have stopped. Regular monitoring of archive log gaps prevents situations where standby databases fall too far behind to be useful for disaster recovery or failover scenarios.

References

Posts in this series