技术开发 频道
IT168首页 > 技术开发 > 技术开发技术 > 正文

Life is DUL without it:DUL流程

作者：IT168 杨宝秋 编辑： 晓熊 2007-11-22 00:00 来源：IT168
分享

5 DUL 使用示例
5.1 无系统表空间的数据转载
如下以无系统表空间这种状况下恢复hr下的employees这张表示例一下这种状况的操作流程。
5.1.1 模拟问题
○1去掉control.dul中的系统表空间的数据文件，删除dul工作目录所有的.ctl及,dat文件。     
$ cat control.dul
1 2 /yang/oradata/orcl/undotbs01.dbf
3 3 /yang/oradata/orcl/example01.dbf
4 4 /yang/oradata/orcl/indx01.dbf
5 5 /yang/oradata/orcl/tools01.dbf
6 6 /yang/oradata/orcl/users01.dbf
6 7 /yang/oradata/orcl/users02.dbf
○2启动dul验证一下，bootstrap会报错。
$ dul
Data UnLoader 9.2.4.4.2 - Internal Use Only - on Mon Oct 29 08:57:38 2007
with 64-bit io functions
Copyright (c) 1994 2004 Bernard van Duijnen All rights reserved.
DUL> bootstrap;
Scanning SYSTEM tablespace to locate compatibility segment ...
DUL: Warning: No files found for tablespace 0
Reading EXT.dat 0 entries loaded and sorted
Reading SEG.dat 0 entries loaded
Reading COMPATSEG.dat 0 entries loaded
DUL: Error: No compatibility segments found
DUL>
5.1.2 描扫库生成EXT.dat和SEG.dat文件
DUL> scan database;
DUL: Warning: Recreating file "EXT.dat"
DUL: Warning: Recreating file "SEG.dat"
DUL: Warning: Recreating file "COMPATSEG.dat"
tablespace 1, data file 2: 25599 blocks scanned
tablespace 3, data file 3: 15359 blocks scanned
tablespace 4, data file 4: 3199 blocks scanned
tablespace 5, data file 5: 1279 blocks scanned
tablespace 6, data file 6: 3199 blocks scanned
tablespace 6, data file 7: 639 blocks scanned
Reading EXT.dat 80 entries loaded and sorted
Reading SEG.dat 68 entries loaded
Reading COMPATSEG.dat 0 entries loaded
5.1.3 用scan database的结果扫描数据文件中的表。
DUL> alter session set use_scanned_extent_map = true;
Parameter altered
DUL> scan tables;
   

    Scan tables会生成大量的输出，可以用crt软件的log session功能等方式所这些输出保存下来，接下来的工作就需要很大的细心、耐心及对应用十分的了解。Scan tables的结果中，dul会猜各个表各个字段的类型，并给出几条示例的记录，同时也给出来unload的语句。如下以hr.employees为例，从输出结果中找到如下一段。
object信息
Analyzing segment: data object id=6302 segment header at ( file=3 block=83)
heap organized table
列信息，可见dul找到的这个object有11列，与hr.employees一样    
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 107 3 0 0 0 100 100 0 0 0 0 0 0 0 0 0 0 0
2 107 11 0 100 100 0 0 0 0 0 0 0 0 0 0 26 0 0
3 107 11 0 100 100 0 0 0 0 0 0 0 0 0 0 35 0 0
4 107 8 0 100 100 0 0 0 0 0 0 0 0 0 0 53 0 0
5 107 18 0 100 100 0 0 0 0 0 0 0 0 0 0 0 0 0
6 107 7 0 0 0 0 0 100 100 0 0 0 0 0 0 0 0 0
7 107 10 0 100 100 0 0 0 0 0 0 0 0 0 0 91 0 0
8 107 3 0 0 0 100 100 0 0 0 0 0 0 0 0 0 0 0
9 107 2 67 0 0 100 100 0 0 0 0 0 0 0 0 0 0 0
10 107 3 0 0 0 100 100 0 0 0 0 0 0 0 0 0 0 0
11 106 3 0 0 0 100 100 0 0 0 0 0 0 0 0 0 0 0
"198" "Donald" "OConnell" "DOCONNEL" "650.507.9833" "21-JUN-1999 AD 00:00:00" "SH_CLERK" "2600" "" "124" "50"
"199" "Douglas" "Grant" "DGRANT" "650.507.9844" "13-JAN-2000 AD 00:00:00" "SH_CLERK" "2600" "" "124" "50"
"200" "Jennifer" "Whalen" "JWHALEN" "515.123.4444" "17-SEP-1987 AD 00:00:00" "AD_ASST" "4400" "" "101" "10"
"201" "Michael" "Hartstein" "MHARTSTE" "515.123.5555" "17-FEB-1996 AD 00:00:00" "MK_MAN" "13000" "" "100" "20"
"202" "Pat" "Fay" "PFAY" "603.123.6666" "17-AUG-1997 AD 00:00:00" "MK_REP" "6000" "" "201" "20"
dul 给出的unload语句，从中我们可以进一步确认这个表是不是hr用户下的employees表，dul并给出了所猜的各个列的类型。
UNLOAD TABLE OBJNO6302 ( COL001 NUMBER, COL002 VARCHAR2(11), COL003 VARCHAR2(11)
, COL004 VARCHAR2(8), COL005 VARCHAR2(18), COL006 DATE, COL007 VARCHAR2(10)
, COL008 NUMBER, COL009 NUMBER, COL010 NUMBER, COL011 NUMBER )
STORAGE( DATAOBJNO 6302 );
5.1.4 Unload 表
○1指定以.dmp方式导出，当然可以选sql*loader方式。
DUL> set export_mode=TRUE;
Parameter altered
○2 对5.1.3中dul生成的Unload语句修改一下表名，并执行这条语句完成Unload操作。
DUL> UNLOAD TABLE EMPLOYEES ( COL001 NUMBER, COL002 VARCHAR2(11), COL003 VARCHAR2(11)
2 , COL004 VARCHAR2(8), COL005 VARCHAR2(18), COL006 DATE, COL007 VARCHAR2(10)
3 , COL008 NUMBER, COL009 NUMBER, COL010 NUMBER, COL011 NUMBER )
4 STORAGE( DATAOBJNO 6302 );
. unloading table EMPLOYEES 107 rows unloaded
DUL> exit
Life is DUL without it
○3查看一下结果。
$ ls -l EMPLOYEE*
-rw-r--r-- 1 ora2 dba 10120 10月 29 09:29 EMPLOYEES.dmp

5.2 被DROP/TRUNCATE表的恢复
    用Dul对drop/truncate的表的恢复的操作流程与5.1是一致的，不同的是这一部份找寻的数据量相对于5.1要小的很多，且这部分有一些限制。前而已经提到，Oracle Drop/truncate一张表的操作从10046的跟踪上可以看到只是对数据字典的操作，并不操作实际的数据块。Dul恢复被drop/truncate了的表成功与否取决于被drop/truncate了的表所占用的数据块是不是被新的段(表、索引等)所重用，如果被重用就无法完成这样的恢复了。如下以对被drop了的表的恢复给个例子：

5.2.1 在hr下建一张表T1,插入一些数据     

SQL> desc T1
Name Null? Type
----------- -------- ------------- 
ID NUMBER
NAME VARCHAR2(12)

SQL> select count(*) from hr.T1;

COUNT(*)
---------- 
16384
SQL>
SQL> select * from T1 where rownum<4;

ID NAME
---------- ------------ 
1 sdfsdfasfsdf
1 sdfsdfasfsdf
1 sdfsdfasfsdf

SQL>
5.2.2 删除表T1
SQL> drop table table hr.T1; 
Table droped. 
SQL> alter system switch logfile; 
System altered. 
SQL>

5.2.3 启动dul,scan database，如果你知道这个表可能在哪些数据文件中，可以在control.dul中去掉其它的文件以减少scan的量。     

$ dul
Data UnLoader 9.2.4.4.2 - Internal Use Only - on Mon Oct 29 10:07:43 2007
with 64-bit io functions
Copyright (c) 1994 2004 Bernard van Duijnen All rights reserved.
DUL> scan database;
DUL: Warning: Recreating file "EXT.dat"
DUL: Warning: Recreating file "SEG.dat"
DUL: Warning: Recreating file "COMPATSEG.dat"
tablespace 1, data file 2: 25599 blocks scanned
tablespace 3, data file 3: 15359 blocks scanned
tablespace 4, data file 4: 3199 blocks scanned
tablespace 5, data file 5: 1279 blocks scanned
tablespace 6, data file 6: 3199 blocks scanned
tablespace 6, data file 7: 639 blocks scanned
Reading EXT.dat 81 entries loaded and sorted
Reading SEG.dat 68 entries loaded
Reading COMPATSEG.dat 0 entries loaded


5.2.4 扫描表，从中找出删除的表   

DUL> alter session set use_scanned_extent_map = true;
Parameter altered
DUL> scan tables;
如下为scan tables结果中找到的T1的部分:
Analyzing segment: data object id=6579 segment header at ( file=3 block=211)
heap organized table
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 16384 2 0 0 0 100 100 0 0 0 0 0 0 0 0 0 0 0
2 16384 12 0 100 100 0 0 0 0 0 0 0 0 0 0 0 0 0
"1" "sdfsdfasfsdf"
"1" "sdfsdfasfsdf"
"1" "sdfsdfasfsdf"
"1" "sdfsdfasfsdf"
"1" "sdfsdfasfsdf"

UNLOAD TABLE OBJNO6579 ( COL001 NUMBER, COL002 VARCHAR2(12) )
STORAGE( DATAOBJNO 6579 );

5.2.5 修改unload语句，完成Unload操作
DUL> UNLOAD TABLE T1 ( ID NUMBER, NAME VARCHAR2(12) )
2 STORAGE( DATAOBJNO 6579 );
. unloading table T1 16384 rows unloaded
DUL> exit

Life is DUL without it

5.3 offline drop 数据文件中表的恢复
    这是在实际操作中特别常见的，经常有一些用户的数据库处于非归档的状态，且数据库没有良好的备份。由于操作系统的异常或突然的断电导致了一些数据文件头的损坏，此时启动数据库会报这些数据文件需要介质恢复，因没有归档方式的备份很多的用户为尽快的将数据库启动，便对这些数据文件执行了offfline drop 操作，然后open数据库。Dul是非常适用这种情况下数据库恢复的。如下以offline drop掉usres表空间的数据文件’ /yang/oradata/orcl/users02.dbf’为例来说明一下这种方式的恢复问题。
5.3.1 模拟问题的产生
○1 USERS表空间有两个数据文件，users02.dbf所对应的file#为7     

SQL> select tablespace_name,file_name
2 from dba_data_files where tablespace_name='USERS';
TABLESPACE_NAME FILE_NAME
------------------------------ ---------------------------------------- 
USERS /yang/oradata/orcl/users01.dbf
USERS /yang/oradata/orcl/users02.dbf
SQL>
SQL> select file#,name from v$datafile
2 where name='/yang/oradata/orcl/users02.dbf';
FILE# NAME
---------- ---------------------------------------- 
7 /yang/oradata/orcl/users02.dbf
SQL>
○2 offline drop掉file#=7的数据文件
SQL> alter database datafile 7 offline drop;
Database altered.
SQL>

○3 查一下file#=7中都含有哪些段,如下查询可以看出它含有分区表RANGE_TAB_EXAMPLE的一个分区 
    SQL> SELECT OWNER, SEGMENT_NAME, PARTITION_NAME, RELATIVE_FNO
2 FROM SYS.SYS_DBA_SEGS W
3 WHERE RELATIVE_FNO = 7
4 /

OWNER SEGMENT_NAME PARTITION_NAME RELATIVE_FNO
----- ------------------ -------------------- ------------
HR RANGE_TAB_EXAMPLE PART2 7
○4 查一下offline drop掉的文件中的分区会报错
SQL>
SQL> select count(*) from hr.RANGE_TAB_EXAMPLE partition(part2);
select count(*) from hr.RANGE_TAB_EXAMPLE partition(part2)
*
ERROR at line 1:
ORA-00376: file 7 cannot be read at this time
ORA-01110: data file 7: '/yang/oradata/orcl/users02.dbf'
SQL>


5.3.2 Dul恢复
○1 把file#=7添加到control.dul文件中
$ cat control.dul
0 1 /yang/oradata/orcl/system01.dbf
1 2 /yang/oradata/orcl/undotbs01.dbf
3 3 /yang/oradata/orcl/example01.dbf
4 4 /yang/oradata/orcl/indx01.dbf
5 5 /yang/oradata/orcl/tools01.dbf
6 6 /yang/oradata/orcl/users01.dbf
6 7 /yang/oradata/orcl/users02.dbf
$
○2 启动dul，执行bootstrap.
$ dul

Data UnLoader 9.2.4.4.2 - Internal Use Only - on Mon Oct 29 13:00:31 2007
with 64-bit io functions

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


DUL: Warning: Recreating file "dul.log"
DUL> bootstrap;
Scanning SYSTEM tablespace to locate compatibility segment ...
tablespace 0, data file 1: 31999 blocks scanned
Reading EXT.dat 1052 entries loaded and sorted
Reading SEG.dat 986 entries loaded
Reading COMPATSEG.dat 1 entries loaded
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
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
Running generated file "@dict.ddl" to unload the dictionary tables
. unloading table OBJ$ 6447 rows unloaded
. unloading table TAB$ 531 rows unloaded
. unloading table COL$ 27905 rows unloaded
. unloading table USER$ 29 rows unloaded
Reading USER.dat 29 entries loaded
Reading OBJ.dat 6447 entries loaded
Reading TAB.dat 531 entries loaded
Reading COL.dat 27905 entries loaded
Reading SEG.dat 986 entries loaded
Reading EXT.dat 1052 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"
6447 rows unloaded
. unloading table TAB$
DUL: Warning: Recreating file "TAB.ctl"
531 rows unloaded
. unloading table COL$
DUL: Warning: Recreating file "COL.ctl"
27905 rows unloaded
. unloading table USER$
DUL: Warning: Recreating file "USER.ctl"
29 rows unloaded
. unloading table TABPART$ 29 rows unloaded
. unloading table INDPART$ 24 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$ 584 rows unloaded
. unloading table ICOL$ 1067 rows unloaded
. unloading table LOB$ 51 rows unloaded
Reading USER.dat 29 entries loaded
Reading OBJ.dat 6447 entries loaded
Reading TAB.dat 531 entries loaded
Reading COL.dat 27905 entries loaded
Reading SEG.dat 986 entries loaded
Reading EXT.dat 1052 entries loaded and sorted
Reading TABPART.dat 29 entries loaded and sorted
Reading TABCOMPART.dat 0 entries loaded and sorted
Reading TABSUBPART.dat 0 entries loaded and sorted
Reading INDPART.dat 24 entries loaded and sorted
Reading INDCOMPART.dat 0 entries loaded and sorted
Reading INDSUBPART.dat 0 entries loaded and sorted
Reading IND.dat 584 entries loaded
Reading LOB.dat 51 entries loaded
Reading ICOL.dat 1067 entries loaded
Reading COMPATSEG.dat 1 entries loaded
Reading BOOTSTRAP.dat 57 entries loaded

○3 设定以.dmp为导出模式，并完成hr.RANGE_TAB_EXAMPLE表的导出。
DUL> set export_mode=true;
Parameter altered

DUL> unload table hr.RANGE_TAB_EXAMPLE;
. unloading table RANGE_TAB_EXAMPLE
. Unloading partition PART1
. Unloading partition PART2
. table RANGE_TAB_EXAMPLE total 5 rows unloaded
DUL>

○4 可见PART2分区unload成功，把RANGE_TAB_EXAMPLE重建掉，导入Dul Unload出来的数据到相应的表即可。

0
123查看全文
第1页：DUL 使用示例第2页：LOB字段的DUL测试第3页：总结及参考资料
相关文章
    盛拓传媒简介 关于IT168 广告服务 使用条款 投稿指南 联系我们
    北京盛拓优讯信息技术有限公司. 版权所有 中华人民共和国增值电信业务经营许可证 编号：京B2-20170206 北京市公安局海淀分局网监中心备案编号：11010802020118
    广播电视节目制作经营许可证:(京) 字第07177号 信息系统安全等级保护备案：11010813655-00001 测绘资质证书:乙测资字11005067 网络文化经营许可证:京网文(2018) 1456-138号
    违法和不良信息举报电话 :010-59548436,010-59544810,17352615267,17816876620,niuxiaotong@pcpop.com,jiachanjuan@126.com