Exploring Redo Log States in Oracle Databases

Leave a reply

Redo logs in Oracle Database can be in different states, showing how the log group is used. These states are important for understanding database behavior, especially during recovery, log switches, and archiving.

🔁 Redo Log States Explained

CURRENT – The log group currently being written to by the database.

ACTIVE – Contains data needed for recovery but is not being written to.

INACTIVE – No longer needed for recovery; safe to overwrite during log switch.

UNUSED – The log group has never been used since database creation.

How to Check Redo Log States

You can query the v$log to check the status of log files:

SELECT GROUP#, STATUS, SEQUENCE#, ARCHIVED FROM V$LOG;

GROUP#: Redo log group number.
STATUS: One of the states listed above.
SEQUENCE#: Log sequence number.
ARCHIVED: Indicates whether the log has been archived (YES or NO).

Why It Matters

CURRENT: Must not be dropped or modified.
ACTIVE: Needed for crash recovery; must be archived before reuse.
INACTIVE: Can be reused or overwritten.
UNUSED: Typically seen right after adding a new log group.

Check the current size of redo log files:

SELECT
    l.group#,
    l.thread#,
    l.bytes/1024/1024 AS size_mb,
    l.members,
    lf.member AS logfile_name
FROM
    v$log l
    JOIN v$logfile lf ON l.group# = lf.group#
ORDER BY
    l.group#;

Leave a Reply