使用bbed修改檔案頭,推進scn,恢複offline drop的資料檔案,bbedscn
最近處理了一起由於使用者操作錯誤導致的資料庫無法開啟的情況。
使用者資料庫為windows 64bit,資料庫版本為11.2.0.1,非歸檔模式。由於異常斷電,資料庫無法正常開啟,而且經過使用者的判斷使用了錯誤的offline drop操作。導致資料庫開啟後,記錄檔切換過多,無法使用recover命令。
因為中間沒有做其他動作,所以接到case後,這裡將受損的幾個資料檔案copy到linux下使用bbed進行修改,再copy回windows,成功跳過recover,開啟了offline drop的資料檔案。
情景還原:
sys@UTF8A> select name ,checkpoint_change# from v$datafile ; NAME CHECKPOINT_CHANGE#--------------------------------------------------------------------/u01/apps/oracle/oradata/utf8a/system01.dbf 1040256/u01/apps/oracle/oradata/utf8a/sysaux01.dbf 1040256/u01/apps/oracle/oradata/utf8a/undotbs01.dbf 1040256/u01/apps/oracle/oradata/utf8a/users01.dbf 1039896 sys@UTF8A> select sequence#, group#,first_change#,statusfrom v$log ; SEQUENCE# GROUP# FIRST_CHANGE# STATUS---------- ---------- ----------------------------- 19 1 1040256 CURRENT 17 2 1040250 INACTIVE 18 3 1040253 INACTIVE sys@UTF8A> recover datafile 4 ;ORA-00279: change 1039896 generated at03/30/2015 09:31:05 needed for thread 1ORA-00289: suggestion :/u01/apps/oracle/product/11.2.0/dbhome_1/dbs/arch1_12_827004096.dbfORA-00280: change 1039896 for thread 1 isin sequence #12 Specify log: {<RET>=suggested |filename | AUTO | CANCEL}autoORA-00308: cannot open archived log'/u01/apps/oracle/product/11.2.0/dbhome_1/dbs/arch1_12_827004096.dbf'ORA-27037: unable to obtain file statusLinux-x86_64 Error: 2: No such file ordirectoryAdditional information: 3 ORA-00308: cannot open archived log'/u01/apps/oracle/product/11.2.0/dbhome_1/dbs/arch1_12_827004096.dbf'ORA-27037: unable to obtain file statusLinux-x86_64 Error: 2: No such file ordirectoryAdditional information: 3
sequence已經過去,無法執行恢複,所以這裡只能使用bbed修改檔案頭了。歸檔以及備份真的很重要。
block為8192。
flist:
1/u01/apps/oracle/oradata/utf8a/system01.dbf 754974720
2/u01/apps/oracle/oradata/utf8a/sysaux01.dbf 566231040
3/u01/apps/oracle/oradata/utf8a/undotbs01.dbf 83886080
4/u01/apps/oracle/oradata/utf8a/users01.dbf 9175040
如何初始化bbed環境,以及各種bbed版本的下載,見我的部落格
http://blog.csdn.net/renfengjun/article/details/7944629
這裡不再重複贅述。
先關閉資料庫並且啟動到mount狀態。
sys@UTF8A> selectfile#,change#,online_status from v$recover_file ; FILE# CHANGE# ONLINE_---------- ---------- ------- 4 1039896 OFFLINE sys@UTF8A> select name ,checkpoint_change# from v$datafile ; NAME CHECKPOINT_CHANGE#--------------------------------------------------------------------/u01/apps/oracle/oradata/utf8a/system01.dbf 1041564/u01/apps/oracle/oradata/utf8a/sysaux01.dbf 1041564/u01/apps/oracle/oradata/utf8a/undotbs01.dbf 1041564/u01/apps/oracle/oradata/utf8a/users01.dbf 1039896
記得修改前先備份所有的資料庫檔案。
BBED> set file 4 FILE# 4 BBED> p kcvfhckpstruct kcvfhckp, 36 bytes @484 struct kcvcpscn, 8 bytes @484 ub4 kscnbas @484 0x000fde18 ub2 kscnwrp @488 0x0000 ub4 kcvcptim @492 0x34321859 ub2 kcvcpthr @496 0x0001 union u, 12 bytes @500 struct kcvcprba, 12 bytes @500 ub4 kcrbaseq @500 0x0000000c ub4 kcrbabno @504 0x00000015 ub2 kcrbabof @508 0x0010 ub1 kcvcpetb[0] @512 0x02 ub1 kcvcpetb[1] @513 0x00 ub1 kcvcpetb[2] @514 0x00 ub1 kcvcpetb[3] @515 0x00 ub1 kcvcpetb[4] @516 0x00 ub1 kcvcpetb[5] @517 0x00 ub1 kcvcpetb[6] @518 0x00 ub1 kcvcpetb[7] @519 0x00
注意offset 484 , kscnbas為資料檔案現在的scn。
使用計算機計算後0x000fde18,得到的十進位數字為1039896,確認無誤,只要修改scn為最新的1041564,即可開啟損壞的資料檔案。
BBED> d /v dba 4,1 offset 484 count 16 File: /u01/apps/oracle/oradata/utf8a/users01.dbf(4) Block: 1 Offsets: 484 to 499 Dba:0x01000001------------------------------------------------------- 18de0f00 00000000 59183234 01000000 l ......Y.24.... <16 bytes per line>
因為是這裡為linux x64 ,為little endian。
1039896=18de0f00
1041564=9ce40f00
直接修改即可。
BBED> set mode edit MODE Edit BBED> m /x 9ce40f dba 4,1 offset 484 File:/u01/apps/oracle/oradata/utf8a/users01.dbf (4) Block: 1 Offsets: 484 to 499 Dba:0x01000001------------------------------------------------------------------------ 9ce40f00 00000000 59183234 01000000 <32 bytes per line> BBED> m /x 9ce40f dba 4,1 offset 484 File: /u01/apps/oracle/oradata/utf8a/users01.dbf(4) Block: 1 Offsets: 484 to 499 Dba:0x01000001------------------------------------------------------------------------ 9ce40f00 00000000 59183234 01000000 <32 bytes per line> BBED> sumCheck value for File 4, Block 1:current = 0x5f67, required = 0x65e3 BBED> sum applyCheck value for File 4, Block 1:current = 0x65e3, required = 0x65e3
完成任務
sys@UTF8A> alter database datafile 4online ; Database altered. sys@UTF8A> selectfile#,change#,online_status from v$recover_file ; FILE# CHANGE# ONLINE_---------- ---------- ------- 4 1041564 ONLINE sys@UTF8A> alter database datafile 4online ; Database altered. sys@UTF8A> alter database open ;alter database open*ERROR at line 1:ORA-01113: file 4 needs media recoveryORA-01110: data file 4:'/u01/apps/oracle/oradata/utf8a/users01.dbf' sys@UTF8A> recover datafile 4 ;Media recovery complete.sys@UTF8A> alter database open ; Database altered. sys@UTF8A> select * from scott.emp where rownum<2 ; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- --------- -------------------- ---------- 7369 SMITH CLERK 7902 17-DEC-80 800 20
完成後記得邏輯匯出所有使用者資料,並且完善備份計劃。