Check Oracle Data Guard Standby Synchronization Status

Check Oracle Data Guard Standby Synchronization Status

Purpose

Is the physical standby caught up with the primary? The fastest first answer is a pair of numbers: the log sequence the primary is writing now, and the newest log sequence the standby has applied. If the two are close, redo is flowing and being applied. If the gap is growing, something between the primary and the standby needs attention.

The queries below produce those two numbers. They are a quick check, not a full health report. Later sections explain what each query reads, where it can mislead, and which Oracle views give a more exact answer for lag and gaps. A standby that is behind is a recovery risk, because a failover would lose the redo that has not yet arrived or been applied.

Code

 1-- Run this on the primary database
 2set numwidth 15
 3select max(sequence#) current_seq
 4from v$log
 5/
 6
 7-- Run this on the standby database
 8set numwidth 15
 9select thread#, max(sequence#) last_applied_seq
10from v$archived_log
11where applied = 'YES'
12and registrar = 'RFS'
13group by thread#
14/

Change from the original version of this post: the standby query used to read max(applied_seq#) from v$archive_dest_status. Oracle describes that column as the most recent applied log "received at the destination", which is a per-destination value kept by the database that ships redo, and every Oracle monitoring example found for the view runs it on the primary. The standby query above reads v$archived_log, where Oracle documents the APPLIED column as meaningful on a physical standby. The primary query is unchanged.

Code Breakdown

SQL*Plus formatting

set numwidth 15 is a SQL*Plus setting. Oracle describes it as setting the default width for displaying numbers, and its documented default is 10. A width of 15 gives long numbers more room than the default.

Primary query

v$log displays log file information from the control file. Its SEQUENCE# column holds the log sequence number, and a STATUS of CURRENT marks the current redo log, which Oracle says implies the log is active. max(sequence#) therefore returns the sequence the primary is writing or has just written. On a RAC primary each thread has its own sequence numbers, so add thread# to the select list and the group by if you run more than one thread.

Standby query

v$archived_log displays archived log information from the control file. On a physical standby, the APPLIED column tells you whether an archived log has been applied. Oracle states that the column is meaningful on a physical standby for rows where REGISTRAR is RFS (the remote file server process that received the log). For those rows:

• APPLIED = 'NO' means the log has been received but not yet applied.
• APPLIED = 'IN-MEMORY' means the log has been applied in memory, but the data files have not yet been updated.
• APPLIED = 'YES' means the log has been applied and the data files have been updated.

The query filters on YES and RFS, then groups by thread#, so it returns the newest fully applied sequence for each redo thread. Oracle also notes that the value is always NO for local destinations, which is why the registrar filter matters.

Key Points

• The two numbers are not the same kind of number. The primary query reads the online log that is current. The standby query reads logs that were received and applied. A small difference is built into the comparison, so decide your alert threshold from your own baseline, not from a fixed rule.
• Sequence numbers are per thread. Compare thread to thread on RAC.
• v$archive_dest_status is a primary-side view. Oracle's Data Guard monitoring page runs its gap check on the primary, using type='PHYSICAL' and gap_status != 'NO GAP', and treats no rows returned as good health. The page does not describe the same query as a standby-side check.
• Sequence comparison is not a gap test. It compares two single numbers per thread. Use it as a first look, and use the gap query and the lag views below to confirm.
• Lag is a time, not a count. v$dataguard_stats reports transport lag and apply lag as time intervals, which is what an RPO is written in.

Insights and Best Practices

Confirm with the lag views

v$dataguard_stats displays Data Guard metrics when queried on a target database, and it returns no rows on a primary. Oracle defines transport lag as how far the transport of redo to the target lags behind the generation of redo on the originating database. It defines apply lag as how far the data in the target lags behind the originating database because of delays in propagating and applying redo. Run this on the standby:

1select name, value, time_computed
2from v$dataguard_stats
3where name in ('transport lag', 'apply lag')
4/

Oracle's own health check on a physical standby selects from this view where the name is transport lag or apply lag and the value is greater than +00 00:01:00. No rows returned means good health, with a threshold of one minute.

Run the gap check on the primary

Oracle's monitoring page gives this gap check for the primary:

1select dest_id, status, gap_status
2from v$archive_dest_status
3where type = 'PHYSICAL'
4and gap_status != 'NO GAP'
5/

If it returns rows, the standby has a redo gap at that destination. The GAP_STATUS values Oracle documents are NO GAP, LOG SWITCH GAP, RESOLVABLE GAP, LOCALLY UNRESOLVABLE GAP and UNRESOLVABLE GAP.

Pick thresholds on purpose

Two Oracle numbers exist and they measure different things. The SQL health checks above use one minute. For the Data Guard broker, Oracle recommends setting the ApplyLagThreshold property to at least 15 minutes. The value is in seconds, and a value of 0 means no warnings are generated when an apply lag exists. Oracle does not reconcile the two figures on that page, so choose a threshold that matches your recovery point objective and apply it the same way everywhere.

Understand real-time apply

By default, apply services wait for a standby redo log file to be archived before applying the redo in it. With real-time apply, Oracle says apply services can apply redo as it is received, without waiting for the current standby redo log file to be archived. This also keeps the standby more closely synchronized with the primary. Real-time apply requires a standby redo log and ARCHIVELOG mode, and a delay set on that destination is ignored.

When real-time apply is on, redo is applied before the log is archived, so an archive-based check such as the standby query above can trail the true apply position. v$archive_dest_status.recovery_mode reports MANAGED REAL TIME APPLY when this mode is active.

Schedule the check

Oracle's health queries are written to return no rows when the configuration is healthy. That makes them easy to schedule: run them from a job on a regular interval and alert only when a query returns a row. Where the Data Guard broker is in use, its ApplyLagThreshold property does the warning for you.

When to Run This

• During routine daily health checks of a physical standby.
• Before a planned switchover or failover, to see how much redo is waiting.
• After a network outage or a restart of the primary or standby.
• During heavy batch loads that generate a large amount of redo.
• When an alert reports apply lag and you need a quick view of how many logs are involved.
• When you confirm disaster-recovery readiness for an audit or a test.

Troubleshooting Common Issues

The standby number is far behind the primary number. First run the gap query on the primary. If GAP_STATUS shows a gap, look at the rest of that destination's row in the same view, and check the alert log on both databases.

The standby query returns no rows. The database may not be a physical standby, redo may not have arrived, or no log has reached APPLIED = 'YES' yet. Remove the applied filter to see which logs were received, and check that v$archived_log has RFS rows.

You need to know whether Redo Apply is running. Oracle documents v$dataguard_process as showing one row for each Data Guard process that is running. v$managed_standby is deprecated as of Oracle Database 12c Release 2. On a standby, run:

1select role, thread#, sequence#, action
2from v$dataguard_process
3/

Oracle's sample output for the older v$managed_standby view shows an RFS process that finished receiving one sequence while the managed recovery process MRP0 was applying the one before it. The same pattern, receive ahead of apply, is normal for a standby that is working.

Recent events are unclear. v$dataguard_status displays messages generated by Data Guard events that were also written to the alert log or a trace file. It is a quick way to see recent errors without opening the alert log.

References

• V$ARCHIVED_LOG - Oracle Database 19c Reference - Defines the APPLIED column and says it is meaningful on a physical standby for rows where REGISTRAR is RFS.
• V$ARCHIVE_DEST_STATUS - Oracle Database 18c Reference - Describes APPLIED_SEQ#, the GAP_STATUS values and the RECOVERY_MODE values for each archive destination.
• V$LOG - Oracle Database 19c Reference - Documents the log sequence number and the CURRENT status of the online redo logs.
• V$DATAGUARD_STATS - Oracle Database 19c Reference - Defines transport lag and apply lag and says the view is read on the target database.
• Monitor an Oracle Data Guard Configuration - Oracle Database 26ai High Availability Overview - Oracle's gap, transport lag and apply lag health queries, and the 15-minute ApplyLagThreshold recommendation.
• Redo Apply Services - Oracle Data Guard Concepts and Administration 19c - Explains real-time apply and its standby redo log and ARCHIVELOG requirements.
• Managing Physical and Snapshot Standby Databases - Oracle Data Guard 19c - Monitoring views for Redo Apply, including v$dataguard_process, v$archived_log and the deprecated v$managed_standby.
• SET system variable summary - SQL*Plus User's Guide and Reference 19c - Describes SET NUMWIDTH as the default width for displaying numbers.
• Data Guard Physical Standby Setup in Oracle Database 11g Release 2 - ORACLE-BASE - Shows v$archived_log queries with the applied column on the primary and the standby.