[20220909]bbed关于删除记录恢复的问题.txt

来源：这里教程网时间：2026-03-03 17:51:53 作者：

[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

编辑推荐：

返回列表

[20220909]bbed关于删除记录恢复的问题.txt

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推