Oracle dul使用初探

前几天碰到一客户，磁盘已格式化，拿到恢复数据公司几近折腾，终于将磁盘数据拿回，但数据文件均已损坏，所幸还有dmp备份，但如果没有任何备份，只能用上dul了，并祈祷自己运气不错，下面是dul的初步使用方法，环境为linux 4 32bit for oracle 9i。
在测试数据库中差得ts#，rfile#，用于创建control.txt
[quote]SQL> col ts# for 9999
SQL> col rfile# for 999
SQL> set head off
SQL> set pagesize 0
SQL> select ts#, rfile#, name from v$datafile;
0 1 /oradata/ora9i/system01.dbf
1 2 /oradata/ora9i/undotbs01.dbf
3 3 /oradata/ora9i/cwmlite01.dbf
4 4 /oradata/ora9i/drsys01.dbf
5 5 /oradata/ora9i/example01.dbf
6 6 /oradata/ora9i/indx01.dbf
7 7 /oradata/ora9i/odm01.dbf
17 8 /oradata/ora9i/xup01.dbf
9 9 /oradata/ora9i/users01.dbf
10 10 /oradata/ora9i/xdb01.dbf
12 11 /oradata/ora9i/STREAM01.dbf
0 12 /oradata/ora9i/system02.dbf
18 14 /oradata/ora9i/xu2_01.dbf[/quote]


创建init.dul

[quote] vi 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

control_file = /oracle/control.txt
db_block_size=8192
export_mode=false
COMPATIBLE=9
LDR_PHYS_REC_SIZE =0[/quote]

创建control.txt
[quote][oracle@mcprod oracle]$ vi control.txt
0 1 /oradata/ora9i/system01.dbf
1 2 /oradata/ora9i/undotbs01.dbf
3 3 /oradata/ora9i/cwmlite01.dbf
4 4 /oradata/ora9i/drsys01.dbf
5 5 /oradata/ora9i/example01.dbf
6 6 /oradata/ora9i/indx01.dbf
7 7 /oradata/ora9i/odm01.dbf
17 8 /oradata/ora9i/xup01.dbf
9 9 /oradata/ora9i/users01.dbf
10 10 /oradata/ora9i/xdb01.dbf
12 11 /oradata/ora9i/STREAM01.dbf
0 12 /oradata/ora9i/system02.dbf
18 14 /oradata/ora9i/xu2_01.dbf
[/quote]
创建dul导出数据字典脚本
[quote][oracle@mcprod oracle]$vi dictv8.ddl

REM DDL Script to unload the dictionary cache for DUL8 (Oracle8)

REM force the settings, so I know what happens
alter session set export_mode = false;
alter session set ldr_phys_rec_size = 0;
alter session set ldr_enclose_char = """" ;
alter session set file = "" ;

unload table OBJ$ ( OBJ# number, DATAOBJ# ignore, OWNER# number,
NAME varchar2(30), NAMESPACE ignore, SUBNAME varchar2(30), TYPE# number)
storage( segobjno 18 file 1);

unload table TAB$( OBJ# number, DATAOBJ# number,
TS# number, FILE# number, BLOCK# number,
BOBJ# number, TAB# number, COLS number, CLUCOLS number,
pctfree$ ignore, pctused$ ignore, initrans ignore, maxtrans ignore,
flags ignore, audit$ ignore, rowcnt ignore, blkcnt ignore,
empcnt ignore, avgspc ignore, chncnt ignore, avgrln ignore,
avgspc_flb ignore, flbcnt ignore,
analyzetime ignore, samplesize ignore,
degree ignore, instances ignore,
intcols ignore, kernelcols ignore, property number)
cluster C_OBJ#(OBJ#)
storage ( tabno 1 segobjno 2 file 1)
;

unload table COL$ ( OBJ# number, COL# number , SEGCOL# number,
SEGCOLLENGTH ignore, OFFSET ignore, NAME char(30),
TYPE# number, LENGTH number, FIXEDSTORAGE ignore,
PRECISION# ignore, SCALE ignore, NULL$ ignore, DEFLENGTH ignore,
DEFAULT$ ignore, INTCOL# number, PROPERTY number)
cluster C_OBJ#(OBJ#)
storage( tabno 5 segobjno 2 file 1)
;

unload table USER$( USER# number, NAME varchar2(30))
cluster C_USER#(USER#)
storage(tabno 1 segobjno 10 file 1)
;

ECHO exit and restart DUL to load the first four dictionary tables in the cache
ECHO OPTIONALLY for partitioned tables, indexes or lobs or for MIGRATED
ECHO use bootstrap procedure
ECHO for full bootstrap start with the following commands:
ECHO scan database;
ECHO bootstrap; (and follow the instructions)[/quote]

导出数据字典
[quote][oracle@mcprod oracle]$ ./dul dictv8.ddl

Data UnLoader 9.2.4.1 - Internal Use Only - on Wed Oct 13 07:58:22 2010
with 64-bit io functions

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


DUL: Warning: Recreating file "dul.log"
Parameter altered
Parameter altered
Parameter altered
Parameter altered
. unloading table OBJ$ 32266 rows unloaded
. unloading table TAB$ 1074 rows unloaded
. unloading table COL$ 38658 rows unloaded
. unloading table USER$ 80 rows unloaded
exit and restart DUL to load the first four dictionary tables in the cache
OPTIONALLY for partitioned tables, indexes or lobs or for MIGRATED
use bootstrap procedure
for full bootstrap start with the following commands:
scan database;
bootstrap; (and follow the instructions)[/quote]
进行数据字典导出，其过程会记录在dul.log中
[quote][oracle@mcprod oracle]$ ./dul

Data UnLoader 9.2.4.1 - Internal Use Only - on Wed Oct 13 08:01:04 2010
with 64-bit io functions

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


DUL: Warning: Recreating file "dul.log"
Reading USER.dat 80 entries loaded
Reading OBJ.dat
DUL: Error: File OBJ.dat, line 1: token missing
DUL: Warning: Ignoring file OBJ.dat cache
Reading TAB.dat 1074 entries loaded
Reading COL.dat 38658 entries loaded
DUL> scan database;
tablespace 0, data file 1: 153599 blocks scanned
tablespace 1, data file 2: 25599 blocks scanned
tablespace 3, data file 3: 2559 blocks scanned
tablespace 4, data file 4: 2559 blocks scanned
tablespace 5, data file 5: 19039 blocks scanned
tablespace 6, data file 6: 3199 blocks scanned
tablespace 7, data file 7: 2559 blocks scanned
tablespace 17, data file 8: 12799 blocks scanned
tablespace 9, data file 9: 39519 blocks scanned
tablespace 10, data file 10: 5759 blocks scanned
tablespace 12, data file 11: 12799 blocks scanned
tablespace 0, data file 12: 69887 blocks scanned
tablespace 18, data file 14: 12799 blocks scanned
Reading EXT.dat 4183 entries loaded and sorted
Reading SEG.dat 3089 entries loaded
Reading COMPATSEG.dat 1 entries loaded
DUL> bootstrap;
Compatibility segment found at file = 1, block = 417
database version 9 bootstrap$ at file 1, block 377
. unloading table BOOTSTRAP$ 57 rows unloaded
Reading BOOTSTRAP.dat 57 entries loaded
Parsing Bootstrap$ contents
Generating dict.ddl for version 9
OBJ$: segobjno 18, file 1
TAB$: segobjno 2, tabno 1, file 1
COL$: segobjno 2, tabno 5, file 1
USER$: segobjno 10, tabno 1, file 1
Running generated file "@dict.ddl" to unload the dictionary tables
. unloading table OBJ$
DUL: Warning: Recreating file "OBJ.ctl"
32266 rows unloaded
. unloading table TAB$
DUL: Warning: Recreating file "TAB.ctl"
1074 rows unloaded
. unloading table COL$
DUL: Warning: Recreating file "COL.ctl"
38658 rows unloaded
. unloading table USER$
DUL: Warning: Recreating file "USER.ctl"
80 rows unloaded
Reading USER.dat 80 entries loaded
Reading OBJ.dat 32266 entries loaded
Reading TAB.dat 1074 entries loaded
Reading COL.dat 38658 entries loaded
Reading SEG.dat 3089 entries loaded
Reading EXT.dat 4183 entries loaded and sorted
Reading COMPATSEG.dat 1 entries loaded
Reading BOOTSTRAP.dat 57 entries loaded
DUL: Warning: Recreating file "dict.ddl"
Generating dict.ddl for version 9
OBJ$: segobjno 18, file 1
TAB$: segobjno 2, tabno 1, file 1
COL$: segobjno 2, tabno 5, file 1
USER$: segobjno 10, tabno 1, file 1
TABPART$: segobjno 230, file 1
INDPART$: segobjno 234, file 1
TABCOMPART$: segobjno 249, file 1
INDCOMPART$: segobjno 253, file 1
TABSUBPART$: segobjno 240, file 1
INDSUBPART$: segobjno 245, file 1
IND$: segobjno 2, tabno 3, file 1
ICOL$: segobjno 2, tabno 4, file 1
LOB$: segobjno 2, tabno 6, file 1
Running generated file "@dict.ddl" to unload the dictionary tables
. unloading table OBJ$
DUL: Warning: Recreating file "OBJ.ctl"
32266 rows unloaded
. unloading table TAB$
DUL: Warning: Recreating file "TAB.ctl"
1074 rows unloaded
. unloading table COL$
DUL: Warning: Recreating file "COL.ctl"
38658 rows unloaded
. unloading table USER$
DUL: Warning: Recreating file "USER.ctl"
80 rows unloaded
. unloading table TABPART$ 157 rows unloaded
. unloading table INDPART$ 202 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$ 1510 rows unloaded
. unloading table ICOL$ 2147 rows unloaded
. unloading table LOB$ 432 rows unloaded
Reading USER.dat 80 entries loaded
Reading OBJ.dat 32266 entries loaded
Reading TAB.dat 1074 entries loaded
Reading COL.dat 38658 entries loaded
Reading SEG.dat 3089 entries loaded
Reading EXT.dat 4183 entries loaded and sorted
Reading TABPART.dat 157 entries loaded and sorted
Reading TABCOMPART.dat 0 entries loaded and sorted
Reading TABSUBPART.dat 0 entries loaded and sorted
Reading INDPART.dat 202 entries loaded and sorted
Reading INDCOMPART.dat 0 entries loaded and sorted
Reading INDSUBPART.dat 0 entries loaded and sorted
Reading IND.dat 1510 entries loaded
Reading LOB.dat 432 entries loaded
Reading ICOL.dat 2147 entries loaded
Reading COMPATSEG.dat 1 entries loaded
Reading BOOTSTRAP.dat 57 entries loaded


DUL>SCAN TABLES;[/quote]

在dul.log中某一表格的导出文件为：

[quote]Analyzing segment: data object id=37981 segment header at ( file=14 block=6041)
heap organized table
This is probably XU2.D21

DUL: Warning: Column 1: type based on heuristic guessing
DUL: Warning: Column 8: type based on heuristic guessing
DUL: Warning: Column 9: type based on heuristic guessing
DUL: Warning: Column 10: type based on heuristic guessing
DUL: Warning: Column 14: type based on heuristic guessing
DUL: Warning: Column 18: type based on heuristic guessing
DUL: Warning: Column 22: type based on heuristic guessing
DUL: Warning: Column 24: type based on heuristic guessing
DUL: Warning: Column 26: type based on heuristic guessing
Col Seen Max PCT PRINT NUMBERS DATES TIMESTAMP WITH TZ INTRVAL ROWIDS LOB
no count Size NUL 75%100% AnyNice AnyNice AnyNice AnyNice Y2M D2S AnyNice
1 1 6 0 0 0 100 100 0 0 0 0 0 0 0 0 100 0 0
2 1 5 0 100 100 0 0 0 0 0 0 0 0 0 0 0 0 0
3 1 7 0 0 0 0 0 100 100 0 0 0 0 0 0 0 0 0
4 1 5 0 0 0 100 100 0 0 0 0 0 0 0 0 0 0 0
5 1 7 0 0 0 0 0 100 100 0 0 0 0 0 0 0 0 0
6 1 4 0 0 0 100 100 0 0 0 0 0 0 0 0 0 0 0
7 1 7 0 0 0 0 0 100 100 0 0 0 0 0 0 0 0 0
8 1 10 0 100 100 0 0 0 0 0 0 0 0 0 0 100 0 0
9 1 8 0 0 0 100 100 0 0 0 0 0 0 0 0 100 0 0
10 1 8 0 0 0 100 100 0 0 0 0 0 0 0 0 100 0 0
11 1 7 0 100 100 0 0 0 0 0 0 0 0 0 0 0 0 0
12 1 7 0 0 0 0 0 100 100 0 0 0 0 0 0 0 0 0
13 1 4 0 0 0 100 100 0 0 0 0 0 0 0 0 0 0 0
14 1 8 0 0 0 100 100 0 0 0 0 0 0 0 0 100 0 0
15 1 7 0 0 0 0 0 100 100 0 0 0 0 0 0 0 0 0
16 1 11 0 100 100 0 0 0 0 0 0 0 0 0 0 0 0 0
17 1 7 0 0 0 0 0 100 100 0 0 0 0 0 0 0 0 0
18 1 10 0 100 100 0 0 0 0 0 0 0 0 0 0 100 0 0
19 1 19 0 100 100 0 0 0 0 0 0 0 0 0 0 0 0 0
20 1 19 0 100 100 0 0 0 0 0 0 0 0 0 0 0 0 0
21 1 7 0 100 100 0 0 0 0 0 0 0 0 0 0 0 0 0
22 1 6 0 0 0 100 100 0 0 0 0 0 0 0 0 100 0 0
23 1 7 0 100 100 0 0 0 0 0 0 0 0 0 0 0 0 0
24 1 8 0 0 0 100 100 0 0 0 0 0 0 0 0 100 0 0
25 1 15 0 100 100 0 0 0 0 0 0 0 0 0 0 0 0 0
26 1 8 0 100 100 0 0 0 0 0 0 0 0 0 0 100 0 0
27 1 4 0 100 100 0 0 0 0 0 0 0 0 0 0 0 0 0
28 1 2 0 100 100 0 0 0 0 0 0 0 0 0 0 0 0 0
29 1 2 0 100 100 0 0 0 0 0 0 0 0 0 0 0 0 0
30 1 2 0 100 100 0 0 0 0 0 0 0 0 0 0 0 0 0
31 1 2 0 100 100 0 0 0 0 0 0 0 0 0 0 0 0 0
"2494723682" "ORA9I" "17-AUG-2009 AD 04:48:34" "36030548" "12-APR-2010 AD 08:31:11" "174968" "17-AUG-2009 AD 04:48:35" "ARCHIVELOG" "10783291662686" "10783291662683" "CURRENT" "17-AUG-2009 AD 04:48:35" "45745" "10783291662686" "26-JUL-2010 AD 11:42:56" "NOT ALLOWED" "26-JUL-2010 AD 07:36:03" "READ WRITE" "MAXIMUM PERFORMANCE" "MAXIMUM PERFORMANCE" "ENABLED" "2524999166" "PRIMARY" "10783291662686" "SESSIONS ACTIVE" "DISABLED" "NONE" "NO" "NO" "NO" "NO"

UNLOAD TABLE OBJNO37981 ( COL001 NUMBER, COL002 CHAR(5), COL003 DATE
, COL004 NUMBER, COL005 DATE, COL006 NUMBER, COL007 DATE
, COL008 CHAR(10), COL009 NUMBER, COL010 NUMBER, COL011 CHAR(7)
, COL012 DATE, COL013 NUMBER, COL014 NUMBER, COL015 DATE
, COL016 CHAR(11), COL017 DATE, COL018 CHAR(10), COL019 CHAR(19)
, COL020 CHAR(19), COL021 CHAR(7), COL022 NUMBER, COL023 CHAR(7)
, COL024 NUMBER, COL025 CHAR(15), COL026 CHAR(8), COL027 CHAR(4)
, COL028 CHAR(2), COL029 CHAR(2), COL030 CHAR(2), COL031 CHAR(2) )
STORAGE( DATAOBJNO 37981 );[/quote]


将导出脚本取出，在dul中进行导出
[quote]DUL> UNLOAD TABLE OBJNO37981 ( COL001 NUMBER, COL002 CHAR(5), COL003 DATE
2 , COL004 NUMBER, COL005 DATE, COL006 NUMBER, COL007 DATE
3 , COL008 CHAR(10), COL009 NUMBER, COL010 NUMBER, COL011 CHAR(7)
4 , COL012 DATE, COL013 NUMBER, COL014 NUMBER, COL015 DATE
5 , COL016 CHAR(11), COL017 DATE, COL018 CHAR(10), COL019 CHAR(19)
6 , COL020 CHAR(19), COL021 CHAR(7), COL022 NUMBER, COL023 CHAR(7)
7 , COL024 NUMBER, COL025 CHAR(15), COL026 CHAR(8), COL027 CHAR(4)
8 , COL028 CHAR(2), COL029 CHAR(2), COL030 CHAR(2), COL031 CHAR(2) )
9 STORAGE( DATAOBJNO 37981 );
. unloading table OBJNO37981 1 row unloaded[/quote]


导至Oracle 10g库中
[quote][ora10g@mcprod oracle]$ imp \"sys/oracle as sysdba\" file=OBJNO37981.dmp full=y

Import: Release 10.2.0.3.0 - Production on Wed Oct 13 08:27:33 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.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 SYS
. importing Bernard's DUL's objects into SYS
. . importing table "OBJNO37981" 1 rows imported
Import terminated successfully without warnings.[/quote]

可以看到数据已经导出，但字段表名均用代号显示，如果没有建表语句，那工作量就大了
[quote]SQL> desc OBJNO37981
Name Null? Type
----------------------------------------- -------- ----------------------------
COL001 NUMBER
COL002 CHAR(5)
COL003 DATE
COL004 NUMBER
COL005 DATE
COL006 NUMBER
COL007 DATE
COL008 CHAR(10)
COL009 NUMBER
COL010 NUMBER
COL011 CHAR(7)
COL012 DATE
COL013 NUMBER
COL014 NUMBER
COL015 DATE
COL016 CHAR(11)
COL017 DATE
COL018 CHAR(10)
COL019 CHAR(19)
COL020 CHAR(19)
COL021 CHAR(7)
COL022 NUMBER
COL023 CHAR(7)
COL024 NUMBER
COL025 CHAR(15)
COL026 CHAR(8)
COL027 CHAR(4)
COL028 CHAR(2)
COL029 CHAR(2)
COL030 CHAR(2)
COL031 CHAR(2)


SQL> select count(*) from OBJNO37981;

COUNT(*)
----------
1
[/quote]