redo logfile 的不同状态

一、V$LOG:STATUS 指的是GROUP的状态
STATUS VARCHAR2(16) Log status:P715 <>

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.

A redo log file becomes INVALID if Oracle cannot access it. It becomes STALE if Oracle suspects that it is not complete or correct. A stale log file becomes valid again the next time its group is made the active group.

与之前自己的理解有出入,文档才是王道.

二、V$LOGFILE:STATUS指的是member的状态

STATUS VARCHAR2(7) Status of the log member:

INVALID - File is inaccessible

STALE - File's contents are incomplete

DELETED - File is no longer used

NULL - File is in use

下面是对metalink上对stale的解释
Log files may have a status of stale regardless of redo log mirroring or
archival mode.

Solution Description:
=====================
In general, the stale status of a redo log member should not be a cause for great concern, unless you observe that this happens frequently or systematically. Keep in mind that a stale log is not necessarily an invalid log, but more of an "in-doubt" one. Once the corresponding redo group becomes the current one again, the stale status will go away by itself.

下面是一个例子：
logfile中有两个file为stale状态，当发生日志切换时，group1变成current状态，从而，group1下的logfile变成 blank(正常状态)
另外，这个例子也展示了logfile group从active到inactive的变化。
当发生switch logfile前，group5为current状态，switch logfile后，group5变为active状态，这说明recovery时 group5是有用的，同时也说明logfile时发生的不是完全检查点。
当手工发生检查点后，group5从active变为了inactive状态，此时，group5对instance recovery不再有用。这说明： alter system checkpoint发生的是完全检查点。
SQL> l
1* select * from v$logfile
SQL> /
GROUP# STATUS TYPE MEMBER
---------- ------- ------- ----------------------------------------
1 STALE ONLINE /opt/oracle/oradata/userlog2/redo01.log
2 STALE ONLINE /opt/oracle/oradata/userlog2/redo02.log
3 ONLINE /opt/oracle/oradata/userlog2/redo03.log
4 ONLINE /opt/oracle/oradata/userlog2/redo04.log
5 ONLINE /opt/oracle/oradata/userlog2/redo05.log
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 21 20971520 1 NO INACTIVE 704228 07-AUG-06
2 1 22 20971520 1 NO INACTIVE 753376 07-AUG-06
3 1 23 20971520 1 NO INACTIVE 785843 07-AUG-06
4 1 24 20971520 1 NO INACTIVE 868923 08-AUG-06
5 1 25 20971520 1 NO CURRENT 951980 10-AUG-06
SQL> alter system switch logfile;
System altered.
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER
---------- ------- ------- ----------------------------------------
1 ONLINE /opt/oracle/oradata/userlog2/redo01.log
2 STALE ONLINE /opt/oracle/oradata/userlog2/redo02.log
3 ONLINE /opt/oracle/oradata/userlog2/redo03.log
4 ONLINE /opt/oracle/oradata/userlog2/redo04.log
5 ONLINE /opt/oracle/oradata/userlog2/redo05.log
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 26 20971520 1 NO CURRENT 994126 10-AUG-06
2 1 22 20971520 1 NO INACTIVE 753376 07-AUG-06
3 1 23 20971520 1 NO INACTIVE 785843 07-AUG-06
4 1 24 20971520 1 NO INACTIVE 868923 08-AUG-06
5 1 25 20971520 1 NO ACTIVE 951980 10-AUG-06
SQL>
SQL> alter system checkpoint;
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 26 20971520 1 NO CURRENT 994126 10-AUG-06
2 1 22 20971520 1 NO INACTIVE 753376 07-AUG-06
3 1 23 20971520 1 NO INACTIVE 785843 07-AUG-06
4 1 24 20971520 1 NO INACTIVE 868923 08-AUG-06
5 1 25 20971520 1 NO INACTIVE 951980 10-AUG-06