[20190213]学习bbed-恢复删除的数据.txt

[20190213]学习bbed-恢复删除的数据.txt

--//以前也做过类似测试,当时在用bbed做verify时错误都不处理,当时的想法就是能读出就ok了.
--//而且当时也做成功,纯粹是依葫芦画瓢,按照别人的blog重复操作,一点不理解为什么这样做.
--//重复测试:

1.环境:
scott@book> @ ver1
port_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 production

scott@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 york
aaawpyaaeaaaailaab         20 research       dallas
aaawpyaaeaaaailaac         30 sales          chicago
aaawpyaaeaaaailaad         40 operations     boston

scott@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: 0x00
cols@8142:    3

col    0[2] @8143: 20
col    1[8] @8146: research
col    2[6] @8155: dallas


bbed> x /rncc  *kdbr[0]
rowdata[66]                                 @8162
-----------
flag@8162: 0x3c (kdrhfl, kdrhff, kdrhfd, kdrhfh)
lock@8163: 0x02
cols@8164:    0

--//可以看出删除的flag=0x3c(正常没有发生行链接或者迁移的情况下).

bbed> assign offset 8162=0x2c;
warning: contents of previous bifile will be lost. proceed? (y/n) y
ub1 rowdata[0]                              @8162     0x2c

bbed> x /rncc  *kdbr[0]
rowdata[66]                                 @8162
-----------
flag@8162: 0x2c (kdrhfl, kdrhff, kdrhfh)
lock@8163: 0x02
cols@8164:    3

col    0[2] @8165: 10
col   1[10] @8168: accounting
col    2[8] @8179: new york

--//ok,现在已经正常读出.
bbed> sum apply
check value for file 4, block 523:
current = 0x8dc2, required = 0x8dc2

bbed> verify
dbverify - verification starting
file = /mnt/ramdisk/book/users01.dbf
block = 523

block checking: dba = 16777739, block type = ktb-managed data block
data header at 0x7f9b8221527c
kdbchk: the amount of space used is not equal to block size
        used=118 fsc=24 avsp=7946 dtl=8064
block 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: 0x02
cols@8164:    3

col    0[2] @8165: 10
col   1[10] @8168: accounting
col    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       0

bbed> sum apply
check value for file 4, block 523:
current = 0x8dda, required = 0x8dda

bbed> verify
dbverify - verification starting
file = /mnt/ramdisk/book/users01.dbf
block = 523

block checking: dba = 16777739, block type = ktb-managed data block
data header at 0x1a98e7c
kdbchk: space available on commit is incorrect
        tosp=7972 fsc=0 stb=0 avsp=7946
block 523 failed with check code 6111

--//计算公式是tosp=fsc+stb+avsp,stb 标识什么不理解.

bbed> p kdbh
struct 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      7972

bbed> assign kdbhtosp=kdbhavsp;
sb2 kdbhtosp                                @136      7946

bbed> assign kdbh.kdbhtosp=kdbh.kdbhavsp;
sb2 kdbhtosp                                @136      7946

--//两者写法都支持.

bbed> sum apply
check value for file 4, block 523:
current = 0x8df4, required = 0x8df4

bbed> verify
dbverify - verification starting
file = /mnt/ramdisk/book/users01.dbf
block = 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 york
aaawpyaaeaaaailaab         20 research       dallas
aaawpyaaeaaaailaac         30 sales          chicago
aaawpyaaeaaaailaad         40 operations     boston

--//随便说一下,不要再使用modify修改信息,这样非常容易出错(要考虑大小头问题).使用assign简单快捷,很少出错.
--//简直就像小时候玩变形金刚的游戏.