【原】实战演练：如何用BBED恢复删除的数据

墨墨导读：的全过程。

墨天轮主页：https://www./u/6722

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:00Start dump data blocks tsn: 9 file#:9 minblk 138 maxblk 138Block dump from cache:Dump of buffer cache at level 4 for pdb=0 tsn=9 rdba=37748874BH (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: 0x4ff72303frmt: 0x02 chkval: 0x8ea3 type: 0x23=PAGETABLE SEGMENT HEADERHex dump of block: st=0, typ_found=1Dump of memory from 0x00007FC4D3834000 to 0x00007FC4D38360007FC4D3834000 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 times7FC4D38340D0 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 times7FC4D3834AB0 02400088 0240008B 00000000 00000000 [..@...@.........]7FC4D3834AC0 00000000 00000000 00000000 00000000 [................] Repeat 151 times7FC4D3835440 00000000 00000000 02400089 00000000 [..........@.....]7FC4D3835450 00000000 00000000 00000000 00000000 [................] Repeat 185 times7FC4D3835FF0 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:9datablock is:139dump 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


数据成功恢复。

作者

肖杰：云和恩墨Oracle技术顾问，长期服务于银行，金融，能源等行业的数据中心，8年数据库运维经验，擅长Oracle，PostgreSQL等数据库高可用设计和运维故障处理，异常恢复，升级迁移，性能优化。

墨天轮原文链接：https://www./db/44829（复制到浏览器中打开或者点击“阅读原文”立即查看）