如何使用bbed恢復delete的rows

小編給大家分享一下如何使用bbed恢復delete的rows，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！

實驗如下：

1.創建環境表DVD
SQL> conn hr/hr;
Connected.
SQL>
SQL> create table dvd(job varchar2(100));

Table created.

SQL> insert into dvd values('Dave is DBA!');

1 row created.

SQL> insert into dvd values('Dave like Oracle!');

1 row created.

SQL> commit;

Commit complete.

SQL>  conn / as sysdba
Connected.
SQL>
SQL> set lines 200
SQL> select OWNER,TABLE_NAME,TABLESPACE_NAME from dba_tables where table_name='DVD';

OWNER                          TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
HR                             DVD                            USERS


2.查詢dvd相關file/block信息；
SQL> select rowid,dbms_rowid.rowid_relative_fno(rowid) rel_fno,dbms_rowid.rowid_block_number(rowid) blockno,dbms_rowid.rowid_row_number(rowid) rowno,a.* from dvd a;

ROWID                 REL_FNO    BLOCKNO      ROWNO JOB
------------------ ---------- ---------- ---------- ----------------------------------------------------------------------------------------------------
AAAV8YAAEAAAAIPAAA          4        527          0 Dave is DBA!
AAAV8YAAEAAAAIPAAB          4        527          1 Dave like Oracle!

SQL>


--新開一個窗口執行dump塊查詢信息
oracle@wang ~]$   sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Nov 28 14:31:49 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter system dump datafile 4 block 527;

System altered.

SQL> oradebug setmypid
Statement processed.
SQL>
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/dbdb/DBdb/trace/DBdb_ora_2013.trc
SQL>


--查看trace：
[oracle@wang ~]$ more /u01/app/oracle/diag/rdbms/dbdb/DBdb/trace/DBdb_ora_2013.trc
Trace file /u01/app/oracle/diag/rdbms/dbdb/DBdb/trace/DBdb_ora_2013.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1
System name:    Linux
Node name:      wang
Release:        3.10.0-327.el7.x86_64
Version:        #1 SMP Thu Oct 29 17:29:29 EDT 2015
Machine:        x86_64
Instance name: DBdb
Redo thread mounted by this instance: 1
Oracle process number: 27
Unix process pid: 2013, image: oracle@wang (TNS V1-V3)


*** 2017-11-28 14:31:56.277
*** SESSION ID:(40.13) 2017-11-28 14:31:56.277
*** CLIENT ID:() 2017-11-28 14:31:56.277
*** SERVICE NAME:(SYS$USERS) 2017-11-28 14:31:56.277
*** MODULE NAME:(sqlplus@wang (TNS V1-V3)) 2017-11-28 14:31:56.277
*** ACTION NAME:() 2017-11-28 14:31:56.277
 
Start dump data blocks tsn: 4 file#:4 minblk 527 maxblk 527
Block dump from cache:
Dump of buffer cache at level 4 for tsn=4 rdba=16777743
BH (0x89ff27e8) file#: 4 rdba: 0x0100020f (4/527) class: 1 ba: 0x89eee000
  set: 3 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 0,0
  dbwrid: 0 obj: 89880 objn: 89880 tsn: 4 afn: 4 hint: f
  hash: [0x8fe6a618,0x87fd9a18] lru: [0x89ff2a10,0x89ff27a0]
  ckptq: [NULL] fileq: [NULL] objq: [0x89ff2a38,0x8b4e5e60] objaq: [0x89ff2a48,0x8b4e5e50]
  st: XCURRENT md: NULL fpin: 'ktspbwh3: ktspfmdb' tch: 5
  flags: block_written_once redo_since_read
  LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [1]
Block dump from disk:
buffer tsn: 4 rdba: 0x0100020f (4/527)
scn: 0x0000.0039530d seq: 0x01 flg: 0x06 tail: 0x530d0601
frmt: 0x02 chkval: 0x9014 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007F3C1C8B7A00 to 0x00007F3C1C8B9A00
7F3C1C8B7A00 0000A206 0100020F 0039530D 06010000  [.........S9.....]
7F3C1C8B7A10 00009014 00000001 00015F18 00395308  [........._...S9.]
7F3C1C8B7A20 00000000 00320002 01000208 00160002  [......2.........]
7F3C1C8B7A30 000005FA 00C3D82B 00180272 00002002  [....+...r.... ..]
7F3C1C8B7A40 0039530D 00000000 00000000 00000000  [.S9.............]
7F3C1C8B7A50 00000000 00000000 00000000 00000000  [................]
7F3C1C8B7A60 00000000 00020100 0016FFFF 1F5D1F73  [............s.].]
7F3C1C8B7A70 00001F5D 1F880002 00001F73 00000000  [].......s.......]
7F3C1C8B7A80 00000000 00000000 00000000 00000000  [................]
        Repeat 500 times
7F3C1C8B99D0 00000000 2C000000 44110101 20657661  [.......,...Dave ]
7F3C1C8B99E0 656B696C 61724F20 21656C63 0C01012C  [like Oracle!,...]
7F3C1C8B99F0 65766144 20736920 21414244 530D0601  [Dave is DBA!...S]
Block header dump:  0x0100020f
 Object id on Block? Y
 seg/obj: 0x15f18  csc: 0x00.395308  itc: 2  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x1000208 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0002.016.000005fa  0x00c3d82b.0272.18  --U-    2  fsc 0x0000.0039530d
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
bdba: 0x0100020f
data_block_dump,data header at 0x7f3c1c8b7a64
===============
tsiz: 0x1f98
hsiz: 0x16
pbl: 0x7f3c1c8b7a64
     76543210
flag=--------
ntab=1
nrow=2
frre=-1
fsbo=0x16
fseo=0x1f73
avsp=0x1f5d
tosp=0x1f5d
0xe:pti[0]      nrow=2  offs=0
0x12:pri[0]     offs=0x1f88
0x14:pri[1]     offs=0x1f73
block_row_dump:
tab 0, row 0, @0x1f88
tl: 16 fb: --H-FL-- lb: 0x1  cc: 1
col  0: [12]  44 61 76 65 20 69 73 20 44 42 41 21
tab 0, row 1, @0x1f73
tl: 21 fb: --H-FL-- lb: 0x1  cc: 1
col  0: [17]  44 61 76 65 20 6c 69 6b 65 20 4f 72 61 63 6c 65 21
end_of_block_dump
End dump data blocks tsn: 4 file#: 4 minblk 527 maxblk 527

*** 2017-11-28 14:31:57.368
Processing Oradebug command 'setmypid'

*** 2017-11-28 14:31:57.368
Oradebug command 'setmypid' console output:

*** 2017-11-28 14:32:00.927
Processing Oradebug command 'tracefile_name'

*** 2017-11-28 14:32:00.927
Oradebug command 'tracefile_name' console output:
/u01/app/oracle/diag/rdbms/dbdb/DBdb/trace/DBdb_ora_2013.trc
[oracle@wang ~]$

我們的表dvd里只有2行記錄，所以這里顯示的row 為2.
注意這里的fb: --H-FL--。 其有8個選項，每個值分別與bitmask 對應。

Therefore,columns that fit within a single block, are not chained, migrated or part of aclustered table and are not deleted will have the following attributes:
（1）Head of Row Piece
（2）First Data Piece
（3）Last Data Piece
       
如果一個row 沒有被刪除，那么它就具有上面的3個屬性，即Flag 表示為：--H-FL--. 這里的字母分別代表屬性的首字母。其對應的值：32 + 8 + 4 =44 or 0x2c.

如果一個row 被delete了，那么row flag 就會更新，bitmask 里的deleted 被設置為16. 此時row flag 為： 32 + 16 + 8 + 4 = 60 or 0x3c.

--驗證一下：
SQL> select rowid,dbms_rowid.rowid_relative_fno(rowid) rel_fno,dbms_rowid.rowid_block_number(rowid) blockno,dbms_rowid.rowid_row_number(rowid) rowno from dvd;

ROWID                 REL_FNO    BLOCKNO      ROWNO
------------------ ---------- ---------- ----------
AAAV84AAEAAAAIPAAA          4        527          0
AAAV84AAEAAAAIPAAB          4        527          1

SQL> select * from dvd;

JOB
--------------------------------------------------------------------------------
Dave is DBA!
Dave like Oracle!

SQL> delete from dvd where rownum=1;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select * from dvd;

JOB
--------------------------------------------------------------------------------
Dave like Oracle!

--再次查看dump 的標記：
[oracle@wang ~]$   sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Nov 28 14:36:29 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

--需先flush buffer_cache清空buffer：
SQL>  alter system flush buffer_cache;

System altered.

SQL> alter system dump datafile 4 block 527;

System altered.

SQL>
SQL> oradebug setmypid
Statement processed.
SQL>
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/dbdb/DBdb/trace/DBdb_ora_2065.trc
SQL>
SQL>

--再次查看trace文件：
[oracle@wang ~]$ more /u01/app/oracle/diag/rdbms/dbdb/DBdb/trace/DBdb_ora_2065.trc
省略................................................................
省略................................................................
省略................................................................
block_row_dump:
tab 0, row 0, @0x1f88
tl: 2 fb: --HDFL-- lb: 0x2                            ---注意由-H-FL變為-HdFL
tab 0, row 1, @0x1f73
tl: 21 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [17]  44 61 76 65 20 6c 69 6b 65 20 4f 72 61 63 6c 65 21
end_of_block_dump
End dump data blocks tsn: 4 file#: 4 minblk 527 maxblk 527

*** 2017-11-28 14:36:42.754
Processing Oradebug command 'setmypid'

*** 2017-11-28 14:36:42.754
Oradebug command 'setmypid' console output:

*** 2017-11-28 14:36:48.261
Processing Oradebug command 'tracefile_name'

*** 2017-11-28 14:36:48.262
Oradebug command 'tracefile_name' console output:
/u01/app/oracle/diag/rdbms/dbdb/DBdb/trace/DBdb_ora_2065.trc
[oracle@wang ~]$  

這里的row 1flag 變成了--HDFL--


3.2 現在我們用bbed 將刪除的row 1 內容找回來
--編輯BBED的PARFILE參數文件
先查看數據文件
SQL> select file#||' '||name||' '||bytes from v$datafile ;

FILE#||''||NAME||''||BYTES
--------------------------------------------------------------------------------
1 /u01/app/oracle/oradata/DBdb/system01.dbf 2936012800
2 /u01/app/oracle/oradata/DBdb/sysaux01.dbf 723517440
3 /u01/app/oracle/oradata/DBdb/undotbs01.dbf 2710568960
4 /u01/app/oracle/oradata/DBdb/users01.dbf 3207331840
5 /u01/app/oracle/oradata/DBdb/example01.dbf 355205120

--進入bbed窗口

--配置BBED參數文件parfile
--編輯listfile
[oracle@wang ~]$ vi filelist.txt
1 /u01/app/oracle/oradata/DBdb/system01.dbf 2936012800
2 /u01/app/oracle/oradata/DBdb/sysaux01.dbf 723517440
3 /u01/app/oracle/oradata/DBdb/undotbs01.dbf 2710568960
4 /u01/app/oracle/oradata/DBdb/users01.dbf 3207331840
5 /u01/app/oracle/oradata/DBdb/example01.dbf 355205120
"filelist.txt" [New] 6L, 275C written                                                                                                                
[oracle@wang ~]$

--編輯parfile：
[oracle@wang ~]$ vi bbed.par
blocksize=8192
listfile=/home/oracle/filelist.txt
mode=edit
~
"bbed.par" [New] 3L, 60C written                                                                                                                     
[oracle@wang ~]$

--進入bbed窗口
[oracle@wang ~]$ bbed parfile=/home/oracle/bbed.par
Password: blockedit

BBED: Release 2.0.0.0.0 - Limited Production on Tue Nov 28 15:02:40 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

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

BBED> show all
        FILE#           1
        BLOCK#          1
        OFFSET          0
        DBA             0x00400001 (4194305 1,1)
        FILENAME        /u01/app/oracle/oradata/DBdb/system01.dbf
        BIFILE          bifile.bbd
        LISTFILE        /home/oracle/filelist.txt
        BLOCKSIZE       8192
        MODE            Edit
        EDIT            Unrecoverable
        IBASE           Dec
        OBASE           Dec
        WIDTH           80
        COUNT           512
        LOGFILE         log.bbd
        SPOOL           No
        
--修改指定到文件4，塊527：
BED> set dba 4,527 offset 0
        DBA             0x0100020f (16777743 4,527)
        OFFSET          0

BBED> show all
        FILE#           4
        BLOCK#          527
        OFFSET          0
        DBA             0x0100020f (16777743 4,527)
        FILENAME        /u01/app/oracle/oradata/DBdb/users01.dbf
        BIFILE          bifile.bbd
        LISTFILE        /home/oracle/filelist.txt
        BLOCKSIZE       8192
        MODE            Edit
        EDIT            Unrecoverable
        IBASE           Dec
        OBASE           Dec
        WIDTH           80
        COUNT           512
        LOGFILE         log.bbd
        SPOOL           No

--查找Dave所在位置
BBED> find /c Dave
 File: /u01/app/oracle/oradata/DBdb/users01.dbf (4)
 Block: 527              Offsets: 8155 to 8191           Dba:0x0100020f
------------------------------------------------------------------------
 44617665 206c696b 65204f72 61636c65 213c0201 0c446176 65206973 20444241
 210206e0 53

 <32 bytes="" per="" line="">

--翻譯其數據
BBED> d /v dba 4,527 offset 8155
 File: /u01/app/oracle/oradata/DBdb/users01.dbf (4)
 Block: 527     Offsets: 8155 to 8191  Dba:0x0100020f
-------------------------------------------------------
 44617665 206c696b 65204f72 61636c65 l Dave like Oracle
 213c0201 0c446176 65206973 20444241 l !<...Dave is DBA
 210206e0 53                         l !...S

 <16 bytes="" per="" line="">

--前后遷移offset偏移量，發現在offset在8156時發現3c標志
BBED> d /v dba 4,527 offset 8156
 File: /u01/app/oracle/oradata/DBdb/users01.dbf (4)
 Block: 527     Offsets: 8156 to 8191  Dba:0x0100020f
-------------------------------------------------------
 61766520 6c696b65 204f7261 636c6521 l ave like Oracle!
 3c02010c 44617665 20697320 44424121 l <...Dave is DBA!
 0206e053                            l ...S

 <16 bytes="" per="" line="">
這里已經出現了我們3c（deleted）標志，但是注意這里的位置的根據我們的查找的字符串來分的，實際在block里的分割方式不一樣按照我們的offset 來進行。 我們可以通過row directory 來進行一個確認。

--print row directory 確認一下
BBED>  p kdbr
sb2 kdbr[0]                                 @118      8072
sb2 kdbr[1]                                 @120      8051

BBED>  p *kdbr[0]
rowdata[21]
-----------
ub1 rowdata[21]                             @8172     0x3c

BBED> p *kdbr[1]
rowdata[0]
----------
ub1 rowdata[0]                              @8151     0x2c

通過row directory，我們可以確認對應row記錄的row header保存在offset 8172的位置，值為3c。 我們find 字符串的目的就是為了和rowdirectory 中的offset 進行比較。 他們相近時，就可以確定。

--現在我們將@8172位置的3c 變成2c。 即從deleted 變成正常
BBED> modify /x 2c offset 8172
 File: /u01/app/oracle/oradata/DBdb/users01.dbf (4)
 Block: 527              Offsets: 8172 to 8191           Dba:0x0100020f
------------------------------------------------------------------------
 2c02010c 44617665 20697320 44424121 0206e053

 <32 bytes="" per="" line="">

--應用更改：
BBED> sum apply
Check value for File 4, Block 527:
current = 0xf25d, required = 0xf25d

BBED>  

--flush buffer cache，然后查詢
SQL> conn / as sysdba
Connected.
SQL> alter system flush buffer_cache;

System altered.

SQL> conn hr/hr;
Connected.
SQL> select * from dvd;

JOB
--------------------------------------------------------------------------------
Dave is DBA!
Dave like Oracle!

以上是“如何使用bbed恢復delete的rows”這篇文章的所有內容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內容對大家有所幫助，如果還想學習更多知識，歡迎關注億速云行業資訊頻道！