导航：首页 > 数据库 >

如何使用bbed恢复delete的rows

发表于：2024-11-22 作者：千家信息网编辑

千家信息网最后更新 2024年11月22日，小编给大家分享一下如何使用bbed恢复delete的rows，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！实验如下：1.创

千家信息网最后更新 2024年11月22日如何使用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!