oracle實驗：用bbed恢複誤刪記錄的全過程

1

準備測試環境：
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:
get_rowid 這個函數是用來獲得row_id的詳資訊，實現如下：
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

登入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, 2005, Oracle.  All rights reserved.

************* !!! For Oracle Internal Use only !!! ***************

 

BBED> set dba 4,18820
        DBA             0x01004984 (16796036 4,18820)

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］，其中n是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>

 以上是完好的行，accounting所在行顯示為2c，那麼，到sqlplus，將其刪除：

3

 登入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.
刪除完畢，並關閉資料庫，再去bbed：

 

4

登入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>
發現沒？accounting所在行變成了以3c打頭了。把3c改成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

登入資料庫，看一下是否恢複了被刪的行：
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>
成功恢複。