Oracle bbed的使用(找回DELETE的行)

   当一行数据被DELETE掉后，实际并没有删除，而是在相应的标记为打了一删除标记，通过BBED修改这个标记就可以把行数据找回来，下面做这个小实验：

1、  创建实验表，并插入一些数据

SQL> create tablelihm.t(msg varchar(20),id int) tablespace users;

Tablecreated.

SQL> insert into lihm.tvalues('aaa',1);

1row created.

SQL> insert into lihm.tvalues('bbb',2);

1row created.

SQL> insert into lihm.tvalues('ccc',3);

1row created.

SQL> commit;

Commitcomplete.

SQL> select * fromlihm.t;

MSG                           ID

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

aaa                             1

bbb                             2

ccc                             3

2、  生成filelist文件

SQL>l

  1* select file#||' '||name||' '||bytes from v$datafile

SQL>/

 

1/u02/app/oracle/oradata/ora10g/system01.dbf 461373440

2/u02/app/oracle/oradata/ora10g/undotbs01.dbf 26214400

3/u02/app/oracle/oradata/ora10g/sysaux01.dbf 251658240

4 /u02/app/oracle/oradata/ora10g/users01.dbf5242880

 

[oracle@hadoop1bbed]$ cat filelist.txt

1 /u02/app/oracle/oradata/ora10g/system01.dbf461373440

2/u02/app/oracle/oradata/ora10g/undotbs01.dbf 26214400

3 /u02/app/oracle/oradata/ora10g/sysaux01.dbf251658240

4 /u02/app/oracle/oradata/ora10g/users01.dbf5242880

 

[oracle@hadoop1bbed]$ cat bbed.par

blocksize=8192

listfile=/home/oracle/bbed/filelist.txt

mode=edit

password=blockedit

3、  查看表数据对应的块信息

    SQL> l

  1  select rowid,

  2      dbms_rowid.rowid_relative_fno(rowid)file_no,

  3      dbms_rowid.rowid_block_number(rowid)block_no,

  4       dbms_rowid.rowid_row_number(rowid) row_no,

  5       msg,

  6       id

  7*  from lihm.t

SQL>/

 

ROWID           FILE_NO  BLOCK_NO     ROW_NO MSG        ID

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

AAAMpWAAEAAAAA8AAA      4       60    0 aaa        1

AAAMpWAAEAAAAA8AAB      4       60    1 bbb        2

AAAMpWAAEAAAAA8AAC      4       60    2 ccc        3

 

4、监控修改前后状态标记的变化

执行DELETE前查看记录状态：

BBED>p *kdbr[2]

rowdata[0]

----------

ub1 rowdata[0]                              @8158     0x2c

 

BBED>x /rcn

rowdata[0]                                  @8158   

----------

flag@8158: 0x2c (KDRHFL, KDRHFF, KDRHFH)

lock@8159: 0x01

cols@8160:   2

 

col   0[3] @8161: ccc

col   1[2] @8165: 3

 

删除表数据：

SQL>delete from lihm.t where id=3;

1 row deleted.

SQL>commit;

Commit complete.

SQL>alter system flush buffer_cache;

System altered.

 

 

再次查看记录状态：

BBED>p *kdbr[2]

rowdata[0]

----------

ub1 rowdata[0]                              @8158     0x3c

BBED>x /rcn

rowdata[0]                                  @8158   

----------

flag@8158: 0x3c (KDRHFL, KDRHFF, KDRHFD,KDRHFH)

lock@8159: 0x02

cols@8160:   0

 

可以看到flag lock cols已经发生了改变，我们通过BBED修改回来：

 

BBED>m /x 2c dba 4,60 offset 8158

Warning: contents of previous BIFILE will belost. Proceed? (Y/N) Y

 File:/u02/app/oracle/oradata/ora10g/users01.dbf (4)

 Block:60               Offsets: 8158 to8167           Dba:0x0100003c

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

 2c020203 63636302 c104

 <32bytes per line>

BBED>p *kdbr[2]

rowdata[0]

----------

ub1 rowdata[0]                              @8158     0x2c

 

BBED>x /rcn

rowdata[0]                                  @8158   

----------

flag@8158: 0x2c (KDRHFL, KDRHFF, KDRHFH)

lock@8159: 0x02

cols@8160:   2

col   0[3] @8161: ccc

col   1[2] @8165: 3

可以看到BBED已经可以看到记录了

5、  通过SQL语句查看记录情况

SQL>select * from lihm.t;

 

MSG           ID

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

aaa            1

bbb            2

ccc            3

 可以看到数据又重新可见了。