DUL使用
前阵子同事Wayne使用DUL恢复了被我删除的同义词，体现了DUL的用武之地。这方面的介绍在网络上有很多文档。我也看看如何使用DUL从数据文件中抽取纪录。
DUL的过程大致如下：
1）从系统表空间中抽取数据字典，包括最基本的对象OBJ$,TAB$,COL$,USER$，和数据段，区间的分配地址(Map)
2）抽取对象纪录。根据步骤1）中得到的数据字典和存储Map定位物理块，读取纪录
3）将抽取的纪录以导出文件或者SQL*Loader文件保存
---------------------------------------------------
--- 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 = c:Newfolderdulcontrol.dul
db_block_size=8192
export_mode=false
COMPATIBLE=9
LDR_PHYS_REC_SIZE =0
--------------------------------------------------
---------------------------------------------------
--- control.dul 控制文件，记录文件的路径
--- from "select ts#, rfile#, name from v$datafile;"
----------------------------------------------------
0 1 c:NewFolderdulsystem01.dbf
--------------------------------------------------
运行dul.exe
C:NewFolderdul>dul dictv8.ddl
Data UnLoader 9.1.1.0.0 - Internal Use Only - on Tue Jan 17 13:57:04 2006
with 64-bit io functions
Copyright (c) 1994/2001 Bernard van Duijnen All rights reserved.
Parameter altered
Parameter altered
Parameter altered
Parameter altered
. unloading table OBJ$ 30189 rows unloaded
. unloading table TAB$ 910 rows unloaded
. unloading table COL$ 35225 rows unloaded
. unloading table USER$ 65 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)
DUL从system表空间中抽取数据字典信息(OBJ$,TAB$,COL$,USER$ automatically).
C:NewFolderdul>dul
Data UnLoader 9.1.1.0.0 - Internal Use Only - on Tue Jan 17 13:57:53 2006
with 64-bit io functions
Copyright (c) 1994/2001 Bernard van Duijnen All rights reserved.
DUL: Warning: Recreating file "dul.log"
Loaded 65 entries from USER.dat
DUL: Error: File OBJ.dat, line 1: token missing
DUL: Warning: Ignoring file OBJ.dat cache
Loaded 910 entries from TAB.dat
Loaded 35225 entries from COL.dat
DUL> scan database;
tablespace 0, data file 1: 52479 blocks scanned
Loaded 1445 entries from EXT.dat
Sorted 1445 entries
Loaded 1083 entries from SEG.dat
Loaded 1 entries from COMPATSEG.dat
Scan database抽取数据段和区间的分布(map)信息
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
Loaded 57 entries from BOOTSTRAP.dat
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"
30189 rows unloaded
. unloading table TAB$
DUL: Warning: Recreating file "TAB.ctl"
910 rows unloaded
. unloading table COL$
DUL: Warning: Recreating file "COL.ctl"
35225 rows unloaded
. unloading table USER$
DUL: Warning: Recreating file "USER.ctl"
65 rows unloaded
Loaded 65 entries from USER.dat
Loaded 30189 entries from OBJ.dat
Loaded 910 entries from TAB.dat
Loaded 35225 entries from COL.dat
Loaded 1083 entries from SEG.dat
Loaded 1445 entries from EXT.dat
Sorted 1445 entries
Loaded 1 entries from COMPATSEG.dat
Loaded 57 entries from BOOTSTRAP.dat
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
TABCOMPART$: segobjno 249, file 1
TABSUBPART$: segobjno 240, 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"
30189 rows unloaded
. unloading table TAB$
DUL: Warning: Recreating file "TAB.ctl"
910 rows unloaded
. unloading table COL$
DUL: Warning: Recreating file "COL.ctl"
35225 rows unloaded
. unloading table USER$
DUL: Warning: Recreating file "USER.ctl"
65 rows unloaded
. unloading table TABPART$ 55 rows unloaded
. unloading table TABCOMPART$ 0 rows unloaded
. unloading table TABSUBPART$ 0 rows unloaded
. unloading table IND$ 1383 rows unloaded
. unloading table ICOL$ 1947 rows unloaded
. unloading table LOB$ 370 rows unloaded
Loaded 65 entries from USER.dat
Loaded 30189 entries from OBJ.dat
Loaded 910 entries from TAB.dat
Loaded 35225 entries from COL.dat
Loaded 1083 entries from SEG.dat
Loaded 1445 entries from EXT.dat
Sorted 1445 entries
Loaded 55 entries from TABPART.dat
Loaded 0 entries from TABCOMPART.dat
Loaded 0 entries from TABSUBPART.dat
Loaded 1383 entries from IND.dat
DUL: Warning: Increased the size of DC_LOBS from 100 to 1024 entries
Loaded 370 entries from LOB.dat
Loaded 1947 entries from ICOL.dat
Loaded 1 entries from COMPATSEG.dat
Loaded 57 entries from BOOTSTRAP.dat
bootstrap 似乎作了同样的事情，抽取数据字典记录和数据段，区间信息等
还需要抽取sys.sys$ 才能找到丢失的同义词
DUL> unload table sys.syn$;
. unloading table SYN$ 11561 rows unloaded
DUL> exit
Life is DUL without it
使用SQL*loader就可以导入这些数据了sys.user$,sys.obj$,sys.syn$.
SQL> SELECT TEXT from dba_views where view_name='DBA_SYNONYMS';
TEXT
--------------------------------------------------------------------------------
select u.name, o.name, s.owner, s.name, s.node
from sys.user$ u, sys.syn$ s, sys.obj$ o
where o.obj# = s.obj#
and o.type# = 5
and o.owner# = u.user#
在这个例子中，使用DUL要比作TSPTR要快。
转：http://nitar.blog.163.com/blog/static/2863879201022613438593/（以下为nt的例子）
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=1024
dc_segments=100000
db_block_size = 8192
export_mode=true
compatible=9
control.dul的例子：
使用下面的方法生成一个control.dul:
C:\>sqlplus /nolog
SQL*Plus: Release 9.2.0.1.0 - Production on 星期五 11月 5 11:05:44 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> conn lunar/lunar@test1 as sysdba
已连接到空闲例程。
SQL> startup mount;
ORACLE 例程已经启动。
Total System Global Area 101784276 bytes
Fixed Size 453332 bytes
Variable Size 75497472 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
数据库装载完毕。
SQL> col name for a100
SQL> set linesize 1000
SQL> set pages 999
SQL> select ts#, rfile#, name from v$datafile;
TS# RFILE# NAME
---------- ---------- ---------------------------------------------------------------
----------------------
0 1 D:\ORACLE92\ORADATA\TEST1\SYSTEM01.DBF
1 2 D:\ORACLE92\ORADATA\TEST1\UNDOTBS01.DBF
3 3 D:\ORACLE92\ORADATA\TEST1\INDX01.DBF
4 4 D:\ORACLE92\ORADATA\TEST1\USERS01.DBF
然后用上面的信息编辑一个control.dul文件，内容如下：
0 1 D:\ORACLE92\ORADATA\TEST1\SYSTEM01.DBF
1 2 D:\ORACLE92\ORADATA\TEST1\UNDOTBS01.DBF
3 3 D:\ORACLE92\ORADATA\TEST1\INDX01.DBF
4 4 D:\ORACLE92\ORADATA\TEST1\USERS01.DBF
然后就可以导数据了，首先用bootstrap命令初始化，然后用unload导数据，可以按database， user, table方式来做，呵呵
导出方式用exp的方式会快一点，但是如果有lob字段则只能用sqlloader的方式来做了，呵呵