无外乎delete的操作最终的变更导向也是指向底层数据块部分,所以在这部分需要了解数据块的基本构成以及行peice的结构.
先看以下的data block structure explain.
KTB Data Block (Table/Cluster)
struct kcbh, 20 bytes @0 --Cache Layer
ub1 type_kcbh @0 -- Block type (see Header Block Types below)
ub1 frmt_kcbh @1 --Block format 1=Oracle 7, 2=Oracle 8+
ub1 spare1_kcbh @2 --Not used
ub1 spare2_kcbh @3 --Not used
ub4 rdba_kcbh @4 --RDBA -Relative Data Block Address
ub4 bas_kcbh @8 --SCN Base
ub2 wrp_kcbh @12 --SCN Wrap
ub1 seq_kcbh @14 --Sequence number, incremented for every change made to the block at the same SCN
ub1 flg_kcbh @15 --Flag:
0x01 New Block
0x02 Delayed Logging Change advanced SCN/seq
0x04 Check value saved - block XOR‘s to zero
0x08 Temporary block
ub2 chkval_kcbh @16 --Optional block checksum (if DB_BLOCK_CHECKSUM=TRUE)
ub2 spare3_kcbh @18 --Not used
struct ktbbh, 72 bytes @20 --Transaction Layer (Transaction Fixed Header Structure)
ub1 ktbbhtyp @20 --Block type (1=DATA, 2=INDEX)
union ktbbhsid, 4 bytes @24 --DATA OBJECT ID
struct ktbbhcsc, 8 bytes @28 --SCN at last block cleanout
b2 ktbbhict @36 --Number of ITL slots
ub1 ktbbhflg @38 --0=on the freelist
ub1 ktbbhfsl @39 --ITL TX freelist slot
ub4 ktbbhfnx @40 -- DBA of next block on the freelist
struct ktbbhitl[2], 48 bytes @44 --ITL list index
struct kdbh, 14 bytes @100 --Data Header (Data Header Structure )
ub1 kdbhflag @100 --N=pctfree hit(clusters); F=do not put on freelist; K=flushable cluster keys
b1 kdbhntab @101 --Number of tables (>1 in clusters)
b2 kdbhnrow @102 --Number of rows
sb2 kdbhfrre @104 --First free row entry index; -1 = you have to add one
sb2 kdbhfsbo @106 --Freespace begin offset
sb2 kdbhfseo @108 --Freespace end offset
b2 kdbhavsp @110 --Available space in the block
b2 kdbhtosp @112 --Total available space when all TXs commit
struct kdbt[1], 4 bytes @114 -- Table Directory
b2 kdbtoffs @114
b2 kdbtnrow @116
sb2 kdbr[4] @118 --Row Directory
ub1 freespace[7970] @126 -- Free Space
ub1 rowdata[92] @8096 -- Row Data
ub4 tailchk @8188 --Tailchk
一般删除数据指的对block type为05的data block上的row piece进行操作,在data block中与行有关的部分主要为row directory(kdbr)以及rowdata.
kdbr里面包含相关信息如下:
BBED> p kdbr --p kdbr打印出row directory的信息。 sb2 kdbr[0] @118 8062 sb2 kdbr[1] @120 8040 sb2 kdbr[2] @122 8020 sb2 kdbr[3] @124 7996 BBED> p *kdbr[0] rowdata[66] ----------- ub1 rowdata[66] @8162 0x2c BBED> p *kdbr[1] rowdata[44] ----------- ub1 rowdata[44] @8140 0x2c BBED> p *kdbr[2] rowdata[24] ----------- ub1 rowdata[24] @8120 0x2c
上面显示，块中有4条记录，记录1开始于数据块偏移量8162，记录2开始于数据块偏移量8140，记录3开始于数据块偏移量8020。后面的0x2c 就是行头的FLAG值.
行头标记数据行头的一部分信息,在oracle程序kdr4.h部分记录着row header的定义,如下:
Row Overhead The values for the row flag are: #define KDRHFK 0x80 Cluster Key #define KDRHFC 0x40 Clustered table member #define KDRHFH 0x20 Head piece of row #define KDRHFD 0x10 Deleted row #define KDRHFF 0x08 First data piece| #define KDRHFL 0x04 Last data piece #define KDRHFP 0x02 First column continues from Previous piece #define KDRHFN 0x01 Last column continues in Next piece
Row flag的算法有以下几种
1.普通表的行标记:
ROW_HEAD_PIECE + ROW_DELETED_ROW + ROW_FIRST_PIECE + ROW_LAST_PIECE= KDRHFH + KDRHFD + KDRHFF +KDRHFL
2.簇键的标记:
ROW_CLUSTER_KEY + ROW_HEAD_PIECE + ROW_FIRST_PIECE + ROW_LAST_PIECE + ROW_DELETED_ROW= KDRHFK KDRHFH + KDRHFD + KDRHFF +KDRHFL
3.簇表的行标记:
ROW_CTABLE_NUMBER + ROW_HEAD_PIECE + ROW_FIRST_PIECE + ROW_LAST_PIECE + ROW_DELETED_ROW= KDRHFK KDRHFH + KDRHFD + KDRHFF +KDRHFL
在这几种标记中,关键为row_deleted_row,让行被删除时候,KDRHFD变为0x10,未被删除则为0.
这里以普通表为例:
当行被删除时候,row flag为0x20+0x10+0x08+0x04=0x3c
当行未被删除时,row flag为0x20+0x08+0x04=0x2c
下面为验证测试案例,以Scott用户下的dept表为例.如果dept中的行记录被删除了，那么相应的行flag值将会变为0x3c ，如下：
SQL> select * from scott.dept;
DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> delete from scott.dept; ---删除数据
4 rows deleted.
SQL> commit;
Commit complete.
SQL> alter system checkpoint;
System altered.
SQL> select * from scott.dept; --验证删除
no rows selected
SQL>
登入bbed
BBED> set filename '/orcl/orcl/users01.dbf';
FILENAME /orcl/orcl/users01.dbf
BBED> set block 16 --通过rowid定位行所在块地址
BLOCK# 16
BBED> print kdbr --查看行目录,4行数据都还在
sb2 kdbr[0] @118 7970
sb2 kdbr[1] @120 8040
sb2 kdbr[2] @122 8020
sb2 kdbr[3] @124 7996
BBED> print *kdbr[0]
rowdata[0]
----------
ub1 rowdata[0] @8070 0x3c -- 行flag已被置为0x3c
BBED> print *kdbr[1]
rowdata[70]
-----------
ub1 rowdata[70] @8140 0x3c
BBED> print *kdbr[2]
rowdata[50]
-----------
ub1 rowdata[50] @8120 0x3c
BBED> print *kdbr[3]
rowdata[26]
-----------
ub1 rowdata[26] @8096 0x3c
可以看到原来的记录不光偏移量变了，标志位也变为了0x3c，也就是DELETE标志位被设置为了1。其实到了这里要知道如何恢复delete的数据已经非常明了,只需要针对row header部分的row_deleted_row做一些文章即可恢复出delete的数据,比如MDATA的批量恢复delete数据处理功能即是如此(需要首先加入object_id的定位表块).
Mdata恢复delete的恢复实践:
http://www.ludatou.com/?p=2016
这里把标志位0x3c改为0x2c，被删除的4行数据将会重新找回来。如下：
BBED> set offset 8070
OFFSET 8070
BBED> dump /v count 16
File: /orcl/orcl/users01.dbf (0)
Block: 16 Offsets: 8070 to 8085 Dba:0x00000000
-------------------------------------------------------
3c020302 c10b0a41 43434f55 4e54494e l <......ACCOUNTIN
<16 bytes per line>
BBED> set mode edit --设置编辑模式
MODE Edit
BBED> modify /x 2c --更改row_deleted_row为非删除状态
File: /orcl/orcl/users01.dbf (0)
Block: 16 Offsets: 8070 to 8085 Dba:0x00000000
------------------------------------------------------------------------
2c020302 c10b0a41 43434f55 4e54494e
<32 bytes per line>
BBED> set offset 8140
OFFSET 8140
BBED> dump /v count 16
File: /orcl/orcl/users01.dbf (0)
Block: 16 Offsets: 8140 to 8155 Dba:0x00000000
-------------------------------------------------------
3c020302 c1150852 45534541 52434806 l <......RESEARCH.
<16 bytes per line>
BBED> modify /x 2c
File: /orcl/orcl/users01.dbf (0)
Block: 16 Offsets: 8140 to 8155 Dba:0x00000000
------------------------------------------------------------------------
2c020302 c1150852 45534541 52434806
<32 bytes per line>
BBED> set offset 8120
OFFSET 8120
BBED> dump /v count 16
File: /orcl/orcl/users01.dbf (0)
Block: 16 Offsets: 8120 to 8135 Dba:0x00000000
-------------------------------------------------------
3c020302 c11f0553 414c4553 07434849 l <......SALES.CHI
<16 bytes per line>
BBED> modify /x 2c
File: /orcl/orcl/users01.dbf (0)
Block: 16 Offsets: 8120 to 8135 Dba:0x00000000
------------------------------------------------------------------------
2c020302 c11f0553 414c4553 07434849
<32 bytes per line>
BBED> set offset 8096
OFFSET 8096
BBED> dump /v count 16
File: /orcl/orcl/users01.dbf (0)
Block: 16 Offsets: 8096 to 8111 Dba:0x00000000
-------------------------------------------------------
3c020302 c1290a4f 50455241 54494f4e l <....).OPERATION
<16 bytes per line>
BBED> modify /x 2c
File: /orcl/orcl/users01.dbf (0)
Block: 16 Offsets: 8096 to 8111 Dba:0x00000000
------------------------------------------------------------------------
2c020302 c1290a4f 50455241 54494f4e
<32 bytes per line>
BBED> sum apply --提交变更
Check value for File 0, Block 16:
current = 0x26ff, required = 0x26ff
BBED> verify --验证块
DBVERIFY - Verification starting
FILE = /orcl/orcl/users01.dbf
BLOCK = 16
Block Checking: DBA = 16777232, Block Type = KTB-managed data block
data header at 0x110203064
kdbchk: the amount of space used is not equal to block size
used=118 fsc=84 avsp=7970 dtl=8088
Block 16 failed with check code 6110
DBVERIFY - Verification complete
Total Blocks Examined : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing (Data) : 1
Total Blocks Processed (Index): 0
Total Blocks Failing (Index): 0
Total Blocks Empty : 0
Total Blocks Marked Corrupt : 0
Total Blocks Influx : 0
BBED> print *kdbr[0]
rowdata[0]
----------
ub1 rowdata[0] @8070 0x2c
BBED> print *kdbr[1]
rowdata[70]
-----------
ub1 rowdata[70] @8140 0x2c
BBED> print *kdbr[2]
rowdata[50]
-----------
ub1 rowdata[50] @8120 0x2c
BBED> print *kdbr[3]
rowdata[26]
-----------
ub1 rowdata[26] @8096 0x2c
BBED> x /rncc offset 8070
rowdata[0] @8070
----------
flag@8070: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8071: 0x02
cols@8072: 3
col 0[2] @8073: 10
col 1[10] @8076: ACCOUNTING
col 2[8] @8087: NEW YORK
BBED> x /rncc offset 8140
rowdata[70] @8140
-----------
flag@8140: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8141: 0x02
cols@8142: 3
col 0[2] @8143: 20
col 1[8] @8146: RESEARCH
col 2[6] @8155: DALLAS
BBED> x /rncc offset 8120
rowdata[50] @8120
-----------
flag@8120: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8121: 0x02
cols@8122: 3
col 0[2] @8123: 30
col 1[5] @8126: SALES
col 2[7] @8132: CHICAGO
BBED> x /rncc offset 8096
rowdata[26] @8096
-----------
flag@8096: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8097: 0x02
cols@8098: 3
col 0[2] @8099: 40
col 1[10] @8102: OPERATIONS
col 2[6] @8113: BOSTON
再次回到sqlplus窗口，即可发现数据已经找回来：
SQL> select * from scott.dept;
no rows selected
SQL> alter system flush buffer_cache;
System altered.
SQL> select * from scott.dept;
DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
到此,从底层的角度解释了delete的操作,希望对大家有帮助.
大头，牛逼！