最近一段时间学习bbed,今天试验使用它恢复删除数据.接着前面的练习:
http://space.itpub.net/267265/viewspace-745846
SQL> select dbms_rowid.rowid_relative_fno(rowid) rfile#,dbms_rowid.ROWID_BLOCK_NUMBER(rowid) block#,dbms_rowid.ROWID_ROW_NUMBER(rowid) row#,dept1.* from dept1;
RFILE# BLOCK# ROW# DEPTNO DNAME LOC
---------- ---------- ---------- ---------- -------------- -------------
8 131 0 50 TEST TEST
8 131 1 10 ACCOUNTING NEW YORK
8 131 2 20 RESEARCH DALLAS
8 131 3 30 SALES CHICAGO
8 131 4 40 OPERATIONS BOSTON
SQL> delete from dept1 where deptno=50;
1 row deleted.
SQL> commit ;
Commit complete.
接着关闭数据库.要恢复这条记录方法很多,bbed并不是最好,只不过想通过它学习bbed的命令.
1.配置并启动bbed:
BBED> set dba 8,131
DBA 0x02000083 (33554563 8,131)
BBED> map /v
File: /u01/app/oracle11g/oradata/test/test01.dbf (8)
Block: 131 Dba:0x02000083
------------------------------------------------------------
KTB Data Block (Table/Cluster)
struct kcbh, 20 bytes @0
ub1 type_kcbh @0
ub1 frmt_kcbh @1
ub1 spare1_kcbh @2
ub1 spare2_kcbh @3
ub4 rdba_kcbh @4
ub4 bas_kcbh @8
ub2 wrp_kcbh @12
ub1 seq_kcbh @14
ub1 flg_kcbh @15
ub2 chkval_kcbh @16
ub2 spare3_kcbh @18
struct ktbbh, 96 bytes @20
ub1 ktbbhtyp @20
union ktbbhsid, 4 bytes @24
struct ktbbhcsc, 8 bytes @28
sb2 ktbbhict @36
ub1 ktbbhflg @38
ub1 ktbbhfsl @39
ub4 ktbbhfnx @40
struct ktbbhitl[3], 72 bytes @44
struct kdbh, 14 bytes @124
ub1 kdbhflag @124
sb1 kdbhntab @125
sb2 kdbhnrow @126
sb2 kdbhfrre @128
sb2 kdbhfsbo @130
sb2 kdbhfseo @132
sb2 kdbhavsp @134
sb2 kdbhtosp @136
struct kdbt[1], 4 bytes @138
sb2 kdbtoffs @138
sb2 kdbtnrow @140
sb2 kdbr[5] @142
ub1 freespace[7928] @152
ub1 rowdata[108] @8080
ub4 tailchk @8188
--使用map命令可以完整的显示块结构.如果要理解这些可以看看>的文档.
--kbdr对应的就是row directory.
BBED> p *kdbr[1]
rowdata[66]
-----------
ub1 rowdata[66] @8146 0x2c
BBED> x /2rncc
rowdata[66] @8146
-----------
flag@8146: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8147: 0x00
cols@8148: 3
col 0[2] @8149: 10
col 1[10] @8152: ACCOUNTING
col 2[8] @8163: NEW YORK
rowdata[92] @8172
-----------
flag@8172: 0x3c (KDRHFL, KDRHFF, KDRHFD, KDRHFH)
lock@8173: 0x02
cols@8174: 0
--这里的*可以理解C语言的指针.意思应该差不多了.
--x命令中的/2表示显示2个,注意oracle数据插入从块底插入的,而这里的显示从上到下,相反的.
--可以发现*kdbr[0]对应的flag=0x3c.而正常的记录显示是0x2c.也就是差 KDRHFD.
--也就是修改8172处 3c变成 2c.
BBED> modify /x 2c offset 8172
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /u01/app/oracle11g/oradata/test/test01.dbf (8)
Block: 131 Offsets: 8172 to 8191 Dba:0x02000083
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2c020302 c1330454 45535404 54455354 0206b2e3
BBED> p *kdbr[1]
rowdata[66]
-----------
ub1 rowdata[66] @8146 0x2c
BBED> x /2rncc
rowdata[66] @8146
-----------
flag@8146: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8147: 0x00
cols@8148: 3
col 0[2] @8149: 10
col 1[10] @8152: ACCOUNTING
col 2[8] @8163: NEW YORK
rowdata[92] @8172
-----------
flag@8172: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8173: 0x02
cols@8174: 3
col 0[2] @8175: 50
col 1[4] @8178: TEST
col 2[4] @8183: TEST
--OK,里面的信息显示正常.再更新检查和就可以了.
BBED> sum
Check value for File 8, Block 131:
current = 0x09e4, required = 0x09f4
BBED> sum apply
Check value for File 8, Block 131:
current = 0x09f4, required = 0x09f4
2.重新启动数据库,检查:
SQL> select rowid ,dbms_rowid.rowid_relative_fno(rowid) rfile#,dbms_rowid.ROWID_BLOCK_NUMBER(rowid) block#,dbms_rowid.ROWID_ROW_NUMBER(rowid) row#,dept1.* from scott.dept1;
ROWID RFILE# BLOCK# ROW# DEPTNO DNAME LOC
------------------ ---------- ---------- ---------- ---------- -------------- -------------
AAAcC1AAIAAAACDAAA 8 131 0 50 TEST TEST
AAAcC1AAIAAAACDAAB 8 131 1 10 ACCOUNTING NEW YORK
AAAcC1AAIAAAACDAAC 8 131 2 20 RESEARCH DALLAS
AAAcC1AAIAAAACDAAD 8 131 3 30 SALES CHICAGO
AAAcC1AAIAAAACDAAE 8 131 4 40 OPERATIONS BOSTON
--删除的数据恢复了.关闭数据库.再进入bbed看看.
3.使用bbed的检查命令:
BBED> set dba 8,131
DBA 0x02000083 (33554563 8,131)
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/app/oracle11g/oradata/test/test01.dbf
BLOCK = 131
Block Checking: DBA = 33554563, Block Type = KTB-managed data block
data header at 0x2a97fb327c
kdbchk: the amount of space used is not equal to block size
used=136 fsc=14 avsp=7928 dtl=8064
Block 131 failed with check code 6110
DBVERIFY - Verification complete
Total Blocks Examined : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing (Data) : 1
Total Blocks Processed (Index): 0
Total Blocks Failing (Index): 0
Total Blocks Empty : 0
Total Blocks Marked Corrupt : 0
Total Blocks Influx : 0
Message 531 not found; product=RDBMS; facility=BBED
--使用dbv检查也是一样.我是关闭数据库进行这些操作,看来遗漏了什么.
$ dbv file=test01.dbf
DBVERIFY: Release 11.2.0.1.0 - Production on Tue Oct 9 08:39:53 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = /u01/app/oracle11g/oradata/test/test01.dbf
Block Checking: DBA = 33554563, Block Type = KTB-managed data block
data header at 0x2a980bb07c
kdbchk: the amount of space used is not equal to block size
used=136 fsc=14 avsp=7928 dtl=8064
Page 131 failed with check code 6110
DBVERIFY - Verification complete
Total Pages Examined : 8192
Total Pages Processed (Data) : 1566
Total Pages Failing (Data) : 1
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 161
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 6465
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 3010061234 (0.3010061234)
参考这篇文档:
http://space.itpub.net/22664653/viewspace-704382
BBED> p kdbh
struct kdbh, 14 bytes @124
ub1 kdbhflag @124 0x00 (NONE)
sb1 kdbhntab @125 1
sb2 kdbhnrow @126 5
sb2 kdbhfrre @128 -1
sb2 kdbhfsbo @130 28
sb2 kdbhfseo @132 7956
sb2 kdbhavsp @134 7928
sb2 kdbhtosp @136 7944
kdbhavsp => 表示Avaiable space in the block.
kdbhtosp => 表示Toatl avaiable space when all TXs commit;
--估计修改kdbhtosp=7928 ,这样就一致了.因为删除记录时,这些空间被回收,从前面的显示也可以看出.
BBED> dump /v offset 134 count 4
File: /u01/app/oracle11g/oradata/test/test01.dbf (8)
Block: 131 Offsets: 134 to 137 Dba:0x02000083
---------------------------------------------------------------------------------------------------------------------------------------------------------------
f81e081f l ....
BBED> modify /x f81e offset 136
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /u01/app/oracle11g/oradata/test/test01.dbf (8)
Block: 131 Offsets: 136 to 139 Dba:0x02000083
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
f81e0000
BBED> dump /v offset 134 count 4
File: /u01/app/oracle11g/oradata/test/test01.dbf (8)
Block: 131 Offsets: 134 to 137 Dba:0x02000083
---------------------------------------------------------------------------------------------------------------------------------------------------------------
f81ef81e l ....
BBED> p kdbh
struct kdbh, 14 bytes @124
ub1 kdbhflag @124 0x00 (NONE)
sb1 kdbhntab @125 1
sb2 kdbhnrow @126 5
sb2 kdbhfrre @128 -1
sb2 kdbhfsbo @130 28
sb2 kdbhfseo @132 7956
sb2 kdbhavsp @134 7928
sb2 kdbhtosp @136 7928
--OK ,修改正确了.
BBED> sum
Check value for File 8, Block 131:
current = 0x09f4, required = 0x0804
BBED> sum apply
Check value for File 8, Block 131:
current = 0x0804, required = 0x0804
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/app/oracle11g/oradata/test/test01.dbf
BLOCK = 131
Block Checking: DBA = 33554563, Block Type = KTB-managed data block
data header at 0x2a97fb327c
kdbchk: the amount of space used is not equal to block size
used=136 fsc=14 avsp=7928 dtl=8064
Block 131 failed with check code 6110
DBVERIFY - Verification complete
Total Blocks Examined : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing (Data) : 1
Total Blocks Processed (Index): 0
Total Blocks Failing (Index): 0
Total Blocks Empty : 0
Total Blocks Marked Corrupt : 0
Total Blocks Influx : 0
Message 531 not found; product=RDBMS; facility=BBED
--发现问题依旧.看来按照文档需要修改itl.
BBED> p ktbbhitl
struct ktbbhitl[0], 24 bytes @44
struct ktbitxid, 8 bytes @44
ub2 kxidusn @44 0xffff
ub2 kxidslt @46 0x0000
ub4 kxidsqn @48 0x00000000
struct ktbituba, 8 bytes @52
ub4 kubadba @52 0x00000000
ub2 kubaseq @56 0x0000
ub1 kubarec @58 0x00
ub2 ktbitflg @60 0x8000 (KTBFCOM)
union _ktbitun, 2 bytes @62
sb2 _ktbitfsc @62 0
ub2 _ktbitwrp @62 0x0000
ub4 ktbitbas @64 0xb369e382
struct ktbbhitl[1], 24 bytes @68
struct ktbitxid, 8 bytes @68
ub2 kxidusn @68 0x0008
ub2 kxidslt @70 0x0006
ub4 kxidsqn @72 0x00002303
struct ktbituba, 8 bytes @76
ub4 kubadba @76 0x00c03b6d
ub2 kubaseq @80 0x116e
ub1 kubarec @82 0x07
ub2 ktbitflg @84 0x2001 (KTBFUPB)
union _ktbitun, 2 bytes @86
sb2 _ktbitfsc @86 14
ub2 _ktbitwrp @86 0x000e
ub4 ktbitbas @88 0xb369e3b2
struct ktbbhitl[2], 24 bytes @92
struct ktbitxid, 8 bytes @92
ub2 kxidusn @92 0x0000
ub2 kxidslt @94 0x0000
ub4 kxidsqn @96 0x00000000
struct ktbituba, 8 bytes @100
ub4 kubadba @100 0x00000000
ub2 kubaseq @104 0x0000
ub1 kubarec @106 0x00
ub2 ktbitflg @108 0x0000 (NONE)
union _ktbitun, 2 bytes @110
sb2 _ktbitfsc @110 0
ub2 _ktbitwrp @110 0x0000
ub4 ktbitbas @112 0x00000000
--sb2 _ktbitfsc @86 14
--也就是修改offset=86 0.
BBED> modify /x 00 offset 86
File: /u01/app/oracle11g/oradata/test/test01.dbf (8)
Block: 131 Offsets: 86 to 89 Dba:0x02000083
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0000b2e3
BBED> p ktbbhitl
struct ktbbhitl[0], 24 bytes @44
struct ktbitxid, 8 bytes @44
ub2 kxidusn @44 0xffff
ub2 kxidslt @46 0x0000
ub4 kxidsqn @48 0x00000000
struct ktbituba, 8 bytes @52
ub4 kubadba @52 0x00000000
ub2 kubaseq @56 0x0000
ub1 kubarec @58 0x00
ub2 ktbitflg @60 0x8000 (KTBFCOM)
union _ktbitun, 2 bytes @62
sb2 _ktbitfsc @62 0
ub2 _ktbitwrp @62 0x0000
ub4 ktbitbas @64 0xb369e382
struct ktbbhitl[1], 24 bytes @68
struct ktbitxid, 8 bytes @68
ub2 kxidusn @68 0x0008
ub2 kxidslt @70 0x0006
ub4 kxidsqn @72 0x00002303
struct ktbituba, 8 bytes @76
ub4 kubadba @76 0x00c03b6d
ub2 kubaseq @80 0x116e
ub1 kubarec @82 0x07
ub2 ktbitflg @84 0x2001 (KTBFUPB)
union _ktbitun, 2 bytes @86
sb2 _ktbitfsc @86 0
ub2 _ktbitwrp @86 0x0000
ub4 ktbitbas @88 0xb369e3b2
struct ktbbhitl[2], 24 bytes @92
struct ktbitxid, 8 bytes @92
ub2 kxidusn @92 0x0000
ub2 kxidslt @94 0x0000
ub4 kxidsqn @96 0x00000000
struct ktbituba, 8 bytes @100
ub4 kubadba @100 0x00000000
ub2 kubaseq @104 0x0000
ub1 kubarec @106 0x00
ub2 ktbitflg @108 0x0000 (NONE)
union _ktbitun, 2 bytes @110
sb2 _ktbitfsc @110 0
ub2 _ktbitwrp @110 0x0000
ub4 ktbitbas @112 0x00000000
--再计算检查和.
BBED> sum
Check value for File 8, Block 131:
current = 0x0804, required = 0x080a
BBED> sum apply
Check value for File 8, Block 131:
current = 0x080a, required = 0x080a
BBED> ver
Verification verify
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/app/oracle11g/oradata/test/test01.dbf
BLOCK = 131
DBVERIFY - Verification complete
Total Blocks Examined : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing (Index): 0
Total Blocks Empty : 0
Total Blocks Marked Corrupt : 0
Total Blocks Influx : 0
Message 531 not found; product=RDBMS; facility=BBED
$ dbv file=test01.dbf
DBVERIFY: Release 11.2.0.1.0 - Production on Tue Oct 9 08:55:23 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = /u01/app/oracle11g/oradata/test/test01.dbf
DBVERIFY - Verification complete
Total Pages Examined : 8192
Total Pages Processed (Data) : 1566
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 161
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 6465
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 3010061234 (0.3010061234)--dbv检查也正常.
--以后修改块,要注意使用verify检查看看.