[20190213]学习bbed-恢复删除的数据.txt
--//以前也做过类似测试,当时在用bbed做verify时错误都不处理,当时的想法就是能读出就ok了.--//而且当时也做成功,纯粹是依葫芦画瓢,按照别人的blog重复操作,一点不理解为什么这样做.--//重复测试:1.环境:SCOTT@book> @ ver1PORT_STRING VERSION BANNER------------------------------ -------------- --------------------------------------------------------------------------------x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionSCOTT@book> create table dept1 as select * from dept ;Table created.SCOTT@book> select rowid,dept1.* from dept1;ROWID DEPTNO DNAME LOC------------------ ---------- -------------- -------------AAAWPyAAEAAAAILAAA 10 ACCOUNTING NEW YORKAAAWPyAAEAAAAILAAB 20 RESEARCH DALLASAAAWPyAAEAAAAILAAC 30 SALES CHICAGOAAAWPyAAEAAAAILAAD 40 OPERATIONS BOSTONSCOTT@book> @ rowid AAAWPyAAEAAAAILAAA OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT---------- ---------- ---------- ---------- -------------------- -------------------- ---------------------------------------- 91122 4 523 0 0x100020B 4,523 alter system dump datafile 4 block 523 ;SCOTT@book> delete from dept1 where deptno=10;1 row deleted.SCOTT@book> commit ;Commit complete.SCOTT@book> alter system checkpoint ;System altered.2.使用bbed恢复测试:BBED> set dba 4,523 DBA 0x0100020b (16777739 4,523)BBED> x /rncc *kdbr[1]rowdata[44] @8140-----------flag@8140: 0x2c (KDRHFL, KDRHFF, KDRHFH)lock@8141: 0x00cols@8142: 3col 0[2] @8143: 20col 1[8] @8146: RESEARCHcol 2[6] @8155: DALLASBBED> x /rncc *kdbr[0]rowdata[66] @8162-----------flag@8162: 0x3c (KDRHFL, KDRHFF, KDRHFD, KDRHFH)lock@8163: 0x02cols@8164: 0--//可以看出删除的flag=0x3c(正常没有发生行链接或者迁移的情况下).BBED> assign offset 8162=0x2c;Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) yub1 rowdata[0] @8162 0x2cBBED> x /rncc *kdbr[0]rowdata[66] @8162-----------flag@8162: 0x2c (KDRHFL, KDRHFF, KDRHFH)lock@8163: 0x02cols@8164: 3col 0[2] @8165: 10col 1[10] @8168: ACCOUNTINGcol 2[8] @8179: NEW YORK--//OK,现在已经正常读出.BBED> sum applyCheck value for File 4, Block 523:current = 0x8dc2, required = 0x8dc2BBED> verifyDBVERIFY - Verification startingFILE = /mnt/ramdisk/book/users01.dbfBLOCK = 523Block Checking: DBA = 16777739, Block Type = KTB-managed data blockdata header at 0x7f9b8221527ckdbchk: the amount of space used is not equal to block size used=118 fsc=24 avsp=7946 dtl=8064Block 523 failed with check code 6110--//以前verify出错,我基本不做修复,因为这些数值不是很好理解.--//实际上计算公司是 --//dtl-used-fsc=avsp 或者 used+fsc+avsp=dtl.--//8064-118-24 = 7922--//前面显示(lock@8163: 0x02)事务使用ITL槽是1(从0开始记数).BBED> p ktbbh.ktbbhitl[1]struct ktbbhitl[1], 24 bytes @68 struct ktbitxid, 8 bytes @68 ub2 kxidusn @68 0x000a ub2 kxidslt @70 0x0007 ub4 kxidsqn @72 0x000058bf struct ktbituba, 8 bytes @76 ub4 kubadba @76 0x00c001e4 ub2 kubaseq @80 0x11ad ub1 kubarec @82 0x1d ub2 ktbitflg @84 0x2001 (KTBFUPB) union _ktbitun, 2 bytes @86 sb2 _ktbitfsc @86 24 ub2 _ktbitwrp @86 0x0018 ub4 ktbitbas @88 0x1775b85d--//_ktbitfsc,_ktbitwrp类似C语言的union,当设置提交标识时,表示scn_wrap.其它表示dml操作回收的空间(不包括flag,lock标识).--//如果insert或者update需要空间比原来大,记录是0.BBED> x /rncc *kdbr[0]rowdata[66] @8162-----------flag@8162: 0x2c (KDRHFL, KDRHFF, KDRHFH)lock@8163: 0x02cols@8164: 3col 0[2] @8165: 10col 1[10] @8168: ACCOUNTINGcol 2[8] @8179: NEW YORK--//1+1+2+1+10+1+8 = 24,正好等于回收空间.注意每个字段前有1个长度指示器(如果字符串长度大于250,需要3个字节)BBED> assign ktbbh.ktbbhitl[1]._ktbitun._ktbitfsc=0;sb2 _ktbitfsc @86 0BBED> sum applyCheck value for File 4, Block 523:current = 0x8dda, required = 0x8ddaBBED> verifyDBVERIFY - Verification startingFILE = /mnt/ramdisk/book/users01.dbfBLOCK = 523Block Checking: DBA = 16777739, Block Type = KTB-managed data blockdata header at 0x1a98e7ckdbchk: space available on commit is incorrect tosp=7972 fsc=0 stb=0 avsp=7946Block 523 failed with check code 6111--//计算公式是tosp=fsc+stb+avsp,stb 标识什么不理解.BBED> p kdbhstruct kdbh, 14 bytes @124 ub1 kdbhflag @124 0x00 (NONE) sb1 kdbhntab @125 1 sb2 kdbhnrow @126 4 sb2 kdbhfrre @128 -1 sb2 kdbhfsbo @130 26 sb2 kdbhfseo @132 7972 sb2 kdbhavsp @134 7946 sb2 kdbhtosp @136 7972BBED> assign kdbhtosp=kdbhavsp;sb2 kdbhtosp @136 7946BBED> assign kdbh.kdbhtosp=kdbh.kdbhavsp;sb2 kdbhtosp @136 7946--//两者写法都支持.BBED> sum applyCheck value for File 4, Block 523:current = 0x8df4, required = 0x8df4BBED> verifyDBVERIFY - Verification startingFILE = /mnt/ramdisk/book/users01.dbfBLOCK = 523--//OK.SCOTT@book> alter system flush buffer_cache;System altered.SCOTT@book> select rowid,dept1.* from dept1;ROWID DEPTNO DNAME LOC------------------ ---------- -------------- -------------AAAWPyAAEAAAAILAAA 10 ACCOUNTING NEW YORKAAAWPyAAEAAAAILAAB 20 RESEARCH DALLASAAAWPyAAEAAAAILAAC 30 SALES CHICAGOAAAWPyAAEAAAAILAAD 40 OPERATIONS BOSTON--//随便说一下,不要再使用modify修改信息,这样非常容易出错(要考虑大小头问题).使用assign简单快捷,很少出错.--//简直就像小时候玩变形金刚的游戏.