[20160526]bbed修复删除记录.txt-白红宇

[20160526]bbed修复删除记录.txt

--以前也做过,链接:

--自己当时完全是依葫芦画瓢,许多东西理解不深刻,重新做一次.

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的内容看参考链接:

#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槽的信息.