Status Of Redo Log File

To see the status of redo log file you have the following two dynamic views:

1) V$LOG

2) V$LOGFILE

SQL>desc v$log

This command will provide you information about GROUP#, THREAD#, SEQURNE#, BYTES, MEMBERS, STATUS, FIRST_TIME. You can see the status of redo log file by using STATUS column:

SQL>select status from v$log;

STATUS

--------------

UNUSED- Online redo log has never been written to. This is the state of a redo log that was just added, or just after a RESETLOGS, when it is not the current redo log.

CURRENT- Current redo log. This implies that the redo log is active. The redo log could be open or closed.

ACTIVE- Log is active but is not the current log. It is needed for crash recovery. It may be in use for block recovery. It may or may not be archived.

CLEARING- Log is being re-created as an empty log after an ALTER DATABASE CLEAR LOGFILE statement. After the log is cleared, the status changes to UNUSED.

CLEARING CURRENT- Current log is being cleared of a closed thread. The log can stay in this status if there is some failure in the switch such as an I/O error writing the new log header.

INACTIVE- Log is no longer needed for instance recovery. It may be in use for media recovery. It might or might not be archived.

SQL>desc v$logfile

V$LOGFILE will provide you information about GROUP#, STATUS, TYPE, MEMBER. You can see status of redo log file by using STATUS column:

SQL> select status from v$logfile;

STATUS

--------------------

INVALID- File is inaccessible

STALE- This redo log file member is new and has never been used.

DELETED- File is no longer used

Null- The redo log file is in use