BBED恢复删除的数据

Oracle中delete行时，数据实际上并没有被删除。而是将行标记为已删除，并相应地调整空闲空间计数器和指针。行状态存储在每行的行标头中，该行标头占用每行的前几个字节。
行标头由行标记、锁字节(ITL条目)和列计数组成。第一个Row标志是一个单字节，它保存一个显示行状态的位掩码。位掩码解码如下

因此行标头=head of row picec + first data picec + last data picec = 44 => 0x2c

当数据被删除后，行标头+16即60=>0x3c
因此，当数据未被覆盖时，修改行标头0x3c为0x2c即可恢复。

测试：

1，创建测试数据

SQL> create table devin.test_delete(id number,name nvarchar2(10)) tablespace devin;

Table created.

SQL> insert into devin.test_delete values(1,'devin1');

1 row created.

SQL> insert into devin.test_delete values(2,'devin2');

1 row created.

SQL> insert into devin.test_delete values(3,'devin3');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from devin.test_delete;

        ID NAME
---------- ----------------------------------------------------------------------
         1 devin1
         2 devin2
         3 devin3
		 
SQL> delete from devin.test_delete where id=3;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select * from devin.test_delete;

        ID NAME
---------- ----------------------------------------------------------------------
         1 devin1
         2 devin2

SQL> col segment_Name for a15
SQL> select segment_name,tablespace_name,header_file,header_block from dba_segments where segment_name='TEST_DELETE';

SEGMENT_NAME    TABLESPACE_NAME                HEADER_FILE HEADER_BLOCK
--------------- ------------------------------ ----------- ------------
TEST_DELETE     DEVIN                                    9          138
复制

可以看到表位于9号文件，段头块是138，先dump 138快，根据段头找到具体的数据存放的block。

2，dump header block

SQL> alter system dump datafile 9 block 138;

System altered.

*** 2021-02-05T11:00:07.663087+08:00
Start dump data blocks tsn: 9 file#:9 minblk 138 maxblk 138
Block dump from cache:
Dump of buffer cache at level 4 for pdb=0 tsn=9 rdba=37748874
BH (0x6ebdb498) file#: 9 rdba: 0x0240008a (9/138) class: 4 ba: 0x6e8da000
  set: 6 pool: 3 bsz: 8192 bsi: 0 sflg: 0 pwc: 0,0
  dbwrid: 0 obj: 75550 objn: 75550 tsn: [0/9] afn: 9 hint: f
  hash: [0x694952d8,0x694952d8] lru: [0x6efe92d8,0x6dffa478]
  ckptq: [NULL] fileq: [NULL]
  objq: [0x6dffa4a0,0x6d3d98c0] objaq: [0x62810c10,0x6dffa4b0]
  st: XCURRENT md: NULL fpin: 'ktswh03: ktscts' fscn: 0x2374fe0 tch: 3
  flags: block_written_once
  LRBA: [0x0.0.0] LSCN: [0x0] HSCN: [0x2374ff7] HSUB: [3]
  Printing buffer operation history (latest change first):
  cnt: 12
  01. sid:09 L192:kcbbic2:bic:FBD     02. sid:09 L191:kcbbic2:bic:FBW   
  03. sid:09 L602:bic1_int:bis:FWC    04. sid:09 L822:bic1_int:ent:rtn  
  05. sid:09 L832:oswmqbg1:clr:WRT    06. sid:09 L930:kubc:sw:mq        
  07. sid:09 L913:bxsv:sw:objq        08. sid:09 L608:bxsv:bis:FBW      
  09. sid:09 L607:bxsv:bis:FFW        10. sid:07 L464:chg1_mn:bic:FMS   
  11. sid:07 L778:chg1_mn:bis:FMS     12. sid:07 L353:gcur:set:MEXCL    
  13. sid:07 L464:chg1_mn:bic:FMS     14. sid:07 L778:chg1_mn:bis:FMS   
  15. sid:07 L353:gcur:set:MEXCL      16. sid:07 L464:chg1_mn:bic:FMS   
Block dump from disk:
buffer tsn: 9 rdba: 0x0240008a (9/138)
scn: 0x2374ff7 seq: 0x03 flg: 0x04 tail: 0x4ff72303
frmt: 0x02 chkval: 0x8ea3 type: 0x23=PAGETABLE SEGMENT HEADER
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007FC4D3834000 to 0x00007FC4D3836000
7FC4D3834000 0000A223 0240008A 02374FF7 04030000  [#.....@..O7.....]
7FC4D3834010 00008EA3 00000000 00000000 00000000  [................]
7FC4D3834020 00000000 00000001 00000008 00000A9C  [................]
7FC4D3834030 00000000 00000008 00000008 02400090  [..............@.]
7FC4D3834040 00000000 00000000 00000000 00000005  [................]
7FC4D3834050 00000000 00000000 00000000 00000000  [................]
7FC4D3834060 00000008 00000008 02400090 00000000  [..........@.....]
7FC4D3834070 00000000 00000000 00000005 02400088  [..............@.]
7FC4D3834080 02400088 00000000 00000000 00000000  [..@.............]
7FC4D3834090 00000000 00000000 00000000 00000000  [................]
        Repeat 3 times
7FC4D38340D0 00000001 00002000 00000000 00001434  [..... ......4...]
7FC4D38340E0 00000000 02400089 00000001 02400088  [......@.......@.]
7FC4D38340F0 02400089 00000000 00000000 00000000  [..@.............]
7FC4D3834100 00000000 00000000 00000001 00000000  [................]
7FC4D3834110 0001271E 10000000 02400088 00000008  [.'........@.....]
7FC4D3834120 00000000 00000000 00000000 00000000  [................]
        Repeat 152 times
7FC4D3834AB0 02400088 0240008B 00000000 00000000  [..@...@.........]
7FC4D3834AC0 00000000 00000000 00000000 00000000  [................]
        Repeat 151 times
7FC4D3835440 00000000 00000000 02400089 00000000  [..........@.....]
7FC4D3835450 00000000 00000000 00000000 00000000  [................]
        Repeat 185 times
7FC4D3835FF0 00000000 00000000 00000000 4FF72303  [.............#.O]
  Extent Control Header
  -----------------------------------------------------------------
  Extent Header:: spare1: 0      spare2: 0      #extents: 1      #blocks: 8     
                  last map  0x00000000  #maps: 0      offset: 2716  
      Highwater::  0x02400090  ext#: 0      blk#: 8      ext size: 8     
  #blocks in seg. hdr's freelists: 0     
  #blocks below: 5     
  mapblk  0x00000000  offset: 0     
                   Unlocked
  --------------------------------------------------------
  Low HighWater Mark : 
      Highwater::  0x02400090  ext#: 0      blk#: 8      ext size: 8     
  #blocks in seg. hdr's freelists: 0     
  #blocks below: 5     
  mapblk  0x00000000  offset: 0     
  Level 1 BMB for High HWM block: 0x02400088
  Level 1 BMB for Low HWM block: 0x02400088
  --------------------------------------------------------
  Segment Type: 1 nl2: 1      blksz: 8192   fbsz: 0      
  L2 Array start offset:  0x00001434
  First Level 3 BMB:  0x00000000
  L2 Hint for inserts:  0x02400089
  Last Level 1 BMB:  0x02400088
  Last Level II BMB:  0x02400089
  Last Level III BMB:  0x00000000
     Map Header:: next  0x00000000  #extents: 1    obj#: 75550  flag: 0x10000000
  Inc # 0 
  Extent Map
  -----------------------------------------------------------------
   0x02400088  length: 8     
  
  Auxillary Map
  --------------------------------------------------------
   Extent 0     :  L1 dba:  0x02400088 Data dba:  0x0240008b
  --------------------------------------------------------
  
   Second Level Bitmap block DBAs 
   --------------------------------------------------------
   DBA 1:   0x02400089
  
End dump data blocks tsn: 9 file#: 9 minblk 138 maxblk 138


--可以看到DATA DBA : 0x0240008b

SQL> select getbfno('0x0240008b') from dual;

GETBFNO('0X0240008B')
----------------------------------------------------
datafile# is:9
datablock is:139
dump command:alter system dump datafile 9 block 139;

--即数据存储从block 139开始
复制

3，查找被删除的数据

BBED> set dba 9,139
        DBA             0x0240008b (37748875 9,139)

BBED> find /c devin3 TOP
BBED-00212: search string not found

--139 block没有，往下一个block找

BBED> set dba 9,140
        DBA             0x0240008c (37748876 9,140)

BBED> find /c devin3 TOP
 File: /u01/app/oracle/oradata/HKORA/datafile/o1_mf_devin_hwyb1byq_.dbf (9)
 Block: 140              Offsets: 8156 to 8191           Dba:0x0240008c
------------------------------------------------------------------------
 64657669 6e332c01 0202c103 06646576 696e322c 010202c1 02066465 76696e31 
 0106fc4f 

 <32 bytes per line>
 --可以看到真实数据在block 140 offset 8156，接下来往前dump找出行标头

BBED> dump /v offset 8140
 File: /u01/app/oracle/oradata/HKORA/datafile/o1_mf_devin_hwyb1byq_.dbf (9)
 Block: 140     Offsets: 8140 to 8191  Dba:0x0240008c
-------------------------------------------------------
 38360200 800000b6 5e3c0202 02c10406 l 86.......
 64657669 6e332c00 0202c103 06646576 l devin3,...ev
 696e322c 000202c1 02066465 76696e31 l in2,...evin1
 02060153                            l ...S

--可以看到行是从offset 8149开始，并且已经被标记为deleted，0x3c，通过行映射验证一下：

BBED> p *kdbr[2]
rowdata[0]
----------
ub1 rowdata[0]                              @8149     0x3c

--可以看到第三行数据确实是从offset 8149开始

BBED> d /v offset 8149
 File: /u01/app/oracle/oradata/HKORA/datafile/o1_mf_devin_hwyb1byq_.dbf (9)
 Block: 140     Offsets: 8149 to 8191  Dba:0x0240008c
-------------------------------------------------------
 3c020202 c1040664 6576696e 332c0002 l <...evin3,..
 02c10306 64657669 6e322c00 0202c102 l .evin2,...
 06646576 696e3102 060153            l .devin1...S

 <16 bytes per line>
--接下来修改offset 8149
复制

4，修改行标头

BBED> m /x 2c offset 8149
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
 File: /u01/app/oracle/oradata/HKORA/datafile/o1_mf_devin_hwyb1byq_.dbf (9)
 Block: 140              Offsets: 8149 to 8191           Dba:0x0240008c
------------------------------------------------------------------------
 2c020202 c1040664 6576696e 332c0002 02c10306 64657669 6e322c00 0202c102 
 06646576 696e3102 060153 

 <32 bytes per line>

BBED> sum apply
Check value for File 9, Block 140:
current = 0x7048, required = 0x7048
复制

5，验证

SQL> alter system flush buffer_cache;

System altered.

SQL> select * from devin.test_delete;

        ID NAME
---------- ----------------------------------------------------------------------
         1 devin1
         2 devin2
         3 devin3
复制

数据成功恢复。