参考：http://blog.csdn.net/tianlesoftware/article/details/6684505
When rows are deleted in Oracle the data is not actually removed. The row is simplymarked as deleted and the free space counters and pointers adjustedaccordingly. The status of a row is stored in the Row Header which occupies thefirst few bytes of each row.
当row 被delete 的时候，实际上data 并没有被remove，只是将该row 标记为delete，然后其对应的空间被统计为free space。 row 的status 存在每个row的row header里。
TheRow Header consists of the Row Flag, Lock Byte (ITL entry) and Column Count.The first of these - the Row Flag - is a single byte that holds a bitmask thatshows the status of the row. The bitmask is decoded as follows:
RowHeader 包含Row Flag，Lock Byte（ITL）和column Count。其中Row Flag占用1个byte，并且以bitmask 来保存。bitmask 的解释如下：
实验如下：
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: 'ktspbwh2: 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!
之前delete 的数据已经恢复出来。。。。。。。。。
When rows are deleted in Oracle the data is not actually removed. The row is simplymarked as deleted and the free space counters and pointers adjustedaccordingly. The status of a row is stored in the Row Header which occupies thefirst few bytes of each row.
当row 被delete 的时候，实际上data 并没有被remove，只是将该row 标记为delete，然后其对应的空间被统计为free space。 row 的status 存在每个row的row header里。
TheRow Header consists of the Row Flag, Lock Byte (ITL entry) and Column Count.The first of these - the Row Flag - is a single byte that holds a bitmask thatshows the status of the row. The bitmask is decoded as follows:
RowHeader 包含Row Flag，Lock Byte（ITL）和column Count。其中Row Flag占用1个byte，并且以bitmask 来保存。bitmask 的解释如下：
实验如下：
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: 'ktspbwh2: 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!
之前delete 的数据已经恢复出来。。。。。。。。。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31397003/viewspace-2147869/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/31397003/viewspace-2147869/
346
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
