Oracle experiment: Use bbed to restore the entire process of accidental deletion records

1

Prepare the test environment:
SQL> Create Table C as select * from Dept;

Table created.

SQL> select * from C;

Deptno dname Loc
-------------------------------------
10 Accounting New York
20 research Dallas
30 sales Chicago
40 operations Boston

SQL> select rowid, C. * from C;

Rowid deptno dname Loc
-------------------------------------------------------
Aaam5/aaeaaaemeaaa 10 Accounting New York
Aaam5/aaeaaaemeaab 20 research Dallas
Aaam5/aaeaaaemeaac 30 sales Chicago
Aaam5/aaeaaaemeaad 40 operations Boston

SQL> select get_rowid ('aaam5/aaeaaaemeaaa') row_id from dual;

Row_id
--------------------------------------------------------------------------------
Object # Is: 52863
Relative_fno is: 4
Block number is: 18820
Row number is: 0

PS:
The get_rowid function is used to obtain detailed information about row_id. The implementation is as follows:
Create or replace function get_rowid
(L_rowid in varchar2)
Return varchar2
Is
Ls_my_rowid varchar2 (200 );
Rowid_type number;
Object_number number;
Relative_fno number;
Block_number number;
Row_number number;
Begin
Dbms_rowid.rowid_info (l_rowid, rowid_type, object_number, relative_fno, block_number, row_number );
Ls_my_rowid: = 'object # is: '| to_char (object_number) | CHR (10) |
'Relative _ fno is: '| to_char (relative_fno) | CHR (10) |
'Block number is: '| to_char (block_number) | CHR (10) |
'Row number is: '| to_char (row_number );
Return ls_my_rowid;
End;

 

2

Log on to bbed
[Oracle @ localhost ~] $ Bbed parfile = bbed. Par
Password:

Bbed: Release 2.0.0.0.0-limited production on Thu Jun 14 13:09:44 2012

Copyright (c) 1982,200 5, Oracle. All rights reserved.

*************!!! For Oracle internal use only !!! ***************

 

Bbed> set DBA 4,18820
DBA 0x01004984 (16796036)

Bbed> Find/C accounting top
File:/u01/APP/Oracle/oradata/orcl/datafile/o1_mf_users_7wvsw7hg _. DBF (4)
Block: 18820 offsets: 8169 to 8191 DBA: 0x01004984
------------------------------------------------------------------------
4143434f 554e5449 4e47084e 45572059 4f524b02 06483a

<32 bytes per line>

Bbed> dump/v dba 4, 18820 offset 8169 Count 23
File:/u01/APP/Oracle/oradata/orcl/datafile/o1_mf_users_7wvsw7hg _. DBF (4)
Block: 18820 offsets: 8169 to 8191 DBA: 0x01004984
-------------------------------------------------------
4143434f 554e5449 4e47084e 45572059 l accounting. New Y
4f524b02 06483a l ork .. h:

<16 bytes per line>

 

Bbed> P * kdbr [0] -- * kdbr [N], where n is row number
Rowdata [66]
-----------
Ub1 rowdata [66] @ 8162 0x2c

Bbed> dump/v dba 4, 18820 offset 8162 Count 23
File:/u01/APP/Oracle/oradata/orcl/datafile/o1_mf_users_7wvsw7hg _. DBF (4)
Block: 18820 offsets: 8162 to 8184 DBA: 0x01004984
-------------------------------------------------------
2c000302 c10b0a41 43434f55 4e54494e L,... accountin
47084e45 572059 l g. New Y

<16 bytes per line>

The above is a good row, and the row where accounting is located is displayed as 2C. Then, go to sqlplus and delete it:

3

Log on to sqlplus

SQL> Delete c Where deptno = 10;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select * from C where deptno = 10;

No rows selected

SQL> shutdown immediate
ORA-01031: insufficient privileges
SQL> conn sys/s as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
Oracle instance shut down.
Delete the database, close the database, and then go to bbed:

 

4

Log on to bbed

Bbed> dump/v dba 4, 18820 offset 8162 Count 23
File:/u01/APP/Oracle/oradata/orcl/datafile/o1_mf_users_7wvsw7hg _. DBF (4)
Block: 18820 offsets: 8162 to 8184 DBA: 0x01004984
-------------------------------------------------------
3c020302 c10b0a41 43434f55 4e54494e L <... accountin
47084e45 572059 l g. New Y

<16 bytes per line>
Found? The row where accounting is located changes to the 3C header. Change 3C to 2C:
Bbed> Modify/X 2C offset 8162
Warning: Contents of previous bifile will be lost. Proceed? (Y/n) y
File:/u01/APP/Oracle/oradata/orcl/datafile/o1_mf_users_7wvsw7hg _. DBF (4)
Block: 18820 offsets: 8162 to 8184 DBA: 0x01004984
------------------------------------------------------------------------
2c020302 c10b0a41 43434f55 4e54494e 47084e45 572059

<32 bytes per line>

Bbed> sum DBA 4, 18820
Check value for file 4, block 18820:
Current = 0xec39, required = 0xec29

Bbed> sum DBA 4, 18820 apply
Check value for file 4, block 18820:
Current = 0xec29, required = 0xec29

 

5

Log on to the database and check whether the deleted rows have been restored:
SQL> startup
Oracle instance started.

Total system global area 524288000 bytes
Fixed size 1220336 bytes
Variable Size 197132560 bytes
Database buffers 318767104 bytes
Redo buffers 7168000 bytes
Database mounted.
Database opened.
SQL> conn Scott/Tiger
Connected.
SQL> select * from C;

Deptno dname Loc
-------------------------------------
10 Accounting New York
20 research Dallas
30 sales Chicago
40 operations Boston

SQL>
Recovery successful.