List All Standby Redo Logs in Oracle Data Guard

calendar Jan 1, 2026 / · 4 min read · Oracle Database Data Guard Standby Logs SQL Scripts Database Administration Redo Logs Oracle Views DBA Tools v$standby_log v$logfile  ·
Share on: linkedin copy

List All Standby Redo Logs in Oracle Data Guard

Purpose

This SQL script helps database administrators quickly identify and list all standby redo logs in an Oracle Data Guard environment. The query retrieves important information about standby redo log groups, including their sequence numbers, sizes in megabytes, and physical file locations, which is essential for monitoring and managing Oracle Data Guard configurations.

Original Code

 1set lines 100 pages 999
 2col member format a70
 3select
 4    st.group#
 5    ,st.sequence#
 6    ,ceil(st.bytes / 1048576) mb
 7    ,lf.member
 8from
 9    v$standby_log st
10    ,v$logfile lf
11where
12    st.group# = lf.group#
13/

Breakdown of the Code

SQL*Plus Formatting Commands

The script begins with formatting settings to improve output readability:

  • set lines 100 pages 999 - Sets line width to 100 characters and page size to 999 rows to minimize page breaks
  • col member format a70 - Formats the member column to display 70 characters wide for full file path visibility

Main SELECT Statement

The query joins two important Oracle Data Guard views:

Selected Columns:

  • st.group# - The standby redo log group identifier number
  • st.sequence# - The log sequence number assigned to the standby redo log
  • ceil(st.bytes / 1048576) mb - Converts the log size from bytes to megabytes using the CEIL function (dividing by 1,048,576)
  • lf.member - The physical file path and name of the standby redo log member

FROM Clause:

  • v$standby_log st - Contains information about standby redo logs including group numbers, sequence numbers, bytes, and status
  • v$logfile lf - Contains information about redo log files, including both online and standby types, with physical member locations

WHERE Clause:

  • st.group# = lf.group# - Joins the two views on the group number to match standby log groups with their corresponding file members

Key Points

  • Standby Redo Logs Role: Standby redo logs are required components in Oracle Data Guard configurations that store redo data received from the primary database
  • Sizing Requirements: Standby redo logs must be equal to or larger than the largest online redo log file on the primary database
  • Group Count: The number of standby redo log groups should be at least one more than the number of online redo log groups for each thread
  • Performance Impact: Single-member standby redo log groups should be placed on the fastest available storage for optimal performance
  • Maximum Protection Mode: Standby redo logs are mandatory when the Data Guard configuration operates in maximum protection or maximum availability modes
  • Real-Time Apply: When real-time apply is enabled, changes from standby redo logs are applied immediately to the standby database

Understanding the Views

V$STANDBY_LOG View

This dynamic performance view displays comprehensive information about standby redo logs in the Data Guard environment. The view contains critical columns including group number, thread number, sequence number, bytes, block size, archive status, and log status. Database administrators use this view to verify standby redo log configuration and monitor their current state.

V$LOGFILE View

The V$LOGFILE view provides detailed information about all redo log files, both online and standby types. Key columns include the group identifier, status, type (ONLINE or STANDBY), and the member name which contains the full physical path. This view is essential for identifying file locations and verifying redo log configuration across the Data Guard setup.

Practical Application

This query is particularly useful in several scenarios for Oracle database administrators. When setting up a new Data Guard configuration, administrators need to verify that standby redo logs are properly created and configured. During troubleshooting, the query helps identify issues with log shipping or apply delays by showing which standby logs are currently active. For capacity planning, the output reveals the size and number of standby redo log groups, helping determine if additional groups or larger sizes are needed.

Output Interpretation

The query results display four columns of information. The GROUP# column shows the log group identifier, which should match between primary and standby configurations. The SEQUENCE# column indicates the current or last used sequence number for that standby redo log group. The MB column displays the size of each standby redo log in megabytes, which should align with or exceed primary redo log sizes. The MEMBER column shows the complete file system path where each standby redo log file physically resides.

References

Posts in this series