How to use bbed to recover delete data in Oracle

When the data in the table is deleted, it does not actually delete the data, but instead it makes a deletion mark that can be recovered as long as it is not overwritten.

The experimental process is as follows:

Sys@orcl>create table Bbed_test (x varchar2 (20));

Table created.

Sys@orcl>insert into bbed_test values (' BADLY9 ');

1 row created.

Sys@orcl>insert into bbed_test values (' JP ');

1 row created.

sys@orcl>commit;

Commit complete.

Sys@orcl>select rowID, Dbms_rowid.rowid_relative_fno (ROWID) Rel_fno,

2 Dbms_rowid.rowid_block_number (ROWID) Blockno,

3 Dbms_rowid.rowid_row_number (ROWID) rowno

4 from Bbed_test;

ROWID rel_fno Blockno Rowno

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

AAAM0VAABAAAOUCAAA 1 60290 0

Aaam0vaabaaaoucaab 1 60290 1

Sys@orcl>alter system flush Buffer_cache;

System altered.

Sys@orcl>alter System dump datafile 1 block 60290;

System altered.

Sys@orcl>oradebug Setmypid

Statement processed.

Sys@orcl>oradebug Tracefile_name

/u01/app/oracle/admin/orcl/udump/orcl_ora_18509.trc

View dump File

Block_row_dump:

tab 0, row 0, @0x1f96

Tl:10 FB:--h-fl--lb:0x1 cc:1

Col 0: [6] 4c 59 39

tab 0, row 1, @0x1f90

Tl:6 FB:--h-fl--lb:0x1 cc:1

Col 0: [2] 4a 50

End_of_block_dump

The position of the red Mark records the status of the row data:

This column more highlights: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/

If it's not deleted, it's--H-FL--, its corresponding value is 0x2c

The words that have been deleted are--hdfl--, and their corresponding values are 0x3c

Now let's delete one line of data to see:

Sys@orcl>delete bbed_test where x= ' BADLY9 ';

1 row deleted.

sys@orcl>commit;

Commit complete.

Sys@orcl>select * from Bbed_test;

X

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

Jp

Sys@orcl> alter system flush Buffer_cache;

System altered.

Sys@orcl>alter System dump datafile 1 block 60290;

System altered.

View dump File

Block_row_dump:

tab 0, row 0, @0x1f96

Tl:2 FB:--hdfl--lb:0x2

tab 0, row 1, @0x1f90

Tl:6 FB:--h-fl--lb:0x0 cc:1

Col 0: [2] 4a 50

End_of_block_dump

You can see that row 0 data has been marked for deletion at this time.

The following use the Bbed tool to restore this data back:

Bbed> Set DBA 1,60290

DBA 0x0040eb82 (4254594 1,60290)

Bbed> f/c BADLY9

File:/u01/app/oracle/oradata/orcl/system01.dbf (1)

block:60290 offsets:8182 to 8191 dba:0x0040eb82

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

4241444c 59390206 1e62

<32 bytes per line>

Bbed> P *KDBR

ROWDATA[6]

----------

Ub1 Rowdata[6] @8178 0x3c

Bbed> Set Offset 8178

OFFSET 8178

Bbed> m/x 2c

File:/u01/app/oracle/oradata/orcl/system01.dbf (1)

block:60290 offsets:8178 to 8191 dba:0x0040eb82

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

2c020106 4241444c 59390206 1e62

<32 bytes per line>

bbed> sum apply

Check value for File 1, block 60290:

Current = 0xc7d4, required = 0xc7d4

Now check it out in the library.

Sys@orcl>alter system flush Buffer_cache;

System altered.

Sys@orcl>select * from Bbed_test;

X

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

BADLY9

Jp

You can see that the data that was delete has been restored back.