探索ORACLE不完全恢複之--基于cancel的恢複 第二篇
作者：吳偉龍 Name：Prodence Woo
QQ：286507175 msn:[email protected]
基于cancel 的不一緻性恢複（歸檔丢失） 第二篇
主要适用于：基于Cancel的不完全恢複适用場景：Recover時，所需的某個歸檔日志損壞，或主機斷電，current狀态的聯機日志損壞。
建立測試表
建立wwl002表，切換日志，再建立新的wwl003表，主機斷電，删除目前日志，模拟檔案損壞。
SQL> conn wwl/wwl
Connected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ -----------------
WWL001 TABLE
SQL> create table WWL002 as select *from wwl001;
Table created.
SQL> conn / as sysdba
切換日志
SQL> alter system switch logfile;
System altered.
後再建立第二張表
SQL> create table wwl003 as select *from wwl001;
檢視目前日志組，确定目前活動的日志組，是組4
SQL> set line 200
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- -------------------- --- ---------------- ------------- ---------
4 1 2 134217728 2 YES ACTIVE 1716929 30-JUL-12
5 1 1 134217728 2 YES INACTIVE 1692728 27-JUL-12
6 1 3 134217728 2 NO CURRENT 1720396 30-JUL-12
7 1 0 134217728 2 YES UNUSED 0
定位目前日志組的日志檔案，有兩個。
SQL> col member format a30
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------------------------------------- ---
7 ONLINE /DBBak2/oradata/WWL/redo7a.log NO
7 ONLINE /DBBak2/oradata/WWL/redo7b.log NO
6 ONLINE /DBBak2/oradata/WWL/redo6a.log NO
6 ONLINE /DBBak2/oradata/WWL/redo6b.log NO
5 STALE ONLINE /DBBak2/oradata/WWL/redo5a.log NO
5 STALE ONLINE /DBBak2/oradata/WWL/redo5b.log NO
4 ONLINE /DBBak2/oradata/WWL/redo4a.log NO
4 ONLINE /DBBak2/oradata/WWL/redo4b.log NO
8 rows selected.
删除目前日志組檔案，模拟線上事務丢失：
SQL> !rm -f/DBBak2/oradata/WWL/redo4a.log
SQL> !rm -f/DBBak2/oradata/WWL/redo4b.log
模拟伺服器斷電
SQL> shutdown abort;
ORACLE instance shut down.
恢複步驟:
1、嘗試啟動資料庫的時候報目前日志丢失。
SQL> startup
ORACLE instance started.
Total System Global Area 100663296 bytes
Fixed Size 1217884 bytes
Variable Size 88083108 bytes
Database Buffers 8388608 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-00313: open failedfor members of log group 4 of thread 1
ORA-00312: online log 4thread 1: '/DBBak2/oradata/WWL/redo4b.log'
ORA-27037: unable toobtain file status
Linux Error: 2: No suchfile or directory
Additional information: 3
ORA-00312: online log 4thread 1: '/DBBak2/oradata/WWL/redo4a.log'
2、嘗試Clear redo4
SQL> alter database clear logfile group4;
alter database clear logfile group 4
*
ERROR at line 1:
ORA-01624: log 4 needed for crash recoveryof instance WWL (thread 1)
ORA-00312: online log 4 thread 1:'/DBBak2/oradata/WWL/redo4a.log'
ORA-00312: online log 4 thread 1:'/DBBak2/oradata/WWL/redo4b.log'
3、在目前庫做基于Cancel的不完全恢複
SQL> recover database until cancel;
ORA-00279: change 1716930 generated at07/30/2012 11:03:51 needed for thread 1
ORA-00289: suggestion :/DBSoft/product/10.2.0/db_1/dbs/arch1_2_789761098.dbf
ORA-00280: change 1716930 for thread 1 isin sequence #2
Specify log: {<RET>=suggested |filename | AUTO | CANCEL}
auto
ORA-00279: change 1720396 generated at07/30/2012 13:37:21 needed for thread 1
ORA-00289: suggestion :/DBSoft/product/10.2.0/db_1/dbs/arch1_3_789761098.dbf
ORA-00280: change 1720396 for thread 1 isin sequence #3
ORA-00278: log file'/DBSoft/product/10.2.0/db_1/dbs/arch1_2_789761098.dbf' no longer needed forthis recovery
ORA-00308: cannot open archived log'/DBSoft/product/10.2.0/db_1/dbs/arch1_3_789761098.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
ORA-01547: warning: RECOVER succeeded butOPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to beconsistent
ORA-01110: data file 1:'/DBBak2/oradata/WWL/system01.dbf'
做完恢複之後必須使用resetlogs選項打開資料庫：
SQL> alter database open resetlogs;
alter database open resetlogs
恢複之後，使用Resetlogs選項，仍無法打開資料庫，提示資料檔案不一緻，System表空間需要進一步的恢複。
目前日志損壞時，不能基于目前的資料庫做不完全恢複。隻能用以前的備份，做一個基于Cancel的不完全恢複。
重建下控制檔案：
SQL> CREATE CONTROLFILE REUSE DATABASE"WWL" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
'/DBBak2/oradata/WWL/redo4a.log',
MAXDATAFILES 100
MAXINSTANCES 8
GROUP 5 (
MAXLOGHISTORY 292
LOGFILE
GROUP 4 (
'/DBBak2/oradata/WWL/redo4b.log'
GROUP 6 (
)SIZE 128M,
'/DBBak2/oradata/WWL/redo5a.log',
'/DBBak2/oradata/WWL/redo5b.log'
'/DBBak2/oradata/WWL/redo7b.log'
'/DBBak2/oradata/WWL/redo6a.log',
'/DBBak2/oradata/WWL/redo6b.log'
GROUP 7 (
'/DBBak2/oradata/WWL/redo7a.log',
)SIZE 128M
-- STANDBY LOGFILE
DATAFILE
'/DBBak2/oradata/WWL/system01.dbf',
'/DBBak2/oradata/WWL/undotbs01.dbf',
'/DBBak2/oradata/WWL/sysaux01.dbf',
'/DBBak2/oradata/WWL/users01.dbf',
'/DBBak2/oradata/WWL/wwl001',
'/DBBak2/oradata/WWL/wwl002',
'/DBBak2/oradata/WWL/wwl003'
CHARACTER SET ZHS16CGB231280
34 ;
Control file created.
再次打開，結果還是不行
可以嘗試使用_allow_resetlogs_corruption隐含參數來打開資料庫
SQL> alter system set"_allow_resetlogs_corruption"=true scope=spfile;
修改完參數之後重新開機資料庫到mount狀态
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
SQL> startup;
Fixed Size 1217884 bytes
Database Buffers 8388608 bytes
ORA-01589: must use RESETLOGS orNORESETLOGS option for database open
同樣以resetlogs模式啟動資料庫
Database altered.
一定記得關閉該參數
SQL> alter system set"_allow_resetlogs_corruption"=FALSE scope=spfile;
讓參數關閉生效，再次啟動資料庫
SQL> startup force;
Database opened.
檢查_allow_resetlogs_corruption
隐含參數是否關閉，确定已經關閉
SQL> show parameter_allow_resetlogs_corruption
NAME TYPE VALUE
----------------------------------------------- ------------------------------
_allow_resetlogs_corruption boolean FALSE
SQL>
因為重建了控制檔案，預設是沒有制定temp表空間，這裡制定下：
SQL> ALTER TABLESPACE TEMP01 ADDTEMPFILE '/DBBak2/oradata/WWL/temp01.dbf' REUSE;
Tablespace altered.
檢查資料
WWL002 TABLE
可以看到表WWL002存在，表WWL003不存在。因為WWL003的建立，是儲存在目前REDO日志中的，而目前REDO日志損壞，所有目前日志中儲存的操作全部丢失了。
歸檔日志、或者REDO日志損失，資料庫就隻能恢複到丢失的日志之前了。
![【MySQL資料庫】資料庫索引事務1.索引2.事務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)