Tag Archives: dul

dul恢复drop表测试

Posted on by

dul对被drop对象进行恢复，需要提供两个信息
1.被删除表所属表空间(非必须)
2.被删除表结构(必须)
模拟删除表

--创建测试表
SQL> create table t_dul_drop tablespace czum
  2  as
  3  select * from dba_tables;

Table created.

--备份被删除表数据，便于比较和提供测试表结构
SQL> create table t_dul_drop_bak tablespace users
  2  as select * from t_dul_drop;

Table created.

SQL> alter system switch logfile;

System altered.

SQL> select count(*) from t_dul_drop;

  COUNT(*)
----------
      1785

SQL> drop table chf.t_dul_drop purge;

Table dropped.

SQL> alter system checkpoint;

System altered.

使用logminer找到data_object_id

delete from "SYS"."OBJ$" where "OBJ#" = '68474' and "DATAOBJ#" = '68474' 
and "OWNER#" = '61' and "NAME" = 'T_DUL_DROP' and "NAMESPACE" = '1' and 
"SUBNAME" IS NULL and "TYPE#" = '2' and "CTIME" = TO_DATE('04-FEB-13', 'DD-MON-RR') 
and "MTIME" = TO_DATE('04-FEB-13', 'DD-MON-RR') and "STIME" = TO_DATE('04-FEB-13', 'DD-MON-RR')
and "STATUS" = '1' and "REMOTEOWNER" IS NULL and "LINKNAME" IS NULL and "FLAGS" = '0' 
and "OID$" IS NULL and "SPARE1" = '6' and "SPARE2" = '1' and "SPARE3" = '61' and 
"SPARE4" IS NULL and "SPARE5" IS NULL and "SPARE6" IS NULL and ROWID = 'AAAAASAABAAAPzCAAV';

这里可以知道，被删除表的data_object_id为68474

DUL恢复被删除表

--dul版本
E:\dul10>dul.exe

Data UnLoader 10.2.4.37 - Oracle Internal Only - on Mon Feb 04 23:49:50 2013
with 64-bit io functions

Copyright (c) 1994 2010 Bernard van Duijnen All rights reserved.

 Strictly Oracle Internal use Only

DUL>  ALTER SESSION SET USE_SCANNED_EXTENT_MAP = TRUE;
Parameter altered

--扫描所属表空间
DUL> scan tablespace 6;
Scanning tablespace 6, data file 6 ...
  13 segment header and 331 data blocks
  tablespace 6, data file 6: 1279 blocks scanned
Reading EXT.dat 13 entries loaded and sorted 13 entries
Reading SEG.dat 13 entries loaded
Reading COMPATSEG.dat 0 entries loaded
Reading SCANNEDLOBPAGE.dat 0 entries loaded and sorted 0 entries

--scan tables得到需求表(可以核对数据样例)
DUL> scan tables;
UNLOAD TABLE OBJNO68474 ( COL001 VARCHAR2(11), COL002 VARCHAR2(30), COL003 VARCHAR2(6)
        , COL004 VARCHAR2(20), COL005 VARCHAR2(30), COL006 VARCHAR2(5), COL007 NUMBER
        , COL008 NUMBER, COL009 NUMBER, COL010 NUMBER, COL011 NUMBER
        , COL012 NUMBER, COL013 NUMBER, COL014 NUMBER, COL015 CHAR
        , COL016 NUMBER, COL017 NUMBER, COL018 VARCHAR2(3), COL019 VARCHAR2(1)
        , COL020 NUMBER, COL021 NUMBER, COL022 NUMBER, COL023 NUMBER
        , COL024 NUMBER, COL025 NUMBER, COL026 NUMBER, COL027 NUMBER
        , COL028 VARCHAR2(10), COL029 VARCHAR2(10), COL030 VARCHAR2(5), COL031 VARCHAR2(7)
        , COL032 NUMBER, COL033 DATE, COL034 VARCHAR2(3), COL035 VARCHAR2(12)
        , COL036 VARCHAR2(1), COL037 VARCHAR2(1), COL038 VARCHAR2(3), COL039 VARCHAR2(7)
        , COL040 VARCHAR2(7), COL041 VARCHAR2(7), COL042 VARCHAR2(8), COL043 VARCHAR2(3)
        , COL044 VARCHAR2(2), COL045 VARCHAR2(15), COL046 VARCHAR2(8), COL047 VARCHAR2(3)
        , COL048 VARCHAR2(3), COL049 VARCHAR2(8), COL050 VARCHAR2(8), COL051 VARCHAR2(5)
        , COL052 VARCHAR2(2), COL053 VARCHAR2(2), COL054 VARCHAR2(3), COL055 VARCHAR2(7) )
    STORAGE( DATAOBJNO 68474 );

--恢复删除表(业务提供表结构)
DUL> unload table t_dul_drop(
  2  OWNER                              VARCHAR2(30),
  3  TABLE_NAME                         VARCHAR2(30),
  4  TABLESPACE_NAME                                    VARCHAR2(30),
  5  CLUSTER_NAME                                       VARCHAR2(30),
  6  IOT_NAME                                           VARCHAR2(30),
  7  STATUS                                             VARCHAR2(8) ,
  8  PCT_FREE                                           NUMBER      ,
  9  PCT_USED                                           NUMBER      ,
 10  INI_TRANS                                          NUMBER      ,
 11  MAX_TRANS                                          NUMBER      ,
 12  INITIAL_EXTENT                                     NUMBER      ,
 13  NEXT_EXTENT                                        NUMBER      ,
 14  MIN_EXTENTS                                        NUMBER      ,
 15  MAX_EXTENTS                                        NUMBER      ,
 16  PCT_INCREASE                                       NUMBER      ,
 17  FREELISTS                                          NUMBER      ,
 18  FREELIST_GROUPS                                    NUMBER      ,
 19  LOGGING                                            VARCHAR2(3) ,
 20  BACKED_UP                                          VARCHAR2(1) ,
 21  NUM_ROWS                                           NUMBER      ,
 22  BLOCKS                                             NUMBER      ,
 23  EMPTY_BLOCKS                                       NUMBER      ,
 24  AVG_SPACE                                          NUMBER      ,
 25  CHAIN_CNT                                          NUMBER      ,
 26  AVG_ROW_LEN                                        NUMBER      ,
 27  AVG_SPACE_FREELIST_BLOCKS                          NUMBER      ,
 28  NUM_FREELIST_BLOCKS                                NUMBER      ,
 29  DEGREE                                             VARCHAR2(20),
 30  INSTANCES                                          VARCHAR2(20),
 31  CACHE                                              VARCHAR2(10),
 32  TABLE_LOCK                                         VARCHAR2(8) ,
 33  SAMPLE_SIZE                                        NUMBER      ,
 34  LAST_ANALYZED                                      DATE        ,
 35  PARTITIONED                                        VARCHAR2(3) ,
 36  IOT_TYPE                                           VARCHAR2(12),
 37  TEMPORARY                                          VARCHAR2(1) ,
 38  SECONDARY                                          VARCHAR2(1) ,
 39  NESTED                                             VARCHAR2(3) ,
 40  BUFFER_POOL                                        VARCHAR2(7) ,
 41  FLASH_CACHE                                        VARCHAR2(7) ,
 42  CELL_FLASH_CACHE                                   VARCHAR2(7) ,
 43  ROW_MOVEMENT                                       VARCHAR2(8) ,
 44  GLOBAL_STATS                                       VARCHAR2(3) ,
 45  USER_STATS                                         VARCHAR2(3) ,
 46  DURATION                                           VARCHAR2(15),
 47  SKIP_CORRUPT                                       VARCHAR2(8) ,
 48  MONITORING                                         VARCHAR2(3) ,
 49  CLUSTER_OWNER                                      VARCHAR2(30),
 50  DEPENDENCIES                                       VARCHAR2(8) ,
 51  COMPRESSION                                        VARCHAR2(8) ,
 52  COMPRESS_FOR                                       VARCHAR2(12),
 53  DROPPED                                            VARCHAR2(3) ,
 54  READ_ONLY                                          VARCHAR2(3) ,
 55  SEGMENT_CREATED                                    VARCHAR2(3) ,
 56  RESULT_CACHE                                       VARCHAR2(7))
 57   STORAGE( DATAOBJNO 68474 );
. unloading table                T_DUL_DROP
DUL: Warning: Recreating file "T_DUL_DROP.ctl"
    1785 rows unloaded

模拟业务规则提供，创建表

SQL> create table t_dul_drop as select * from t_dul_drop_bak where 1=0;

Table created.

导入数据

e:\dul10>sqlldr chf/xifenfei control=T_DUL_DROP.ctl

SQL*Loader: Release 11.2.0.3.0 - Production on Mon Feb 4 23:35:57 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 64
Commit point reached - logical record count 128
Commit point reached - logical record count 192
Commit point reached - logical record count 256
Commit point reached - logical record count 320
Commit point reached - logical record count 384
Commit point reached - logical record count 448
Commit point reached - logical record count 512
Commit point reached - logical record count 576
Commit point reached - logical record count 640
Commit point reached - logical record count 704
Commit point reached - logical record count 768
Commit point reached - logical record count 832
Commit point reached - logical record count 896
Commit point reached - logical record count 960
Commit point reached - logical record count 1024
Commit point reached - logical record count 1088
Commit point reached - logical record count 1152
Commit point reached - logical record count 1216
Commit point reached - logical record count 1280
Commit point reached - logical record count 1344
Commit point reached - logical record count 1408
Commit point reached - logical record count 1472
Commit point reached - logical record count 1536
Commit point reached - logical record count 1600
Commit point reached - logical record count 1664
Commit point reached - logical record count 1728
Commit point reached - logical record count 1785

恢复数据结果

SQL> select count(*) from t_dul_drop;

  COUNT(*)
----------
      1785

SQL> select owner,table_name from t_dul_drop where rownum<10;

OWNER                          TABLE_NAME
------------------------------ ------------------------------
SYS                            IDL_CHAR$
SYS                            IDL_UB2$
SYS                            IDL_SB4$
SYS                            ERROR$
SYS                            SETTINGS$
SYS                            NCOMP_DLL$
SYS                            PROCEDUREJAVA$
SYS                            PROCEDUREC$
SYS                            PROCEDUREPLSQL$

9 rows selected.

dul 10支持oracle 11g r2

Posted on by

以前一直以为dul对应的版本只能恢复最高的数据库版本一致,今天测试发现dul 10可以恢复11g最新版的数据库.
模拟环境

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') "WWW.XIFENFEI.COM" FROM DUAL;

WWW.XIFENFEI.COM
-------------------
2012-08-04 12:49:29

SQL> create table t_xifenfei 
  2  as
  3  select * from dba_objects;

Table created.

SQL> select count(*) from t_xifenfei;

  COUNT(*)
----------
     74491

SQL> alter system checkpoint;

System altered.

SQL> /

System altered.

dul 参数配置

[oracle@xifenfei dul]$ more init.dul 
osd_big_endian_flag=false
osd_dba_file_bits=10
osd_c_struct_alignment=32
osd_file_leader_size=0
osd_word_size = 32
dc_columns=2000000
dc_tables=10000
dc_objects=1000000
dc_users=400
dc_segments=100000
control_file = control11.dul
db_block_size=8192
export_mode=false
compatible=11
BUFFER=10000000
LDR_ENCLOSE_CHAR=|

[oracle@xifenfei dul]$ more control11.dul 
         0          1 /u01/oracle/oradata/ora11g/system01.dbf
         1          2 /u01/oracle/oradata/ora11g/sysaux01.dbf
         2          3 /u01/oracle/oradata/ora11g/undotbs01.dbf
         4          4 /u01/oracle/oradata/ora11g/users01.dbf

dul恢复11g数据库

[oracle@xifenfei dul]$ ./dul10

Data UnLoader: 10.2.0.5.13 - Internal Only - on Sat Aug  4 00:36:15 2012
with 64-bit io functions

Copyright (c) 1994 2012 Bernard van Duijnen All rights reserved.

 Strictly Oracle Internal Use Only


DUL: Warning: Recreating file "dul.log"
Found db_id = 4185048347
Found db_name = ORA11G
DUL>  bootstrap;
Probing file = 1, block = 520
. unloading table                BOOTSTRAP$
DUL: Warning: block number is non zero but marked deferred trying to process it anyhow
      60 rows unloaded
DUL: Warning: Dictionary cache DC_BOOTSTRAP is empty
Reading BOOTSTRAP.dat 60 entries loaded
Parsing Bootstrap$ contents
Generating dict.ddl for version 11
 OBJ$: segobjno 18, file 1 block 240
 TAB$: segobjno 2, tabno 1, file 1  block 144
 COL$: segobjno 2, tabno 5, file 1  block 144
 USER$: segobjno 10, tabno 1, file 1  block 208
Running generated file "@dict.ddl" to unload the dictionary tables
. unloading table                      OBJ$   74493 rows unloaded
. unloading table                      TAB$    2858 rows unloaded
. unloading table                      COL$   93503 rows unloaded
. unloading table                     USER$      88 rows unloaded
Reading USER.dat 88 entries loaded
Reading OBJ.dat 74493 entries loaded and sorted 74493 entries
Reading TAB.dat 2858 entries loaded
Reading COL.dat 93503 entries loaded and sorted 93503 entries
Reading BOOTSTRAP.dat 60 entries loaded

DUL: Warning: Recreating file "dict.ddl"
Generating dict.ddl for version 11
 OBJ$: segobjno 18, file 1 block 240
 TAB$: segobjno 2, tabno 1, file 1  block 144
 COL$: segobjno 2, tabno 5, file 1  block 144
 USER$: segobjno 10, tabno 1, file 1  block 208
 TABPART$: segobjno 568, file 1 block 3872
 INDPART$: segobjno 573, file 1 block 3912
 TABCOMPART$: segobjno 590, file 1 block 4056
 INDCOMPART$: segobjno 595, file 1 block 4096
 TABSUBPART$: segobjno 580, file 1 block 3976
 INDSUBPART$: segobjno 585, file 1 block 4016
 IND$: segobjno 2, tabno 3, file 1  block 144
 ICOL$: segobjno 2, tabno 4, file 1  block 144
 LOB$: segobjno 2, tabno 6, file 1  block 144
 COLTYPE$: segobjno 2, tabno 7, file 1  block 144
 TYPE$: segobjno 495, tabno 1, file 1  block 3344
 COLLECTION$: segobjno 495, tabno 2, file 1  block 3344
 ATTRIBUTE$: segobjno 495, tabno 3, file 1  block 3344
 LOBFRAG$: segobjno 601, file 1 block 4144
 LOBCOMPPART$: segobjno 604, file 1 block 4168
 UNDO$: segobjno 15, file 1 block 224
 TS$: segobjno 6, tabno 2, file 1  block 176
 PROPS$: segobjno 98, file 1 block 800
Running generated file "@dict.ddl" to unload the dictionary tables
. unloading table                      OBJ$
DUL: Warning: Recreating file "OBJ.ctl"
   74493 rows unloaded
. unloading table                      TAB$
DUL: Warning: Recreating file "TAB.ctl"
    2858 rows unloaded
. unloading table                      COL$
DUL: Warning: Recreating file "COL.ctl"
   93503 rows unloaded
. unloading table                     USER$
DUL: Warning: Recreating file "USER.ctl"
      88 rows unloaded
. unloading table                  TABPART$      90 rows unloaded
. unloading table                  INDPART$     106 rows unloaded
. unloading table               TABCOMPART$       1 row  unloaded
. unloading table               INDCOMPART$       0 rows unloaded
. unloading table               TABSUBPART$      32 rows unloaded
. unloading table               INDSUBPART$       0 rows unloaded
. unloading table                      IND$    5092 rows unloaded
. unloading table                     ICOL$    7518 rows unloaded
. unloading table                      LOB$    1251 rows unloaded
. unloading table                  COLTYPE$    2967 rows unloaded
. unloading table                     TYPE$    2872 rows unloaded
. unloading table               COLLECTION$     985 rows unloaded
. unloading table                ATTRIBUTE$   11127 rows unloaded
. unloading table                  LOBFRAG$       1 row  unloaded
. unloading table              LOBCOMPPART$       0 rows unloaded
. unloading table                     UNDO$      21 rows unloaded
. unloading table                       TS$       6 rows unloaded
. unloading table                    PROPS$      36 rows unloaded
Reading USER.dat 88 entries loaded
Reading OBJ.dat 74493 entries loaded and sorted 74493 entries
Reading TAB.dat 2858 entries loaded
Reading COL.dat 93503 entries loaded and sorted 93503 entries
Reading TABPART.dat 90 entries loaded and sorted 90 entries
Reading TABCOMPART.dat 1 entries loaded and sorted 1 entries
Reading TABSUBPART.dat 32 entries loaded and sorted 32 entries
Reading INDPART.dat 106 entries loaded and sorted 106 entries
Reading INDCOMPART.dat 0 entries loaded and sorted 0 entries
Reading INDSUBPART.dat 0 entries loaded and sorted 0 entries
Reading IND.dat 5092 entries loaded
Reading LOB.dat
DUL: Warning: Increased the size of DC_LOBS from 1024 to 8192 entries
 1251 entries loaded
Reading ICOL.dat 7518 entries loaded
Reading COLTYPE.dat 2967 entries loaded
Reading TYPE.dat 2872 entries loaded
Reading ATTRIBUTE.dat 11127 entries loaded
Reading COLLECTION.dat 985 entries loaded
Reading BOOTSTRAP.dat 60 entries loaded
Reading LOBFRAG.dat 1 entries loaded and sorted 1 entries
Reading LOBCOMPPART.dat 0 entries loaded and sorted 0 entries
Reading UNDO.dat 21 entries loaded
Reading TS.dat 6 entries loaded
Reading PROPS.dat 36 entries loaded
Database character set is ZHS16GBK
Database national character set is AL16UTF16
DUL> desc chf.t_xifenfei;
Table CHF.T_XIFENFEI
obj#= 75562, dataobj#= 75562, ts#= 4, file#= 4, block#=170
      tab#= 0, segcols= 15, clucols= 0
Column information:
icol# 01 segcol# 01        OWNER len   30 type  1 VARCHAR2 cs 852(ZHS16GBK)
icol# 02 segcol# 02  OBJECT_NAME len  128 type  1 VARCHAR2 cs 852(ZHS16GBK)
icol# 03 segcol# 03 SUBOBJECT_NAME len   30 type  1 VARCHAR2 cs 852(ZHS16GBK)
icol# 04 segcol# 04    OBJECT_ID len   22 type  2 NUMBER(0,-127)
icol# 05 segcol# 05 DATA_OBJECT_ID len   22 type  2 NUMBER(0,-127)
icol# 06 segcol# 06  OBJECT_TYPE len   19 type  1 VARCHAR2 cs 852(ZHS16GBK)
icol# 07 segcol# 07      CREATED len    7 type 12 DATE
icol# 08 segcol# 08 LAST_DDL_TIME len    7 type 12 DATE
icol# 09 segcol# 09    TIMESTAMP len   19 type  1 VARCHAR2 cs 852(ZHS16GBK)
icol# 10 segcol# 10       STATUS len    7 type  1 VARCHAR2 cs 852(ZHS16GBK)
icol# 11 segcol# 11    TEMPORARY len    1 type  1 VARCHAR2 cs 852(ZHS16GBK)
icol# 12 segcol# 12    GENERATED len    1 type  1 VARCHAR2 cs 852(ZHS16GBK)
icol# 13 segcol# 13    SECONDARY len    1 type  1 VARCHAR2 cs 852(ZHS16GBK)
icol# 14 segcol# 14    NAMESPACE len   22 type  2 NUMBER(0,-127)
icol# 15 segcol# 15 EDITION_NAME len   30 type  1 VARCHAR2 cs 852(ZHS16GBK)
DUL> unload table chf.t_xifenfei;
. unloading table                T_XIFENFEI   74491 rows unloaded

通过一些列的dul 10测试,发现dul 10功能确实较9强大了很多.支持asm,支持11g,支持字符串换行+lob类型

DUL挖ORACLE 8.0数据库

Posted on by

老古董的东西,在你一不小心的时候就可能遇到,测试了dul成功挖ORACLE 8.0数据库
创建模拟环境

SVRMGR> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle8 Release 8.0.5.0.0 - Production
PL/SQL Release 8.0.5.0.0 - Production
CORE Version 4.0.5.0.0 - Production
TNS for 32-bit Windows: Version 8.0.5.0.0 - Production
NLSRTL Version 3.3.2.0.0 - Production
5 rows selected.

SVRMGR> create table t_xifenfei
     2> as
     3> select * from dba_tables;
Statement processed.
SVRMGR> select count(*) from t_xifenfei;
COUNT(*)
----------
       183
1 row selected.

SVRMGR> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

dul参数配置

    osd_big_endian_flag=false
    osd_dba_file_bits=10
    osd_c_struct_alignment=32
    osd_file_leader_size=1
    dc_columns=2000000
    dc_tables=10000
    dc_objects=1000000
    dc_users=400
    dc_segments=100000
    control_file = control.txt
    db_block_size=2048
    export_mode=true
    compatible=8
    file = dump

dul挖数据

C:\dul>dul8.exe dictv8.ddl

Data UnLoader 8.0.6.8 - Internal Use Only - on Thu Jul 26 20:08:33 2012
with 64-bit io functions

Copyright (c) 1994/2000 Bernard van Duijnen All rights reserved.


DUL: Warning: Recreating file "dul.log"
Parameter altered
Parameter altered
Parameter altered
Parameter altered
. unloading table                      OBJ$    2681 rows unloaded
. unloading table                      TAB$     187 rows unloaded
. unloading table                      COL$   11181 rows unloaded
. unloading table                     USER$      23 rows unloaded
. unloading table                  TABPART$       0 rows unloaded
. unloading table                      IND$     221 rows unloaded
. unloading table                     ICOL$     419 rows unloaded
. unloading table                      LOB$      13 rows unloaded

Life is DUL without it

C:\dul>dul8.exe

Data UnLoader 8.0.6.8 - Internal Use Only - on Thu Jul 26 20:13:44 2012
with 64-bit io functions

Copyright (c) 1994/2000 Bernard van Duijnen All rights reserved.


DUL: Warning: Recreating file "dul.log"
Loaded 23 entries from USER.dat
Loaded 2681 entries from OBJ.dat
Loaded 188 entries from TAB.dat
Loaded 11218 entries from COL.dat
Loaded 0 entries from TABPART.dat
Loaded 221 entries from IND.dat
Loaded 13 entries from LOB.dat
Loaded 419 entries from ICOL.dat
DUL> unload table chf.t_xifenfei;
. unloading table                T_XIFENFEI     183 rows unloaded

启动8.0数据库

C:\oracle\ora80\BIN>SVRMGR30.EXE

Oracle Server Manager Release 3.0.5.0.0 - Production

(c) Copyright 1997, Oracle Corporation.  All Rights Reserved.

Oracle8 Release 8.0.5.0.0 - Production
PL/SQL Release 8.0.5.0.0 - Production

SVRMGR> connect internal/xifenfei
Connected.
SVRMGR> startup
ORACLE instance started.
Total System Global Area                         15077376 bytes
Fixed Size                                          49152 bytes
Variable Size                                    12906496 bytes
Database Buffers                                  2048000 bytes
Redo Buffers                                        73728 bytes
Database mounted.
Database opened.

imp导入数据

C:\dul>imp chf/xifenfei file=dump001.dmp full=y

Import: Release 8.0.5.0.0 - Production on 星期二 11月 1 23:34:36 2011

(c) Copyright 2000 Oracle Corporation.  All rights reserved.


连接到: Oracle8 Release 8.0.5.0.0 - Production
PL/SQL Release 8.0.5.0.0 - Production

经由常规路径导出由EXPORT:V07.00.07创建的文件

警告: 此对象由 Bernard's DUL 导出, 而不是当前用户

. 正在将Bernard's DUL的对象导入到 CHF
. . 正在导入表                    "T_XIFENFEI"        183行被导入
成功终止导入，但出现警告。

测试恢复数据

SVRMGR> connect chf/xifenfei
Connected.
SVRMGR> select count(*) from t_xifenfei;
COUNT(*)
----------
       183
1 row selected.

再次证明了dul确实异常的强大,第三方的工具在某些方面确实不是它的对手

dul 10 export_mode=true功能增强

Posted on by

在有次8i的库恢复中,因为硬盘损坏导致几个表出现很多诡异性坏块,尝试使用dul对其进行挖掘数据,当时使用dul 9 遇到一个难题:当一张表中有lob类型,同时又有varchar2类型,而且varchar2类型数据中包含回车键,使得解决起来很麻烦(因为export_mode=false支持lob,但是不支持字符串含回车;export_mode=true支持字符串含回车,但是不支持lob),最后放弃了对部分数据的挖掘.这个问题让我一直不甘心,今天测试dul 10 发现是用export_mode=true可以完美解决该问题
创建模拟表和插入数据

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(),
  7  'www.orasos.com
  8  WWW.XIFENFEI.COM
  9  惜分飞
 10  欢迎访问惜分飞博客
 11  提供数据库异常恢复技术支持')
 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
---------------------------------------------------------------
www.orasos.com
WWW.XIFENFEI.COM
惜分飞
欢迎访问惜分飞博客
提供数据库异常恢复技术支持

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

导入数据测试
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
---------------------------------------------------------------
www.orasos.com
WWW.XIFENFEI.COM
惜分飞
欢迎访问惜分飞博客
提供数据库异常恢复技术支持

--试验结果证明在出现表中同时有lob和varchar2列(含回车)时,export_mode=true正常工作

使用 dul 挖数据文件初试

Posted on by

最近测试了下dul,整体感觉和odu差不多
1.配置init.dul

[oracle@xifenfei dul]$ more init.dul
osd_big_endian_flag=false
osd_dba_file_bits=10
osd_c_struct_alignment=32
osd_file_leader_size=1
osd_word_size = 32
dc_columns=2000000
dc_tables=10000
dc_objects=1000000
dc_users=400
dc_segments=100000
Buffer=10485760
control_file = control.txt
db_block_size=8192
export_mode=true
--false表示是sqlloader,true表示imp
compatible=10

2.配置控制文件

[oracle@xifenfei dul]$ more control.txt
         0          1 /u01/oracle/oradata/XFF/system01.dbf
         1          2 /u01/oracle/oradata/XFF/undotbs01.dbf
         2          3 /u01/oracle/oradata/XFF/sysaux01.dbf
         4          4 /u01/oracle/oradata/XFF/users01.dbf
         6          5 /u01/oracle/oradata/XFF/datfttuser.dbf

--sql语句
select ts#,rfile#,name from v$datafile;

3.启动dul

[oracle@xifenfei dul]$ ./dul

Data UnLoader: 10.2.0.5.13 - Internal Only - on Sun Jun 10 06:39:47 2012
with 64-bit io functions

Copyright (c) 1994 2012 Bernard van Duijnen All rights reserved.

 Strictly Oracle Internal Use Only


Found db_id = 3426707456
Found db_name = XFF

4.加载初始化数据字典

DUL> BOOTSTRAP;
Probing file = 1, block = 377
. unloading table                BOOTSTRAP$
DUL: Warning: block number is non zero but marked deferred trying to process it anyhow
      57 rows unloaded
DUL: Warning: Dictionary cache DC_BOOTSTRAP is empty
Reading BOOTSTRAP.dat 57 entries loaded
Parsing Bootstrap$ contents
Generating dict.ddl for version 10
 OBJ$: segobjno 18, file 1 block 121
 TAB$: segobjno 2, tabno 1, file 1  block 25
 COL$: segobjno 2, tabno 5, file 1  block 25
 USER$: segobjno 10, tabno 1, file 1  block 89
Running generated file "@dict.ddl" to unload the dictionary tables
. unloading table                      OBJ$   50930 rows unloaded
. unloading table                      TAB$    1593 rows unloaded
. unloading table                      COL$   55163 rows unloaded
. unloading table                     USER$      61 rows unloaded
Reading USER.dat 61 entries loaded
Reading OBJ.dat 50930 entries loaded and sorted 50930 entries
Reading TAB.dat 1593 entries loaded
Reading COL.dat 55163 entries loaded and sorted 55163 entries
Reading BOOTSTRAP.dat 57 entries loaded

DUL: Warning: Recreating file "dict.ddl"
Generating dict.ddl for version 10
 OBJ$: segobjno 18, file 1 block 121
 TAB$: segobjno 2, tabno 1, file 1  block 25
 COL$: segobjno 2, tabno 5, file 1  block 25
 USER$: segobjno 10, tabno 1, file 1  block 89
 TABPART$: segobjno 266, file 1 block 2121
 INDPART$: segobjno 271, file 1 block 2161
 TABCOMPART$: segobjno 288, file 1 block 2297
 INDCOMPART$: segobjno 293, file 1 block 2345
 TABSUBPART$: segobjno 278, file 1 block 2217
 INDSUBPART$: segobjno 283, file 1 block 2257
 IND$: segobjno 2, tabno 3, file 1  block 25
 ICOL$: segobjno 2, tabno 4, file 1  block 25
 LOB$: segobjno 2, tabno 6, file 1  block 25
 COLTYPE$: segobjno 2, tabno 7, file 1  block 25
 TYPE$: segobjno 181, tabno 1, file 1  block 1297
 COLLECTION$: segobjno 181, tabno 2, file 1  block 1297
 ATTRIBUTE$: segobjno 181, tabno 3, file 1  block 1297
 LOBFRAG$: segobjno 299, file 1 block 2393
 LOBCOMPPART$: segobjno 302, file 1 block 2425
 UNDO$: segobjno 15, file 1 block 105
 TS$: segobjno 6, tabno 2, file 1  block 57
 PROPS$: segobjno 96, file 1 block 721
Running generated file "@dict.ddl" to unload the dictionary tables
. unloading table                      OBJ$
DUL: Warning: Recreating file "OBJ.ctl"
   50930 rows unloaded
. unloading table                      TAB$
DUL: Warning: Recreating file "TAB.ctl"
    1593 rows unloaded
. unloading table                      COL$
DUL: Warning: Recreating file "COL.ctl"
   55163 rows unloaded
. unloading table                     USER$
DUL: Warning: Recreating file "USER.ctl"
      61 rows unloaded
. unloading table                  TABPART$      90 rows unloaded
. unloading table                  INDPART$      99 rows unloaded
. unloading table               TABCOMPART$       0 rows unloaded
. unloading table               INDCOMPART$       0 rows unloaded
. unloading table               TABSUBPART$       0 rows unloaded
. unloading table               INDSUBPART$       0 rows unloaded
. unloading table                      IND$    2251 rows unloaded
. unloading table                     ICOL$    3669 rows unloaded
. unloading table                      LOB$     537 rows unloaded
. unloading table                  COLTYPE$    1702 rows unloaded
. unloading table                     TYPE$    1886 rows unloaded
. unloading table               COLLECTION$     552 rows unloaded
. unloading table                ATTRIBUTE$    7051 rows unloaded
. unloading table                  LOBFRAG$       1 row  unloaded
. unloading table              LOBCOMPPART$       0 rows unloaded
. unloading table                     UNDO$      21 rows unloaded
. unloading table                       TS$       7 rows unloaded
. unloading table                    PROPS$      27 rows unloaded
Reading USER.dat 61 entries loaded
Reading OBJ.dat 50930 entries loaded and sorted 50930 entries
Reading TAB.dat 1593 entries loaded
Reading COL.dat 55163 entries loaded and sorted 55163 entries
Reading TABPART.dat 90 entries loaded and sorted 90 entries
Reading TABCOMPART.dat 0 entries loaded and sorted 0 entries
Reading TABSUBPART.dat 0 entries loaded and sorted 0 entries
Reading INDPART.dat 99 entries loaded and sorted 99 entries
Reading INDCOMPART.dat 0 entries loaded and sorted 0 entries
Reading INDSUBPART.dat 0 entries loaded and sorted 0 entries
Reading IND.dat 2251 entries loaded
Reading LOB.dat 537 entries loaded
Reading ICOL.dat 3669 entries loaded
Reading COLTYPE.dat 1702 entries loaded
Reading TYPE.dat 1886 entries loaded
Reading ATTRIBUTE.dat 7051 entries loaded
Reading COLLECTION.dat 552 entries loaded
Reading BOOTSTRAP.dat 57 entries loaded
Reading LOBFRAG.dat 1 entries loaded and sorted 1 entries
Reading LOBCOMPPART.dat 0 entries loaded and sorted 0 entries
Reading UNDO.dat 21 entries loaded
Reading TS.dat 7 entries loaded
Reading PROPS.dat 27 entries loaded
Database character set is ZHS16GBK
Database national character set is AL16UTF16

5.导出某种表

DUL> desc chf.t_xifenfei;
Table CHF.T_XIFENFEI
obj#= 52189, dataobj#= 52189, ts#= 4, file#= 4, block#=123
      tab#= 0, segcols= 2, clucols= 0
Column information:
icol# 01 segcol# 01           ID len   22 type  2 NUMBER(0,-127)
icol# 02 segcol# 02         NAME len  100 type  1 VARCHAR2 cs 852(ZHS16GBK)
DUL> UNLOAD TABLE chf.t_xifenfei;
. unloading table                T_XIFENFEI       2 rows unloaded

6.验证导出dmp文件

[oracle@xifenfei dul]$ strings  CHF_T_XIFENFEI.dmp
EXPORT:V07.00.07
UBernard's DUL
RTABLES
1024
                                                Direct UnLoader(C) in EXPort mode
TABLE "T_XIFENFEI"
CREATE TABLE "T_XIFENFEI"("ID" NUMBER,"NAME" VARCHAR2(100))
INSERT INTO "T_XIFENFEI" ("ID", "NAME") VALUES (:1, :2)
www.orasos.com
WWW.XIFENEI.COM
EXIT