Lesson 3	Listing redo log files
Objective	List redo log file groups and members.

Listing Online Redo Log Groups and Members

Oracle Database records redo log configuration information in the control file and exposes it through dynamic performance views. A complete inventory uses V$LOG for redo log groups and V$LOGFILE for the individual members belonging to those groups.

These views answer different questions. V$LOG shows which group is current or still needed for recovery, while V$LOGFILE shows where each member is stored and whether Oracle has detected a problem with that member.

Use V$LOG and V$LOGFILE Together

View	Level	Important information
`V$LOG`	One row per redo log group	`THREAD#`, `SEQUENCE#`, size, member count, archive state, and group status
`V$LOGFILE`	One row per redo log member	Group number, member name, member status, online or standby type, recovery-destination indicator, and container identifier

Query the Complete Online Redo Log Configuration

The following query joins the views on GROUP#. Including THREAD# makes the output useful for both single-instance databases and Oracle Real Application Clusters environments.

SELECT l.thread#,
       l.group#,
       l.sequence#,
       ROUND(l.bytes / 1024 / 1024) AS size_mb,
       l.members,
       l.archived,
       l.status AS group_status,
       NVL(lf.status, 'IN USE') AS member_status,
       lf.type,
       lf.is_recovery_dest_file,
       lf.member
FROM   v$log l
JOIN   v$logfile lf
       ON lf.group# = l.group#
ORDER BY l.thread#, l.group#, lf.member;

Representative ASM Output

THREAD# GROUP# SEQUENCE# SIZE_MB MEMBERS ARCHIVED GROUP_STATUS MEMBER_STATUS TYPE   MEMBER
------- ------ --------- ------- ------- -------- ------------ ------------- ------ ------------------------
      1      1       184    1024       2 YES      INACTIVE     IN USE        ONLINE +DATA/ORCL/ONLINELOG/...
      1      1       184    1024       2 YES      INACTIVE     IN USE        ONLINE +RECO/ORCL/ONLINELOG/...
      1      2       185    1024       2 NO       CURRENT      IN USE        ONLINE +DATA/ORCL/ONLINELOG/...
      1      2       185    1024       2 NO       CURRENT      IN USE        ONLINE +RECO/ORCL/ONLINELOG/...

ASM generates complete member names, so the displayed path is typically longer than the abbreviated example. Databases using conventional filesystems display operating-system paths instead.

Interpret Group Status Correctly

CURRENT: LGWR is writing to this group.
ACTIVE: The group is not current, but it is still required for instance recovery.
INACTIVE: The group is no longer required for instance recovery and can be reused when other requirements are satisfied.
UNUSED: The group has not yet been written to, such as a newly added group.

Other transitional values, including CLEARING and CLEARING_CURRENT, can appear while Oracle clears a log. Always evaluate ARCHIVED together with group status before performing maintenance.

Interpret Member Status Separately

The STATUS column in V$LOGFILE describes a member, not its group. A null value means that the member is in use; the query displays that null as IN USE for readability.

INVALID: Oracle cannot access the member.
STALE: The member's contents are incomplete.
DELETED: The member is no longer used.

Investigate any non-null problem status and corroborate it with the alert log and relevant trace files. Do not confuse these member values with the CURRENT, ACTIVE, or INACTIVE group values from V$LOG.

Check Multiplexing and Placement

For every group, compare the MEMBERS count from V$LOG with the rows returned from V$LOGFILE. Multiplexed members should be distributed across independent storage or ASM failure groups so that one storage failure does not remove every member of a redo log group.

The IS_RECOVERY_DEST_FILE column identifies members located in the Fast Recovery Area. A balanced configuration commonly places members so that a single disk group, mount point, controller, or failure group is not the only location for a group's redo.

Required Privileges

Dynamic performance views are normally queried by database administrators or monitoring accounts with appropriate catalog privileges. Grant only the access required by the operational role; application schemas generally should not need direct access to redo log configuration views.

Operational Checklist

Confirm that every expected group appears in V$LOG.
Confirm that the member count agrees with the rows in V$LOGFILE.
Identify the CURRENT group for each redo thread.
Investigate non-null member problem statuses.
Confirm that multiplexed members are placed on independent storage.
Retain the query output as a baseline before adding, dropping, or relocating redo logs.

In the next lesson, you will learn how to create new redo log groups and members for your database.

Listing Redo Log Files - Quiz

Before you move on, take this quiz covering the material so far.

Listing Redo Log Files - Quiz