Display Log Destinations Options for Oracle Data Guard Primary Database

calendar Jan 5, 2026 / · 4 min read · Oracle Database Data Guard Database Administration Archive Logs Primary Database Monitoring Scripts SQL Queries High Availability v$archive_dest  ·
Share on: linkedin copy

Display Log Destinations Options for Oracle Data Guard Primary Database

Purpose

This SQL script displays detailed information about all configured archive log destinations on an Oracle Data Guard primary database. The query retrieves critical configuration parameters from the V$ARCHIVE_DEST view, allowing database administrators to monitor how redo logs are transmitted to standby databases and archived locally. This monitoring is essential for ensuring proper log shipping, identifying configuration issues, and maintaining high availability in Data Guard environments.

Code

 1set numwidth 8 lines 100
 2column id format 99 
 3select
 4  dest_id id
 5  ,archiver
 6  ,transmit_mode
 7  ,affirm
 8  ,async_blocks async
 9  ,net_timeout net_time
10  ,delay_mins delay
11  ,reopen_secs reopen
12  ,register
13  ,binding 
14from
15  v$archive_dest
16order by
17  dest_id
18/

Breakdown of Code

Formatting Commands

The script begins with formatting commands to improve output readability:

  • set numwidth 8 lines 100 - Sets numeric column width to 8 characters and line width to 100 characters
  • column id format 99 - Formats the ID column to display 2 digits

Query Structure

The SELECT statement retrieves ten important columns from the V$ARCHIVE_DEST dynamic performance view:

dest_id (id) - Archive destination identifier ranging from 1 to 31, indicating which LOG_ARCHIVE_DEST_n parameter corresponds to this destination.

archiver - Specifies the Oracle process responsible for archiving (ARCH for archiver process, LGWR for log writer process).

transmit_mode - Indicates how redo data is transmitted: SYNCHRONOUS (transaction waits until redo is sent), ASYNCHRONOUS (transaction commits without waiting), or PARALLELSYNC (parallel synchronous transmission).

affirm - Shows whether disk write confirmation is required (YES or NO) before redo is considered successfully transmitted.

async_blocks (async) - Number of blocks used for asynchronous redo transport buffer when using ASYNC mode.

net_timeout (net_time) - Network timeout value in seconds (default 30) before the primary database stops waiting for acknowledgment from the standby site.

delay_mins (delay) - Minimum time lag in minutes between when redo data is archived on the standby site and when it becomes available for recovery (default 30 minutes).

reopen_secs (reopen) - Number of seconds (default 300) before redo transport services attempt to reopen a failed destination.

register - Indicates whether the destination registers with the Data Guard broker.

binding - Specifies how destination failure affects archival operations (MANDATORY or OPTIONAL).

Key Points

  • The V$ARCHIVE_DEST view displays all destinations in the Data Guard configuration, including both local and remote destinations
  • Monitoring transmit_mode is critical for understanding data protection levels and transaction performance impact
  • The net_timeout parameter prevents primary database stalls when standby databases are unreachable
  • Oracle recommends net_timeout values of 8-10 seconds minimum to avoid false network errors
  • The binding column determines whether a destination failure will halt archiving (MANDATORY) or allow it to continue (OPTIONAL)
  • Destinations with ASYNC transmit_mode provide better primary database performance but offer lower data protection than SYNC mode
  • The reopen_secs parameter controls automatic retry behavior for failed destinations at log switch time

Insights

Running this query regularly on the primary database provides immediate visibility into the health and configuration of your Data Guard environment. Database administrators can quickly identify misconfigurations, such as incorrect transmit modes for maximum protection requirements or inappropriate timeout values that could cause performance issues.

The dest_id column allows you to correlate query results with specific LOG_ARCHIVE_DEST_n initialization parameters, making troubleshooting more efficient. When destinations show unexpected values, you can immediately reference the corresponding parameter for correction.

Understanding the relationship between transmit_mode, affirm, and binding parameters is essential for balancing data protection requirements against primary database performance. For example, a destination configured with SYNC transmit_mode and MANDATORY binding provides maximum protection but may impact transaction commit times if network latency exists.

The delay_mins parameter is particularly useful for protecting against logical corruption by creating a time buffer before applying redo to standby databases. This allows administrators time to detect and prevent propagation of corrupted data.

References

Posts in this series