案例:Oracle dul資料採礦 磁碟損壞dul提取資料檔案中表的資料及l

標籤：

通過使用Oracle DUL工具提取損壞磁碟裡的資料庫檔案中的表及lob欄位中內容

在有次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                    4282573SQL>  select c_varchar from t_xff;C_VARCHAR---------------------------------------------------------------資料庫異常恢複

2.dul 挖資料

[[email protected] dul]$ ./dulData UnLoader: 10.2.0.5.13 - Internal Only - on Mon Jul  2 04:29:10 2012with 64-bit io functionsCopyright (c) 1994 2012 Bernard van Duijnen All rights reserved. Strictly Oracle Internal Use OnlyDUL> bootstrap;DUL> desc chf.t_xff;Table CHF.T_XFFobj#= 51353, dataobj#= 51353, ts#= 4, file#= 4, block#=67      tab#= 0, segcols= 2, clucols= 0Column 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#=83icol# 02 segcol# 02    C_VARCHAR len 4000 type  1 VARCHAR2 cs 852(ZHS16GBK)--export_mode=falseDUL> unload table chf.t_xff;. unloading (index organized) table     LOB01000053      65 rows unloadedPreparing lob metadata from lob indexReading 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=trueDUL> unload table chf.t_xff;. unloading (index organized) table     LOB01000053DUL: Warning: Recreating file "LOB01000053.ctl"      65 rows unloadedPreparing lob metadata from lob indexReading 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.[[email protected] dul]$ sqlldr chf/xifenfei control=CHF_T_XFF.ctlSQL*Loader: Release 10.2.0.1.0 - Production on Mon Jul 2 04:23:18 2012Copyright (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.[[email protected] dul]$ sqlldr chf/xifenfei control=CHF_T_XFF.ctl readsize=20971520SQL*Loader: Release 10.2.0.1.0 - Production on Mon Jul 2 04:26:50 2012Copyright (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.[[email protected] dul]$ imp chf/xifenfei file=CHF_T_XFF.dmp full=yImport: Release 10.2.0.1.0 - Production on Mon Jul 2 04:30:30 2012Copyright (c) 1982, 2005, Oracle.  All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining optionsExport file created by EXPORT:V07.00.07 via conventional pathWarning: 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 importedSQL> select length(c_varchar),dbms_lob.getlength(c_blob) from t_xff;LENGTH(C_VARCHAR) DBMS_LOB.GETLENGTH(C_BLOB)----------------- --------------------------               61                    4282573SQL>  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

Oracle研究中心 關鍵詞：Oracle dul資料採礦磁碟損壞dul提取資料檔案中表中的資料及lob欄位

案例:Oracle dul資料採礦 磁碟損壞dul提取資料檔案中表的資料及l