Oracle 日誌狀態為stale解決方案

SQLDBA> SELECT * FROM V$LOGFILE; 
 
    GROUP#       STATUS        MEMBER 
    ---------- ------- ------------------------------ 
             1          STALE           /Oracle/7.3.4/dbs/log1P734.dbf 
 
             2                              /oracle/7.3.4/dbs/log2P734.dbf 
 
             3                              /oracle/7.3.4/dbs/log3P734.dbf 

解決方案總結：

1，千萬不要在發生stale的情況下強制關閉資料庫。

2，多查詢幾次日誌狀態，看stale狀態是否會消失。

3，如果再數次查詢無效後，可以切換日誌。

4，切換日誌過戶，可以執行強制檢查點操作。

5，不斷切換日誌，強制檢查點，查詢，直至stale狀態消失。

WARNING: 

 DO NOT ISSUE A SHUTDOWN ABORT BEFORE GOING THROUGH STEPS 1 AND 2   BELOW.

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. 
 

Here is the recommended procedure to deal with a stale redo log: 

1. Issue the following query: 
 
        SELECT V2.GROUP#, MEMBER, V2.STATUS MEMBER_STATUS, 
                V1.STATUS GROUP_STATUS 
        FROM V$LOG V1, V$LOGFILE V2 
        WHERE V1.GROUP# = V2.GROUP# AND V2.STATUS = 'STALE'; 
 
        This will show you the group status for all stale log files. 

2. For each stale log file, 
 
        2.a) If the GROUP_STATUS is 'INACTIVE', do nothing.  That implies 
             Oracle has already checkpointed past that redo group, and thus 
             its contents are no longer needed for instance recovery. 
             Once the redo group becomes current again, the stale status of 
             the log file will go away by itself. 
 
        2.b) If the GROUP_STATUS is 'ACTIVE', go back to Step 1 and repeat 
             the query a few more times.  This status usually means that 
             the log group is no longer the current one, but the corresponding 
             checkpoint has not completed yet.  Unless there is a problem, 
             the log group should become inactive shortly. 
 
        2.c) If the GROUP_STATUS is 'CURRENT', force a log switch now. 
 
                ALTER SYSTEM SWITCH LOGFILE; 
 
             This will also force a checkpoint.  If the checkpoint 
             completes successfully, the contents of the redo group 
             are no longer needed for instance recovery.  Go back to 
             Step 1 and repeat the query a few more times until the 
             log group becomes inactive. 
 
        IMPORTANT: If the stale logfile belongs to an active group 
        or the current group (cases 2.b and 2.c above), DO NOT ISSUE 
        A SHUTDOWN ABORT UNTIL THE GROUP BECOMES INACTIVE. 

3. Investigate the extent of the problem. 
 
        Examine the alert.log file for this instance and the LGWR 
        trace file, if one can be found in your background_dump_dest. 
        See if there is any pattern to the problem.  Do you see any 
        recent errors, such as ORA-312, referencing that particular log  
        file?  If so, there may be some corruption problem with the file 
        or a problem with the I/O subsystem (disk, controllers, etc.)    .  
        If you are running any other Oracle version on any other platform 
        If there is no pattern to the problem, it is more likely an 
        isolated incident. 
 
4. If you are archiving, make sure the log has been correctly archived. 
 
        Before archiving a redo log group, the ARCH process actually 
        verifies that its contents are valid.  If that is not the case, 
        it issues an error such as ORA-255 ("error archiving log %s 
        of thread %s, sequence # %s"。Therefore, if the log group to 
        which the stale member belongs has been successfully archived, 
        it means the redo contents of the group are good, and that 
        archived log can be safely used for recovery.  ARCH errors, if 
        any, will be reported in your alert.log file and in an ARCH 
        trace file.

Explanation: 
============ 
 
A stale redo log file is one that Oracle believes might be incomplete for some 
reason.  This typically happens when a temporary error prevents the LGWR 
background process from writing to a redo log group member.  If Oracle cannot 
write to a redo log file at any one time, that log file can no longer be 
trusted, and Oracle marks it as "STALE". This indicates that the log file 
cannot be relied upon to provide all the data written to the log.