Skip to content

MDATA

Oracle 恢复工具 Mdata 5.0.1 版本发布

经过2016,修复了一些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 person  5.0.1 Download

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结果图作为参考:

unused_column

drop_column

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个月周期都会发步新的下载版本.

下载地址:
http://www.dataunload.net/?/article/20

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的操作,希望对大家有帮助.