以前在网上看到过使用BBED来恢复删除的数据的例子，由于当时对数据块的格式不太理解。
一直看不懂别人写的例子，为什么要改数据块的这个字节，而不是改另一个字节，为什么要改为XXX而不是改为YYY等之类的问题。
前段时间研究过一段时间的数据块格式，因此也打算自己试验一下，顺便记录一下过程。
需要说明的是：一条记录如果被删除的话，实际ORACLE在数据块中并没有立马把这条记录清除，而是在行头中将该条记录标记为删除。
在操作之前，我们需要了解一点基础知识。
下面的信息来自Maclean Liu大虾的网站:
Oracle中表的数据块由块头、事务槽、行字典、行数据等多种结构组成。 行数据(rowdata)实际是由许多row piece 行片组成的，每一条row piece的头部都有flag、locks、cols(cc)三个标志位。
其中flag标记了该row piece的类型，该flag位占用一个字节，其不同的bit位代表不同的含义，见下表：
ROW_CLUSTER_KEY = 0x80; KDRHFK
ROW_CTABLE_NUMBER = 0x40; KDRHFC
ROW_HEAD_PIECE = 0x20; KDRHFH
ROW_DELETED_ROW = 0x10; KDRHFD
ROW_FIRST_PIECE = 0x08; KDRHFF
ROW_LAST_PIECE = 0x04; KDRHFL
ROW_FROM_PREVIOUS = 0x02; KDRHFP
ROW_CONTINUE_NEXT = 0x01; KDRHFN
一般来说最普通的一条row piece是普通堆表(heap table)的未被删除的且无行迁移/链接的，其flag位应为
普通row的flag一般为
Single Row = ROW_HEAD_PIECE + ROW_FIRST_PIECE + ROW_LAST_PIECE= 0x20 + 0x08 + 0x04= 0x2c
===================================================================================
cluster key的flag一般为
Cluster Key =
ROW_CLUSTER_KEY + ROW_HEAD_PIECE + ROW_FIRST_PIECE + ROW_LAST_PIECE=
KDRHFL, KDRHFF, KDRHFH, KDRHFK =0x80 + 0x2c = 0xac
BBED> x /rn
rowdata[68] @8166
-----------
: 0xac (KDRHFL, KDRHFF, KDRHFH, KDRHFK)
: 0x00
: 1
: 1
: 1
col 0[2] @8185: 10
===================================================================================
Cluster Row =
ROW_CTABLE_NUMBER + ROW_HEAD_PIECE + ROW_FIRST_PIECE + ROW_LAST_PIECE =(KDRHFL, KDRHFF, KDRHFH, KDRHFC) = 0x6c
BBED> x /rncc
rowdata[0] @8098
----------
: 0x6c (KDRHFL, KDRHFF, KDRHFH, KDRHFC)
: 0x00
: 10
col 0[2] @8102: 200
col 1[8] @8105: Jennifer
col 2[6] @8114: Whalen
col 3[7] @8121: JWHALEN
col 4[12] @8129: 515.123.4444
col 5[7] @8142: w....
col 6[7] @8150: AD_ASST
col 7[2] @8158:
col 8[0] @8161: *NULL*
col 9[3] @8162: .
从上面可以看出：
普通row的flag一般为
Single Row = ROW_HEAD_PIECE + ROW_FIRST_PIECE + ROW_LAST_PIECE= 0x20 + 0x08 + 0x04= 0x2c
如果这行记录被删除了，那么ROW_DELETED_ROW标志位会被置为1,此时
Single Row = ROW_HEAD_PIECE + ROW_DELETED_ROW+ ROW_FIRST_PIECE + ROW_LAST_PIECE= 0x20 + 0x10+ 0x08 + 0x04= 0x3c
如果要把这条记录找回来，只需要把标志位ROW_DELETED_ROW 从1改为0，也就是把0X3C改为0X2C。
我们以SCOTT的DEPT表为例：
SQL> select * from scott.dept;
DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> select dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block# from scott.dept;
FILE# BLOCK#
---------- ----------
4 16
4 16
4 16
4 16
SQL> select * from v$dbfile;
FILE# NAME
---------- ------------------------------
4 /test/orcl/orcl/users01.dbf
3 /test/orcl/orcl/sysaux01.dbf
2 /test/orcl/orcl/undotbs01.dbf
1 /test/orcl/orcl/system01.dbf
5 /test/orcl/orcl/abcd_0.dbf
6 /test/orcl/orcl/abcd_1.dbf
7 /test/orcl/orcl/abcd_2.dbf
7 rows selected.
表的数据在文件4，BLOCK 16中。
[oracle@sxffdb1 ~]$ bbed
Password:
BBED: Release 2.0.0.0.0 - Limited Production on Mon May 14 10:03:02 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> set filename '/test/orcl/orcl/users01.dbf';
FILENAME /test/orcl/orcl/users01.dbf
BBED> set block 16
BLOCK# 16
BBED> map /v
File: /test/orcl/orcl/users01.dbf (0)
Block: 16 Dba:0x00000000
------------------------------------------------------------
KTB Data Block (Table/Cluster)
struct kcbh, 20 bytes @0 --Cache Layer
ub1 type_kcbh @0 -- Block type (see Header Block Types below)
ub1 frmt_kcbh @1 --Block format 1=Oracle 7, 2=Oracle 8+
ub1 spare1_kcbh @2 --Not used
ub1 spare2_kcbh @3 --Not used
ub4 rdba_kcbh @4 --RDBA -Relative Data Block Address
ub4 bas_kcbh @8 --SCN Base
ub2 wrp_kcbh @12 --SCN Wrap
ub1 seq_kcbh @14 --Sequence number, incremented for every change made to the block at the same SCN
ub1 flg_kcbh @15 --Flag:
0x01 New Block
0x02 Delayed Logging Change advanced SCN/seq
0x04 Check value saved - block XOR‘s to zero
0x08 Temporary block
ub2 chkval_kcbh @16 --Optional block checksum (if DB_BLOCK_CHECKSUM=TRUE)
ub2 spare3_kcbh @18 --Not used
struct ktbbh, 72 bytes @20 --Transaction Layer (Transaction Fixed Header Structure)
ub1 ktbbhtyp @20 --Block type (1=DATA, 2=INDEX)
union ktbbhsid, 4 bytes @24 --DATA OBJECT ID
struct ktbbhcsc, 8 bytes @28 --SCN at last block cleanout
b2 ktbbhict @36 --Number of ITL slots
ub1 ktbbhflg @38 --0=on the freelist
ub1 ktbbhfsl @39 --ITL TX freelist slot
ub4 ktbbhfnx @40 -- DBA of next block on the freelist
struct ktbbhitl[2], 48 bytes @44 --ITL list index
struct kdbh, 14 bytes @100 --Data Header (Data Header Structure )
ub1 kdbhflag @100 --N=pctfree hit(clusters); F=do not put on freelist; K=flushable cluster keys
b1 kdbhntab @101 --Number of tables (>1 in clusters)
b2 kdbhnrow @102 --Number of rows
sb2 kdbhfrre @104 --First free row entry index; -1 = you have to add one
sb2 kdbhfsbo @106 --Freespace begin offset
sb2 kdbhfseo @108 --Freespace end offset
b2 kdbhavsp @110 --Available space in the block
b2 kdbhtosp @112 --Total available space when all TXs commit
struct kdbt[1], 4 bytes @114 -- Table Directory
b2 kdbtoffs @114
b2 kdbtnrow @116
sb2 kdbr[4] @118 --Row Directory
ub1 freespace[7970] @126 -- Free Space
ub1 rowdata[92] @8096 -- Row Data
ub4 tailchk @8188 --Tailchk
Header Block Types
|
ID |
Type |
|
01 |
Undo segment header |
|
02 |
Undo data block |
|
03 |
Save undo header |
|
04 |
Save undo data block |
|
05 |
Data segment header (temp, index, data and so on) |
|
06 |
KTB managed data block (with ITL) |
|
07 |
Temp table data block (no ITL) |
|
08 |
Sort Key |
|
09 |
Sort Run |
|
10 |
Segment free list block |
|
11 |
Data file header |
kdbr 是块中的Row Directory结构，里面包含块中行的相关信息。
BBED> p kdbr --p kdbr打印出row directory的信息。
sb2 kdbr[0] @118 8062
sb2 kdbr[1] @120 8040
sb2 kdbr[2] @122 8020
sb2 kdbr[3] @124 7996
如果需要知道N行开始的位置，可以使用print *kdbr[n-1] 即可。
BBED> p *kdbr[0]
rowdata[66]
-----------
ub1 rowdata[66] @8162 0x2c
BBED> p *kdbr[1]
rowdata[44]
-----------
ub1 rowdata[44] @8140 0x2c
BBED> p *kdbr[2]
rowdata[24]
-----------
ub1 rowdata[24] @8120 0x2c
BBED> p *kdbr[3]
rowdata[0]
----------
ub1 rowdata[0] @8096 0x2c
BBED>
上面显示，块中有4条记录，记录1开始于数据块偏移量8162，记录2开始于数据块偏移量8140，记录3开始于数据块偏移量1020，记录4开始于数据块偏移量8096处。
后面的0x2c 就是对应偏移量中的值，也就是行头的FLAG值 (Single Row = ROW_HEAD_PIECE + ROW_FIRST_PIECE + ROW_LAST_PIECE= 0x20 + 0x08 + 0x04= 0x2c)。
如果这些记录被删除了，那么相应的值将会变为0x3c ，如下：
SQL> select * from scott.dept;
DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> delete from scott.dept;
4 rows deleted.
SQL> commit;
Commit complete.
SQL> alter system checkpoint;
System altered.
SQL> select * from scott.dept;
no rows selected
SQL>
BBED> set filename '/test/orcl/orcl/users01.dbf';
FILENAME //test/orcl/orcl/users01.dbf
BBED> set block 16
BLOCK# 16
BBED> print kdbr
sb2 kdbr[0] @118 7970
sb2 kdbr[1] @120 8040
sb2 kdbr[2] @122 8020
sb2 kdbr[3] @124 7996
BBED> print *kdbr[0]
rowdata[0]
----------
ub1 rowdata[0] @8070 0x3c
BBED> print *kdbr[1]
rowdata[70]
-----------
ub1 rowdata[70] @8140 0x3c
BBED> print *kdbr[2]
rowdata[50]
-----------
ub1 rowdata[50] @8120 0x3c
BBED> print *kdbr[3]
rowdata[26]
-----------
ub1 rowdata[26] @8096 0x3c
可以看到原来的记录不光偏移量变了，标志位也变为了0x3c，也就是DELETE标志位被设置为了1。
如果我们把标志位0x3c改为0x2c，数据将会重新找回来。
如下：
BBED> set offset 8070
OFFSET 8070
BBED> dump /v count 16
File: //test/orcl/orcl/users01.dbf (0)
Block: 16 Offsets: 8070 to 8085 Dba:0x00000000
-------------------------------------------------------
3c020302 c10b0a41 43434f55 4e54494e l <......ACCOUNTIN
<16 bytes per line>
BBED> modify /x 2c
BBED-00215: editing not allowed in BROWSE mode
BBED> set mode edit
MODE Edit
BBED> modify /x 2c
File: //test/orcl/orcl/users01.dbf (0)
Block: 16 Offsets: 8070 to 8085 Dba:0x00000000
------------------------------------------------------------------------
2c020302 c10b0a41 43434f55 4e54494e
<32 bytes per line>
BBED> set offset 8140
OFFSET 8140
BBED> dump /v count 16
File: //test/orcl/orcl/users01.dbf (0)
Block: 16 Offsets: 8140 to 8155 Dba:0x00000000
-------------------------------------------------------
3c020302 c1150852 45534541 52434806 l <......RESEARCH.
<16 bytes per line>
BBED> modify /x 2c
File: //test/orcl/orcl/users01.dbf (0)
Block: 16 Offsets: 8140 to 8155 Dba:0x00000000
------------------------------------------------------------------------
2c020302 c1150852 45534541 52434806
<32 bytes per line>
BBED> set offset 8120
OFFSET 8120
BBED> dump /v count 16
File: //test/orcl/orcl/users01.dbf (0)
Block: 16 Offsets: 8120 to 8135 Dba:0x00000000
-------------------------------------------------------
3c020302 c11f0553 414c4553 07434849 l <......SALES.CHI
<16 bytes per line>
BBED> modify /x 2c
File: //test/orcl/orcl/users01.dbf (0)
Block: 16 Offsets: 8120 to 8135 Dba:0x00000000
------------------------------------------------------------------------
2c020302 c11f0553 414c4553 07434849
<32 bytes per line>
BBED> set offset 8096
OFFSET 8096
BBED> dump /v count 16
File: //test/orcl/orcl/users01.dbf (0)
Block: 16 Offsets: 8096 to 8111 Dba:0x00000000
-------------------------------------------------------
3c020302 c1290a4f 50455241 54494f4e l <....).OPERATION
<16 bytes per line>
BBED> modify /x 2c
File: //test/orcl/orcl/users01.dbf (0)
Block: 16 Offsets: 8096 to 8111 Dba:0x00000000
------------------------------------------------------------------------
2c020302 c1290a4f 50455241 54494f4e
<32 bytes per line>
BBED> sum dba 4,16 apply
BBED-00312: no LISTFILE specified
BBED> sum apply
Check value for File 0, Block 16:
current = 0x26ff, required = 0x26ff
BBED> verify
DBVERIFY - Verification starting
FILE = //test/orcl/orcl/users01.dbf
BLOCK = 16
Block Checking: DBA = 16777232, Block Type = KTB-managed data block
data header at 0x110203064
kdbchk: the amount of space used is not equal to block size
used=118 fsc=84 avsp=7970 dtl=8088
Block 16 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
BBED> print *kdbr[0]
rowdata[0]
----------
ub1 rowdata[0] @8070 0x2c
BBED> print *kdbr[1]
rowdata[70]
-----------
ub1 rowdata[70] @8140 0x2c
BBED> print *kdbr[2]
rowdata[50]
-----------
ub1 rowdata[50] @8120 0x2c
BBED> print *kdbr[3]
rowdata[26]
-----------
ub1 rowdata[26] @8096 0x2c
BBED> x /rncc offset 8070
rowdata[0] @8070
----------
: 0x2c (KDRHFL, KDRHFF, KDRHFH)
: 0x02
: 3
col 0[2] @8073: 10
col 1[10] @8076: ACCOUNTING
col 2[8] @8087: NEW YORK
BBED> x /rncc offset 8140
rowdata[70] @8140
-----------
: 0x2c (KDRHFL, KDRHFF, KDRHFH)
: 0x02
: 3
col 0[2] @8143: 20
col 1[8] @8146: RESEARCH
col 2[6] @8155: DALLAS
BBED> x /rncc offset 8120
rowdata[50] @8120
-----------
: 0x2c (KDRHFL, KDRHFF, KDRHFH)
: 0x02
: 3
col 0[2] @8123: 30
col 1[5] @8126: SALES
col 2[7] @8132: CHICAGO
BBED> x /rncc offset 8096
rowdata[26] @8096
-----------
: 0x2c (KDRHFL, KDRHFF, KDRHFH)
: 0x02
: 3
col 0[2] @8099: 40
col 1[10] @8102: OPERATIONS
col 2[6] @8113: BOSTON
再次回到sqlplus窗口，即可发现数据已经找回来：
SQL> select * from scott.dept;
no rows selected
SQL> alter system flush buffer_cache;
System altered.
SQL> select * from scott.dept;
DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL>
