Check Oracle Data Guard Standby Synchronization Status

calendar Jan 3, 2026 / · 5 min read · v$log v$archive_dest_status Oracle Data Guard Standby Database Database Monitoring Oracle Views DBA Scripts Sequence Numbers Replication  ·
Share on: linkedin copy

How to Check Physical Standby Database Synchronization

Physical standby databases in Oracle Data Guard need regular monitoring to ensure they stay synchronized with the primary database. The most effective way to check synchronization status is by comparing the current sequence number on the primary database with the applied sequence number on the standby database.

Purpose

These SQL queries help database administrators quickly identify if a physical standby database is lagging behind the primary database. By comparing sequence numbers, you can determine the replication gap and take corrective action if the standby falls too far behind. This monitoring is essential for disaster recovery readiness and maintaining your Recovery Point Objective (RPO).

Code Breakdown

Original 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 max(applied_seq#) last_seq
10from v$archive_dest_status
11/

Primary Database Query

The first query runs on the primary database and retrieves the highest sequence number from the v$log view. The v$log view displays information about online redo log files from the control file, including the current log sequence number being written. The set numwidth 15 command formats the numeric output to display up to 15 digits for better readability.

Standby Database Query

The second query executes on the standby database and retrieves the maximum applied sequence number from v$archive_dest_status. This view displays runtime and configuration information for archived redo log destinations, including which sequence numbers have been successfully applied to the standby. The applied_seq# column specifically shows the last archived redo log file that was applied at the standby destination.

Key Points

Understanding Sequence Numbers

Sequence numbers are sequential identifiers assigned to each redo log file as it fills up and switches. When the primary database fills one online redo log, it switches to the next one and increments the sequence number. These sequence numbers are critical for tracking which changes have been shipped and applied to standby databases.

Identifying Replication Lag

The difference between the primary's current sequence and the standby's applied sequence indicates how many log files are pending. A gap of 1-2 sequences is typically normal during regular operations, but larger gaps may indicate network issues, slow apply rates, or other problems.

View Differences

The v$log view on the primary shows currently active online redo logs, while v$archive_dest_status on the standby shows archived log information including what has been received and applied. This distinction is important because the standby works with archived logs, not the primary's current online logs.

Insights and Best Practices

Regular Monitoring Schedule

Oracle recommends monitoring Data Guard lag at least every 5-15 minutes in production environments. Automated scripts with alert thresholds should notify administrators when the sequence gap exceeds acceptable limits based on your RPO requirements.

Transport vs Apply Lag

This script specifically checks apply lag (how much has been applied to the standby). You should also monitor transport lag (how much has been shipped to the standby) using the v$dataguard_stats view for complete visibility.

Alternative Monitoring Methods

More comprehensive monitoring queries can show additional details like the actual time lag, SCN differences, and per-thread statistics in RAC environments. The v$archived_log view provides detailed history of all archived logs including timestamps and apply status.

Threshold Guidelines

As a best practice, Oracle recommends setting apply lag thresholds to at least 15 minutes before generating warnings. For high-transaction environments or strict RPO requirements, you may need shorter thresholds with more frequent monitoring.

Real-Time Apply

When Real-Time Apply is enabled, redo data is applied to the standby as it arrives, before archiving completes. This significantly reduces apply lag and can bring your standby within seconds of the primary.

When to Use This Check

Use this monitoring approach during:

  • Regular operational health checks
  • Before planned switchovers or failovers
  • After network outages or connectivity issues
  • During high transaction volume periods
  • When validating disaster recovery readiness

Troubleshooting Common Issues

If you find a large sequence gap, check the archive destination status on the primary for errors, verify network connectivity between sites, review alert logs on both databases, and confirm that the standby's managed recovery process (MRP) is running.

References

Posts in this series