Case: Oracle dul Data Mining disk corruption Dul extracting data from tables in data files and L

Extract the contents of the tables and LOB fields in the database files in the damaged disk by using the Oracle Dul tool

In a 8i library recovery, as hard disk damage caused a number of tables to have a lot of paradoxical bad blocks, trying to use Dul to mine data, when using Dul 9 encountered a problem: when a table has a lob type, but also has a varchar2 type, and VARCHAR2 type data contains the ENTER key , which makes it cumbersome to solve (because Export_mode=false supports lobs, but does not support strings with carriage returns; Export_mode=true supports strings with carriage returns, but not lob), and finally discards the mining of some of the data. This question has kept me from being reconciled, Today Test Dul 10 discovery is using export_mode=true to solve the problem perfectly

1. Create a mock table and insert data

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 ()), returning C_blob into A_blob; Dbms_lob.fileopen (Bfile_name); Dbms_lob.loadfromfile (A_blob, Bfile_name, Dbms_lob.getlength (bfile_name)); Dbms_lob.fileclose (Bfile_name); commit; The 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)-------------------------------------------4282573sql> Select C _varchar from T_xff; C_varchar---------------------------------------------------------------Database Exception Recovery 

2.dul Digging Data

[[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, 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 # segcol# C_blob len 4000 type 113 BLOB LOB segment:dataobj#= 51354, ts#= 4, file#= 4, block#=75 chunk=1 LO B index:dataobj#= 51355, ts#= 4, file#= 4, block#=83icol# segcol#, C_varchar len 4000 type 1 VARCHAR2 CS 852 (ZHS 16GBK)--export_mode=falsedul> unload table chf.t_xff;. Unloading (index organized) Table LOB01000053 rows unloadedpreparing LOB metadata from LOB indexreading LOB010 00053.dat entries loaded and sorted entries. Unloading table T_xff 1 row unloaded--Export Data file-rw-r--r--1 Oracle oinstall 6.1K Jul 2 04:15 LOB0 1000053.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" Rows Unloadedpre Paring LOB metadata from LOB indexreading LOB01000053.dat entries loaded and sorted entries. Unloading table T_xff 1 row unloaded--Export Data file-rw-r--r--1 Oracle oinstall 6229 Jul 2 04:29 L  ob01000053.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. Import data Test

SQLLDR Import

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--test results prove that Export_mode=false does not work properly when there are both LOB and VARCHAR2 columns (including carriage return) in the resulting table

IMP Import

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 M Ining 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)-------------------------------------------4282573sql> Select C _varchar from T_xff; C_varchar---------------------------------------------------------------Database Exception Recovery--the test results show that both LOB and Var are present in the table.CHAR2 column (including carriage return), Export_mode=true is working properly 

-----------------Tips--------------------operation is risky, hands-on need to be cautious Oracle Research Center http://www.oracleplus.net This article by the master cherish the original sharing, reproduced please try to retain the site website

--------------------------------------ORACLE-DBA----------------------------------------

The most authoritative and professional example of Oracle Case resource Summary: Oracle DUL Data Mining disk corruption Dul extract data from tables in data files and L

Original URL: http://www.oracleplus.net/arch/oracle-20160522-213.html

Oracle Research Center Key Words:Oracle dul Data Mining disk corruption Dul extract data from tables in a data file and LOB fields

Case: Oracle dul Data Mining disk corruption Dul extracting data from tables in data files and L