经过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
从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> 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> select * from luda.lobtab 2 ; SP2-0678: Column or attribute type can not be displayed by SQL*Plus SQL> select count(*) from luda.lobtab; COUNT(*) ---------- 2 SQL> COLUMN varchar2_content FORMAT A16 SQL> COLUMN date_content FORMAT A12 SQL> COLUMN clob_content FORMAT A20 SQL> 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> 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>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>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> select count(*) from luda.lobtab; COUNT(*) ---------- 2 SQL> COLUMN varchar2_content FORMAT A16 SQL> COLUMN date_content FORMAT A12 SQL> COLUMN clob_content FORMAT A20 SQL> 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有坏块则会造成恢复失败，需要额外处理。
