下面我們進行更復雜情況的處理，就是非一致性關閉的情況下日誌損壞的情況。這種情況是很有實際意義的。因為在出現錯誤的時候，Oracle可能不會允許一致性關閉。沒有經驗的處理人員往往會貿然的強制關閉，我們獲得到的場景往往也是非一致的情況。

4、非一致性關閉非當前日誌組處理——Inactive日誌組

非一致性關閉情況下，online日誌組全員損壞是很複雜的，也是潛在會有資料損失的。我們這裡說的非一致性關閉，就是shutdown abort或者強制斷電之後。由於Oracle資料庫採用寫日誌在先的策略，關閉點上我們不能保證髒塊（Dirty Block）全都被寫入到資料檔案，各個資料檔案和控制檔案在SCN時間上保證一致性。

但是當資料庫重新啟動時，在open階段，Oracle會進行instance recovery，從最後一個增量檢查點check point追起，重演事務並且回滾事務。Instance Recovery過程也被稱為前滾回滾過程，或者成為Cache Recovery和Transaction Recovery過程。

如果進行不一致關閉就會需要進行instance recovery，進行instance recovery最需要的內容就是online redo log。如果我們刪除online redo log group恰好是進行instance recovery所需要的，那麼我們就只能犧牲掉一部分資料，進行不完全恢復。

我們刪除的非當前日誌組也有很大風險，因為在日誌切換成非當前日誌之後，日誌組有一段時間對應髒塊是不能寫入到資料檔案中的。這也就是日誌檔案的active狀態。本節討論非當前日誌成員被刪除的情況，具體劃分為Inactive日誌組和非Inactive日誌組。

我們下面實驗選擇Oracle 11g的Linux版本進行。

SQL> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

PL/SQL Release 11.2.0.1.0 - Production

CORE 11.2.0.1.0 Production

當前日誌情況如下：

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

GROUP# STATUS ARCHIVED SEQUENCE#

---------- ---------------- -------- ----------

1 INACTIVE YES 108

2 CURRENT NO 110

3 INACTIVE YES 109

SQL> drop table t;

Table dropped

SQL> create table t as select * from dba_objects;

Table created

SQL> select group#, status, member from v$logfile;

GROUP# STATUS MEMBER

---------- ------- --------------------------------------------------------------------------------

3 /u01/oradata/WILSON/onlinelog/o1_mf_3_7xt456o0_.log

3 /u01/flash_recovery_area/WILSON/onlinelog/o1_mf_3_7xt45bvp_.log

2 /u01/oradata/WILSON/onlinelog/o1_mf_2_7xt44w3k_.log

2 /u01/flash_recovery_area/WILSON/onlinelog/o1_mf_2_7xt450rv_.log

1 /u01/oradata/WILSON/onlinelog/o1_mf_1_870jlj6v_.log

1 /u01/flash_recovery_area/WILSON/onlinelog/o1_mf_1_870jljvk_.log

6 rows selected

強制停機之後，刪除日誌組1物件。

[oracle@bspdev ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Sat Oct 6 22:27:57 2012

SQL> conn / as sysdba

Connected.

SQL> shutdown abort

ORACLE instance shut down.

[oracle@bspdev ~]$ mv /u01/oradata/WILSON/onlinelog/o1_mf_1_870jlj6v_.log /u01/oradata/WILSON/onlinelog/redogroup1.log

[oracle@bspdev ~]$ rm /u01/flash_recovery_area/WILSON/onlinelog/o1_mf_1_870jljvk_.log

[oracle@bspdev ~]$

重新啟動之後，無法找到對應的日誌檔案，直接報錯。

SQL> conn / as sysdba

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 849530880 bytes

Fixed Size 1339824 bytes

Variable Size 562040400 bytes

Database Buffers 281018368 bytes

Redo Buffers 5132288 bytes

Database mounted.

ORA-03113: end-of-file on communication channel

Process ID: 5059

Session ID: 1 Serial number: 5

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

Errors in file /u01/diag/rdbms/wilson/wilson/trace/wilson_ora_5059.trc:

ORA-00313: open failed for members of log group 1 of thread

ORA-00312: online log 1 thread 1: '/u01/oradata/WILSON/onlinelog/o1_mf_1_870jlj6v_.log'

ORA-00312: online log 1 thread 1: '/u01/flash_recovery_area/WILSON/onlinelog/o1_mf_1_870jljvk_.log'

USER (ospid: 5059): terminating the instance due to error 313

Instance terminated by USER, pid = 5059

重新啟動到mount狀態，進行處理。

SQL> conn / as sysdba

Connected to an idle instance.

SQL> startup mount

ORACLE instance started.

Total System Global Area 849530880 bytes

Fixed Size 1339824 bytes

Variable Size 562040400 bytes

Database Buffers 281018368 bytes

Redo Buffers 5132288 bytes

Database mounted.

SQL> select open_mode from v$database;

OPEN_MODE

--------------------

MOUNTED

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

GROUP# STATUS ARC SEQUENCE#

---------- ---------------- --- ----------

1 INACTIVE YES 108

3 INACTIVE YES 109

2 CURRENT NO 110

SQL> select checkpoint_change#, last_change# from v$datafile;

CHECKPOINT_CHANGE# LAST_CHANGE#

------------------ ------------

1950243 1950243

7 rows selected.

SQL> select checkpoint_change# from v$datafile_header;

CHECKPOINT_CHANGE#

------------------

1950243

7 rows selected.

SQL>

雖然啟動失效，但是我們沒有看到很嚴重的一致性問題。我們嘗試啟動資料庫。

SQL> alter database clear logfile group 1;

Database altered.

SQL> alter database open;

Database altered.

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

GROUP# STATUS ARCHIVED SEQUENCE#

---------- ---------------- -------- ----------

1 CURRENT NO 111

2 INACTIVE YES 110

3 INACTIVE YES 109

這種場景是比較好處理的。下面我們討論如果是Active狀態的日誌被刪除如何？

5、非一致性關閉非當前日誌組處理——Active日誌組

我們構造出Active日誌組物件。

SQL> select group#, status, member from v$logfile;

GROUP# STATUS MEMBER

---------- ------- --------------------------------------------------------------------------------

3 /u01/oradata/WILSON/onlinelog/o1_mf_3_7xt456o0_.log

3 /u01/flash_recovery_area/WILSON/onlinelog/o1_mf_3_7xt45bvp_.log

2 /u01/oradata/WILSON/onlinelog/o1_mf_2_7xt44w3k_.log

2 /u01/flash_recovery_area/WILSON/onlinelog/o1_mf_2_7xt450rv_.log

1 /u01/oradata/WILSON/onlinelog/o1_mf_1_870kg322_.log

1 /u01/flash_recovery_area/WILSON/onlinelog/o1_mf_1_870kg3mr_.log

6 rows selected

SQL> alter system switch logfile;

System altered

SQL> alter system switch logfile;

System altered

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

GROUP# STATUS ARCHIVED SEQUENCE#

---------- ---------------- -------- ----------

1 CURRENT NO 114

2 ACTIVE YES 113

3 INACTIVE YES 112

之後強制關閉資料庫，刪除對應的日誌組2。

SQL> shutdown abort;

ORACLE instance shut down.

SQL>

[oracle@bspdev trace]$ mv /u01/oradata/WILSON/onlinelog/o1_mf_2_7xt44w3k_.log /u01/oradata/WILSON/onlinelog/redogroup2.log

[oracle@bspdev trace]$ rm /u01/flash_recovery_area/WILSON/onlinelog/o1_mf_2_7xt450rv_.log

重新啟動資料庫。

SQL> conn / as sysdba

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 849530880 bytes

Fixed Size 1339824 bytes

Variable Size 562040400 bytes

Database Buffers 281018368 bytes

Redo Buffers 5132288 bytes

Database mounted.

ORA-00313: open failed for members of log group 2 of thread 1

ORA-00312: online log 2 thread 1:

'/u01/flash_recovery_area/WILSON/onlinelog/o1_mf_2_7xt450rv_.log'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

ORA-00312: online log 2 thread 1:

'/u01/oradata/WILSON/onlinelog/o1_mf_2_7xt44w3k_.log'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

SQL> select open_mode from v$database;

OPEN_MODE

--------------------

MOUNTED

檢查檔案狀態情況。

SQL> select checkpoint_change#, last_change# from v$datafile;

CHECKPOINT_CHANGE# LAST_CHANGE#

------------------ ------------

1970578

7 rows selected.

SQL> select checkpoint_change# from v$datafile_header;

CHECKPOINT_CHANGE#

------------------

1970578

7 rows selected.

從v$datafile的情況，我們就可以看出關閉是不完全關閉情況。我們只能嘗試進行恢復。

SQL> recover database;

ORA-00279: change 1970578 generated at 10/06/2012 22:59:02 needed for thread 1

ORA-00289: suggestion :

/u01/flash_recovery_area/WILSON/archivelog/2012_10_06/o1_mf_1_113_870kwzkl_.arc

ORA-00280: change 1970578 for thread 1 is in sequence #113

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

cancel

Media recovery cancelled.

提出恢復使用sequence#=113的日誌，但是這個恰恰是被刪除的物件。所以，只能退而求其次選擇使用不完全恢復。

SQL> recover database until cancel;

ORA-00279: change 1970578 generated at 10/06/2012 22:59:02 needed for thread 1

ORA-00289: suggestion :

/u01/flash_recovery_area/WILSON/archivelog/2012_10_06/o1_mf_1_113_870kwzkl_.arc

ORA-00280: change 1970578 for thread 1 is in sequence #113

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

auto

ORA-00279: change 1970779 generated at 10/06/2012 23:00:47 needed for thread 1

ORA-00289: suggestion :

/u01/flash_recovery_area/WILSON/archivelog/2012_10_06/o1_mf_1_114_%u_.arc

ORA-00280: change 1970779 for thread 1 is in sequence #114

ORA-00278: log file

'/u01/flash_recovery_area/WILSON/archivelog/2012_10_06/o1_mf_1_113_870kwzkl_.arc

' no longer needed for this recovery

ORA-00308: cannot open archived log

'/u01/flash_recovery_area/WILSON/archivelog/2012_10_06/o1_mf_1_114_%u_.arc'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

ORA-01194: file 1 needs more recovery to be consistent

ORA-01110: data file 1:

'/u01/oradata/WILSON/datafile/o1_mf_system_7xt3yzhj_.dbf'

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-01194: file 1 needs more recovery to be consistent

ORA-01110: data file 1:

'/u01/oradata/WILSON/datafile/o1_mf_system_7xt3yzhj_.dbf'

嘗試使用RMAN SCN方式開啟。

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

GROUP# SEQUENCE# FIRST_CHANGE#

---------- ---------- -------------

1 114 1970779

3 112 1950574

2 113 1970577

RMAN> recover database until sequence 112;

Starting recover at 06-OCT-12

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=18 device type=DISK

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 10/06/2012 23:19:42

RMAN-06556: datafile 1 must be restored from backup older than SCN 1950574

System檔案的日期已經超過了recover範疇。只能嘗試利用RMAN備份進行還原。

RMAN> restore database;

Starting restore at 06-OCT-12

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00001 to /u01/oradata/WILSON/datafile/o1_mf_system_7xt3yzhj_.dbf

channel ORA_DISK_1: restoring datafile 00002 to /u01/oradata/WILSON/datafile/o1_mf_sysaux_7xt3yzkb_.dbf

channel ORA_DISK_1: restoring datafile 00003 to /u01/oradata/WILSON/datafile/o1_mf_undotbs1_7xt3yzl5_.dbf

channel ORA_DISK_1: restoring datafile 00004 to /u01/oradata/WILSON/datafile/o1_mf_users_805nxydh_.dbf

channel ORA_DISK_1: restoring datafile 00005 to /u01/oradata/WILSON/datafile/o1_mf_example_7xt46m9x_.dbf

channel ORA_DISK_1: restoring datafile 00006 to /u01/oradata/WILSON/datafile/o1_mf_nbscommo_820frtg1_.dbf

channel ORA_DISK_1: restoring datafile 00007 to /u01/oradata/WILSON/datafile/o1_mf_nbscommo_820ft5y5_.dbf

channel ORA_DISK_1: reading from backup piece /u01/flash_recovery_area/WILSON/backupset/2012_10_06/o1_mf_nnndf_TAG20121006T220912_870gwjoy_.bkp

channel ORA_DISK_1: piece handle=/u01/flash_recovery_area/WILSON/backupset/2012_10_06/o1_mf_nnndf_TAG20121006T220912_870gwjoy_.bkp tag=TAG20121006T220912

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:02:46

Finished restore at 06-OCT-12

--進行部分恢復

RMAN> recover database until sequence 112;

Starting recover at 06-OCT-12

using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 106 is already on disk as file /u01/flash_recovery_area/WILSON/archivelog/2012_10_06/o1_mf_1_106_870gzbfg_.arc

archived log for thread 1 with sequence 107 is already on disk as file /u01/flash_recovery_area/WILSON/archivelog/2012_10_06/o1_mf_1_107_870jlysq_.arc

archived log for thread 1 with sequence 108 is already on disk as file /u01/flash_recovery_area/WILSON/archivelog/2012_10_06/o1_mf_1_108_870jo98m_.arc

archived log for thread 1 with sequence 109 is already on disk as file /u01/flash_recovery_area/WILSON/archivelog/2012_10_06/o1_mf_1_109_870jx2gj_.arc

archived log for thread 1 with sequence 110 is already on disk as file /u01/flash_recovery_area/WILSON/archivelog/2012_10_06/o1_mf_1_110_870kgmdp_.arc

archived log for thread 1 with sequence 111 is already on disk as file /u01/flash_recovery_area/WILSON/archivelog/2012_10_06/o1_mf_1_111_870knhwb_.arc

archived log file name=/u01/flash_recovery_area/WILSON/archivelog/2012_10_06/o1_mf_1_106_870gzbfg_.arc thread=1 sequence=106

archived log file name=/u01/flash_recovery_area/WILSON/archivelog/2012_10_06/o1_mf_1_107_870jlysq_.arc thread=1 sequence=107

archived log file name=/u01/flash_recovery_area/WILSON/archivelog/2012_10_06/o1_mf_1_108_870jo98m_.arc thread=1 sequence=108

archived log file name=/u01/flash_recovery_area/WILSON/archivelog/2012_10_06/o1_mf_1_109_870jx2gj_.arc thread=1 sequence=109

archived log file name=/u01/flash_recovery_area/WILSON/archivelog/2012_10_06/o1_mf_1_110_870kgmdp_.arc thread=1 sequence=110

archived log file name=/u01/flash_recovery_area/WILSON/archivelog/2012_10_06/o1_mf_1_111_870knhwb_.arc thread=1 sequence=111

media recovery complete, elapsed time: 00:00:20

Finished recover at 06-OCT-12

明顯，得益於歸檔模式和備份集合。我們似乎成功的完成了recover過程。下面我們嘗試開啟資料庫，注意這裡已經是incomplete恢復了，我們必須承擔這部分的資料損失。

SQL> conn / as sysdba

Connected.

SQL> alter database open resetlogs;

Database altered.

SQL> select group#, status, member from v$logfile;

GROUP# STATUS MEMBER

---------- ------- --------------------------------------------------------------------------------

3 /u01/oradata/WILSON/onlinelog/o1_mf_3_870n4lsg_.log

3 /u01/flash_recovery_area/WILSON/onlinelog/o1_mf_3_870n4o31_.log

2 /u01/oradata/WILSON/onlinelog/o1_mf_2_870n48hc_.log

2 /u01/flash_recovery_area/WILSON/onlinelog/o1_mf_2_870n4dtl_.log

1 /u01/oradata/WILSON/onlinelog/o1_mf_1_870n42n1_.log

1 /u01/flash_recovery_area/WILSON/onlinelog/o1_mf_1_870n44z3_.log

6 rows selected

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

GROUP# STATUS ARCHIVED SEQUENCE#

---------- ---------------- -------- ----------

1 CURRENT NO 1

2 UNUSED YES 0

3 UNUSED YES 0

總結：當我們incomplete關閉資料庫，又刪除了Active狀態日誌組成員之後，我們必須要承擔對應的資料損失，一部分資料可能因此丟失。筆者之後的處理，是藉助之前rman的一個備份集合，進行非完全恢復到最後一個可以找到的日誌檔案組。這裡，我們再次強調歸檔模式和備份的重要性。

如果我們刪除的是一個當前的日誌成員，在非完全關閉的情況下，事情也是很複雜的處理。在下一節中，我們選擇另外一種方法進行處理。

Online Redo Log損壞處理實驗（中）

相關文章