1. Oracle delete的数据并没有真正的删除，而是在块上标记为delete.然后其空间被统计为空闲空间，row 的status 存在每个row的row header里。

2. SQL>select dbms_rowid.rowid_relative_fno(rowid) rel_no,

Dbms_rowid.rowid_block_number(rowid) block_no ,

dbms_rowid.rowid_row_number(rowid) rowno,x from lerry.t1 ;

REL_NO BLOCK_NO ROWNO X

--------------------------------------------------------------------------------

6 135 0 this is dog

6 135 1 this is cat

3. Dump datafile block

SQL> alter system dump datafile 6 block 135;

System altered.

SQL> oradebug setmypid;

Statement processed.

SQL> oradebug tracefile_name;

/ora101/diag/rdbms/ora11/ora11/trace/ora11_ora_31594.trc

查看block信息

其中lerry.t1 table中只有2条数据，所以这里显示的row为2，

注意这里的fb:--H-FL--。其有8个选项每个选项的值分别对应bitmask即(32+8+4)=0x2c

Therefore,columns that fit within a single block, are not chained, migrated or part of aclustered table and are not deleted will have the following attributes:

（1）Head of Row Piece

（2）First Data Piece

（3）Last Data Piece

如果一个row 没有被删除，那么它就具有上面的3个属性，即Flag 表示为：--H-FL--. 这里的字母分别代表属性的首字母。其对应的值：32 + 8 + 4 =44 or 0x2c.

如果一个row 被delete了，那么row flag 就会更新，bitmask 里的deleted 被设置为16. 此时row flag 为： 32 + 16 + 8 + 4 = 60 or 0x3c.

4. 删除数据并dump block

SQL> delete from t1 where y=1;

1 row deleted.

SQL> commit;

Commit complete.

SQL>alter system flush buffer_cache;

Sql>select * from lerry.t1;

X Y

-------------------------------------------------------------------------------

this is cat 2

SQL> alter system dump datafile 6 block 135;

System altered.

SQL> oradebug setmypid;

Statement processed.

SQL> oradebug tracefile_name;

/ora101/diag/rdbms/ora11/ora11/trace/ora11_ora_4322.trc

查看dump文件

这里的row 1flag 变成了--HDFL--。

现在我们用bbed 将删除的row 1 内容找回来。

5. 利用bbed找回delete数据

[oracle@STCS oradata]$ bbed parfile=par.txt

Password:

BBED: Release 2.0.0.0.0 - Limited Production on Wed Dec 21 10:36:27 2011

************* !!! For Oracle Internal Use only !!! ***************

BBED> set dba 6,135 offset 0

DBA 0x01800087 (25165959 6,135)

OFFSET 0

BBED> find /c dog

File: /oradata/ora11/test02.dbf (6)

Block: 135 Offsets: 8182 to 8191 Dba:0x01800087

------------------------------------------------------------------------

646f6702 c1020206 5a3e

<32 bytes per line>

Dump（d）命令可以讲block 的内从显示到屏幕。每次显示的bytes由count 控制，默认是512 bytes。使用 /v 选项，可以显示更多详细信息。

BBED> d /v dba 6,135 offset 8182 count 128

File: /oradata/ora11/test02.dbf (6)

Block: 135 Offsets: 8182 to 8191 Dba:0x01800087

-------------------------------------------------------

646f6702 c1020206 5a3e l dog.Á...Z>

<16 bytes per line>

我们的row 的内容保存在offset 8182的位置，我们将offset 往前移动一段，在dump，来确定row header的内容（3c）

一直移动，知道找到有3c的数据

这个移位有一定的规律。我们看一下

BBED> d /v dba 6,135 offset 8181

File: /oradata/ora11/test02.dbf (6)

Block: 135 Offsets: 8181 to 8191 Dba:0x01800087

-------------------------------------------------------

20646f67 02c10202 065a3e l dog.Á...Z>

<16 bytes per line>

BBED> d /v dba 6,135 offset 8178

File: /oradata/ora11/test02.dbf (6)

Block: 135 Offsets: 8178 to 8191 Dba:0x01800087

-------------------------------------------------------

20697320 646f6702 c1020206 5a3e l is dog.Á...Z>

<16 bytes per line>

BBED> d /v dba 6,135 offset 8174

File: /oradata/ora11/test02.dbf (6)

Block: 135 Offsets: 8174 to 8191 Dba:0x01800087

-------------------------------------------------------

74686973 20697320 646f6702 c1020206 l this is dog.Á...

5a3e l Z>

<16 bytes per line>

BBED> d /v dba 6,135 offset 8170

File: /oradata/ora11/test02.dbf (6)

Block: 135 Offsets: 8170 to 8191 Dba:0x01800087

-------------------------------------------------------

3c02020b 74686973 20697320 646f6702 l <...this is dog.

c1020206 5a3e l Á...Z>

<16 bytes per line>

这里已经出现了我们3c（deleted）标志，但是注意这里的位置的根据我们的查找的字符串来分的，实际在block里的分割方式不一样按照我们的offset 来进行。我们可以通过row directory 来进行一个确认。

BBED> p kdbr

sb2 kdbr[0] @118 8070

sb2 kdbr[1] @120 8052

BBED> p *kdbr[0]

rowdata[18]

-----------

ub1 rowdata[18] @8170 0x3c

BBED> p *kdbr[1]

rowdata[0]

----------

ub1 rowdata[0] @8152 0x2c

BBED> modify /x 2c offset 8170

Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y

File: /oradata/ora11/test02.dbf (6)

Block: 135 Offsets: 8170 to 8191 Dba:0x01800087

------------------------------------------------------------------------

2c02020b 74686973 20697320 646f6702 c1020206 5a3e

<32 bytes per line>

BBED> sum apply

Check value for File 6, Block 135:

current = 0xf1c8, required = 0xf1c8

BBED>quit

6. 检查数据

SQL> alter system flush buffer_cache;

System altered.

SQL> select * from lerry.t1;

X Y

--------------------------------------------------------------------------------

this is dog 1

this is cat 2

ITPub博客

利用bbed找回已删除的数据

最新文章