Oracle 非常规恢复之一dul恢复初试
dul介绍
DUL是Data Unloader的缩写，Oracle内部恢复工具，为Oracle公司工程师 Bernard van Duijnen 开发，以标准C写成，在不同平台上会使用不同的binary文件，可以直接从Oracle的数据文件中读取数据，转换为DMP或文本格式输出，在特殊情况下可以用来进行数据恢复。这些特殊情况主要指，数据库没有有效备份、或者系统表空间损坏，或者在非归档模式下的不可逆数据损失等等，一旦普通手段失去作用，DUL就可以作为最后一招来最大限度的挽救用户数据。
由于bernard.van.duijnen 开发DUL的时候使用了一些Oracle数据库的内核头文件（主要是一些.h)文件，所以在Oracle来说DUL是需要被严格控制的，因为DUL直接用了Oracle数据库的源代码，属于Oracle知识产权的一部分。然而DUL并非一个商业工具，这个工具仅在Oracle内部流通，但是逐渐的，DUL也开始流入民间，被一些资深Oracle工程师所使用。随着数据库版本的变化，DUL工具也在逐渐升级之中，对应Oracle8 / Oracle8i / Oracle9i / Oracle10g都有其相应版本。
早期DUL是不加锁的，后来变成加日期锁，就是说 好比bernard.van.duijnen 在10月1日发布了一个版本，日期锁是30天，那么这个版本到11月1日基本就失效了， DUL不是简单的读OS时间，所以改OS时间是没用的。 因为Oracle的datafile里也记录了一个当前时间，所以DUL读的是datafile里的时间。 一般用户不可能为了用DUL去改那个时间。
作为DBA们首先应该知道DBA第一守则：备份重于一切。首先应该把备份作为数据恢复的第一手段。
dul使用
配置文件
dul软件本身只有500多K，使用dul有2个配置文件分别为init.dul，control.txt
[oracle@19db1 dul]$ ll
total 1152
-rw-r--r-- 1 oracle oinstall 218 May 3 00:01 control.txt
-rwxr-xr-x 1 oracle oinstall 850520 Mar 26 16:22 dul
-rw-r--r-- 1 oracle oinstall 306359 Dec 11 2020 dul.gz
-rw-r--r-- 1 oracle oinstall 5021 May 5 13:28 dul.log
-rw-r--r-- 1 oracle oinstall 257 Apr 7 2016 dul.sql
-rw-r--r-- 1 oracle oinstall 334 May 3 00:00 init.dul
[oracle@19db1 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
[oracle@19db1 dul]$ more control.txt
0 1 /u01/dbsid/system01.dbf block_size 8192
5 5 /u01/dbsid/base01.dbf block_size 8192
5 130 /u01/dbsid/base02.dbf block_size 8192
5 176 /u01/dbsid/base03.dbf block_size 8192
5 177 /u01/dbsid/base05.dbf block_size 8192
dul加入了有效期验证(早期DUL是不加锁的，后来变成加日期锁)，如果没有最新的dul，则需要调整系统日期和数据文件的日期
[oracle@zsdb dul]$ ./dul
Data UnLoader: 12.0.0.0.0 - Internal Only - on Mon Dec 14 13:43:33 2020
with 64-bit io functions and the decompression option and the parallel option (beta)
Copyright (c) 1994 2018 Bernard van Duijnen All rights reserved.
Strictly Oracle Internal Use Only
Cannot start now
You need a more recent DUL version for this os
实践过程
注：以下恢复测试并非针对truncate恢复，是针对数据文件异常不可读的情况。
测试数据生成
在数据库中添加新用户test，创建表T1
SQL> create user test identified by test;
SQL> grant dba to test;
SQL> conn test/test
Connected.
SQL> select * from cat;
SQL> create table t1 as select * from dba_objects;
Table created.
SQL> select count(1) from t1;
COUNT(1)
----------
75011
SQL> alter system checkpoint;
假如此时t1表所在数据文件异常或者其他原因无法查询，进行dul恢复。
编辑dul配置文件
参考《配置文件》章节，调整control.txt路径，需提前知道t1表所在的表空间，以及表空间内的数据文件
SQL> SQL> select ts# || ' '|| rfile# || ' '|| name || ' block_size '|| block_size
2 from v$datafile
3 /
TS#||''||RFILE#||''||NAME||'BLOCK_SIZE'||BLOCK_SIZE
--------------------------------------------------------------------------------
0 1 /oradata/orcl/system01.dbf block_size 8192
4 4 /oradata/orcl/users01.dbf block_size 8192
[oracle@zsdb dul]$ vi control.txt
0 1 /home/oracle/dul/system01.dbf block_size 8192
4 4 /home/oracle/dul/users01.dbf block_size 8192
[oracle@zsdb dul]$ 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
Buffer=10485760
control_file = control.txt
db_block_size=8192
export_mode=true
--false表示是sqlloader,true表示imp
compatible=11
调整系统时间和数据文件时间
[root@zsdb dul]# touch -d '2019-03-01' users01.dbf
[root@zsdb dul]# touch -d '2019-03-01' system01.dbf
[root@zsdb dul]# date -s '2019-03-01'
2019年 03月 01日 星期五 00:00:00 CST
dul数据抽取
[oracle@zsdb dul]$ ./dul
Data UnLoader: 12.0.0.0.0 - Internal Only - on Fri Mar 1 00:02:46 2019
with 64-bit io functions and the decompression option and the parallel option (beta)
Copyright (c) 1994 2018 Bernard van Duijnen All rights reserved.
Strictly Oracle Internal Use Only
DUL: Warning: Recreating file "dul.log"
and sorted 0 entries
and sorted 0 entries
and sorted 0 entries
and sorted 0 entries
and sorted 0 entries
and sorted 0 entries
and sorted 0 entries
and sorted 0 entries
and sorted 0 entries
and sorted 0 entries
and sorted 0 entries
and sorted 0 entries
Found db_id = 1581297075
Found db_name = ORCL
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
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$ 75019 rows unloaded
. unloading table TAB$ 2888 rows unloaded
. unloading table COL$ 94219 rows unloaded
. unloading table USER$ 91 rows unloaded
Reading USER.dat 91 entries loaded
Reading OBJ.dat 75019 entries loaded
and sorted 75019 entries
and sorted 0 entries
Reading TAB.dat 2888 entries loaded
Reading COL.dat 94219 entries loaded
and sorted 94219 entries
and sorted 0 entries
and sorted 0 entries
and sorted 0 entries
and sorted 0 entries
and sorted 0 entries
and sorted 0 entries
and sorted 0 entries
Reading BOOTSTRAP.dat 60 entries loaded
and sorted 0 entries
and sorted 0 entries
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"
75019 rows unloaded
. unloading table TAB$
DUL: Warning: Recreating file "TAB.ctl"
2888 rows unloaded
. unloading table COL$
DUL: Warning: Recreating file "COL.ctl"
94219 rows unloaded
. unloading table USER$
DUL: Warning: Recreating file "USER.ctl"
91 rows unloaded
. unloading table TABPART$ 272 rows unloaded
. unloading table INDPART$ 303 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$ 5143 rows unloaded
. unloading table ICOL$ 7581 rows unloaded
. unloading table LOB$ 1272 rows unloaded
. unloading table COLTYPE$ 3003 rows unloaded
. unloading table TYPE$ 2872 rows unloaded
. unloading table COLLECTION$ 985 rows unloaded
. unloading table ATTRIBUTE$ 11127 rows unloaded
. unloading table LOBFRAG$ 3 rows unloaded
. unloading table LOBCOMPPART$ 0 rows unloaded
. unloading table UNDO$ 33 rows unloaded
. unloading table TS$ 12 rows unloaded
. unloading table PROPS$ 36 rows unloaded
Reading USER.dat 91 entries loaded
Reading OBJ.dat 75019 entries loaded
and sorted 75019 entries
and sorted 0 entries
Reading TAB.dat 2888 entries loaded
Reading COL.dat 94219 entries loaded
and sorted 94219 entries
and sorted 0 entries
Reading TABPART.dat 272 entries loaded
and sorted 272 entries
Reading TABCOMPART.dat 1 entries loaded
and sorted 1 entries
Reading TABSUBPART.dat 32 entries loaded
and sorted 32 entries
Reading INDPART.dat 303 entries loaded
and sorted 303 entries
Reading INDCOMPART.dat 0 entries loaded
and sorted 0 entries
Reading INDSUBPART.dat 0 entries loaded
and sorted 0 entries
Reading IND.dat 5143 entries loaded
Reading LOB.dat
DUL: Notice: Increased the size of DC_LOBS from 1024 to 8192 entries
1272 entries loaded
Reading ICOL.dat 7581 entries loaded
Reading COLTYPE.dat 3003 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 3 entries loaded
and sorted 3 entries
Reading LOBCOMPPART.dat 0 entries loaded
and sorted 0 entries
Reading UNDO.dat 33 entries loaded
Reading TS.dat 12 entries loaded
Reading PROPS.dat 36 entries loaded
Database character set is AL32UTF8
Database national character set is AL16UTF16
DUL> unload table test.t1;
. unloading table T1 75013 rows unloaded
[oracle@zsdb dul]$ ll TEST_T1.dmp
-rw-r--r-- 1 oracle oinstall 8507058 Mar 1 00:25 TEST_T1.dmp
导入测试
SQL> SQL> conn test/test
Connected.
SQL> truncate table t1;
Table truncated.
[oracle@zsdb dul]$ imp test/test file=/home/oracle/dul/TEST_T1.dmp full=y ignore=y
Import: Release 11.2.0.3.0 - Production on Fri Mar 1 00:32:12 2019
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing 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 TEST
. importing Bernard's DUL's objects into TEST
. . importing table "T1" 75013 rows imported
Import terminated successfully without warnings.
SQL> select count(1) from test.t1;
COUNT(1)
----------
75013
至此，dul实验结束，成功恢复test用户t1表数据。