如何使用bbed恢复delete的rows

31次阅读

没有评论

共计 11343 个字符，预计需要花费 29 分钟才能阅读完成。

丸趣 TV 小编给大家分享一下如何使用 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

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

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

************* !!! 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”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注丸趣 TV 行业资讯频道！

正文完