BBED找回删除数据的一个简单例子

以前在网上看到过使用BBED来恢复删除的数据的例子，由于当时对数据块的格式不太理解。
一直看不懂别人写的例子，为什么要改数据块的这个字节，而不是改另一个字节，为什么要改为XXX而不是改为YYY等之类的问题。
前段时间研究过一段时间的数据块格式，因此也打算自己试验一下，顺便记录一下过程。

需要说明的是：一条记录如果被删除的话，实际ORACLE在数据块中并没有立马把这条记录清除，而是在行头中将该条记录标记为删除。

在操作之前，我们需要了解一点基础知识。

下面的信息来自Maclean Liu大虾的网站:

http://www.askmaclean.com/%e6%89%8b%e5%b7%a5%e6%a8%a1%e6%8b%9foracle%e6%95%b0%e6%8d%ae%e5%9d%97%e9%80%bb%e8%be%91%e8%ae%b9%e8%af%af%e5%bc%95%e5%8f%91ora-0060013013-5001%e4%b8%80%e4%be%8b.html

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
-----------
flag@8166: 0xac (KDRHFL, KDRHFF, KDRHFH, KDRHFK)
lock@8167: 0x00
cols@8168:    1
kref@8169:    1
mref@8171:    1
hrid@8173:0x01800014.0
nrid@8179:0x01800014.0

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
----------
flag@8098: 0x6c (KDRHFL, KDRHFF, KDRHFH, KDRHFC)
lock@8099: 0x00
cols@8100:   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   
----------
flag@8070: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8071: 0x02
cols@8072:    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   
-----------
flag@8140: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8141: 0x02
cols@8142:    3

col    0[2] @8143: 20
col    1[8] @8146: RESEARCH
col    2[6] @8155: DALLAS

BBED> x /rncc offset 8120
rowdata[50]                                 @8120   
-----------
flag@8120: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8121: 0x02
cols@8122:    3

col    0[2] @8123: 30
col    1[5] @8126: SALES
col    2[7] @8132: CHICAGO

BBED> x /rncc offset 8096
rowdata[26]                                 @8096   
-----------
flag@8096: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8097: 0x02
cols@8098:    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>