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表数据。

posted on 2020-12-14 16:49  空白葛  阅读(1257)  评论(1)  编辑  收藏  举报

导航