在有次8i的库恢复中,因为硬盘损坏导致几个表出现很多诡异性坏块,尝试使用dul对其进行挖掘数据,当时使用dul 9 遇到一个难题:当一张表中有lob类型,同时又有varchar2类型,而且varchar2类型数据中包含回车键,使得解决起来很麻烦(因为export_mode=false支持lob,但是不支持字符串含回车;export_mode=true支持字符串含回车,但是不支持lob),最后放弃了对部分数据的挖掘.这个问题让我一直不甘心,今天测试dul 10 发现是用export_mode=true可以完美解决该问题
1.创建模拟表和插入数据
SQL> desc t_xff Name Null? Type ----------------------------------------- -------- ---------------------------- C_BLOB BLOB C_VARCHAR VARCHAR2(4000) SQL> declare 2 a_blob BLOB; 3 bfile_name BFILE := BFILENAME('ULTLOBDIR','awr_ora11g_2012-06-01_174_175.html'); 4 begin 5 insert into t_xff(C_BLOB,C_VARCHAR) values ( 6 empty_blob()) 12 returning C_BLOB into a_blob; 13 dbms_lob.fileopen(bfile_name); 14 dbms_lob.loadfromfile(a_blob, bfile_name, dbms_lob.getlength(bfile_name)); 15 dbms_lob.fileclose(bfile_name); 16 commit; 17 end; 18 / PL/SQL procedure successfully completed. SQL> select length(c_varchar),dbms_lob.getlength(c_blob) from t_xff; LENGTH(C_VARCHAR) DBMS_LOB.GETLENGTH(C_BLOB) ----------------- -------------------------- 61 4282573 SQL> select c_varchar from t_xff; C_VARCHAR --------------------------------------------------------------- 数据库异常恢复
2.dul 挖数据
[oracle@xifenfei dul]$ ./dul Data UnLoader: 10.2.0.5.13 - Internal Only - on Mon Jul 2 04:29:10 2012 with 64-bit io functions Copyright (c) 1994 2012 Bernard van Duijnen All rights reserved. Strictly Oracle Internal Use Only DUL> bootstrap; DUL> desc chf.t_xff; Table CHF.T_XFF obj#= 51353, dataobj#= 51353, ts#= 4, file#= 4, block#=67 tab#= 0, segcols= 2, clucols= 0 Column information: icol# 01 segcol# 01 C_BLOB len 4000 type 113 BLOB LOB Segment: dataobj#= 51354, ts#= 4, file#= 4, block#=75 chunk=1 LOB Index: dataobj#= 51355, ts#= 4, file#= 4, block#=83 icol# 02 segcol# 02 C_VARCHAR len 4000 type 1 VARCHAR2 cs 852(ZHS16GBK) --export_mode=false DUL> unload table chf.t_xff; . unloading (index organized) table LOB01000053 65 rows unloaded Preparing lob metadata from lob index Reading LOB01000053.dat 65 entries loaded and sorted 65 entries . unloading table T_XFF 1 row unloaded --导出数据文件 -rw-r--r-- 1 oracle oinstall 6.1K Jul 2 04:15 LOB01000053.dat -rw-r--r-- 1 oracle oinstall 335 Jul 2 04:15 LOB01000053.ctl -rw-r--r-- 1 oracle oinstall 8.2M Jul 2 04:15 CHF_T_XFF.dat -rw-r--r-- 1 oracle oinstall 263 Jul 2 04:15 CHF_T_XFF.ctl ----export_mode=true DUL> unload table chf.t_xff; . unloading (index organized) table LOB01000053 DUL: Warning: Recreating file "LOB01000053.ctl" 65 rows unloaded Preparing lob metadata from lob index Reading LOB01000053.dat 65 entries loaded and sorted 65 entries . unloading table T_XFF 1 row unloaded --导出数据文件 -rw-r--r-- 1 oracle oinstall 6229 Jul 2 04:29 LOB01000053.dat -rw-r--r-- 1 oracle oinstall 335 Jul 2 04:29 LOB01000053.ctl -rw-r--r-- 1 oracle oinstall 4285027 Jul 2 04:29 CHF_T_XFF.dmp
3.导入数据测试
sqlldr导入
SQL> truncate table chf.t_xff; Table truncated. [oracle@xifenfei dul]$ sqlldr chf/xifenfei control=CHF_T_XFF.ctl SQL*Loader: Release 10.2.0.1.0 - Production on Mon Jul 2 04:23:18 2012 Copyright (c) 1982, 2005, Oracle. All rights reserved. SQL*Loader-510: Physical record in data file (CHF_T_XFF.dat) is longer than the maximum(1048576) SQL*Loader-2026: the load was aborted because SQL Loader cannot continue. [oracle@xifenfei dul]$ sqlldr chf/xifenfei control=CHF_T_XFF.ctl readsize=20971520 SQL*Loader: Release 10.2.0.1.0 - Production on Mon Jul 2 04:26:50 2012 Copyright (c) 1982, 2005, Oracle. All rights reserved. SQL> select length(c_varchar),dbms_lob.getlength(c_blob) from chf.t_xff; no rows selected --试验结果证明在出现表中同时有lob和varchar2列(含回车)时,export_mode=false不能正常工作
imp导入
SQL> drop table chf.t_xff; Table dropped. [oracle@xifenfei dul]$ imp chf/xifenfei file=CHF_T_XFF.dmp full=y Import: Release 10.2.0.1.0 - Production on Mon Jul 2 04:30:30 2012 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options Export file created by EXPORT:V07.00.07 via conventional path Warning: the objects were exported by Bernard's DUL, not by you . importing Bernard's DUL's objects into CHF . importing Bernard's DUL's objects into CHF . . importing table "T_XFF" 1 rows imported SQL> select length(c_varchar),dbms_lob.getlength(c_blob) from t_xff; LENGTH(C_VARCHAR) DBMS_LOB.GETLENGTH(C_BLOB) ----------------- -------------------------- 61 4282573 SQL> select c_varchar from t_xff; C_VARCHAR --------------------------------------------------------------- 数据库异常恢复 --试验结果证明在出现表中同时有lob和varchar2列(含回车)时,export_mode=true正常工作
-----------------温馨提示-------------------- 操作有风险,动手需谨慎Oracle研究中心http://www.oracleplus.net本文由大师惜分飞原创分享,转载请尽量保留本站网址
--------------------------------------ORACLE-DBA----------------------------------------
最权威、专业的Oracle案例资源汇总之案例:Oracle dul数据挖掘 磁盘损坏dul提取数据文件中表的数据及l
原文唯一网址:http://www.oracleplus.net/arch/oracle-20160522-213.html