[20170412]bbed恢复修改记录(不等长).txt
--//昨天做了删除记录恢复测试，今天测试update记录如何还原通过bbed。
--//实际上类似的测试我自己做了好几次，都是按照别人的帖子依葫芦画瓢，没有很好理解一些内在的东西。
http://blog.itpub.net/267265/viewspace-2137000/
http://blog.itpub.net/267265/viewspace-2107060/
http://blog.itpub.net/267265/viewspace-1193074/
http://blog.itpub.net/267265/viewspace-1192369/
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
--// drop table t purge ;
SCOTT@book> create table t as select * from dept ;
Table created.
SCOTT@book> @ &r/qq t 1
ROWID OBJECT FILE BLOCK ROW ROWID_DBA DEPTNO DNAME LOC
------------------ ---------- ---------- ---------- ---------- -------------------- ---------- -------------- -------------
AAAWGrAAEAAAAIjAAA 90539 4 547 0 0x1000223 10 ACCOUNTING NEW YORK
SCOTT@book> update t set dname=lpad('a',1,'a');
4 rows updated.
--//注:修改后长度缩短，主要这样修改增加一点点难度，对应fsc > 0
SCOTT@book> commit ;
Commit complete.
SCOTT@book> alter system checkpoint ;
System altered.
2.修改前说明:
SCOTT@book> select sum(length(dname)) from dept ;
SUM(LENGTH(DNAME))
------------------
33
SCOTT@book> select sum(length(dname)) from t;
SUM(LENGTH(DNAME))
------------------
4
SCOTT@book> alter tablespace users offline ;
Tablespace altered.
--//修复的要点,首先要不等长,如果等长oracle是原地替换这个是无法修复,原来的信息连影子都看不到.除非有归档或者备份.
--//另外块内有足够的空间保证修改前信息还在,如果不存在也是无法修复.
--//1.首先修改行目录指向正确的偏移.
--//2.修改相应记录的lock等于原来的itl槽.
--//3.使用verify检查根据提示,dtl-used-fsc= avsp
3.首先修改行目录指向正确的偏移.
--//oralce的数据是从块底部向上插入数据的,也就是第1条记录靠近块底部.也就是修改前的记录在*kdbr[0]偏移的后面。
BBED> set dba 4,547
DBA 0x01000223 (16777763 4,547)
BBED> p dba 4,547 kdbr
sb2 kdbr[0] @142 7955
sb2 kdbr[1] @144 7940
sb2 kdbr[2] @146 7924
sb2 kdbr[3] @148 7909
--//记录0在相对偏移7955,比后面的偏移大.
BBED> x /rncc *kdbr[0]
rowdata[46] @8079
-----------
flag@8079: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8080: 0x02
cols@8081: 3
col 0[2] @8082: 10
col 1[1] @8085: a
col 2[8] @8087: NEW YORK
--//注意的地方显示的绝对偏移8079, 而kbdr记录的是相对偏移.8079-7955=124.这样找到记录绝对偏移-124.
--//这个偏移量由当前 kdbh的偏移位置来决定.
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 7909
sb2 kdbhavsp @134 7946
sb2 kdbhtosp @136 7975
BBED> dump /v offset 8079 count 800
File: /mnt/ramdisk/book/users01.dbf (4)
Block: 547 Offsets: 8079 to 8191 Dba:0x01000223
-----------------------------------------------------------------------------------------------------------
2c020302 c10b0161 084e4557 20594f52 4b2c0003 02c1290a 4f504552 4154494f l ,......a.NEW YORK,....).OPERATIO
4e530642 4f53544f 4e2c0003 02c11f05 53414c45 53074348 49434147 4f2c0003 l NS.BOSTON,......SALES.CHICAGO,..
02c11508 52455345 41524348 0644414c 4c41532c 000302c1 0b0a4143 434f554e l ....RESEARCH.DALLAS,......ACCOUN
54494e47 084e4557 20594f52 4b0506f7 8f l TING.NEW YORK....
<32 bytes per line>
--//可以通过记录长度计算:
BBED> x /rncc *kdbr[0]
rowdata[46] @8079
-----------
flag@8079: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8080: 0x02
cols@8081: 3
col 0[2] @8082: 10
col 1[1] @8085: a
col 2[8] @8087: NEW YORK
--//这条记录占(每个记录长度指示器占1字节)3+2+1+8+1+1+1=17,8079+17=8096,这样偏移量8096就是update前的记录.
BBED> x /rncc offset 8096
rowdata[115] @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
--//继续推道就可以知道下一条update前的记录偏移量.
--//3+2+10+6+1+1+1=24 ,8096+24=8120
BBED> x /rncc offset 8120
rowdata[139] @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
3+2+5+7+3=20
3+2+8+6+3=22
--//最后得出偏移量是 8096,8120,8140,8162.
--//这样每个偏移减去kdbh的偏移量(124)就是相对偏移
8096-124=7972
8120-124=7996
8140-124=8016
8162-124=8038
--//注还可以简单使用find命令直接查询0x2c找到flag标识。也就是偏移开始，减少计算量。
BBED> help find
FIND[/x|d|u|o|c] numeric/character string [ TOP | CURR ]
--//奇怪bbed的find不能指定offset。
BBED> set count 10
COUNT 10
BBED> set offset 8079
OFFSET 8079
BBED> find /x 2c curr
File: /mnt/ramdisk/book/users01.dbf (4)
Block: 547 Offsets: 8079 to 8088 Dba:0x01000223
--------------------------------------------------------------------
2c020302 c10b0161 084e
<64 bytes per line>
--//注这条指向*kdbr[0],不是要恢复的。另外不要设置count太大，可能出现跳过的情况。
BBED> f
File: /mnt/ramdisk/book/users01.dbf (4)
Block: 547 Offsets: 8096 to 8105 Dba:0x01000223
--------------------------------------------------------------------
2c000302 c1290a4f 5045
<64 bytes per line>
BBED> f
File: /mnt/ramdisk/book/users01.dbf (4)
Block: 547 Offsets: 8120 to 8129 Dba:0x01000223
--------------------------------------------------------------------
2c000302 c11f0553 414c
<64 bytes per line>
BBED> f
File: /mnt/ramdisk/book/users01.dbf (4)
Block: 547 Offsets: 8140 to 8149 Dba:0x01000223
--------------------------------------------------------------------
2c000302 c1150852 4553
<64 bytes per line>
BBED> f
File: /mnt/ramdisk/book/users01.dbf (4)
Block: 547 Offsets: 8162 to 8171 Dba:0x01000223
--------------------------------------------------------------------
2c000302 c10b0a41 4343
<64 bytes per line>
--//可以发现偏移与前面的测试一致。8096,8120,8140,8162.写入的是相对偏移要减去124(注kdbh的偏移量)
--//另外注意0x2c对应的ascii是逗号，要特别注意，可能并不是记录的开头。
--//修改kdbr的内容如下(记录0的偏移量最大,估计不按这个顺序也可以,我没测试),执行第1行要单独执行(不要copy and paste),第一次执行要确认:
assign kdbr[3] = 7972
assign kdbr[2] = 7996
assign kdbr[1] = 8016
assign kdbr[0] = 8038
--//检查显示是否正确:
BBED> x /4rncc *kdbr[3]
rowdata[181] @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。
4.修改相应记录的lock等于原来的itl槽.
BBED> sum apply
Check value for File 4, Block 547:
current = 0xea74, required = 0xea74
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 0x7f40a9a8a27c
kdbchk: xaction header lock count mismatch
trans=2 ilk=4 nlo=0
Block 547 failed with check code 6108
--//要修改lock的标识指向原来的位置。
BBED> x /4rncc *kdbr[3]
rowdata[115] @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[139] @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[159] @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[181] @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
--//修改lock地址为原来的值,前面是值是0x02.执行如下:
modify /x 0x02 8097
modify /x 0x02 8121
modify /x 0x02 8141
modify /x 0x02 8163
--//然后在执行如下:x /4rncc *kdbr[3] ,确认修改是否正确.
BBED> sum apply
Check value for File 4, Block 547:
current = 0xea74, required = 0xea74
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 0x1af9a7c
kdbchk: the amount of space used is not equal to block size
used=118 fsc=29 avsp=7946 dtl=8064
Block 547 failed with check code 6110
--//以前一般我做到这里就结束了,因为我以前测试这样已经正常显示信息了.注意这里fsc不再是0.
--//实际上修改时总长度减少时oracle会利用itl记录这个减少量.而总长度增加不记录.前面我计算
--//sum(length(dname)) 从33 到4 ，这样相差29，这样fsc=29.
BBED> p ktbbh.ktbbhitl[1]
struct ktbbhitl[1], 24 bytes @68
struct ktbitxid, 8 bytes @68
ub2 kxidusn @68 0x0002
ub2 kxidslt @70 0x001b
ub4 kxidsqn @72 0x000006b0
struct ktbituba, 8 bytes @76
ub4 kubadba @76 0x00c0062d
ub2 kubaseq @80 0x0226
ub1 kubarec @82 0x21
ub2 ktbitflg @84 0x2004 (KTBFUPB)
union _ktbitun, 2 bytes @86
sb2 _ktbitfsc @86 29
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ub2 _ktbitwrp @86 0x001d
ub4 ktbitbas @88 0x175e2076
SCOTT@book> select rowid,t.* from t;
ROWID DEPTNO DNAME LOC
------------------ ---------- -------------- -------------
AAAWEJAAEAAAAIjAAA 10 ACCOUNTING NEW YORK
AAAWEJAAEAAAAIjAAB 20 RESEARCH DALLAS
AAAWEJAAEAAAAIjAAC 30 SALES CHICAGO
AAAWEJAAEAAAAIjAAD 40 OPERATIONS BOSTON
--//原来dname的长度相加减去修改后的长度 10+8+5+10-4=29,正好相符.
5.使用verify检查根据提示,dtl-used-fsc= avsp
--//根据上面提示 used=118 fsc=29 avsp=7946 dtl=8064
--//正常 dtl-used-fsc = avsp,
--//注意used = 118 当前使用的。
--//如果修改fsc=0后。
8064-118-0=7946
BBED> p ktbbh.ktbbhitl[1]
struct ktbbhitl[1], 24 bytes @68
struct ktbitxid, 8 bytes @68
ub2 kxidusn @68 0x0009
ub2 kxidslt @70 0x001a
ub4 kxidsqn @72 0x00000aec
struct ktbituba, 8 bytes @76
ub4 kubadba @76 0x00c06d00
ub2 kubaseq @80 0x03f5
ub1 kubarec @82 0x16
ub2 ktbitflg @84 0x2004 (KTBFUPB)
union _ktbitun, 2 bytes @86
sb2 _ktbitfsc @86 29
ub2 _ktbitwrp @86 0x001d
ub4 ktbitbas @88 0x17658ff7
BBED> assign ktbbh.ktbbhitl[1]._ktbitun._ktbitfsc=0
sb2 _ktbitfsc @86 0
BBED> sum apply
Check value for File 4, Block 547:
current = 0xea69, required = 0xea69
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 0x1afaa7c
kdbchk: space available on commit is incorrect
tosp=7975 fsc=0 stb=0 avsp=7946
Block 547 failed with check code 6111
--//原来avsp+fsc+stb=tops,7946+29+0=7975,而现在fsc=0.要修改kdbh.kdbhtosp=kdbh.kdbhavsp。
--//stb是什么不懂？？？
BBED> assign kdbh.kdbhtosp=kdbh.kdbhavsp
sb2 kdbhtosp @136 7946
BBED> sum apply
Check value for File 4, Block 547:
current = 0xea44, required = 0xea44
BBED> verify
DBVERIFY - Verification starting
FILE = /mnt/ramdisk/book/users01.dbf
BLOCK = 547
--OK,这样就修复完成了.online表空间,显示看看:
SCOTT@book> alter tablespace users online ;
Tablespace altered.
SCOTT@book> select rowid,t.* from t;
ROWID DEPTNO DNAME LOC
------------------ ---------- -------------- -------------
AAAWGrAAEAAAAIjAAA 10 ACCOUNTING NEW YORK
AAAWGrAAEAAAAIjAAB 20 RESEARCH DALLAS
AAAWGrAAEAAAAIjAAC 30 SALES CHICAGO
AAAWGrAAEAAAAIjAAD 40 OPERATIONS BOSTON
总结:
--//1.首先修改行目录指向正确的偏移.
--//2.修改相应记录的lock等于原来的itl槽 等于itl槽+1.也就是lock =0x2,实际上itl槽=1
--//3.使用verify检查根据提示,修改相应itl槽的_ktbitun._ktbitfsc=0,然后根据
--//4.dtl-used－fsc 算出 kdbh.kdbhavsp.
--//5.先执行sum apply，再执行veryify，注意看提示确定修改那些参数。