數(shù)據(jù)文件頭中的scn要與控制文件中的scn一致,數(shù)據(jù)庫才可以open,在open過程中我們可以通過bbed來修改某個數(shù)據(jù)文件頭的scn,來欺騙oracle,來open庫。
我們提供的服務(wù)有:做網(wǎng)站、網(wǎng)站制作、微信公眾號開發(fā)、網(wǎng)站優(yōu)化、網(wǎng)站認(rèn)證、精河ssl等。為近千家企事業(yè)單位解決了網(wǎng)站和推廣的問題。提供周到的售前咨詢和貼心的售后服務(wù),是有科學(xué)管理、有技術(shù)的精河網(wǎng)站制作公司1、環(huán)境如下
使用Oracle 11gR2進(jìn)行測試,具體版本為11.2.0.4
SYS@xbtst SQL>select file#,name,checkpoint_change#,checkpoint_time from v$datafile; FILE# NAME CHECKPOINT_CHANGE# CHECKPOINT_TIME ---------- ---------------------------------------- ------------------ ------------------- 1 /dbdata/oradata/xbtst/system01.dbf 1233066 2016-11-10 11:23:44 2 /dbdata/oradata/xbtst/sysaux01.dbf 1233066 2016-11-10 11:23:44 3 /dbdata/oradata/xbtst/undotbs01.dbf 1233066 2016-11-10 11:23:44 4 /dbdata/oradata/xbtst/users01.dbf 1233066 2016-11-10 11:23:44 5 /dbdata/oradata/xbtst/moe01.dbf 1233066 2016-11-10 11:23:442、模擬某個數(shù)據(jù)文件在問題
shutdown immediate數(shù)據(jù)庫,然后拷貝moe01.dbf這個數(shù)據(jù)文件,然后打開數(shù)據(jù)庫,做日志切換,然后shutdown immediate,再把之前的拷貝替換現(xiàn)在的moe01.dbf數(shù)據(jù)文件
SYS@xbtst SQL>shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. [oracle@tstdb-25-220 xbtst]$ cp moe01.dbf moe01.dbf.bak [oracle@tstdb-25-220 xbtst]$ ll 總用量 3872076 -rw-r-----. 1 oracle oinstall 9748480 11月 10 09:48 control01.ctl -rw-r-----. 1 oracle oinstall 9748480 11月 10 09:48 control02.ctl -rw-r-----. 1 oracle oinstall 1073750016 11月 10 09:48 moe01.dbf -rw-r-----. 1 oracle oinstall 1073750016 11月 10 11:23 moe01.dbf.bak -rw-r-----. 1 oracle oinstall 52429312 11月 9 13:13 redo01.log -rw-r-----. 1 oracle oinstall 52429312 11月 9 22:00 redo02.log -rw-r-----. 1 oracle oinstall 52429312 11月 10 09:48 redo03.log -rw-r-----. 1 oracle oinstall 587210752 11月 10 09:48 sysaux01.dbf -rw-r-----. 1 oracle oinstall 807411712 11月 10 09:48 system01.dbf -rw-r-----. 1 oracle oinstall 30416896 11月 9 22:00 temp01.dbf -rw-r-----. 1 oracle oinstall 131080192 11月 10 09:48 undotbs01.dbf -rw-r-----. 1 oracle oinstall 111419392 11月 10 09:48 users01.dbf SYS@xbtst SQL>startup ORACLE instance started. Total System Global Area 2455228416 bytes Fixed Size 2255712 bytes Variable Size 620758176 bytes Database Buffers 1811939328 bytes Redo Buffers 20275200 bytes Database mounted. Database opened. SYS@xbtst SQL>alter system switch logfile; System altered. SYS@xbtst SQL>alter system switch logfile; System altered. SYS@xbtst SQL>alter system switch logfile; System altered. SYS@xbtst SQL>shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. [oracle@tstdb-25-220 xbtst]$ cp moe01.dbf moe01.dbf.bak.f [oracle@tstdb-25-220 xbtst]$ cp moe01.dbf.bak moe01.dbf 啟動數(shù)據(jù)庫報(bào)錯 SYS@xbtst SQL>startup ORACLE instance started. Total System Global Area 2455228416 bytes Fixed Size 2255712 bytes Variable Size 620758176 bytes Database Buffers 1811939328 bytes Redo Buffers 20275200 bytes Database mounted. ORA-01113: file 5 needs media recovery ORA-01110: data file 5: '/dbdata/oradata/xbtst/moe01.dbf' SYS@xbtst SQL>recover datafile 5; ORA-00279: change 1233063 generated at 11/10/2016 09:48:02 needed for thread 1 ORA-00289: suggestion : /apps/oracle/11.2.0/db_1/dbs/arch2_24_925478204.dbf ORA-00280: change 1233063 for thread 1 is in sequence #24 Specify log: {3、場景模擬出來了,下面是修復(fù)過程
使用BBED,將文件頭的SCN等關(guān)鍵信息修改到與控制文件control file相匹配即可
SYS@xbtst SQL>select file#, CHECKPOINT_CHANGE# from v$datafile; FILE# CHECKPOINT_CHANGE# ---------- ------------------ 1 1233692 2 1233692 3 1233692 4 1233692 5 1233692 SYS@xbtst SQL>select CHECKPOINT_CHANGE# from v$database; CHECKPOINT_CHANGE# ------------------ 1233692 SYS@xbtst SQL>select file#, recover, fuzzy, CHECKPOINT_CHANGE# from v$datafile_header; FILE# REC FUZ CHECKPOINT_CHANGE# ---------- --- --- ------------------ 1 NO NO 1233692 2 NO NO 1233692 3 NO NO 1233692 4 NO NO 1233692 5 YES NO 1233063控制文件中datafile5的scn是:1233692,而數(shù)據(jù)文件頭中的scn是:1233063
使用bbed將datafile5的數(shù)據(jù)文件頭對應(yīng)的SCN修改為與其他文件相同,我們先看一下users01.dbf這個文件的文件頭
BBED> set filename '/dbdata/oradata/xbtst/users01.dbf' FILENAME /dbdata/oradata/xbtst/users01.dbf BBED> set block 1 BLOCK# 1 BBED> map File: /dbdata/oradata/xbtst/users01.dbf (0) Block: 1 Dba:0x00000000 ------------------------------------------------------------ Data File Header struct kcvfh, 860 bytes @0 ub4 tailchk @8188因?yàn)槲覀円薷臄?shù)據(jù)文件頭,因此我們需要關(guān)注四個偏移量offset點(diǎn),分別為484、492、140和148
1、datafile 的file header 存儲在第一個block里
2、Oracle considers four attributes of this data structure when determining if a datafile is sync with the other data files of the database:(不同oracle版本offset可能不同)
(1)kscnbas (at offset 484) – SCN of last change to the datafile.
(2)kcvcptim (at offset 492) -Time of the last change to the datafile.
(3)kcvfhcpc (at offset 140) – Checkpoint count.
(4)kcvfhccc (at offset 148) – Unknown, but is always 1 less than thecheckpoint point count.
Oracle有4個屬性來判斷datafile 是否和其他的datafile 一致,如果都一致,可以正常操作,如果不一致,那么會報(bào)ORA-01113錯誤
下面我們看下這幾個offset
BBED> set offset 484 OFFSET 484 BBED> dump File: /dbdata/oradata/xbtst/users01.dbf (0) Block: 1 Offsets: 484 to 995 Dba:0x00000000 ------------------------------------------------------------------------ 1cd31200 00000000 bd384937 01000000 1b000000 30010000 100051f2 02000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 0d000d00 0d000100 00000000 00000000 00000000 02000001 03000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 <32 bytes per line> BBED> set offset 492 OFFSET 492 BBED> dump File: /dbdata/oradata/xbtst/users01.dbf (0) Block: 1 Offsets: 492 to 1003 Dba:0x00000000 ------------------------------------------------------------------------ bd384937 01000000 1b000000 30010000 100051f2 02000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 0d000d00 0d000100 00000000 00000000 00000000 02000001 03000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 <32 bytes per line> BBED> set offset 140 OFFSET 140 BBED> dump File: /dbdata/oradata/xbtst/users01.dbf (0) Block: 1 Offsets: 140 to 651 Dba:0x00000000 ------------------------------------------------------------------------ 78000000 14ff4737 77000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 04000000 05005553 45525300 00000000 00000000 00000000 00000000 00000000 00000000 04000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 7ac92131 01000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 1cd31200 00000000 bd384937 01000000 1b000000 30010000 100051f2 02000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 0d000d00 0d000100 <32 bytes per line> BBED> set offset 148 OFFSET 148 BBED> dump File: /dbdata/oradata/xbtst/users01.dbf (0) Block: 1 Offsets: 148 to 659 Dba:0x00000000 ------------------------------------------------------------------------ 77000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 04000000 05005553 45525300 00000000 00000000 00000000 00000000 00000000 00000000 04000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 7ac92131 01000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 1cd31200 00000000 bd384937 01000000 1b000000 30010000 100051f2 02000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 0d000d00 0d000100 00000000 00000000 <32 bytes per line>其中,位于484和488偏移量的是數(shù)據(jù)文件對應(yīng)的SCN編號。在Oracle內(nèi)部,SCN是使用wrap*4*1024*1024*1024+base來進(jìn)行標(biāo)示的。通常我們看到的數(shù)據(jù)庫wrap都是0。位于492偏移量的是最后一次檢查點(diǎn)對應(yīng)的時(shí)間信息。位于140和148偏移量的是檢查點(diǎn)次數(shù)。這些信息都是會由于時(shí)間推動和檢查點(diǎn)動作引起變化,我們嚴(yán)格情況下,需要保證文件頭塊的信息和控制文件信息一致。
另外一點(diǎn),由于Linux是Little字節(jié)系統(tǒng),要關(guān)注寫入時(shí)候的格式問題。最簡單的方式是dump一下偏移量,看看是怎么保存的。
BBED> dump File: /dbdata/oradata/xbtst/users01.dbf (0) Block: 1 Offsets: 484 to 995 Dba:0x00000000 ------------------------------------------------------------------------ 1cd31200 00000000 bd384937 01000000 1b000000 30010000 100051f2 02000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000然后我們修改moe01.dbf的文件頭(修改這幾個offset,我這里只修改了484、492就可以open庫了)
BBED> set filename '/dbdata/oradata/xbtst/moe01.dbf' FILENAME /dbdata/oradata/xbtst/moe01.dbf BBED> set block 1 BLOCK# 1 BBED> set mode edit MODE Edit BBED> set offset 484 OFFSET 484 BBED> dump File: /dbdata/oradata/xbtst/moe01.dbf (0) Block: 1 Offsets: 484 to 995 Dba:0x00000000 ------------------------------------------------------------------------ a7d01200 00000000 52204937 01000000 18000000 4d8d0000 100051f2 02000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 0d000d00 0d000100 00000000 00000000 00000000 02004001 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 <32 bytes per line> BBED> m /x 1cd31200 484 Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y File: /dbdata/oradata/xbtst/moe01.dbf (0) Block: 1 Offsets: 484 to 995 Dba:0x00000000 ------------------------------------------------------------------------ 1cd31200 00000000 52204937 01000000 18000000 4d8d0000 100051f2 02000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 0d000d00 0d000100 00000000 00000000 00000000 02004001 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 <32 bytes per line> BBED> m /x bd384937 492 BBED-00209: invalid number (bd384937) BBED> m /x bd38 492 File: /dbdata/oradata/xbtst/moe01.dbf (0) Block: 1 Offsets: 492 to 1003 Dba:0x00000000 ------------------------------------------------------------------------ bd384937 01000000 18000000 4d8d0000 100051f2 02000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 0d000d00 0d000100 00000000 00000000 00000000 02004001 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 <32 bytes per line> BBED> m /x 4937 494 File: /dbdata/oradata/xbtst/moe01.dbf (0) Block: 1 Offsets: 494 to 1005 Dba:0x00000000 ------------------------------------------------------------------------ 49370100 00001800 00004d8d 00001000 51f20200 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000d00 0d000d00 01000000 00000000 00000000 00000200 40010000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 <32 bytes per line> BBED> sum apply Check value for File 0, Block 1: current = 0x1683, required = 0x1683 BBED> verify DBVERIFY - Verification starting FILE = /dbdata/oradata/xbtst/moe01.dbf BLOCK = 1 DBVERIFY - Verification complete Total Blocks Examined : 1 Total Blocks Processed (Data) : 0 Total Blocks Failing (Data) : 0 Total Blocks Processed (Index): 0 Total Blocks Failing (Index): 0 Total Blocks Empty : 0 Total Blocks Marked Corrupt : 0 Total Blocks Influx : 0 Message 531 not found; product=RDBMS; facility=BBED這時(shí)我們再次查看各個數(shù)據(jù)文件頭的scn,發(fā)現(xiàn)scn一致了
SYS@xbtst SQL>select file#, recover, fuzzy, CHECKPOINT_CHANGE# from v$datafile_header; FILE# REC FUZ CHECKPOINT_CHANGE# ---------- --- --- ------------------ 1 NO NO 1233692 2 NO NO 1233692 3 NO NO 1233692 4 NO NO 1233692 5 YES NO 1233692試著open庫
SYS@xbtst SQL>alter database open 2 ; alter database open * ERROR at line 1: ORA-01113: file 5 needs media recovery ORA-01110: data file 5: '/dbdata/oradata/xbtst/moe01.dbf' SYS@xbtst SQL>recover datafile 5; Media recovery complete. SYS@xbtst SQL>alter database open; Database altered.看一下alert文件
ALTER DATABASE RECOVER datafile 5 Media Recovery Start Serial Media Recovery started Media Recovery Complete (xbtst) Completed: ALTER DATABASE RECOVER datafile 5 Thu Nov 10 13:47:24 2016 alter database open Thu Nov 10 13:47:25 2016 Thread 1 opened at log sequence 27 Current log# 3 seq# 27 mem# 0: /dbdata/oradata/xbtst/redo03.log Successful open of redo thread 1 MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set Thu Nov 10 13:47:25 2016 SMON: enabling cache recovery [20910] Successfully onlined Undo Tablespace 2. Undo initialization finished serial:0 start:3710149144 end:3710149224 diff:80 (0 seconds) Verifying file header compatibility for 11g tablespace encryption.. Verifying 11g file header compatibility for tablespace encryption completed SMON: enabling tx recovery Database Characterset is AL32UTF8 No Resource Manager plan active replication_dependency_tracking turned off (no async multimaster replication found) Starting background process QMNC Thu Nov 10 13:47:25 2016 QMNC started with pid=20, OS id=21409 Completed: alter database open在本次恢復(fù)過程中,recover是可行的,原因是該庫并沒做過resetlog,如果datafile是在 resetlog之前就已經(jīng)offline的數(shù)據(jù)文件,那recover是不可行的。
另外有需要云服務(wù)器可以了解下創(chuàng)新互聯(lián)cdcxhl.cn,海內(nèi)外云服務(wù)器15元起步,三天無理由+7*72小時(shí)售后在線,公司持有idc許可證,提供“云服務(wù)器、裸金屬服務(wù)器、高防服務(wù)器、香港服務(wù)器、美國服務(wù)器、虛擬主機(jī)、免備案服務(wù)器”等云主機(jī)租用服務(wù)以及企業(yè)上云的綜合解決方案,具有“安全穩(wěn)定、簡單易用、服務(wù)可用性高、性價(jià)比高”等特點(diǎn)與優(yōu)勢,專為企業(yè)上云打造定制,能夠滿足用戶豐富、多元化的應(yīng)用場景需求。