下面我們進(jìn)行更復(fù)雜情況的處理，就是非一致性關(guān)閉的情況下日志損壞的情況。這種情況是很有實(shí)際意義的。因?yàn)樵诔霈F(xiàn)錯(cuò)誤的時(shí)候，Oracle可能不會(huì)允許一致性關(guān)閉。沒(méi)有經(jīng)驗(yàn)的處理人員往往會(huì)貿(mào)然的強(qiáng)制關(guān)閉，我們獲得到的場(chǎng)景往往也是非一致的情況。
員工經(jīng)過(guò)長(zhǎng)期磨合與沉淀，具備了協(xié)作精神，得以通過(guò)團(tuán)隊(duì)的力量開(kāi)發(fā)出優(yōu)質(zhì)的產(chǎn)品。創(chuàng)新互聯(lián)堅(jiān)持“專注、創(chuàng)新、易用”的產(chǎn)品理念，因?yàn)椤皩Ｗ⑺詫I(yè)、創(chuàng)新互聯(lián)網(wǎng)站所以易用所以簡(jiǎn)單”。公司專注于為企業(yè)提供成都做網(wǎng)站、網(wǎng)站設(shè)計(jì)、微信公眾號(hào)開(kāi)發(fā)、電商網(wǎng)站開(kāi)發(fā)，小程序設(shè)計(jì)，軟件按需求定制網(wǎng)站等一站式互聯(lián)網(wǎng)企業(yè)服務(wù)。
4、非一致性關(guān)閉非當(dāng)前日志組處理——Inactive日志組
非一致性關(guān)閉情況下，online日志組全員損壞是很復(fù)雜的，也是潛在會(huì)有數(shù)據(jù)損失的。我們這里說(shuō)的非一致性關(guān)閉，就是shutdown abort或者強(qiáng)制斷電之后。由于Oracle數(shù)據(jù)庫(kù)采用寫(xiě)日志在先的策略，關(guān)閉點(diǎn)上我們不能保證臟塊（Dirty Block）全都被寫(xiě)入到數(shù)據(jù)文件，各個(gè)數(shù)據(jù)文件和控制文件在SCN時(shí)間上保證一致性。
但是當(dāng)數(shù)據(jù)庫(kù)重新啟動(dòng)時(shí)，在open階段，Oracle會(huì)進(jìn)行instance recovery，從最后一個(gè)增量檢查點(diǎn)check point追起，重演事務(wù)并且回滾事務(wù)。Instance Recovery過(guò)程也被稱為前滾回滾過(guò)程，或者成為Cache Recovery和Transaction Recovery過(guò)程。
如果進(jìn)行不一致關(guān)閉就會(huì)需要進(jìn)行instance recovery，進(jìn)行instance recovery最需要的內(nèi)容就是online redo log。如果我們刪除online redo log group恰好是進(jìn)行instance recovery所需要的，那么我們就只能犧牲掉一部分?jǐn)?shù)據(jù)，進(jìn)行不完全恢復(fù)。
我們刪除的非當(dāng)前日志組也有很大風(fēng)險(xiǎn)，因?yàn)樵谌罩厩袚Q成非當(dāng)前日志之后，日志組有一段時(shí)間對(duì)應(yīng)臟塊是不能寫(xiě)入到數(shù)據(jù)文件中的。這也就是日志文件的active狀態(tài)。本節(jié)討論非當(dāng)前日志成員被刪除的情況，具體劃分為Inactive日志組和非Inactive日志組。
我們下面實(shí)驗(yàn)選擇Oracle 11g的Linux版本進(jìn)行。
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
當(dāng)前日志情況如下：
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
強(qiáng)制停機(jī)之后，刪除日志組1對(duì)象。
[oracle@bspdev ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Sat Oct 6 22:27:57 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
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 ~]$
重新啟動(dòng)之后，無(wú)法找到對(duì)應(yīng)的日志文件，直接報(bào)錯(cuò)。
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
重新啟動(dòng)到mount狀態(tài)，進(jìn)行處理。
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
1950243 1950243
1950243 1950243
1950243 1950243
1950243 1950243
1950243 1950243
1950243 1950243
7 rows selected.
SQL> select checkpoint_change# from v$datafile_header;
CHECKPOINT_CHANGE#
------------------
1950243
1950243
1950243
1950243
1950243
1950243
1950243
7 rows selected.
SQL>
雖然啟動(dòng)失效，但是我們沒(méi)有看到很嚴(yán)重的一致性問(wèn)題。我們嘗試啟動(dòng)數(shù)據(jù)庫(kù)。
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
這種場(chǎng)景是比較好處理的。下面我們討論如果是Active狀態(tài)的日志被刪除如何？
5、非一致性關(guān)閉非當(dāng)前日志組處理——Active日志組
我們構(gòu)造出Active日志組對(duì)象。
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
之后強(qiáng)制關(guān)閉數(shù)據(jù)庫(kù)，刪除對(duì)應(yīng)的日志組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
重新啟動(dòng)數(shù)據(jù)庫(kù)。
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
檢查文件狀態(tài)情況。
SQL> select checkpoint_change#, last_change# from v$datafile;
CHECKPOINT_CHANGE# LAST_CHANGE#
------------------ ------------
1970578
1970578
1970578
1970578
1970578
1970578
1970578
7 rows selected.
SQL> select checkpoint_change# from v$datafile_header;
CHECKPOINT_CHANGE#
------------------
1970578
1970578
1970578
1970578
1970578
1970578
1970578
7 rows selected.
從v$datafile的情況，我們就可以看出關(guān)閉是不完全關(guān)閉情況。我們只能嘗試進(jìn)行恢復(fù)。
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: {
cancel
Media recovery cancelled.
提出恢復(fù)使用sequence#=113的日志，但是這個(gè)恰恰是被刪除的對(duì)象。所以，只能退而求其次選擇使用不完全恢復(fù)。
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: {
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方式打開(kāi)。
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文件的日期已經(jīng)超過(guò)了recover范疇。只能嘗試?yán)?FONT face=Calibri>RMAN備份進(jìn)行還原。
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
--進(jìn)行部分恢復(fù)
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過(guò)程。下面我們嘗試開(kāi)啟數(shù)據(jù)庫(kù)，注意這里已經(jīng)是incomplete恢復(fù)了，我們必須承擔(dān)這部分的數(shù)據(jù)損失。
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
總結(jié)：當(dāng)我們incomplete關(guān)閉數(shù)據(jù)庫(kù)，又刪除了Active狀態(tài)日志組成員之后，我們必須要承擔(dān)對(duì)應(yīng)的數(shù)據(jù)損失，一部分?jǐn)?shù)據(jù)可能因此丟失。筆者之后的處理，是借助之前rman的一個(gè)備份集合，進(jìn)行非完全恢復(fù)到最后一個(gè)可以找到的日志文件組。這里，我們?cè)俅螐?qiáng)調(diào)歸檔模式和備份的重要性。
重慶分公司
重慶分公司
