Oracle Database Log Destinations Monitoring Script for Data Guard

calendar Jan 4, 2026 / · 5 min read · Oracle Data Guard Database Monitoring SQL Scripts High Availability Backup Recovery DBA Scripts Oracle Views v$archive_dest_status v$archive_dest  ·
Share on: linkedin copy

Oracle Database Log Destinations Monitoring Script for Data Guard

Purpose

This Oracle SQL script monitors all log destinations in a Data Guard configuration from the primary database. The query provides real-time information about archive log destinations, their status, operational modes, and standby redo log activity. Database administrators use this script to verify that archive logs are being shipped correctly to standby databases and to identify any issues with log transmission.

Original Code

 1set lines 100
 2set numwidth 15
 3column ID format 99
 4column "SRLs" format 99 
 5column active format 99 
 6col type format a4
 7
 8select
 9    ds.dest_id id
10    ,ad.status
11    ,ds.database_mode db_mode
12    ,ad.archiver type
13    ,ds.recovery_mode
14    ,ds.protection_mode
15    ,ds.standby_logfile_count "SRLs"
16    ,ds.standby_logfile_active active
17    ,ds.archived_seq#
18from
19    v$archive_dest_status ds
20    ,v$archive_dest ad
21where
22    ds.dest_id = ad.dest_id
23    and ad.status != 'INACTIVE'
24order by
25    ds.dest_id
26/

Breakdown of Code

Formatting Commands

The script begins with SQL*Plus formatting commands to make the output readable:

  • set lines 100 - Sets the line width to 100 characters
  • set numwidth 15 - Defines numeric column width as 15 characters
  • column ID format 99 - Formats the destination ID column
  • column "SRLs" format 99 - Formats the standby redo log count column
  • column active format 99 - Formats the active standby log count
  • col type format a4 - Formats the archiver type as a 4-character string

Main Query Structure

The query joins two critical Data Guard views:

v$archive_dest_status - Provides runtime and configuration information for archive log destinations. This view shows the current state of log shipping including sequence numbers, modes, and active standby logs.

v$archive_dest - Displays all destinations in the Data Guard configuration with their current values, modes, and status. This view identifies whether destinations are using LGWR or ARCH processes for log transmission.

Selected Columns Explained

  • dest_id - Unique identifier for each archive destination (typically 1 for local, 2+ for remote standby databases)
  • status - Current operational status (VALID, ERROR, DEFERRED, DISABLED)
  • database_mode - Mode of the database at the destination (OPEN, MOUNTED)
  • archiver type - Process handling log archival: LGWR (Log Writer) for real-time shipping or ARCH (Archiver) for asynchronous archiving
  • recovery_mode - Type of recovery being performed at the destination
  • protection_mode - Data Guard protection level: Maximum Performance (default), Maximum Availability, or Maximum Protection
  • standby_logfile_count - Total number of standby redo logs configured at the destination
  • standby_logfile_active - Number of standby redo logs currently being written to
  • archived_seq# - Sequence number of the most recently archived redo log at the destination

Filter Conditions

The WHERE clause filters the results

  • ds.dest_id = ad.dest_id - Joins both views on the destination identifier
  • ad.status != 'INACTIVE' - Excludes inactive destinations to show only configured and active archive targets

Key Points

  • This script must be run on the primary database to monitor log shipping to standby destinations
  • The query helps identify lag between primary and standby by comparing sequence numbers
  • LGWR-based shipping provides better performance and supports synchronous modes for zero data loss
  • ARCH-based shipping is asynchronous and suitable for Maximum Performance mode
  • Active standby redo logs indicate real-time log transmission is occurring

Insights and Monitoring Tips

Status Monitoring

A VALID status indicates the destination is functioning correctly. ERROR status requires immediate investigation as it means logs are not reaching the standby database. DEFERRED status shows that archiving to this destination is temporarily disabled.

Protection Modes

Maximum Performance is the default mode offering best performance with minimal data loss risk. Maximum Availability ensures no data loss without compromising availability, but requires synchronous transmission. Maximum Protection guarantees zero data loss but will shut down the primary if standby transmission fails.

Standby Redo Logs

The number of standby redo logs should equal or exceed the number of online redo logs plus one for each thread. Active standby logs indicate the RFS (Remote File Server) process is receiving redo data. Monitoring these counts helps ensure sufficient log files are configured for continuous log shipping.

Sequence Number Tracking

Comparing archived_seq# values between primary and standby destinations reveals replication lag. A growing gap between sequence numbers indicates transmission delays or failures requiring investigation. Regular monitoring prevents data loss scenarios by alerting administrators to shipping issues early.

Common Use Cases

Database administrators run this script to verify Data Guard health during routine maintenance, after configuration changes, before planned switchovers, and when investigating replication issues. The output provides a quick snapshot of all archive destinations and their operational state, making it essential for production database monitoring.

References

Posts in this series