[20220909]bbed關於刪除記錄恢復的問題.txt
[20220909]bbed關於刪除記錄恢復的問題.txt
--//快下班被別人問的關於刪除記錄使用bbed恢復的問題,我開始以為很快講解完,刪除記錄oracle僅僅打上一個標識,實際的記錄還存在.
--//實際上地方問的是多次DML(刪除記錄的情況),實際上只要dump還能看到,bbed還是可以恢復的,做一個例子說明:
1.環境:
SCOTT@test01p> @ver1
PORT_STRING VERSION BANNER CON_ID
-------------------- ---------- ---------------------------------------------------------------------------- ------
IBMPC/WIN_NT64-9.1.0 12.2.0.1.0 Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0
SCOTT@test01p> create table deptx as select * from dept ;
Table created.
SCOTT@test01p> select rowid from deptx where rownum=1;
ROWID
------------------
AAAHGVAALAAAACjAAA
SCOTT@test01p> @ rowid AAAHGVAALAAAACjAAA
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
---------- ---------- ---------- ---------- ----------- ------ ----------------------------------------
29077 11 163 0 0x2C000A3 11,163 alter system dump datafile 11 block 163
SCOTT@test01p> alter system checkpoint ;
System altered.
SCOTT@test01p> alter system checkpoint ;
System altered.
2.測試:
BBED> set dba 11,164
DBA 0x02c000a4 (46137508 11,164)
--//注:bbed for windows 訪問的block要加1,主要問題在於無法識別資料檔案的第0塊(OS塊頭)
BBED> p *kdbr
rowdata[66]
-----------
ub1 rowdata[66] @8162 0x2c
BBED> p kdbr
sb2 kdbr[0] @142 8038
sb2 kdbr[1] @144 8016
sb2 kdbr[2] @146 7996
sb2 kdbr[3] @148 7972
SCOTT@test01p> delete from deptx where deptno=20;
1 row deleted.
SCOTT@test01p> commit ;
Commit complete.
SCOTT@test01p> alter system checkpoint ;
System altered.
--//透過bbed觀察如下:
BBED> p kdbr dba 11,164
sb2 kdbr[0] @142 8038
sb2 kdbr[1] @144 8016
sb2 kdbr[2] @146 7996
sb2 kdbr[3] @148 7972
BBED> x /rncc *kdbr[0]
rowdata[66] @8162
-----------
flag@8162: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8163: 0x00
cols@8164: 3
col 0[2] @8165: 10
col 1[10] @8168: ACCOUNTING
col 2[8] @8179: NEW YORK
BBED> x /rncc *kdbr[1]
rowdata[44] @8140
-----------
flag@8140: 0x3c (KDRHFL, KDRHFF, KDRHFD, KDRHFH)
lock@8141: 0x02
cols@8142: 0
--//你可以發現flag 從0x2c變成 0x3c，打上了KDRHFD標識表示刪除，恢復實際上就是修改flag=0x2c(也就是取消KDRHFD標識).
--//繼續:
SCOTT@test01p> delete from deptx where deptno=30;
1 row deleted.
SCOTT@test01p> commit ;
Commit complete.
SCOTT@test01p> alter system checkpoint ;
System altered.
BBED> p kdbr dba 11,164
sb2 kdbr[0] @142 8038
sb2 kdbr[1] @144 8016
sb2 kdbr[2] @146 7996
sb2 kdbr[3] @148 7972
--//偏移量還是不變,也就是這樣的情況下以上兩條記錄都可以恢復.
SCOTT@test01p> update deptx set dname=upper(dname) where deptno=10;
1 row updated.
SCOTT@test01p> commit ;
Commit complete.
SCOTT@test01p> alter system checkpoint ;
System altered.
BBED> p kdbr dba 11,164
sb2 kdbr[0] @142 8038
sb2 kdbr[1] @144 8016
sb2 kdbr[2] @146 7996
sb2 kdbr[3] @148 7972
--//我執行update操作，但是行目錄並沒有修改，主要原因在於DML修改的記錄長度沒有變化，oracle並不會改變行目錄的偏移。
--//而是就地修改相關記錄資訊。
SCOTT@test01p> update deptx set dname=upper(dname)||'0' where deptno=10;
1 row updated.
SCOTT@test01p> commit ;
Commit complete.
SCOTT@test01p> alter system checkpoint ;
System altered.
BBED> p kdbr dba 11,164
sb2 kdbr[0] @142 7945
sb2 kdbr[1] @144 2
sb2 kdbr[2] @146 -1
sb2 kdbr[3] @148 7972
--//在DML後修改記錄長度發生變化後,oracle在修改kdbr[0]的偏移時,同時也修改kdbr[1],kdbr[2]的資訊,
--//這個應該是oracle的一個設計理念，順手把以前沒做的事情做完...
--//你可以從修改的資訊可以推斷，如果下次操作是插入，使用那個行目錄時應該從kdbr[1]，kdbr[2]選擇。
--//如果你仔細觀察可以發現刪除記錄的行目錄記錄的偏移記錄的資訊形成1個連結串列結構,sb2 kdbr[2] = -1 表示連結串列結構的尾部.
--//並且可以透過行目錄偏移 kdbr[N] 是否 >= kdbh.kdbhnrow (當前為4),來確定是否指向正確的記錄資訊。
BBED> p kdbh dba 11,164
struct kdbh, 14 bytes @124
ub1 kdbhflag @124 0x00 (NONE)
b1 kdbhntab @125 1
b2 kdbhnrow @126 4
=======================================================
sb2 kdbhfrre @128 1
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
sb2 kdbhfsbo @130 26
sb2 kdbhfseo @132 7945
b2 kdbhavsp @134 7987
b2 kdbhtosp @136 7987
--//kdbhfrre = 1 ,表示連結串列結構的開頭，也就是如果在塊DML有插入時,會先使用kdbr[1]行目錄.
--//你可以發現這時kdbr[1],kdbr[2]執行的偏移並沒有覆蓋,要恢復一定ok的.
BBED> assign kdbr[1] = 8016;
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
sb2 kdbr[0] @144 8016
BBED> assign kdbr[2] = 7996;
sb2 kdbr[0] @146 7996
BBED> x /rncc *kdbr[1]
rowdata[71] @8140
-----------
flag@8140: 0x3c (KDRHFL, KDRHFF, KDRHFD, KDRHFH)
lock@8141: 0x02
cols@8142: 0
BBED> x /rncc *kdbr[2]
rowdata[51] @8120
-----------
flag@8120: 0x3c (KDRHFL, KDRHFF, KDRHFD, KDRHFH)
lock@8121: 0x03
cols@8122: 0
--//修改flag取消KDRHFD標識:
BBED> assign offset 8140 = 0x2c;
ub1 rowdata[0] @8140 0x2c
BBED> assign offset 8120 = 0x2c;
ub1 rowdata[0] @8120 0x2c
BBED> x /2rncc *kdbr[2]
rowdata[51] @8120
-----------
flag@8120: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8121: 0x03
cols@8122: 3
col 0[2] @8123: 30
col 1[5] @8126: SALES
col 2[7] @8132: CHICAGO
rowdata[71] @8140
-----------
flag@8140: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8141: 0x02
cols@8142: 3
col 0[2] @8143: 20
col 1[8] @8146: RESEARCH
col 2[6] @8155: DALLAS
--//現在2條記錄都可以顯見,剩下就是恢復對應資料塊的完整性以及一致性問題.
BBED> sum apply
Check value for File 11, Block 164:
current = 0x9fe2, required = 0x9fe2
BBED> verify
DBVERIFY - Verification starting
FILE = D:\APP\ORACLE\ORADATA\TEST\TEST01P\USERS01.DBF
BLOCK = 163
Block Checking: DBA = 46137507, Block Type = KTB-managed data block
data header at 0x2c4027c
kdbchk: row locked by non-existent transaction
table=0 slot=2
lockid=3 ktbbhitc=3
Block 163 failed with check code 6101
--//lock 偏移8121 =0x0.
BBED> assign offset 8121=0x0;
ub1 rowdata[0] @8121 0x00
BBED> sum apply
Check value for File 11, Block 164:
current = 0x9ce2, required = 0x9ce2
BBED> verify
DBVERIFY - Verification starting
FILE = D:\APP\ORACLE\ORADATA\TEST\TEST01P\USERS01.DBF
BLOCK = 163
Block Checking: DBA = 46137507, Block Type = KTB-managed data block
data header at 0x2c4027c
kdbchk: entries on the free list are not ordered
next=8016 nrow=4 chas=1
Block 163 failed with check code 6106
BBED> assign kdbh.kdbhfrre=-1
sb2 kdbhfrre @128 -1
BBED> sum apply
Check value for File 11, Block 164:
current = 0x631c, required = 0x631c
BBED> verify
DBVERIFY - Verification starting
FILE = D:\APP\ORACLE\ORADATA\TEST\TEST01P\USERS01.DBF
BLOCK = 163
Block Checking: DBA = 46137507, Block Type = KTB-managed data block
data header at 0x2c4027c
kdbchk: xaction header lock count mismatch
trans=2 ilk=1 nlo=2
Block 163 failed with check code 6108
--//lock 偏移8141 =0x0.
BBED> assign offset 8141=0x0;
ub1 rowdata[0] @8141 0x00
BBED> sum apply
Check value for File 11, Block 164:
current = 0x611c, required = 0x611c
BBED> verify
DBVERIFY - Verification starting
FILE = D:\APP\ORACLE\ORADATA\TEST\TEST01P\USERS01.DBF
BLOCK = 163
Block Checking: DBA = 46137507, Block Type = KTB-managed data block
data header at 0x2c4027c
kdbchk: the amount of space used is not equal to block size
used=119 fsc=0 avsp=7987 dtl=8064
Block 163 failed with check code 6110
--//dtl-used-fsc = avsp
--//8064-119-0 = 7945
BBED> assign kdbh.kdbhavsp=7945;
b2 kdbhavsp @134 7945
BBED> sum apply
Check value for File 11, Block 164:
current = 0x6126, required = 0x6126
BBED> verify
DBVERIFY - Verification starting
FILE = D:\APP\ORACLE\ORADATA\TEST\TEST01P\USERS01.DBF
BLOCK = 163
Block Checking: DBA = 46137507, Block Type = KTB-managed data block
data header at 0x2c4027c
kdbchk: space available on commit is incorrect
tosp=7987 fsc=0 stb=0 avsp=7945
Block 163 failed with check code 6111
BBED> assign kdbh.kdbhtosp=7945;
b2 kdbhtosp @136 7945
BBED> sum apply
Check value for File 11, Block 164:
current = 0x611c, required = 0x611c
BBED> verify
DBVERIFY - Verification starting
FILE = D:\APP\ORACLE\ORADATA\TEST\TEST01P\USERS01.DBF
BLOCK = 163
--//OK現在恢復完成.
SCOTT@test01p> alter system flush BUFFER_CACHE;
System altered.
SCOTT@test01p> select * from deptx;
DEPTNO DNAME LOC
---------- -------------------- -------------
10 ACCOUNTING0 NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
--//刪除記錄已經恢復.
--//原來deptno= 10 的記錄沒有覆蓋，理論將也可以恢復.繼續嘗試看看.
BBED> assign kdbr[0]=8038
sb2 kdbr[0] @142 8038
BBED> x /rncc *kdbr[0]
rowdata[93] @8162
-----------
flag@8162: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8163: 0x00
cols@8164: 3
col 0[2] @8165: 10
col 1[10] @8168: ACCOUNTING
col 2[8] @8179: NEW YORK
--//這樣修改行目錄偏移指向執行原來的位置.
BBED> sum apply
Check value for File 11, Block 164:
current = 0x6173, required = 0x6173
BBED> verify
DBVERIFY - Verification starting
FILE = D:\APP\ORACLE\ORADATA\TEST\TEST01P\USERS01.DBF
BLOCK = 163
Block Checking: DBA = 46137507, Block Type = KTB-managed data block
data header at 0x2c4027c
kdbchk: xaction header lock count mismatch
trans=2 ilk=1 nlo=0
Block 163 failed with check code 6108
BBED> assign offset 8163 = 0x02
ub1 rowdata[0] @8163 0x02
BBED> sum apply
Check value for File 11, Block 164:
current = 0x6373, required = 0x6373
BBED> verify
DBVERIFY - Verification starting
FILE = D:\APP\ORACLE\ORADATA\TEST\TEST01P\USERS01.DBF
BLOCK = 163
Block Checking: DBA = 46137507, Block Type = KTB-managed data block
data header at 0x2c4027c
kdbchk: the amount of space used is not equal to block size
used=118 fsc=0 avsp=7945 dtl=8064
Block 163 failed with check code 6110
--//dtl-used-fsc = avsp
--//8064-118-0 = 7946
BBED> assign kdbh.kdbhavsp=7946;
b2 kdbhavsp @134 7946
BBED> assign kdbh.kdbhtosp=7946;
b2 kdbhtosp @136 7946
BBED> sum apply
Check value for File 11, Block 164:
current = 0x6373, required = 0x6373
BBED> verify
DBVERIFY - Verification starting
FILE = D:\APP\ORACLE\ORADATA\TEST\TEST01P\USERS01.DBF
BLOCK = 163
--//ok,現在恢復到原始建立時的狀態.
SCOTT@test01p> alter system flush BUFFER_CACHE;
System altered.
SCOTT@test01p> select * from deptx;
DEPTNO DNAME LOC
---------- -------------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
3.總結：
--//很久不使用bbed，有點生疏了。
--//我前面的恢復定位行目錄資訊時使用原來顯示的資訊，實際的恢復只能透過find檢索0x2c,0x3c字元定位。
--//然後透過執行 x /rncc offset NNNN,確定顯示的資訊是否正確。獲得NNNN偏移是絕對偏移，必須減去kdbh的偏移(這裡是124，前面
--//有3個ITL槽),這樣才能確定行目錄的相對偏移值。
--//總之到對應的資料塊操作相對複雜!!!
BBED> p kdbr dba 11,164
sb2 kdbr[0] @142 8038
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
sb2 kdbr[1] @144 8016
sb2 kdbr[2] @146 7996
sb2 kdbr[3] @148 7972
BBED> p kdbh dba 11,164
struct kdbh, 14 bytes @124
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ub1 kdbhflag @124 0x00 (NONE)
b1 kdbhntab @125 1
b2 kdbhnrow @126 4
sb2 kdbhfrre @128 1
sb2 kdbhfsbo @130 26
sb2 kdbhfseo @132 7945
b2 kdbhavsp @134 7987
b2 kdbhtosp @136 7987
BBED> x /rncc *kdbr[0]
rowdata[66] @8162
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-----------
flag@8162: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8163: 0x00
cols@8164: 3
col 0[2] @8165: 10
col 1[10] @8168: ACCOUNTING
col 2[8] @8179: NEW YORK
--// 8162-124 = 8038
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2915698/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20210930]bbed恢復刪除的資料.txt
- [20190130]刪除tab$記錄的恢復.txt
- [20190213]學習bbed-恢復刪除的資料.txt
- [20190225]刪除tab$記錄的恢復5.txt
- [20190130]刪除tab$記錄的恢復2.txt
- [20190212]刪除tab$記錄的恢復3.txt
- [20190124]bbed恢復資料遇到延遲塊清除的問題.txt
- [20210803]刪除user$的恢復準備.txt
- [20190125]bbed恢復資料遇到延遲塊清除的問題3.txt
- [20190124]bbed恢復資料遇到延遲塊清除的問題2.txt
- [20181227]bbed的使用問題.txt
- oracle使用小記、刪除恢復Oracle
- 刪除的微信聊天記錄怎麼恢復？（已解決）
- [20181204]bbed修改問題.txt
- [20180619]bbed verify問題.txt
- [20230329]記錄除錯sql語句遇到的問題.txt除錯SQL
- [20210303]bbed使用小問題.txt
- [20231020]rename IDL_UB1$後使用bbed的恢復.txt
- hbase 恢復 誤刪除
- NTFS刪除及恢復
- Git恢復被刪除的分支Git
- Git恢復刪除的檔案Git
- [20180612]刪除bootstrap$記錄無法啟動.txtboot
- 記錄一次刪除檔案失敗的問題
- [20200423]12c刪除不需要的記錄.txt
- 華為、榮耀手機微信聊天記錄刪除怎麼恢復
- [20191129]關於hugepages的問題.txt
- 行動硬碟刪除的檔案能恢復嗎，怎麼恢復硬碟刪除的檔案硬碟
- 關於離線的群聊天記錄問題
- [20230427]bbed sum apply問題2.txtAPP
- [20191220]關於共享記憶體段相關問題.txt記憶體
- 記錄一個由於倉庫層錯誤導致軟刪除失效的問題
- docker筆記40-ceph osd誤刪除恢復Docker筆記
- [20221121]rman刪除歸檔日誌問題.txt
- iptables刪除命令中的相關問題
- [20181229]關於字串的分配問題.txt字串
- [20210401]使用bbed讀取資料塊恢復注意6.txt
- [20191202]關於hugepages相關問題.txt