主頁 > 知識庫 > oracle bbed恢復刪除數(shù)據(jù)實例

oracle bbed恢復刪除數(shù)據(jù)實例

熱門標簽:word地圖標注方向 外呼線路外顯本地號碼 美圖秀秀地圖標注 征服者火車站地圖標注 百度地圖標注素材 征服眼公司地圖標注 開封智能外呼系統(tǒng)廠家 阿爾巴尼亞地圖標注app 人工智能地圖標注自己能做嗎

恢復己刪除數(shù)據(jù)
一、創(chuàng)建模擬環(huán)境

復制代碼 代碼如下:

SQL> create table hr.xifenfei (id number,name varchar2(20)) tablespace xff;
Table created.
SQL> insert into hr.xifenfei values(1,'xifenfei');
1 row created.
SQL> insert into hr.xifenfei values(2,'xff');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from hr.xifenfei;
ID NAME
———- ——————–
1 xifenfei
2 xff
SQL> select rowid,
2 dbms_rowid.rowid_relative_fno(rowid)rel_fno,
3 dbms_rowid.rowid_block_number(rowid)blockno,
4 dbms_rowid.rowid_row_number(rowid) rowno
5 from hr.xifenfei;
ROWID REL_FNO BLOCKNO ROWNO
—————— ———- ———- ———-
AAAHy3AACAAAAISAAA 2 530 0
AAAHy3AACAAAAISAAB 2 530 1
查詢file#,block,后面恢復要用
SQL> delete from hr.xifenfei where id=2;
1 row deleted.
SQL> commit;
Commit complete.
SQL> select * from hr.xifenfei;
ID NAME
———- ——————–
1 xifenfei
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
 

二、bbed恢復刪除數(shù)據(jù)
復制代碼 代碼如下:

[oracle@localhost ~]$ bbed parfile=/tmp/parfile.cnf
Password:
BBED: Release 2.0.0.0.0 – Limited Production on Mon Aug 22 01:52:52 2011
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> show all
FILE# 2
BLOCK# 1
OFFSET 0
DBA 0×00800001 (8388609 2,1)
FILENAME /opt/oracle/oradata/xifenfei/xff01.dbf
BIFILE bifile.bbd
LISTFILE /tmp/list
BLOCKSIZE 8192
MODE Edit
EDIT Unrecoverable
IBASE Dec
OBASE Dec
WIDTH 80
COUNT 512
LOGFILE log.bbd
SPOOL No
BBED> set dba 2,530
DBA 0×00800212 (8389138 2,530)
BBED> find /c xff
File: /opt/oracle/oradata/xifenfei/xff01.dbf (2)
Block: 530 Offsets: 8170 to 8191 Dba:0×00800212
————————————————————————
7866662c 000202c1 02087869 66656e66 65690106 80e2
32 bytes per line>
BBED> dump /v
File: /opt/oracle/oradata/xifenfei/xff01.dbf (2)
Block: 530 Offsets: 8170 to 8191 Dba:0×00800212
——————————————————-
7866662c 000202c1 02087869 66656e66 l xff,……xifenf
65690106 80e2 l ei….
16 bytes per line>
BBED> dump /v offset 8160
File: /opt/oracle/oradata/xifenfei/xff01.dbf (2)
Block: 530 Offsets: 8160 to 8191 Dba:0×00800212
——————————————————-
0000003c 020202c1 03037866 662c0002 l …......xff,..
02c10208 78696665 6e666569 010680e2 l ....xifenfei....
16 bytes per line>
BBED> dump /v offset 8164
File: /opt/oracle/oradata/xifenfei/xff01.dbf (2)
Block: 530 Offsets: 8164 to 8191 Dba:0×00800212
——————————————————-
020202c1 03037866 662c0002 02c10208 l ……xff,……
78696665 6e666569 010680e2 l xifenfei….
16 bytes per line>
BBED> dump /v offset 8162
File: /opt/oracle/oradata/xifenfei/xff01.dbf (2)
Block: 530 Offsets: 8162 to 8191 Dba:0×00800212
——————————————————-
003c0202 02c10303 7866662c 000202c1 l .......xff,....
02087869 66656e66 65690106 80e2 l ..xifenfei....
16 bytes per line>
BBED> dump /v offset 8163
File: /opt/oracle/oradata/xifenfei/xff01.dbf (2)
Block: 530 Offsets: 8163 to 8191 Dba:0×00800212
——————————————————-
3c020202 c1030378 66662c00 0202c102 l ......xff,.....
08786966 656e6665 69010680 e2 l .xifenfei....
16 bytes per line>
通過嘗試,推斷出來3c的offset
BBED> modify /x 2c
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /opt/oracle/oradata/xifenfei/xff01.dbf (2)
Block: 530 Offsets: 8163 to 8191 Dba:0×00800212
————————————————————————
2c020202 c1030378 66662c00 0202c102 08786966 656e6665 69010680 e2
32 bytes per line>
修改3c為2c
BBED> sum apply
Check value for File 2, Block 530:
current = 0xb1b9, required = 0xb1b9
 

三、核對結果
復制代碼 代碼如下:

SQL> startup
ORACLE instance started.
Total System Global Area 236000356 bytes
Fixed Size 451684 bytes
Variable Size 201326592 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL> select * from hr.xifenfei;
ID NAME
———- ——————–
1 xifenfei
2 xff

說明:
1)如果數(shù)據(jù)未刪除:row flag的值為 32+8+4=44或者0x2c
2)如果數(shù)據(jù)被刪除:row flag的值為 32+16+8+4=60或者0x3c

找回被刪除數(shù)據(jù)

創(chuàng)建模擬表數(shù)據(jù)
復制代碼 代碼如下:

SQL> create table t_xifenfei(id number,name varchar2(10));

Table created.

SQL> insert into t_xifenfei values(1,'xifenfei');

1 row created.

SQL> insert into t_xifenfei values(2,'XIFENFEI');

1 row created.

SQL> commit;

Commit complete.
dump數(shù)據(jù)塊
SQL> alter system flush BUFFER_CACHE;

System altered.

SQL> select   rowid,id,name,
  2  dbms_rowid.rowid_relative_fno(rowid)rel_fno,
  3  dbms_rowid.rowid_block_number(rowid)blockno,
  4  dbms_rowid.rowid_row_number(rowid) rowno
  5  from chf.t_xifenfei;

ROWID                      ID NAME          REL_FNO    BLOCKNO      ROWNO
------------------ ---------- ---------- ---------- ---------- ----------
AAASdmAAEAAAACvAAA          1 xifenfei            4        175          0
AAASdmAAEAAAACvAAB          2 XIFENFEI            4        175          1

SQL> alter system dump datafile 4 block 175;

System altered.
dump文件內容
block_row_dump:
tab 0, row 0, @0x1f89
tl: 15 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 02
col  1: [ 8]  78 69 66 65 6e 66 65 69
tab 0, row 1, @0x1f7a
tl: 15 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 03
col  1: [ 8]  58 49 46 45 4e 46 45 49
end_of_block_dump
2012-05-01 05:09:29.287714 : kjbmbassert [0xaf.4]
End dump data blocks tsn: 4 file#: 4 minblk 175 maxblk 175
 

刪除表數(shù)據(jù)
復制代碼 代碼如下:

SQL> delete from t_xifenfei;

2 rows deleted.

SQL> commit;

Commit complete.

SQL> alter system flush BUFFER_CACHE;

System altered.

SQL> alter system dump datafile 4 block 175;

System altered.
dump文件內容
block_row_dump:
tab 0, row 0, @0x1f89
tl: 2 fb: --HDFL-- lb: 0x2
tab 0, row 1, @0x1f7a
tl: 2 fb: --HDFL-- lb: 0x2
end_of_block_dump
2012-05-01 05:13:35.214357 : kjbmbassert [0xaf.4]
End dump data blocks tsn: 4 file#: 4 minblk 175 maxblk 175
通過對比這兩次的dump文件發(fā)現(xiàn)
1.數(shù)據(jù)內容被刪除,并不是真正刪除,而是給其增加了一個標識位(fd:---D----)
2.fb:--H-FL--(head of row piece+first data piece+last data piece )
  其有8個選項每個選項的值分別對應bitmask即32+8+4=44 or 0x2c
3.如果一個row被delete了,那么row flag就會更新,bitmask里的deleted被設置為16.
  此時row flag為:32+16+8+4 = 60 or 0x3c.
4.如果我們要找回來被刪除的數(shù)據(jù),只需要把3c改為2c即可
 

關閉數(shù)據(jù)庫
復制代碼 代碼如下:

SQL> select * from chf.t_xifenfei;

no rows selected

SQL> select name from v$datafile where file#=4;

NAME
------------------------------------------------
/tmp/user01.dbf

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
bbed修改數(shù)據(jù)
BBED> set filename '/tmp/user01.dbf'
        FILENAME        /tmp/user01.dbf

BBED> set block 175
        BLOCK#          175

BBED> set blocksize 8192
        BLOCKSIZE       8192

BBED> set mode edit
        MODE            Edit

BBED> map
 File: /tmp/user01.dbf (0)
 Block: 175                                   Dba:0x00000000
------------------------------------------------------------
 KTB Data Block (Table/Cluster)

 struct kcbh, 20 bytes                      @0     

 struct ktbbh, 72 bytes                     @20    

 struct kdbh, 14 bytes                      @100   

 struct kdbt[1], 4 bytes                    @114   

 sb2 kdbr[2]                                @118   

 ub1 freespace[8036]                        @122   

 ub1 rowdata[30]                            @8158  

 ub4 tailchk                                @8188  

BBED> p *kdbr[0]
rowdata[15]
-----------
ub1 rowdata[15]                             @8173     0x3c

BBED> p *kdbr[1]
rowdata[0]
----------
ub1 rowdata[0]                              @8158     0x3c

BBED> m /x 2c offset 8158
 File: /tmp/user01.dbf (0)
 Block: 175              Offsets: 8158 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 2c630202 c1030858 4946454e 46454932 630202c1 02087869 66656e66 65690106
 b47e

 32 bytes per line>

BBED>  m /x 2c offset 8173
 File: /tmp/user01.dbf (0)
 Block: 175              Offsets: 8173 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 2c630202 c1020878 6966656e 66656901 06b47e

 32 bytes per line>

BBED> sum apply
Check value for File 0, Block 175:
current = 0x4d13, required = 0x4d13

啟動數(shù)據(jù)庫驗證
復制代碼 代碼如下:

SQL> startup
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1346140 bytes
Variable Size             411043236 bytes
Database Buffers          117440512 bytes
Redo Buffers                5832704 bytes
Database mounted.
Database opened.
SQL> select * from chf.t_xifenfei;

        ID NAME
---------- ----------
         1 xifenfei
         2 XIFENFEI

您可能感興趣的文章:
  • Oracle 11g下編譯使用BBED的方法教程

標簽:淮南 六安 葫蘆島 宜春 海北 酒泉 泰安 孝感

巨人網(wǎng)絡通訊聲明:本文標題《oracle bbed恢復刪除數(shù)據(jù)實例》,本文關鍵詞  oracle,bbed,恢復,刪除,數(shù)據(jù),;如發(fā)現(xiàn)本文內容存在版權問題,煩請?zhí)峁┫嚓P信息告之我們,我們將及時溝通與處理。本站內容系統(tǒng)采集于網(wǎng)絡,涉及言論、版權與本站無關。
  • 相關文章
  • 下面列出與本文章《oracle bbed恢復刪除數(shù)據(jù)實例》相關的同類信息!
  • 本頁收集關于oracle bbed恢復刪除數(shù)據(jù)實例的相關信息資訊供網(wǎng)民參考!
  • 推薦文章