Looking at Oracle Redo Log Information

Inventory: groups, members, sizes

One row per group; shows size, member count, current/active state, and whether archived:

SELECT l.group#,
       l.sequence#,
       ROUND(l.bytes/1024/1024) AS size_mb,
       l.members,
       l.archived,              -- YES/NO
       l.status                 -- CURRENT | ACTIVE | INACTIVE
FROM   v$log l
ORDER  BY l.group#;

List the physical files (members) for each group and their status/path:

SELECT lf.group#,
       lf.member,               -- full path
       lf.type,                 -- ONLINE | STANDBY
       lf.status                -- VALID | INVALID | STALE | DELETED
FROM   v$logfile lf
ORDER  BY lf.group#, lf.member;

Current status & quick checks

Which group is Oracle writing to now?

SELECT group#, sequence#, status
FROM   v$log
WHERE  status = 'CURRENT';

Archive destinations and any errors (watch FRA fullness & archiver health):

SELECT dest_id, destination, status, error
FROM   v$archive_dest
ORDER  BY dest_id;

Switch cadence & sizing signals

Recent log switches (helps size redo logs for a steady interval, e.g., ~15–30 minutes at peak):

SELECT sequence#, first_time, next_time
FROM   v$log_history
ORDER  BY first_time DESC
FETCH  FIRST 20 ROWS ONLY;

Switches per hour (last 24 hours), useful to spot bursts:

SELECT TO_CHAR(first_time, 'YYYY-MM-DD HH24') AS hour_slot,
       COUNT(*) AS switches
FROM   v$log_history
WHERE  first_time > SYSDATE - 1
GROUP  BY TO_CHAR(first_time, 'YYYY-MM-DD HH24')
ORDER  BY hour_slot DESC;

Operational guidance

• Multiplex: ≥2 members per group on different devices (or ASM failure groups).
• ARCHIVELOG + FRA: Ensure archiving keeps up; size FRA to avoid stalls. Purge obsolete archives with RMAN.
• Right-size logs: Too-small → frequent switches/checkpoints; too-large → slower crash recovery and delayed archiving.
• Alert log: Watch for “checkpoint not complete”, archiver errors, invalid members.
• Fleet view: Use Enterprise Manager Cloud Control for dashboards, alerts, and automation across databases.

Example outputs (illustrative)

GROUP# SEQUENCE# SIZE_MB MEMBERS ARCHIVED STATUS
------ --------- ------- ------- -------- --------
1      10429     2048    2       YES      INACTIVE
2      10430     2048    2       YES      INACTIVE
3      10431     2048    2       NO       CURRENT

Common pitfalls

• Single-member groups (no protection from file loss)
• All members on the same LUN/path (no failure isolation)
• Undersized FRA → archiving stalls, DML slowdowns
• Logs sized without measuring switch cadence

Redo Log Info - Quiz