有關重做日誌的狀態及switch logfile時的不正常案例分析

首先回顧一下有關重做日誌的狀態：
V$logfile:
INVALID - File is inaccessible
STALE - File's contents are incomplete
DELETED - File is no longer used
null - File is in use

V$log:
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.

如下的案例是一個手工強制切換日誌的例子，其過程如下：
在windows xp上，9206版本。執行完alter system switch logfile後一直掛著，v$session_wait中switch logfile command的事件。具體為：
SQL> select * from v$session_wait where sid=11;

SID SEQ# EVENT P1TEXT P1 P1RAW P2TEXT P2 P2RAW P3TEXT P3 P3RAW WAIT_TIME SECONDS_IN_WAIT STATE
---------- ---------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------- -------- ---------------------------------------------------------------- ---------- -------- ---------------------------------------------------------------- ---------- -------- ---------- --------------- -------------------
11 1124 switch logfile command 0 00 0 00 0 00 0 517 WAITING

SQL>
SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- -----------
1 1 0 104857600 1 YES CLEARING 11809968 2006-12-1 1
2 1 190 104857600 1 NO CURRENT 12328406 2006-12-6 1
3 1 189 104857600 1 YES ACTIVE 12327853 2006-12-6 1

SQL>

發覺日誌組1的狀態為CLEARING，對其執行clear命令：
SQL> alter database clear logfile 'C:ORACLEORADATAORA9IREDO01.LOG';

Database altered
SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER
---------- ------- ------- --------------------------------------------------------------------------------
3 ONLINE C:ORACLEORADATAORA9IREDO03.LOG
2 ONLINE C:ORACLEORADATAORA9IREDO02.LOG
1 ONLINE C:ORACLEORADATAORA9IREDO01.LOG

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- -----------
1 1 0 104857600 1 YES UNUSED 11809968 2006-12-1 1
2 1 194 104857600 1 NO CURRENT 12374486 2006-12-6 1
3 1 193 104857600 1 YES INACTIVE 12374339 2006-12-6 1

SQL> alter system switch logfile;

System altered

SQL> alter system switch logfile;

System altered

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- -----------
1 1 195 104857600 1 YES ACTIVE 12379388 2006-12-6 1
2 1 194 104857600 1 YES ACTIVE 12374486 2006-12-6 1
3 1 196 104857600 1 NO CURRENT 12379391 2006-12-6 1

SQL>

一切正常！

[@more@]