oracle丟失inactive日誌檔案的恢復操作過程

先刪除一個非活動日誌組,模擬一次故障損失:
SQL> !rm /u01/app/oracle/product/10.2.0/oradata/jingyong/redo03.log

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

此時啟動資料庫,資料庫會提示日誌丟失
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1218316 bytes
Variable Size              67111156 bytes
Database Buffers           96468992 bytes
Redo Buffers                2973696 bytes
Database mounted.
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1:
'/u01/app/oracle/product/10.2.0/oradata/jingyong/redo03.log'

此時在mount狀態,可以檢視各日誌組及日誌檔案的狀態:
SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIME
------------- ------------
         1          1         26   52428800          1 NO  INACTIVE
       913689 06-JAN-13

         3          1         28   52428800          1 NO  CURRENT
       913701 06-JAN-13

         2          1         27   52428800          1 NO  INACTIVE
       913696 06-JAN-13

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
IS_
---
         3         ONLINE
/u01/app/oracle/product/10.2.0/oradata/jingyong/redo03.log
NO

         2         ONLINE
/u01/app/oracle/product/10.2.0/oradata/jingyong/redo02.log
NO

    GROUP# STATUS  TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
IS_
---

         1         ONLINE
/u01/app/oracle/product/10.2.0/oradata/jingyong/redo01.log
NO

清除該日誌組後即可啟動資料庫:
SQL>alter database clear logfile group 3;
Database altered

SQL>alter database open;
Database altered

注意,如果資料庫處於歸檔模式下,並且該日誌組未完成歸檔則需要使用如下命令強制清除
alter database clear unarchived logfile group 3;

開啟資料庫之後,狀態為stale的日誌檔案,在下次正常寫入後,狀態即可恢復正常:

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIME
------------- ------------
         1          1          2   52428800          1 NO  INACTIVE
       914153 06-JAN-13

         2          1          3   52428800          1 NO  INACTIVE
       914157 06-JAN-13

         3          1          4   52428800          1 NO  CURRENT
       914164 06-JAN-13