[20170411]bbed删除记录的恢复.txt
--//昨天上午做的测试,链接:http://blog.itpub.net/267265/viewspace-2136933/
--//我当时并没有选择恢复记录,仅仅看删除的内容.因为这样恢复是存在许多问题.
--//执行 drop function scott.sleep ; 删除sys.source$相关记录仅仅是该命令的一小部分,恢复
--//sys.source$相关记录会存在许多问题,但是如果是应用数据恢复还是可以,实际上以前我的博客也做过类似操作.
--//当时刚刚开始学习bbed,基本按照别人的例子做的,自己在重复加深理解.
--//主要一些bbed相关知识不懂。
1.环境：
SCOTT@book> @ &r/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 deptx as select * from dept ;
Table created.
SCOTT@book> @ &r/qq deptx 1
ROWID OBJECT FILE BLOCK ROW ROWID_DBA DEPTNO DNAME LOC
------------------ ---------- ---------- ---------- ---------- -------------------- ---------- -------------- -------------
AAAWGhAAEAAAAIjAAA 90529 4 547 0 0x1000223 10 ACCOUNTING NEW YORK
SCOTT@book> alter system checkpoint ;
System altered.
SCOTT@book> alter system checkpoint ;
System altered.
--//脏块写盘.
--//删除恢复一般：
A.首先删除后并没有覆盖,如果覆盖了这样恢复不行.
B.恢复就是恢复flag=0x2c(删除flag=0x3c)
C.恢复相关设置一致,我以前这步从来不做,我觉得麻烦,因为select已经可以访问了.本文主要关注这里。而是直接sum apply。
D.sum apply.
2.使用bbed观察：
BBED> map dba 4 ,547
File: /mnt/ramdisk/book/users01.dbf (4)
Block: 547 Dba:0x01000223
------------------------------------------------------------
KTB Data Block (Table/Cluster)
struct kcbh, 20 bytes @0
struct ktbbh, 96 bytes @20
struct kdbh, 14 bytes @124
struct kdbt[1], 4 bytes @138
sb2 kdbr[4] @142
ub1 freespace[7946] @150
ub1 rowdata[92] @8096
ub4 tailchk @8188
BBED> p dba 4,547 kdbh
struct kdbh, 14 bytes @124 Data Header Structure
ub1 kdbhflag @124 0x00 (NONE) N=pctfree hit(clusters); F=do not put on freelist; K=flushable cluster keys
sb1 kdbhntab @125 1 Number of tables (>1 in clusters)
sb2 kdbhnrow @126 4 Number of rows
sb2 kdbhfrre @128 -1 First free row entry index; -1 = you have to add one
sb2 kdbhfsbo @130 26 Freespace begin offset
sb2 kdbhfseo @132 7972 Freespace end offset
sb2 kdbhavsp @134 7946 Available space in the block
sb2 kdbhtosp @136 7946 Total available space when all TXs commit
--//人为改动kdbh.kdbhavsp=7945;产生不一致。
BBED> assign kdbh.kdbhavsp=7945;
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
sb2 kdbhavsp @134 7945
BBED> sum dba 4,547 apply
Check value for File 4, Block 547:
current = 0x48ba, required = 0x48ba
BBED> verify
DBVERIFY - Verification starting
FILE = /mnt/ramdisk/book/users01.dbf
BLOCK = 547
Block Checking: DBA = 16777763, Block Type = KTB-managed data block
data header at 0x7f7a00b3427c
kdbchk: the amount of space used is not equal to block size
used=118 fsc=0 avsp=7945 dtl=8064
Block 547 failed with check code 6110
--// dtl 猜测一下 最后tailchk 占用4个字节. 8192-4=8188,再减去124(注:kdbh的偏移量位置)就是8064.也就真正能写数据的空间8064.
--// 如果2个ITL槽对于一般表 kdbh的偏移就是100(表空间类型ASSM)，很好记，ctas建立的表多了1个ITL槽，也就是多了24字节。
SCOTT@book> select sum(length(deptno)+length(dname)+length(loc)) n10 from dept;
N10
----
68
--// used=118 看字面意思就是使用量。
--//如果加上长度指示器,字段数量指示器 (3+1)*4=16,68+16=84.
--//如果包括每条记录flag,lock 2个字节,共4条记录. 84+8=92,也与rowdata[92]对上。
--//还差118-92=26 字节。
BBED> map dba 4 ,547
File: /mnt/ramdisk/book/users01.dbf (4)
Block: 547 Dba:0x01000223
------------------------------------------------------------
KTB Data Block (Table/Cluster)
struct kcbh, 20 bytes @0
struct ktbbh, 96 bytes @20
struct kdbh, 14 bytes @124
struct kdbt[1], 4 bytes @138
sb2 kdbr[4] @142
ub1 freespace[7946] @150
ub1 rowdata[92] @8096
ub4 tailchk @8188
--//注意freespace的偏移量150，kdbh偏移量是124，相差26.正好合适对上。
--//观察 kdbhfsbo @130 26 Freespace begin offset 也正好对上。
--//根据以前我自己的计算公式：
dtl - used +fsc = avsp (注：这时错误，正确应该是dtl - used - fsc = avsp,这里是fsc=0)
8064-118+0=7946
--//还原：
BBED> assign kdbh.kdbhavsp=7946;
sb2 kdbhavsp @134 7946
BBED> sum dba 4,547 apply
Check value for File 4, Block 547:
current = 0x48b9, required = 0x48b9
3.删除恢复后bbed观察：
SCOTT@book> delete from deptx;
4 rows deleted.
SCOTT@book> commit ;
Commit complete.
SCOTT@book> alter system checkpoint ;
System altered.
--//说明当dml操作后,如果总长度缩短oracle会在对应ITL中的fsc记录空间回收的量.当然如果比原来大，fsc=0.没有变化.
--//如果加上长度指示器,字段数量指示器 (3+1)*4=16,68+16=84.（不包括flag，lock标识）
--//注3个字段,4条记录,而且没有NULL.可以参考链接:http://blog.itpub.net/267265/viewspace-2108017/
BBED> x /rncc *kdbr[0]
rowdata[66] @8162
-----------
flag@8162: 0x3c (KDRHFL, KDRHFF, KDRHFD, KDRHFH)
lock@8163: 0x02
cols@8164: 0
--//lock=0x02,使用ITL槽1.
BBED> p ktbbh.ktbbhitl[1]
struct ktbbhitl[1], 24 bytes @68
struct ktbitxid, 8 bytes @68
ub2 kxidusn @68 0x000a
ub2 kxidslt @70 0x0010
ub4 kxidsqn @72 0x00004f79
struct ktbituba, 8 bytes @76
ub4 kubadba @76 0x00c00119
ub2 kubaseq @80 0x0fcc
ub1 kubarec @82 0x04
ub2 ktbitflg @84 0x2004 (KTBFUPB)
union _ktbitun, 2 bytes @86
sb2 _ktbitfsc @86 84
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ub2 _ktbitwrp @86 0x0054
ub4 ktbitbas @88 0x1764e8fb
--//注意看下划线， ktbbh.ktbbhitl._ktbitun._ktbitfsc=84.与推测一致。
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 8038
--//人为改动kdbh.kdbhavsp=7945;产生不一致。
BBED> assign kdbh.kdbhavsp=7945;
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
sb2 kdbhavsp @134 7945
BBED> sum dba 4,547 apply
Check value for File 4, Block 547:
current = 0xd46b, required = 0xd46b
BBED> verify
DBVERIFY - Verification starting
FILE = /mnt/ramdisk/book/users01.dbf
BLOCK = 547
Block Checking: DBA = 16777763, Block Type = KTB-managed data block
data header at 0x7f04d01be27c
kdbchk: the amount of space used is not equal to block size
used=34 fsc=84 avsp=7945 dtl=8064
Block 547 failed with check code 6110
--//这样我以前的计算公式就不对了。实际上是减fsc
--// dtl - used - fsc = avsp ,特此更正。
--//8064-34-84=7946 ,哎学习还是不认真，缺少交流。
--//used=34 很容易确定
--//注意freespace的偏移量150，kdbh偏移量是124，相差26.正好合适对上。
--//观察 kdbhfsbo @130 26 Freespace begin offset 也正好对上。
--//删除记录后 包括每条记录flag,lock 2个字节,共4条记录，8个字节，相加正好26+8＝34.
--//简单一点 118-84=34.
4.删除恢复：
--//先还原kdbh.kdbhavsp=7946;
BBED> assign kdbh.kdbhavsp=7946;
sb2 kdbhavsp @134 7946
BBED> sum dba 4,547 apply
Check value for File 4, Block 547:
current = 0xd468, required = 0xd468
--//有了以上信息，要恢复删除记录就简单了。
1.修改flag=0x2c
2.清除fsc=0 .
3.修改tosp=avsp .
BBED> x /rncc *kdbr[0]
rowdata[66] @8162
-----------
flag@8162: 0x3c (KDRHFL, KDRHFF, KDRHFD, KDRHFH)
lock@8163: 0x02
cols@8164: 0
BBED> x /rncc *kdbr[1]
rowdata[44] @8140
-----------
flag@8140: 0x3c (KDRHFL, KDRHFF, KDRHFD, KDRHFH)
lock@8141: 0x02
cols@8142: 0
BBED> x /rncc *kdbr[2]
rowdata[24] @8120
-----------
flag@8120: 0x3c (KDRHFL, KDRHFF, KDRHFD, KDRHFH)
lock@8121: 0x02
cols@8122: 0
BBED> x /rncc *kdbr[3]
rowdata[0] @8096
----------
flag@8096: 0x3c (KDRHFL, KDRHFF, KDRHFD, KDRHFH)
lock@8097: 0x02
cols@8098: 0
--//执行如下：
assign dba 4,547 offset 8162=0x2c
assign dba 4,547 offset 8140=0x2c
assign dba 4,547 offset 8120=0x2c
assign dba 4,547 offset 8096=0x2c
BBED> x /4rncc *kdbr[3]
rowdata[0] @8096
----------
flag@8096: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8097: 0x00
cols@8098: 3
col 0[2] @8099: 40
col 1[10] @8102: OPERATIONS
col 2[6] @8113: BOSTON
rowdata[24] @8120
-----------
flag@8120: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8121: 0x00
cols@8122: 3
col 0[2] @8123: 30
col 1[5] @8126: SALES
col 2[7] @8132: CHICAGO
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
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
--//ok,bbed写显示正常。
BBED> sum dba 4,547 apply
Check value for File 4, Block 547:
current = 0xd468, required = 0xd468
BBED> verify
DBVERIFY - Verification starting
FILE = /mnt/ramdisk/book/users01.dbf
BLOCK = 547
Block Checking: DBA = 16777763, Block Type = KTB-managed data block
data header at 0x172107c
kdbchk: the amount of space used is not equal to block size
used=118 fsc=84 avsp=7946 dtl=8064
Block 547 failed with check code 6110
--//修改fsc=0.满足 dtl-used-fsc=avsp
BBED> assign ktbbh.ktbbhitl[1]._ktbitun._ktbitfsc=84
sb2 _ktbitfsc @86 0
BBED> sum dba 4,547 apply
Check value for File 4, Block 547:
current = 0xd43c, required = 0xd43c
BBED> verify
DBVERIFY - Verification starting
FILE = /mnt/ramdisk/book/users01.dbf
BLOCK = 547
Block Checking: DBA = 16777763, Block Type = KTB-managed data block
data header at 0x172127c
kdbchk: space available on commit is incorrect
tosp=8038 fsc=0 stb=0 avsp=7946
Block 547 failed with check code 6111
--//再修改tosp=avsp。
BBED> assign kdbh.kdbhtosp=kdbh.kdbhavsp
sb2 kdbhtosp @136 7946
BBED> sum dba 4,547 apply
Check value for File 4, Block 547:
current = 0xd450, required = 0xd450
BBED> verify
DBVERIFY - Verification starting
FILE = /mnt/ramdisk/book/users01.dbf
BLOCK = 547
--//OK,现在正常了。
5.检查恢复情况：
SCOTT@book> alter system flush buffer_cache ;
System altered.
SCOTT@book> select * from deptx;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
总结：
1.修改flag=0x2c
2.清除fsc=0 .
3.修改tosp=avsp .
4.更正以前的错误：dtl - used - fsc = avsp
