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

从ASM DISKS中确定DATAFILE的存储位置

承接ASM恢复时间的研究思考,这里补充从ASM中恢复出数据文件的需要知道细节部分。

首先找到asm的file directory的物理位置:

[oracle@rac1 ~]$ kfed read /dev/raw/raw1 aunum=0 blknum=0
kfbh.endian:                          1 ; 0×000: 0×01
kfbh.hard:                          130 ; 0×001: 0×82
kfbh.type:                            1 ; 0×002: KFBTYP_DISKHEAD
……
kfdhdb.blksize:                    4096 ; 0x0ba: 0×1000
kfdhdb.ausize:                  1048576 ; 0x0bc: 0×00100000
kfdhdb.mfact:                    113792 ; 0x0c0: 0x0001bc80
kfdhdb.dsksize:                    3067 ; 0x0c4: 0x00000bfb
kfdhdb.pmcnt:                         2 ; 0x0c8: 0×00000002
kfdhdb.fstlocn:                       1 ; 0x0cc: 0×00000001
kfdhdb.altlocn:                       2 ; 0x0d0: 0×00000002
kfdhdb.f1b1locn:                      2 ; 0x0d4: 0×00000002

从结果里我们可以看到,file directory的第1个block在AU2上,而从10-12c中的研究分析,ASM的1号文件的file dir的地址都在0号盘的au2上,如果到这里看不懂,研究下asm的metadata文件1-255。

接下来看一下AU2的1号block:

[oracle@rac1 ~]$ kfed read /dev/raw/raw1 aunum=2 blknum=1
kfbh.endian:                          1 ; 0×000: 0×01
kfbh.hard:                          130 ; 0×001: 0×82
kfbh.type:                            4 ; 0×002: KFBTYP_FILEDIR
……
kfffdb.usm:                             ; 0x0a0: length=0
<strong>kfffde[0].xptr.au:                    2 ; 0x4a0: 0×00000002</strong>
kfffde[0].xptr.disk:                  0 ; 0x4a4: 0×0000
kfffde[0].xptr.flags:                 0 ; 0x4a6: L=0 E=0 D=0 C=0 S=0
kfffde[0].xptr.chk:                  40 ; 0x4a7: 0×28
kfffde[1].xptr.au:                    2 ; 0x4a8: 0×00000002
kfffde[1].xptr.disk:                  1 ; 0x4ac: 0×0001
kfffde[1].xptr.flags:                 0 ; 0x4ae: L=0 E=0 D=0 C=0 S=0
kfffde[1].xptr.chk:                  41 ; 0x4af: 0×29
kfffde[2].xptr.au:           4294967294 ; 0x4b0: 0xfffffffe
kfffde[2].xptr.disk:              65534 ; 0x4b4: 0xfffe
kfffde[2].xptr.flags:                 0 ; 0x4b6: L=0 E=0 D=0 C=0 S=0
kfffde[2].xptr.chk:                  42 ; 0x4b7: 0x2a
<strong>kfffde[3].xptr.au:                   93 ; 0x4b8: 0x0000005d</strong>
kfffde[3].xptr.disk:                  0 ; 0x4bc: 0×0000
kfffde[3].xptr.flags:                 0 ; 0x4be: L=0 E=0 D=0 C=0 S=0
kfffde[3].xptr.chk:                 119 ; 0x4bf: 0×77
kfffde[4].xptr.au:                   93 ; 0x4c0: 0x0000005d
kfffde[4].xptr.disk:                  1 ; 0x4c4: 0×0001
kfffde[4].xptr.flags:                 0 ; 0x4c6: L=0 E=0 D=0 C=0 S=0
kfffde[4].xptr.chk:                 118 ; 0x4c7: 0×76
kfffde[5].xptr.au:           4294967294 ; 0x4c8: 0xfffffffe
……
kfffde[39].xptr.disk:             65535 ; 0x5dc: 0xffff

从结果里我们可以知道/dev/raw/raw1只有两个file directory,第一个file directory在AU2上,第二个file directory在AU93上。asm的默认au为1m,每个file的dir大小为4k,所以每个au最多只能存储256个file,asm1-255file为metadata,所以通常的数据库数据文件都在第二个AU后(这里建议关注下,如果datafile所占有的au数量非常多,4k信息装不下怎么办?)。
在这里1M AU的情况下对于第二个file directory而言,datafile 1就在block 0里,datafile 2就在block 1里,依次类推,所以datafile 4在block 3里(au的基础单元为os块4k大小),下来观察这个block:

[oracle@rac1 ~]$ kfed read /dev/raw/raw1 aunum=93 blknum=3
kfbh.endian:                          1 ; 0×000: 0×01
kfbh.hard:                          130 ; 0×001: 0×82
kfbh.type:                            4 ; 0×002: KFBTYP_FILEDIR
kfbh.datfmt:                          1 ; 0×003: 0×01
kfbh.block.blk:                     259 ; 0×004: T=0 NUMB=0×103
kfbh.block.obj:                       1 ; 0×008: TYPE=0×0 NUMB=0×1
……
kfffdb.spare[15]:                     0 ; 0x09c: 0×00000000
kfffdb.usm:                             ; 0x0a0: length=0
kfffde[0].xptr.au:                  211 ; 0x4a0: 0×000000d3
kfffde[0].xptr.disk:                  1 ; 0x4a4: 0×0001
kfffde[0].xptr.flags:                 0 ; 0x4a6: L=0 E=0 D=0 C=0 S=0
kfffde[0].xptr.chk:                 106 ; 0x4a7: 0x6a
kfffde[1].xptr.au:                  211 ; 0x4a0: 0×000000d3
kfffde[1].xptr.disk:                  0 ; 0x4ac: 0×0000
kfffde[1].xptr.flags:                 0 ; 0x4ae: L=0 E=0 D=0 C=0 S=0
kfffde[1].xptr.chk:                 107 ; 0x4af: 0x6b
kfffde[2].xptr.au:                  212 ; 0x4b0: 0×000000d4
kfffde[2].xptr.disk:                  0 ; 0x4b4: 0×0000
kfffde[2].xptr.flags:                 0 ; 0x4b6: L=0 E=0 D=0 C=0 S=0
kfffde[2].xptr.chk:                 106 ; 0x4b7: 0x6a
kfffde[3].xptr.au:                  212 ; 0x4b0: 0×000000d4
kfffde[3].xptr.disk:                  1 ; 0x4bc: 0×0001
kfffde[3].xptr.flags:                 0 ; 0x4be: L=0 E=0 D=0 C=0 S=0
kfffde[3].xptr.chk:                 107 ; 0x4bf: 0x6b
kfffde[4].xptr.au:                  213 ; 0x4c0: 0×000000d5
kfffde[4].xptr.disk:                  1 ; 0x4c4: 0×0001
kfffde[4].xptr.flags:                 0 ; 0x4c6: L=0 E=0 D=0 C=0 S=0
kfffde[4].xptr.chk:                 108 ; 0x4c7: 0x6c
kfffde[5].xptr.au:                  213 ; 0x4c0: 0×000000d5
kfffde[5].xptr.disk:                  0 ; 0x4cc: 0×0000
kfffde[5].xptr.flags:                 0 ; 0x4ce: L=0 E=0 D=0 C=0 S=0
kfffde[5].xptr.chk:                 109 ; 0x4cf: 0x6d
kfffde[6].xptr.au:                  214 ; 0x4d0: 0×000000d6
kfffde[6].xptr.disk:                  0 ; 0x4d4: 0×0000
kfffde[6].xptr.flags:                 0 ; 0x4d6: L=0 E=0 D=0 C=0 S=0
kfffde[6].xptr.chk:                 108 ; 0x4d7: 0x6c
kfffde[7].xptr.au:                  214 ; 0x4d0: 0×000000d6
kfffde[7].xptr.disk:                  1 ; 0x4dc: 0×0001
kfffde[7].xptr.flags:                 0 ; 0x4de: L=0 E=0 D=0 C=0 S=0
kfffde[12].xptr.au:          4294967295 ; 0×500: 0xffffffff
kfffde[12].xptr.disk:             65535 ; 0×504: 0xffff
……
kfffde[23].xptr.au:          4294967295 ; 0×558: 0xffffffff
kfffde[23].xptr.disk:             65535 ; 0x55c: 0xffff

根据上面的信息可以发现datafile 4在/dev/raw/raw1上的物理存储位置可以看到datafile 4一共占了6个AU,分别是AU211,AU212,AU213,AU214.

使用MDATA恢复Oracle中truncate的数据

创建测试表并模拟truncate的场景:

SQL> create table trutab as select * from t1;

Table created.

SQL> select count(*) from trutab;

  COUNT(*)
----------
     10000

SQL> commit
  2  ;

Commit complete.

SQL>
SQL>
SQL> truncate table trutab;

Table truncated.

SQL> select count(*) from trutab;

  COUNT(*)
----------
         0

SQL> select segment_name,tablespace_name from dba_segments where segment_name='TRUTAB' and owner='LUDA';

SEGMENT_NAME                             TABLESPACE_NAME
---------------------------------------- ----------------------------------------
TRUTAB                                   USERS

加载mdata

MDATA>reload dict
Start reload dict,Mon Jan 12 13:46:45 CST 2015
load BOOTSTRAP$ success.
load TAB$ success.
load COL$ success.
load OBJ$ success.
load USER$ success.
load PROPS$ success.
load TABPART$ success.
load TABSUBPART$ success.
load IND$ success.
End reload dict,Mon Jan 12 13:46:51 CST 2015,reload success.
MDATA>list table luda
OWNER                         TABLE
---------------               --------------
LUDA                          T2
LUDA                          T1
LUDA                          TRUTAB
owner:LUDA has 3  rows selected.

通过scan的方式扫描被truncate的表所在的tablespace

MDATA>unload table luda.trutab
unload schema:LUDA;tab:TRUTAB
tabName:LUDA.TRUTAB,dic_obj:com.olm.b.H@f0ca71,getFileid:4,getBlockid:195
0 rows unloaded
MDATA>scan extent tablespace 4 datafile 4
scan extent start: Mon Jan 12 13:48:14 CST 2015
scanning extent...
scanning extent finished.
scan extent completed: Mon Jan 12 13:48:15 CST 2015
MDATA>unload table luda.trutab
unload schema:LUDA;tab:TRUTAB
tabName:LUDA.TRUTAB,dic_obj:com.olm.b.H@26e5d8,getFileid:4,getBlockid:195
0 rows unloaded
MDATA>scan extent tablespace 4 datafile 4 auto
scan extent [tablespace <ts #> [datafile <rfile #>] ] [object <data_object_data>] parallel [parallel_degree]

scan做操完成后,对该表执行unload操作

MDATA>unload table luda.trutab object auto
Auto mode truncated table.
unload schema:LUDA;tab:TRUTAB
tabName:LUDA.TRUTAB,dic_obj:com.olm.b.H@cd1365,getFileid:4,getBlockid:195
10000 rows unloaded
MDATA>

导出恢复的数据后,通过sqlload工具将导出的数据恢复到trutab表中:

[oracle@DB01 data]$ sqlldr luda/luda control=LUDA.TRUTAB.ctl

SQL*Loader: Release 10.2.0.4.0 - Production on Mon Jan 12 13:52:44 2015

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Commit point reached - logical record count 2501
Commit point reached - logical record count 5002
Commit point reached - logical record count 7503
Commit point reached - logical record count 10000

恢复完成后验证恢复:

[oracle@DB01 data]$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Jan 12 13:52:54 2015

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select count(*) from luda.trutab;

  COUNT(*)
----------
     10000

至此truncate的恢复已经完成。提示一个小技巧,mdata的scan功能支持并行模式

使用MDATA恢复Oracle误删除(deleted)的数据

构建测试的环境并模拟删除数据:

SQL> create user luda identified by luda default tablespace users;

User created.

SQL> grant dba to luda;

Grant succeeded.

SQL> alter system checkpoint;

System altered.

SQL>
SQL> conn luda/luda
Connected.
SQL> create table t1 as select * from dba_objects where rownum < 10001;

Table created.

SQL> create table t2 as select object_id,object_name from dba_objects where rownum < 100;

Table created.

SQL> commit;

Commit complete.

SQL> alter system checkpoint;

System altered.

#rows before delete from luda.t2
SQL> select count(*) from t2;

  COUNT(*)
----------
        99

#delete 90rows from luda

SQL> delete from luda.t2 where rownum < 91;

90 rows deleted.

SQL> commit;

Commit complete.

SQL> select count(*) from luda.t2;

  COUNT(*)
----------
         9

SQL> alter system checkpoint;

System altered.

加载mdata

#using mdata unload data that have deleted from luda.t2
MDATA>reload dict
Start reload dict,Sun Jan 11 00:01:50 CST 2015
SQLException: State:   X0X95 Severity: 30000
Operation 'TRUNCATE TABLE' cannot be performed on object 'IDUL_OBJ' because there is an open ResultSet dependent on that object.
load BOOTSTRAP$ success.
load TAB$ success.
load COL$ success.
load OBJ$ success.
load USER$ success.
load PROPS$ success.
load TABPART$ success.
load TABSUBPART$ success.
load IND$ success.
End reload dict,Sun Jan 11 00:01:56 CST 2015,reload success.

通过unload的功能恢复出delete数据

MDATA>unload table luda.t2 only deleted data
load only deleted data,schema:LUDA;tab:T2
tabName:LUDA.T2,dic_obj:com.olm.b.H@2d6636,getFileid:4,getBlockid:187
90 rows unloaded

退出mdata切换到恢复出数据的目录,这时候将恢复出的数据使用sqlloadl恢复到表中

#then u can find the unload data at /oracle/mdata/data
[root@DB01 data]# ls -lFrt
-rw-r--r-- 1 oracle oinstall    1042 Jan 11 00:02 LUDA.T2.dat
-rw-r--r-- 1 oracle oinstall     204 Jan 11 00:02 LUDA.T2.ctl
...

这里介绍2个delete的功能
第一种是unload表数据时候包含被删除数据

using mdata unload luda.t2's data contrain deleted data
MDATA>unload table luda.t2 contain deleted data
unload schema:LUDA;tab:T2 contain deleted data
tabName:LUDA.T2,dic_obj:com.olm.b.H@a0b1e1,getFileid:4,getBlockid:187
99 rows unloaded

第二种是unload表数据库时候不包含被删除数据

# using mdata unload luda.t2's data is not contrain deleted data
MDATA>unload table luda.t2
unload schema:LUDA;tab:T2
tabName:LUDA.T2,dic_obj:com.olm.b.H@1c76b8d,getFileid:4,getBlockid:187
9 rows unloaded

使用MDATA恢复Oracle丢失的lob数据(DELETE以及TRUNCATE通用)

lob data recover
构建包含lob数据的表

SQL&gt; desc lobtab
Name Null? Type
----------------------------------------- -------- ----------------------------
NUMBER_CONTENT NUMBER(10)
VARCHAR2_CONTENT VARCHAR2(100)
DATE_CONTENT DATE
CLOB_CONTENT CLOB
BLOB_CONTENT BLOB

[oracle@DB01 ~]$ ls
blob_01.lob blob_02.lob clob_01.lob clob_02.lob lob_data_test.txt lobtab.sql
[oracle@DB01 ~]$ cat blob_01.lob
luda's blob data 01
[oracle@DB01 ~]$ cat blob_02.lob
luda's blob data 02
[oracle@DB01 ~]$ cat clob_02.lob
luda's clob data 02
[oracle@DB01 ~]$ cat clob_01.lob
luda's clob data 01

[oracle@DB01 ~]$ cat lobtab.ctl
LOAD DATA
INFILE 'lob_data_test.txt'
INTO TABLE lobtab
FIELDS TERMINATED BY ','
(number_content CHAR(10),
varchar2_content CHAR(100),
date_content DATE "DD-MON-YYYY" ":date_content",
clob_filename FILLER CHAR(100),
clob_content LOBFILE(clob_filename) TERMINATED BY EOF,
blob_filename FILLER CHAR(100),
blob_content LOBFILE(blob_filename) TERMINATED BY EOF)
--将lob数据导入到表中
[oracle@DB01 ~]$ sqlldr luda/luda control=lobtab.ctl log=lob_tab.log bad=lob_tab.bad

SQL*Loader: Release 10.2.0.4.0 - Production on Thu Jan 15 00:39:40 2015

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Commit point reached - logical record count 2
[oracle@DB01 ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Jan 15 00:39:48 2015

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
--查询lob数据
SQL&gt; select * from luda.lobtab
2 ;
SP2-0678: Column or attribute type can not be displayed by SQL*Plus
SQL&gt; select count(*) from luda.lobtab;

COUNT(*)
----------
2

SQL&gt; COLUMN varchar2_content FORMAT A16
SQL&gt; COLUMN date_content FORMAT A12
SQL&gt; COLUMN clob_content FORMAT A20
SQL&gt; SELECT number_content,varchar2_content,TO_CHAR(date_content, 'DD-MON-YYYY') AS date_content,clob_content,DBMS_LOB.getlength(blob_content) AS blob_length
2 FROM luda.lobtab;

NUMBER_CONTENT VARCHAR2_CONTENT DATE_CONTENT CLOB_CONTENT BLOB_LENGTH
-------------- ---------------- ------------ -------------------- -----------
1 one 01-JAN-2006 luda's clob data 01 20
2 two 02-JAN-2006 luda's clob data 02 20

模拟truncate lob数据

SQL&gt; truncate table luda.lobtab

加载mdata

loading default config.......
load config file 'config.txt' successful
loading default asm disk file ......
start loading default control file ......
load control file 'control.txt' successful
load BOOTSTRAP$ success.
load TAB$ success.
load COL$ success.
load OBJ$ success.
load USER$ success.
load PROPS$ success.
load TABPART$ success.
load TABSUBPART$ success.
load IND$ success.

通过scan的方式扫描该表所在tablespace

MDATA&gt;scan extent tablespace 4 datafile 4
scan extent start: Thu Jan 15 00:54:43 CST 2015
scanning extent...
scanning extent finished.
scan extent completed: Thu Jan 15 00:54:43 CST 2015

通过unload恢复出被truncate的lob数据

MDATA&gt;unload table luda.lobtab object auto
Auto mode truncated table.
unload schema:LUDA;tab:LOBTAB
. unloading (index organized) table LOB010001e3
Preparing lob metadata from lob index
Reading LOB010001e3.dat 0 entries loaded and sorted 0 entries

. unloading (index organized) table LOB010001d3
Preparing lob metadata from lob index
Reading LOB010001d3.dat 0 entries loaded and sorted 0 entries

tabName:LUDA.LOBTAB,dic_obj:com.olm.b.H@4e94a4,getFileid:4,getBlockid:451
2 rows unloaded

切换到恢复数据的存放目录

[oracle@DB01 data]$ pwd
/oracle/mdata/data

通过sqlload加载恢复出的数据

[oracle@DB01 data]$ sqlldr luda/luda control=LUDA.LOBTAB.ctl log=LUDA.LOBTAB.log

SQL*Loader: Release 10.2.0.4.0 - Production on Thu Jan 15 00:57:15 2015

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Commit point reached - logical record count 2

加载完成后验证恢复后的lob数据

SQL&gt; select count(*) from luda.lobtab;

COUNT(*)
----------
2
SQL&gt; COLUMN varchar2_content FORMAT A16
SQL&gt; COLUMN date_content FORMAT A12
SQL&gt; COLUMN clob_content FORMAT A20
SQL&gt; SELECT number_content,varchar2_content,TO_CHAR(date_content, 'DD-MON-YYYY') AS date_content,clob_content,DBMS_LOB.getlength(blob_content) AS blob_length
2 FROM luda.lobtab;

NUMBER_CONTENT VARCHAR2_CONTENT DATE_CONTENT CLOB_CONTENT BLOB_LENGTH
-------------- ---------------- ------------ -------------------- -----------
1 one 01-JAN-2006 luda's clob data 01 20
2 two 02-JAN-2006 luda's clob data 02 20

恢复lob的关键在于lob index所在的块的保存,如果lob index有坏块则会造成恢复失败,需要额外处理。