Oracle experiment: the whole process of recovering accidentally deleted records with bbed

Oracle experiment: the whole process of recovering accidentally deleted records with bbed
the
1 Prepare the test environment:
SQL> create table c as select * from dept;
the
Table created.
the
SQL> select * from c;
the
  DEPTNO DNAME       LOC
———- ————– ————-
                                           
                                     
                                   
                                           
the
SQL> select rowid,c.* from c;
the
ROWID           DEPTNO DNAME          
—————— ———- ————– ——– —–
AAAM5/AAEAAAEmEAAA                                                      
AAAM5/AAEAAAEmEAAB 20 RESEARCH DALLAS
AAAM5/AAEAAAEmEAAC 30 SALES CHICAGO
AAAM5/AAEAAAEmEAAD 40 OPERATIONS BOSTON
www.2cto.com
SQL> select get_rowid('AAAM5/AAEAAAEmEAAA') row_id from dual;
the
ROW_ID
————————————————– ——————————
Object# is :52863
Relative_fno is :4
Block number is :18820
Row number is :0
the
PS:
get_rowid This function is used to obtain the detailed information of 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 www.2cto.com
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;
the
2 Login to bbed
[oracle@localhost ~]$ bbed parfile=bbed.par
Password:
the
BBED: Release 2.0.0.0.0 – Limited Production on Thu Jun 14 13:09:44 2012
the
Copyright (c) 1982, 2005, Oracle. All rights reserved.
the
************* !!! For Oracle Internal Use only !!! ***************
the
BBED> set dba 4,18820
DBA 0x01004984 (16796036 4,18820)
the
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
the

the
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:
www.2cto.com

the
BBED> p *kdbr[0] –*kdbr[n], where n is row number
rowdata[66]
———–
ub1 rowdata[66]                     @8162   0x2c
the
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
the

the
The above is a complete line, the line where accounting is located is displayed as 2c, then, go to sqlplus and delete it.
the
3 Login to sqlplus
the
SQL> delete c where deptno=10;
the
1 row deleted.
the
SQL> commit;
www.2cto.com
Commit complete.
the
SQL> select * from c where deptno=10;
the
no rows selected
the
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.
After the deletion is complete, close the database, and then go to bbed:
the
4 Login to bbed
the
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
the

Did you find it? The line where accounting is located starts with 3c. 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
the

www.2cto.com
BBED> sum dba 4,18820
Check value for File 4, Block 18820:
current = 0xec39, required = 0xec29
the
BBED> sum dba 4,18820 apply
Check value for File 4, Block 18820:
current = 0xec29, required = 0xec29
the
5 Log in to the database to see if the deleted row is restored:
SQL> startup
ORACLE instance started.
the
Total System Global Area 524288000 bytes
Fixed Size                                                            
Variable Size                                           197132560 bytes
Database Buffers 318767104 bytes
Redo Buffers 7168000 bytes
Database mounted. www.2cto.com
Database opened.
SQL> conn scott/tiger
Connected.
SQL> select * from c;
the
  DEPTNO DNAME       LOC
———- ————– ————-
                                           
                                     
                                   
                                           
the
SQL>
Successful recovery.
the
the
the
Author linwaterbin