表中的数据被delete后,其实数据并没有真正的删除,oracle只是把这一行打了一个删除的标记,虽然数据还在,但是此行所占用的空间是可以被其他数据覆盖的,所以恢复也有一定限制
下面我们来模拟一个环境
[oracle@localhost xiaoming]$ sqlplus scott/tiger
SQL*Plus: Release 11.2.0.1.0 Production on Wed Oct 23 16:58:56 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
--创建一个表,并插入两条数据
SQL> create table goolen (id number,name varchar2(20)) tablespace users;
Table created.
SQL> insert into goolen values(1,'GOOLEN');
1 row created.
SQL> insert into goolen values(2,'ORACLE');
1 row created.
--查看这两条数据所在的文件号和块号
SQL> select
2 dbms_rowid.rowid_relative_fno(rowid) file_number,
3 dbms_rowid.rowid_block_number(rowid) block_number,
4 id,name
5 from goolen;
FILE_NUMBER BLOCK_NUMBER ID NAME
----------- ------------ ---------- --------------------
4 172 1 GOOLEN
4 172 2 ORACLE
SQL> commit;
Commit complete.
--这里刷一个buffer_cache,生产环境慎用。
SQL> alter system flush buffer_cache;
System altered.
下面我们先dump一下这个块,看看没有删除数据之前的信息:
SQL> alter system dump datafile 4 block 172;
System altered.
SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/opt/app/oracle/diag/rdbms/goolen/goolen/trace/goolen_ora_4114.trc
SQL>
block_row_dump:
tab 0, row 0, @0x1f8b
tl: 13 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 02
col 1: [ 6] 47 4f 4f 4c 45 4e
tab 0, row 1, @0x1f7e
tl: 13 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 03
col 1: [ 6] 4f 52 41 43 4c 45
end_of_block_dump
End dump data blocks tsn: 4 file#: 4 minblk 172 maxblk 172
观察红色标记的fb: --H-FL--
如果一个row没有被删除,那么row flag为--H-FL--,起对应的值为0x2c
如果一个row被删除,那么row flag为--HDFL--,起对应的值为0x3c
接下来我们继续删除一行数据来验证一下:
SQL> delete goolen where rownum =1;
1 row deleted.
SQL> select * from goolen;
ID NAME
---------- --------------------
2 ORACLE
SQL> commit;
Commit complete.
SQL> alter system flush buffer_cache;
System altered.
--dump 数据
SQL> alter system dump datafile 4 block 172;
System altered.
SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/opt/app/oracle/diag/rdbms/goolen/goolen/trace/goolen_ora_4114.trc
--查看一下dump信息:
block_row_dump:
tab 0, row 0, @0x1f8b
tl: 2 fb: --HDFL-- lb: 0x2
tab 0, row 1, @0x1f7e
tl: 13 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 03
col 1: [ 6] 4f 52 41 43 4c 45
end_of_block_dump
End dump data blocks tsn: 4 file#: 4 minblk 172 maxblk 172
--我们发现,被删除的那行,已经被标记为--HDFL--
--现在我们使用bbed来修改这个标记,恢复刚刚删除的数据
[oracle@localhost bbed]$ bbed parfile=bbed.par
Password:
BBED: Release 2.0.0.0.0 - Limited Production on Wed Oct 23 17:03:33 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> set dba 4,172 offset 0
DBA 0x010000ac (16777388 4,172)
OFFSET 0
BBED> f /c GOOLEN
File: /opt/app/oracle/oradata/goolen/users01.dbf (4)
Block: 172 Offsets: 8182 to 8191 Dba:0x010000ac
------------------------------------------------------------------------
474f4f4c 454e0206 5fd4
<32 bytes per line>
BBED> p *kdbr[0] --*kdbr[n],其中n是block_row_dump信息里的row 0
rowdata[13]
-----------
ub1 rowdata[13] @8175 0x3c
BBED> d /v dba 4,172 offset 8175
File: /opt/app/oracle/oradata/goolen/users01.dbf (4)
Block: 172 Offsets: 8175 to 8191 Dba:0x010000ac
-------------------------------------------------------
3c020202 c1020647 4f4f4c45 4e02065f l <...n.GOOLEN.._
d4 l ?
<16 bytes per line>
--这里,我们只要将@8175位置的3c改为2c就可以
BBED> modify /x 2c offset 8175
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /opt/app/oracle/oradata/goolen/users01.dbf (4)
Block: 172 Offsets: 8175 to 8191 Dba:0x010000ac
------------------------------------------------------------------------
2c020202 c1020647 4f4f4c45 4e02065f d4
<32 bytes per line>
BBED> sum apply
Check value for File 4, Block 172:
current = 0xfac8, required = 0xfac8
--刷新一下
SQL> alter system flush buffer_cache;
System altered.
SQL> select * from goolen;
ID NAME
---------- --------------------
1 GOOLEN
2 ORACLE
我们刚刚删除的数据已经恢复。