经过2016到2018，修复了一些bug.
现在从新开放下载.企业版新增table drop后的lob恢复.
使用须知：
1.新版本下载后，建议使用jdk 1.4或者jdk 1.5运行.
2.新版不限制数据恢复量，不限制恢复行数，依然免费使用
3.新版程序绑定机器，需发邮件给我们提供运行时候的feature code，我们将根据feature code 生成注册码。
4.使用前请阅读下载文件中的使用说明书
联系邮箱：
feigigi@qq.com;
564439763@qq.com
下载地址：
MDATA
unused column 和 drop column操作实质
unused column和drop column的操作从本质上讲是不一样的，unused是通过变更数据字典的信息让sql无法访问到column，而drop是直接在物理数据层做了变动。这里的操作后台跟踪可以用event 10046去验证,这里不做描述.
下面通过实验的方式验证unused和drop column的操作对比情况:
1.创建测试表
SQL> grant dba to luda;
Grant succeeded.
SQL> conn luda/luda
Connected.
SQL> create table luda_t1 as select * from dba_objects;
Table created.
SQL> set timing on
SQL> set serverout on
SQL> exec showspace('LUDA_T1','LUDA')
Total Blocks............................768
Total Bytes.............................6291456
Unused Blocks...........................53
Unused Bytes............................434176
Last Used Ext FileId....................4
Last Used Ext BlockId...................2953
Last Used Block.........................75
*************************************************
The segment is analyzed
0% -- 25% free space blocks.............0
0% -- 25% free space bytes..............0
25% -- 50% free space blocks............0
25% -- 50% free space bytes.............0
50% -- 75% free space blocks............0
50% -- 75% free space bytes.............0
75% -- 100% free space blocks...........0
75% -- 100% free space bytes............0
Unused Blocks...........................0
Unused Bytes............................0
Total Blocks............................695
Total bytes.............................5693440
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
SQL> select b.name,a.value from v$mystat a,v$statname b where a.statistic#=b.statistic# and b.name='redo size';
NAME VALUE
---------------------------------------------------------------- ----------
redo size 80916
Elapsed: 00:00:00.01
SQL> select b.name,a.value from v$mystat a,v$statname b where a.statistic#=b.statistic# and b.name='undo change vector size';
NAME VALUE
---------------------------------------------------------------- ----------
undo change vector size 21012
Elapsed: 00:00:00.00
2.设置object_name为unused
SQL> alter table luda_t1 set unused column object_name;
Table altered.
Elapsed: 00:00:00.02
SQL> exec showspace('LUDA_T1','LUDA') ---- 对比操作前,可以发现luda_t1表存储信息未有变动
Total Blocks............................768
Total Bytes.............................6291456
Unused Blocks...........................53
Unused Bytes............................434176
Last Used Ext FileId....................4
Last Used Ext BlockId...................2953
Last Used Block.........................75
*************************************************
The segment is analyzed
0% -- 25% free space blocks.............0
0% -- 25% free space bytes..............0
25% -- 50% free space blocks............0
25% -- 50% free space bytes.............0
50% -- 75% free space blocks............0
50% -- 75% free space bytes.............0
75% -- 100% free space blocks...........0
75% -- 100% free space bytes............0
Unused Blocks...........................0
Unused Bytes............................0
Total Blocks............................695
Total bytes.............................5693440
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
SQL> select b.name,a.value from v$mystat a,v$statname b where a.statistic#=b.statistic# and b.name='redo size';
NAME VALUE
---------------------------------------------------------------- ----------
redo size 92176
Elapsed: 00:00:00.00
---产生了少量redo日志
SQL> select b.name,a.value from v$mystat a,v$statname b where a.statistic#=b.statistic# and b.name='undo change vector size';
NAME VALUE
---------------------------------------------------------------- ----------
undo change vector size 25212
Elapsed: 00:00:00.00
--产生了少量undo
3.执行drop unused column
SQL> alter table luda_t1 drop unused column;
Table altered.
Elapsed: 00:00:00.26
SQL> exec showspace('LUDA_T1','LUDA') --对比操作前可以发现在freespace层面25%-50%多出了642的block
Total Blocks............................768
Total Bytes.............................6291456
Unused Blocks...........................53
Unused Bytes............................434176
Last Used Ext FileId....................4
Last Used Ext BlockId...................2953
Last Used Block.........................75
*************************************************
The segment is analyzed
0% -- 25% free space blocks.............0
0% -- 25% free space bytes..............0
25% -- 50% free space blocks............642
25% -- 50% free space bytes.............5259264
50% -- 75% free space blocks............0
50% -- 75% free space bytes.............0
75% -- 100% free space blocks...........0
75% -- 100% free space bytes............0
Unused Blocks...........................0
Unused Bytes............................0
Total Blocks............................53 --总占用block降低为53个,总块数不变642+53=695
Total bytes.............................434176
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
SQL> select b.name,a.value from v$mystat a,v$statname b where a.statistic#=b.statistic# and b.name='redo size';
NAME VALUE
---------------------------------------------------------------- ----------
redo size 12393932
--产生大量的redo日志相对比上一次操作的redo量
Elapsed: 00:00:00.01
SQL> select b.name,a.value from v$mystat a,v$statname b where a.statistic#=b.statistic# and b.name='undo change vector size';
NAME VALUE
---------------------------------------------------------------- ----------
undo change vector size 5128064
--产生大量的undo信息相对比上一次操作的undo量
Elapsed: 00:00:00.00
SQL>
4.执行drop column的测试
SQL> alter table luda_t1 drop column object_type;
Table altered.
Elapsed: 00:00:00.25
SQL> exec showspace('LUDA_T1','LUDA') -- drop 操作效果与drop unused一致,释放空间,降低高水位
Total Blocks............................768
Total Bytes.............................6291456
Unused Blocks...........................53
Unused Bytes............................434176
Last Used Ext FileId....................4
Last Used Ext BlockId...................2953
Last Used Block.........................75
*************************************************
The segment is analyzed
0% -- 25% free space blocks.............0
0% -- 25% free space bytes..............0
25% -- 50% free space blocks............664
25% -- 50% free space bytes.............5439488
50% -- 75% free space blocks............1
50% -- 75% free space bytes.............8192
75% -- 100% free space blocks...........0
75% -- 100% free space bytes............0
Unused Blocks...........................0
Unused Bytes............................0
Total Blocks............................30
Total bytes.............................245760
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
SQL>
SQL> select b.name,a.value from v$mystat a,v$statname b where a.statistic#=b.statistic# and b.name='redo size';
NAME VALUE
---------------------------------------------------------------- ----------
redo size 23902388
--产生大量的redo日志相对比上一次操作的redo量
Elapsed: 00:00:00.01
SQL> select b.name,a.value from v$mystat a,v$statname b where a.statistic#=b.statistic# and b.name='undo change vector size';
--产生大量的undo信息相对比上一次操作的undo量
NAME VALUE
---------------------------------------------------------------- ----------
undo change vector size 9439452
Elapsed: 00:00:00.00
SQL>
这里验几个情况:
1.unused column只产生少量的redo和undo,真实在表存储部分并未做变动,高水位线没有变动.真实数据部分并未被oracle处理,而根据trace信息可以发现unused column是在数据字典层面做的变动,对被unused操作的字段打上对于的flag.
2.drop unused column 操作会对被标记为unused flag的数据进行rewrite(trace可以发现)并释放空间,降低高水位,同时产生大量的redo和undo.
3.drop column操作会对整个字段物理数据部分直接进行删除(bbed可以发现),并更新table entries.同时降低高水位产生大量的redo和undo.相当于一次数据重组.
以下的set unused 以及drop column的trace结果图作为参考:
Mdata 4.0.3 更新下载 新增asm unload table
版本更新到4之后,后面的版本一直没有放开下载,因为要修复验证bug.经过一段时间的测试,修复了部分已经发现的bug,比如system多数据文件情况下识别错误,dmp格式导出后无法导入的问题.
放在这个论坛上下载是为了把Mdata的问题提交到该站上面,这样Mdata维护以及更新起来较为方便.Mdata一直都是免费使用,是程序难免有bug,我们从Mdata 1.0版本到现在更新了如此多的版本就是不断的修复各种bug.我和hc都是利用工作之外的时间来处理,因此还请谅解!
大家以后碰到mdata的使用问题或者报错请发截图以及日志到Mdata使用问题版块上,我们会跟进处理以及更新.
目前最新版本为Mdata 4.0.3 ,新增在Oracle asm instance下直接unload table的功能.
下载选择附件.以后每2个月周期都会发步新的下载版本.
Mdata针对column删除恢复的支持情况说明
从前面分析的unused column和drop column的实质结果上可以获得初步的结论，在使用drop column后是无法使用恢复工具恢复这部分drop的数据的，dul也好mdata也罢都回天无力，只能借助有备份的情况下基于时间点的恢复或者oracle12c中的表恢复功能。
而由于unused的特性，使得unused column部分的时间是可以通过mdata恢复的，需要使用到drop table的恢复机制，通过指定恢复表的字段属性列表，还有objdataid来恢复对应的数据。这部分可以用现用的mdata 3.62版本测试。这里不作描述。
测试案例：
1
从行构成的微观角度解析Delete操作的实质
无外乎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的操作,希望对大家有帮助.
