Oracle Data Guard Standby Redo Log Query: Groups and Members

calendar Jan 1, 2026 / · 10 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

A switchover attempted on a busy Friday afternoon reveals the gap: the standby has been synchronized for months, but no standby redo log group was sized to match the primary's 500 MB online redo logs. The configuration worked fine for the archived-log transport model, but enabling real-time apply requires SRL groups that were never created. The query below surfaces exactly this class of misconfiguration before it becomes a switchover incident.

V$STANDBY_LOG and V$LOGFILE together hold the complete picture of standby redo log (SRL) configuration. V$STANDBY_LOG reports each group's sequence number, size in bytes, thread assignment, and operational status. V$LOGFILE holds the physical member paths. Joining them on GROUP# gives a DBA the full inventory: how many groups exist, how large each is, and where the files live on disk. This is the foundational check before a switchover, before adding real-time apply, or when log shipping shows unexpected delays.

Standby redo logs are distinct from online redo logs. They are written by the Remote File Server (RFS) process on the standby, not by the Log Writer (LGWR). The primary's LGWR ships redo to the standby's RFS, which writes into SRL groups. The Managed Recovery Process (MRP) then reads from those groups and applies the changes. When real-time apply is active, this pipeline is continuous — changes flow from the primary's LGWR directly into SRL groups and into the standby data files without waiting for the primary to archive first.

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/

For a more complete diagnostic view that includes STATUS and THREAD# — essential in RAC configurations and when investigating log-shipping delays:

 1set lines 160 pages 999
 2col member  format a60
 3col status  format a12
 4
 5select
 6    st.group#
 7    ,st.thread#
 8    ,st.sequence#
 9    ,ceil(st.bytes / 1048576)  mb
10    ,st.status
11    ,lf.member
12from
13    v$standby_log st
14    ,v$logfile lf
15where
16    st.group# = lf.group#
17order by
18    st.thread#, st.group#
19/

Code Breakdown

SQL*Plus formatting

set lines 100 pages 999 widens the output line to 100 characters and suppresses page breaks at 999 rows — both settings prevent wrapped lines from breaking the member path column. col member format a70 allocates 70 characters to the file-path column, which covers most standard mount-point naming conventions. Extend it to a80 or longer if your SRL paths use deep directory trees.

Driving view: V$STANDBY_LOG

v$standby_log contains one row per standby redo log group, not per member file. Key columns in this query:

  • group# — the group identifier. SRL groups are numbered independently of online redo log groups, so groups 4, 5, and 6 on a standby that has online groups 1, 2, and 3 is a normal configuration.
  • sequence# — the redo sequence number written to this group in its most recent use. A sequence of zero means the group has never been used and its status is UNASSIGNED.
  • ceil(st.bytes / 1048576) — converts the raw byte count to megabytes, rounding up. CEIL ensures that a 524,288,000-byte log reports as 500 MB rather than 499 MB. That rounding matters when comparing against the primary's v$log.bytes to verify the sizing rule is met.
  • thread# (extended query) — the redo thread the group serves. In a single-instance Data Guard pair, all groups belong to thread 1. In a RAC primary with two active nodes, each thread needs its own dedicated SRL groups on the standby; thread 2 redo cannot land in a group assigned to thread 1.
  • status (extended query) — the operational state of the group: UNASSIGNED (available, not in use), ACTIVE (the RFS process is currently writing to this group), CLEARED (reset after a log switch), or CURRENT (in real-time apply, the group currently being read by MRP). All groups showing UNASSIGNED while log shipping is expected to be active indicates that redo is arriving as archived logs only, or that real-time apply is not running.

The join: V$LOGFILE

v$logfile holds one row per physical log member, not per group. Its TYPE column carries 'STANDBY' for SRL members and 'ONLINE' for online redo log members. The join on st.group# = lf.group# — combined with the fact that v$standby_log only contains standby group numbers — returns SRL members only. No explicit TYPE = 'STANDBY' filter is required, but adding one makes the intent unambiguous in production scripts shared with a team.

The member column shows the full operating-system path of each physical file. This is what a storage or capacity team needs to locate the files, verify the mount point, or move them to faster storage.

Key Points

  • SRL sizing rule: each SRL group must be at least as large as the largest online redo log group on the primary. Run SELECT group#, CEIL(bytes/1048576) mb FROM v$log ORDER BY group# on the primary and compare against this query's output to verify compliance.
  • Group count formula: Oracle's rule is at least (online redo log groups per thread) + 1 SRL groups per thread on the standby. A primary with three online redo log groups needs at least four SRL groups on each standby. Too few groups and the RFS process cannot find an UNASSIGNED group to write into, causing log-shipping stalls.
  • Single-member SRL groups are the default but not optimal. This query shows exactly one member row per group in a default configuration — one physical file, one point of failure. Adding a second member to each group mirrors the protection that multiplexed online redo logs give the primary.
  • Maximum protection and maximum availability modes require SRLs. In these modes, the primary's LGWR synchronously ships redo to the standby's RFS before the commit acknowledgment returns to the client. Without SRL groups to receive that redo, the primary will hang waiting on the standby.
  • SRL groups are reused, not consumed. When MRP finishes applying a standby redo log, the contents are archived to the standby's archive log destination and the group status returns to UNASSIGNED. The group itself is kept and reused for the next redo shipment.
  • A STATUS of ACTIVE when no shipping is in progress indicates an abnormal RFS termination. The group is not being written but is not cleared. Manual clearing or an MRP restart is required to return it to UNASSIGNED.

Insights and Best Practices

Confirm sizing against the primary before enabling real-time apply

Before running ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE, compare the MB column from this query on the standby against the primary's v$log sizes. Every SRL group on the standby must be >= the largest value from the primary. Mismatched sizes allow the configuration to start but cause RFS to fail mid-write when a high-redo workload generates more redo per log cycle than the undersized standby group can hold.

Size for peak load, not average load

The primary's redo generation rate spikes during batch loads, partition moves, and bulk inserts. The sizing rule refers to the log file size, not the average redo rate. If the primary's online redo logs are 200 MB but frequent log switches occur during batch windows, consider increasing both the primary's online redo log size and the standby SRL size to 500 MB or 1 GB. Fewer log switches reduce transport overhead and the frequency of SRL group transitions on the standby.

RAC: verify one set of SRL groups per active thread

In a RAC primary with two active threads, the standby needs two independent sets of SRL groups — one set for thread 1 and one for thread 2. The extended query's THREAD# column confirms this at a glance. A standby with all three groups assigned to thread 1 cannot receive redo from thread 2, and the standby will fall behind immediately once the second RAC node becomes active.

Multiplex SRL members on separate storage

The member column reveals the multiplexing factor for each group. A group that returns only one row in this output has a single member — a single point of failure on the standby's storage. Add a second member to each SRL group on a separate volume with ALTER DATABASE ADD STANDBY LOGFILE MEMBER '/u02/oradata/standby/srl_0n_b.log' TO GROUP n. The per-group row count in this query increases to two when multiplexing is in place.

Run this check before every switchover and every real-time apply enablement

Add this query to every pre-switchover checklist. A healthy pre-switchover result shows all groups UNASSIGNED except at most one ACTIVE — the group currently receiving redo from the primary. Any group stuck ACTIVE when shipping is not in progress, or a total UNASSIGNED count below (online log groups per thread), is a configuration issue to resolve before the role transition.

When to Use This Check

  • Pre-switchover and pre-failover validation to confirm SRL count, sizing, and member paths are correct.
  • After adding or removing standby databases in a multi-standby environment — each new standby needs its own SRL groups.
  • When log shipping latency increases unexpectedly — check whether RFS is finding UNASSIGNED groups promptly or queuing behind an undersized pool.
  • Before enabling real-time apply on a standby that has been running in archived-log-only mode.
  • After any primary redo log resize — the standby SRL groups must be resized to match.
  • When the standby alert log shows RFS errors about no available group.

Troubleshooting Common Issues

When this query returns zero rows, the standby has no SRL groups configured. Archived-log transport and apply will continue to work, but real-time apply and maximum protection or maximum availability modes are unavailable. Create SRL groups with ALTER DATABASE ADD STANDBY LOGFILE GROUP n MEMBER '/path/to/srl.log' SIZE <bytes>, where <bytes> matches or exceeds the primary's largest online redo log size.

When a group shows STATUS = 'ACTIVE' but the primary is not shipping redo — for example, during a period when the standby was disconnected — the group may be stuck from an interrupted RFS session. Cancel and restart the MRP process on the standby (ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL, then restart recovery) to trigger SRL state cleanup. If the group remains stuck after MRP restarts, ALTER DATABASE CLEAR LOGFILE GROUP n forces the status back to UNASSIGNED. Use this command carefully: clear only an SRL group you are certain is not holding redo that has not yet been applied.

If the query returns more rows than expected groups — for example, six rows for three groups — each group has two member files and is correctly multiplexed. Divide the total row count by the distinct GROUP# count to confirm the multiplexing factor.

If STATUS shows ACTIVE for multiple groups simultaneously, more than one RFS process is writing at once. In a RAC primary this is normal — one group per thread. In a single-instance primary with multiple ACTIVE groups, it suggests concurrent RFS sessions from different Data Guard configurations (for example, a cascaded standby setup), which is also expected behavior.

References

Posts in this series