Redo Log File（inactive、active）損壞，處理恢復對策

redolog的生命週期中共有四種狀態：
current -> 正在使用的
active -> 非正在使用的，對應的Dirty Block還沒有完全寫入到資料檔案中
inactive -> 非正在使用的，可以覆蓋的，Dirty Block已經完全寫入。
unused -> 沒有使用過的
-- 檢視redolog狀態
SQL> select group#,status from v$log;

模擬三種狀態下redolog丟失，處理方案：

一、inactive 情況 （Inactive表示Dirty Block已經完全寫入。）

-- 檢視redolog狀態
SQL> select group#,status from v$log;

GROUP# STATUS
---------- ----------------
1 INACTIVE
2 CURRENT
3 UNUSED
SQL> 
col member for a45;
select group#,status,type,member from v$logfile;
GROUP# STATUS TYPE MEMBER
---------- ------- ------- ---------------------------------------------
3 ONLINE /home/oracle/app/oradata/orcl/redo03.log
2 ONLINE /home/oracle/app/oradata/orcl/redo02.log
1 ONLINE /home/oracle/app/oradata/orcl/redo01.log

--模擬 inactive 狀態丟失
[oracle@11g orcl]$ rm -rf /home/oracle/app/oradata/orcl/redo01.log
-- inactive 丟失後的資料庫症狀
[oracle@11g trace]$ tail -f alert_orcl.log 
Errors in file /home/oracle/app/diag/rdbms/orcl/orcl/trace/orcl_m000_25727.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/home/oracle/app/oradata/orcl/redo01.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> alter system switch logfile; （多切幾次日誌後，會話hang住）
SQL> conn andy/andy （使用者登入不進去）
ERROR:
ORA-00257: archiver error. Connect internal only, until freed.
Warning: You are no longer connected to ORACLE.

--處理流程
SQL> alter database clear logfile group 1 ;
alter database clear logfile group 1
*
ERROR at line 1:
ORA-00350: log 1 of instance orcl (thread 1) needs to be archived
ORA-00312: online log 1 thread 1: '/home/oracle/app/oradata/orcl/redo01.log'

SQL> select group#,sequence#,archived,status from v$log;

GROUP# SEQUENCE# ARC STATUS
---------- ---------- --- ----------------
1 4 NO INACTIVE
2 5 NO INACTIVE
3 6 NO CURRENT

SQL> alter database clear unarchived logfile group 1;

Database altered.
-- 檢查redo自否正常建立
[oracle@11g ~]$ cd /home/oracle/app/oradata/orcl/
[oracle@11g orcl]$ ll redo*
-rw-r-----. 1 oracle oinstall 52429312 Mar 25 13:44 redo01.log
-rw-r-----. 1 oracle oinstall 52429312 Mar 25 13:19 redo02.log
-rw-r-----. 1 oracle oinstall 52429312 Mar 25 13:36 redo03.log
SQL> alter system switch logfile;

System altered.
SQL> /
SQL> /
SQL> /
SQL> select group#,sequence#,archived,status from v$log
GROUP# SEQUENCE# ARC STATUS
---------- ---------- --- ----------------
1 10 YES INACTIVE
2 11 YES INACTIVE
3 12 NO CURRENT
資料庫恢復正常，OK。

————————————————————————————————————————————————————————

二、 active 情況 （Active是說日誌組已經不是當前日誌組，但是Redo Log Entry對應的Dirty Block還沒有完全寫入到資料檔案中。）
--切換日誌直至 redolog 的 status 出現 ACTIVE
SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> select group#,sequence#,archived,status from v$log;
GROUP# SEQUENCE# ARC STATUS
---------- ---------- --- ----------------
1 13 YES ACTIVE
2 14 NO CURRENT
3 12 YES ACTIVE
--模擬 ACTIVE 狀態的 redolog 丟失
[oracle@11g orcl]$ rm -rf redo01.log
--資料庫狀態
[oracle@11g trace]$ tail -f alert_orcl.log
Errors in file /home/oracle/app/diag/rdbms/orcl/orcl/trace/orcl_arc2_23552.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/home/oracle/app/oradata/orcl/redo01.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Master background archival failure: 313
SQL> alter system switch logfile; （多次切換redolog，發現多次以後會話 hang 住）
--處理過程
SQL> select group#,sequence#,archived,status from v$log;
GROUP# SEQUENCE# ARC STATUS
---------- ---------- --- ----------------
1 16 NO INACTIVE
2 17 NO INACTIVE
3 18 NO CURRENT

SQL> alter database clear unarchived logfile group 1;
--檢查redolog是否正常生成
[oracle@11g orcl]$ ll redo*
-rw-r-----. 1 oracle oinstall 52429312 Mar 25 14:31 redo01.log
-rw-r-----. 1 oracle oinstall 52429312 Mar 25 14:32 redo02.log
-rw-r-----. 1 oracle oinstall 52429312 Mar 25 14:31 redo03.log
--多次切換日誌，看資料庫是否正常
SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

OK，一切正常。