[20160526]bbed修复删除记录.txt
--以前也做过,链接:
http://blog.itpub.net/267265/viewspace-745944/
--自己当时完全是依葫芦画瓢,许多东西理解不深刻,重新做一次.
1.环境:
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
create table t (id int,name varchar2(100));
insert into t values(1,'AAAAA');
insert into t values(2,'BBBBB');
insert into t values(3,'CCCCC');
insert into t values(4,'DDDDD');
insert into t values(5,'EEEEE');
commit;
alter system checkpoint;
SCOTT@book> select ora_rowscn,rowid,t.* from t;
ORA_ROWSCN ROWID ID NAME
------------ ------------------ ------------ ---------
13237855931 AAAW7TAAEAAAT/7AAA 1 AAAAA
13237855931 AAAW7TAAEAAAT/7AAB 2 BBBBB
13237855931 AAAW7TAAEAAAT/7AAC 3 CCCCC
13237855931 AAAW7TAAEAAAT/7AAD 4 DDDDD
13237855931 AAAW7TAAEAAAT/7AAE 5 EEEEE
SCOTT@book> @ &r/rowid AAAW7TAAEAAAT/7AAA
OBJECT FILE BLOCK ROW DBA TEXT
----------- ------------ ------------ ------------ -------------------- ----------------------------------------
93907 4 81915 0 4,81915 alter system dump datafile 4 block 81915
2.删除id=1,3,5:
SCOTT@book> delete from t where id in (1,3,5);
3 rows updated.
SCOTT@book> commit ;
Commit complete.
SCOTT@book> select ora_rowscn,rowid,t.* from t;
ORA_ROWSCN ROWID ID NAME
------------ ------------------ ------------ --------------------
13237855967 AAAW7TAAEAAAT/7AAB 2 BBBBB
13237855967 AAAW7TAAEAAAT/7AAD 4 DDDDD
SCOTT@book> alter system checkpoint;
System altered.
SCOTT@book> alter system flush buffer_cache;
System altered.
--OK现在使用bbed是否可以恢复原样.
3.使用bbed恢复:
BBED> set dba 4,81915
DBA 0x01013ffb (16859131 4,81915)
BBED> p kdbr
sb2 kdbr[0] @118 8076
sb2 kdbr[1] @120 8064
sb2 kdbr[2] @122 8052
sb2 kdbr[3] @124 8040
sb2 kdbr[4] @126 8028
--//可以发现行目录指向的偏移还在,一般只要没有覆盖,恢复相对容易.
BBED> p *kdbr[4]
rowdata[0]
----------
ub1 rowdata[0] @8128 0x3c
BBED> p *kdbr[3]
rowdata[12]
-----------
ub1 rowdata[12] @8140 0x2c
BBED> x /rnc
rowdata[12] @8140
-----------
flag@8140: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8141: 0x00
cols@8142: 2
col 0[2] @8143: 4
col 1[5] @8146: DDDDD
--可以对比发现delete的flag=0x3c,而正常的记录是0x2c,关于flag的内容看参考链接:
http://blog.itpub.net/267265/viewspace-1753924/
http://blog.itpub.net/267265/viewspace-1753933/
#define KDRHFK 0x80 Cluster Key
#define KDRHFC 0x40 Clustered table member
#define KDRHFH 0x20 Head piece of row
#define KDRHFD 0x10 Deleted row
#define KDRHFF 0x08 First data piece
#define KDRHFL 0x04 Last data piece
#define KDRHFP 0x02 First column continues from Previous piece
#define KDRHFN 0x01 Last column continues in Next piece
modify /x 0x2c offset 8176
modify /x 0x2c offset 8152
modify /x 0x2c offset 8128
--注意偏移量要加100.
BBED> x /5rnc rowdata
rowdata[0] @8128
----------
flag@8128: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8129: 0x02
cols@8130: 2
col 0[2] @8131: 5
col 1[5] @8134: EEEEE
rowdata[12] @8140
-----------
flag@8140: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8141: 0x00
cols@8142: 2
col 0[2] @8143: 4
col 1[5] @8146: DDDDD
rowdata[24] @8152
-----------
flag@8152: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8153: 0x02
cols@8154: 2
col 0[2] @8155: 3
col 1[5] @8158: CCCCC
rowdata[36] @8164
-----------
flag@8164: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8165: 0x00
cols@8166: 2
col 0[2] @8167: 2
col 1[5] @8170: BBBBB
rowdata[48] @8176
-----------
flag@8176: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8177: 0x02
cols@8178: 2
col 0[2] @8179: 1
col 1[5] @8182: AAAAA
--OK,已经可以看到全部记录.
BBED> sum apply
Check value for File 4, Block 81915:
current = 0x214f, required = 0x214f
BBED> verify
DBVERIFY - Verification starting
FILE = /mnt/ramdisk/book/users01.dbf
BLOCK = 81915
Block Checking: DBA = 16859131, Block Type = KTB-managed data block
data header at 0x7ff574ed6264
kdbchk: the amount of space used is not equal to block size
used=88 fsc=30 avsp=8000 dtl=8088
Block 81915 failed with check code 6110
BBED> p kdbh
struct kdbh, 14 bytes @100
ub1 kdbhflag @100 0x00 (NONE)
sb1 kdbhntab @101 1
sb2 kdbhnrow @102 5
sb2 kdbhfrre @104 -1
sb2 kdbhfsbo @106 28
sb2 kdbhfseo @108 8028
sb2 kdbhavsp @110 8000
sb2 kdbhtosp @112 8036
--注意看ITL槽.
BBED> p ktbbh.ktbbhitl[1]
struct ktbbhitl[1], 24 bytes @68
struct ktbitxid, 8 bytes @68
ub2 kxidusn @68 0x000a
ub2 kxidslt @70 0x0012
ub4 kxidsqn @72 0x00009c07
struct ktbituba, 8 bytes @76
ub4 kubadba @76 0x00c0043f
ub2 kubaseq @80 0x1e13
ub1 kubarec @82 0x30
ub2 ktbitflg @84 0x2003 (KTBFUPB)
union _ktbitun, 2 bytes @86
sb2 _ktbitfsc @86 30
ub2 _ktbitwrp @86 0x001e
ub4 ktbitbas @88 0x1509a6df
--我估计在快速提交时如果数据块空间回收,信息会记录在ktbbh.ktbbhitl[1]._ktbitun._ktbitfsc里面,_ktbitun是一个union,既可以记
--录_ktbitfsc也可以记录_ktbitwrp. 30字节,很奇怪kdbhtosp记录的是36.我估计delete 后行目录保留不计算这部分长度.
-- dtl - used = 8088-88 = 8000 .
-- 纠正这个问题,比较正规的做法是:
BBED> assign kdbh.kdbhtosp = kdbh.kdbhavsp
sb2 kdbhtosp @112 8000
BBED> assign ktbbh.ktbbhitl[1]._ktbitun._ktbitfsc=0
sb2 _ktbitfsc @86 0
BBED> sum apply
Check value for File 4, Block 81915:
current = 0x2175, required = 0x2175
BBED> verify
DBVERIFY - Verification starting
FILE = /mnt/ramdisk/book/users01.dbf
BLOCK = 81915
--ok修复完成.
--检查结果:
SCOTT@book> select ora_rowscn,rowid,t.* from t;
ORA_ROWSCN ROWID ID NAME
------------ ------------------ ------------ --------------------
13237855967 AAAW7TAAEAAAT/7AAA 1 AAAAA
13237855967 AAAW7TAAEAAAT/7AAB 2 BBBBB
13237855967 AAAW7TAAEAAAT/7AAC 3 CCCCC
13237855967 AAAW7TAAEAAAT/7AAD 4 DDDDD
13237855967 AAAW7TAAEAAAT/7AAE 5 EEEEE
总结:
1.删除后修复相对简单,就是讲flag标识修改会话,我的测试没有考虑其他情况,仅仅简单将0x3c修改为0x2c.如果出现行链接等情况相对复杂.
2.修复kdbchk: the amount of space used is not equal to block size used=88 fsc=30 avsp=8000 dtl=8088错误.
常用方法如下:
assign kdbh.kdbhtosp = kdbh.kdbhavsp
assign ktbbh.ktbbhitl[1]._ktbitun._ktbitfsc=0
--//注意要清除对应itl槽的信息.
