恢复delete的rows

When rows are deleted in Oracle the data is not actually removed. The row is simply marked as deleted and the free space counters and pointers adjusted accordingly. The status of a row is stored in the Row Header which occupies the first few bytes of each row.

当row 被delete 的时候，实际上data 并没有被remove，只是将该row 标记为delete，然后其对应的空间被统计为free space。 row 的status 存在每个row的row header里。

The Row Header consists of the Row Flag, Lock Byte (ITL entry) and Column Count.The first of these - the Row Flag - is a single byte that holds a bitmask thatshows the status of the row. The bitmask is decoded as follows:

 RowHeader 包含Row Flag，Lock Byte（ITL）和column Count。其中Row Flag占用1个byte，并且以bitmask 来保存。bitmask 的解释如下：

 

Cluster Key

Cluster Table Member

Head of row piece

Deleted

First data piece

Last data piece

1st Column continues from previous piece

Last column continues in next piece

128

64

32

16

8

4

2

1


 

  1. 删除前

在sqlplus中操作

select * from scott.emp;

select rowid,

dbms_rowid.rowid_relative_fno(rowid)rel_fno,

dbms_rowid.rowid_block_number(rowid)blockno,

dbms_rowid.rowid_row_number(rowid) rowno

from scott.emp;

col segment_name for a10

select extent_id,segment_name,bytes/1024 k,file_id,block_id from dba_extents where owner='SCOTT';


alter system checkpoint;

 

alter system dump datafile 4 block 145;

查询dump到哪个trace文件中：

oradebug setmypid

oradebug tracefile_name

more /u01/app/oracle/oradata/PROD/dump/diag/rdbms/prod/PROD/trace/PROD_ora_6241.trc

可以看到这个表有14条记录。

注意这里的fb: --H-FL--。 其有8个选项，每个值分别与bitmask 对应。

       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.

delete from scott.emp where empno=7900;

commit;

select * from scott.emp;

  1. 删除后

alter system checkpoint;

alter system dump datafile 4 block 145;

查询dump到哪个trace文件中：

oradebug setmypid

oradebug tracefile_name

more /u01/app/oracle/oradata/PROD/dump/diag/rdbms/prod/PROD/trace/PROD_ora_6241.trc

 

注意上面的标签被删除的数据是HDFL，一般是H-FL。

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

在bbed中操作

set dba 4,145 offset 0

find /c JAMES top

dump /v dba 4,145 offset 7706 count 128

d /v dba 4,145 offset 7705 count 128

比刚刚多了两个字符。

8个字符才是一个完整的信息，所以要改变4个偏移量才能展示完整的信息。

7706-8=7698，再加1=7699.

寻找原则：和row directory核对，寻找前面最接近的值。

d /v dba 4,145 offset 7699

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

验证一下

p kdbr

p *kdbr[9]

p *kdbr[10]

p *kdbr[11]

p *kdbr[12]

确定7699就是3c的开头。

modify /x 2c offset 7699

sum apply

在sqlplus中操作

alter system flush buffer_cache;

select * from scott.emp;

之前delete 的数据已经恢复出来。

但是：

系统认为已经删掉了。

select count(*) from scott.emp;

alter table scott.emp move;