实战演练:如何用BBED恢复删除的数据
墨墨导读:的全过程。
墨天轮主页:https://www.modb.pro/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: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
数据成功恢复。
作者
肖杰:云和恩墨Oracle技术顾问,长期服务于银行,金融,能源等行业的数据中心,8年数据库运维经验,擅长Oracle,PostgreSQL等数据库高可用设计和运维故障处理,异常恢复,升级迁移,性能优化。
墨天轮原文链接:https://www.modb.pro/db/44829(复制到浏览器中打开或者点击“阅读原文”立即查看)