假设我们的 数据库 遇到以下情况：
第一,没有备份;
第二,常规方法无法恢复;
第三,数据很重要, 但又无法或成本太高而进行重新输入. 如丢失了Oracle的System表空间, System表空间损坏到无法启动的地步, 意外删除表空间或表, 意外截断(Truncate)表等,
在这3中情况下， 最后的方法就是通过工具直接读取数据文件里的数据，将我们的数据找回来。并且工具不需要Oracle 环境的支持。
据我目前的了解，有3种工具：
（1）Oracle 的内部工具是DUL（Data UnLoader）。 这个需要Oracle 的支持。
（2）老熊写的ODU。 网址：www.oracleodu.com/en/
（3）d.c.b.a （支付宝 楼方鑫）写的AUL. 网址：www.anysql.net/download
ODU 之前是免费的， 现在老熊和dbsnake 在维护ODU，需要购买才能使用。
d.c.b.a的AUL 是用C 语言写的， 免费版本最大只支持2个，最大256M的datafile。 如果是更大的datafile，也是需要购买授权。
能写出这样的软件都是牛人了，需要了解各个版本Oracle block的详细信息。 AUL 和 ODU的操作和Oracle DUL 类似。
关于DUL,MOS 有说明: MOS Note 72554.1
Using DUL to Recover fromDatabase Corruption
Table Of Contents复制代码~~~~~~~~~~~~~~~~~复制代码1. Introduction复制代码2. Using DUL复制代码 2.1 Create an appropriate init.dul file复制代码 2.2 Create the control.dul file复制代码 2.3 Unload the object information复制代码 2.4 Invoke DUL复制代码 2.5 Rebuild the database复制代码3. How to rebuild object definitions that are stored in the data dictionary ?复制代码4. How to unload data when the segment header block is corrupted ?复制代码5. How to unload data when the file header block is corrupted ?复制代码6. How to unload data without the system tablespace ?复制代码7. Appendix A : Where to find the executables ?复制代码8. References复制代码复制代码1. Introduction复制代码~~~~~~~~~~~~~~~复制代码复制代码This document is to explain how to use DUL rather than to give a full复制代码explanation of Bernard's Data UnLoader capabilities.复制代码复制代码This document is for internal use only and should not be given to customers at复制代码any time, Dul should always be used by or under the supervision of a support复制代码analyst.复制代码复制代码DUL (Data UnLoader) is intended to retrieve data from the Oracle Database that复制代码cannot be retrieved otherwise. This is not an alternative for the export复制代码utility or SQL*Loader. The database may be corrupted but an individual data复制代码block used must be 100% correct. During all unloading checks are made to make复制代码sure that blocks are not corrupted and belong to the correct segment. If a复制代码corrupted block is detected by DUL, an error message is printed in the loader复制代码file and to the standard output, but this will not terminate the unloading of复制代码the next row or block.复制代码复制代码2. Using DUL复制代码~~~~~~~~~~~~复制代码复制代码First you must retrieve the necessary information about the objects that exists复制代码in the database, these statistics will be loaded into the DUL dictionary to复制代码unload the database objects.复制代码复制代码This information is retrieved from the USER$, OBJ$, TAB$ and COL$ tables that复制代码were created at database creation time, they can be unloaded based on the fact复制代码that object numbers are fixed for these tables due to the rigid nature of sql.复制代码bsq. DUL can find the information in the system tablespace, therefor the system复制代码tablespace datafile(s) must be included in the control file, if this datafile(s)复制代码is not present see chapter 6.复制代码复制代码 2.1 Create an appropriate "init.dul" file复制代码 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~复制代码复制代码 REM Platform specific parameters (NT)复制代码 REM A List of parameters for the most common platforms can be obtained from复制代码 REM http://www.nl.oracle.com/support/dul/index.html复制代码 osd_big_endian_flag=false复制代码 osd_dba_file_bits=10复制代码 osd_c_struct_alignment=32复制代码 osd_file_leader_size=1复制代码 osd_word_size = 32复制代码复制代码 REM Sizes of dul dictionary caches. If one of these is too low startup will复制代码 REM fail.复制代码 dc_columns=2000000复制代码 dc_tables=10000复制代码 dc_objects=1000000复制代码 dc_users=400复制代码 dc_segments=100000复制代码复制代码 REM Location and filename of the control file, default value is control.dul复制代码 REM in the current directory复制代码 control_file = D:\Dul\control_orcl.dul复制代码复制代码 REM Database blocksize, can be found in the init<SID>.ora file or can be复制代码 REM retrieved by doing "show parameter %db_block_size%" in server manager复制代码 REM (svrmgr23/30/l) changes this parameter to whatever the block size is of复制代码 REM the crashed database.复制代码 db_block_size=4096复制代码复制代码 REM Can/must be specified when data is needed into export/import format.复制代码 REM this will create a file suitable to use by the oracle import utility,复制代码 REM although the generated file is completely different from a table mode复制代码 REM export generated by the EXP utility. It is a single table dump file复制代码 REM with only a create table structure statement and the table data.复制代码 REM Grants, storage clauses, triggers are not included into this dump file !复制代码 export_mode=true复制代码复制代码 REM Compatible parameter must be specified an can be either 6, 7 or 8复制代码 compatible=8复制代码复制代码 REM This parameter is optional and can be specified on platforms that do复制代码 REM not support long file names (e.g. 8.3 DOS) or when the file format that复制代码 REM DUL uses "owner_name.table_name.ext" is not acceptable. The dump files复制代码 REM will be something like dump001.ext, dump002.ext, etc in this case.复制代码 file = dump复制代码复制代码 A complete list can be obtained at http://www.nl.oracle.com/support/DUL/ucg8.复制代码 html section "DUL Parameters" although this init.dul file will work in most复制代码 cases and contains all accurate parameters to succesfully complete the复制代码 unloading.复制代码复制代码 2.2 Create the "control.dul" file复制代码 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~复制代码复制代码 A good knowledge about the logical tablespace and physical datafile复制代码 structure is needed or you can do the following queries when the database复制代码 is mounted :复制代码复制代码 Oracle 6, 7复制代码 -----------复制代码 > connect internal复制代码 > spool control.DUL复制代码 > select * from v$dbfile;复制代码 > spool off复制代码复制代码 Oracle 8复制代码 --------复制代码 > connect internal复制代码 > spool control.DUL复制代码 > select ts#, rfile#, name from v$datafile;复制代码 > spool off复制代码复制代码 Edit the spool file and change, if needed, the datafile location and stripe复制代码 out unnecessary information like table headers, feedback line, etc...复制代码 A sample control file looks something like this :复制代码复制代码 REM Oracle7 control file复制代码 1 D:\DUL\DATAFILE\SYS1ORCL.DBF复制代码 3 D:\DUL\DATAFILE\DAT1ORCL.DBF复制代码 7 D:\DUL\DATAFILE\USR1ORCL.DBF复制代码复制代码 REM Oracle8 control file复制代码 0 1 D:\DUL\DATAFILE\SYS1ORCL.DBF复制代码 1 2 D:\DUL\DATAFILE\USR1ORCL.DBF复制代码 1 3 D:\DUL\DATAFILE\USR2ORCL.DBF复制代码 2 4 D:\DUL\DATAFILE\DAT1ORCL.DBF复制代码复制代码 Note : Each entry can contain a part of a datafile, this can be useful when复制代码 you need to split datafiles that are too big for DUL, so that each复制代码 part is smaller than for example 2GB. For example :复制代码复制代码 REM Oracle8 with a datafile split into multiple parts, each part is复制代码 REM smaller than 1GB !复制代码 0 1 D:\DUL\DATAFILE\SYS1ORCL.DBF复制代码 1 2 D:\DUL\DATAFILE\USR1ORCL.DBF startblock 1 endblock 1000000复制代码 1 2 D:\DUL\DATAFILE\USR1ORCL.DBF startblock 1000001 endblock 2000000复制代码 1 2 D:\DUL\DATAFILE\USR1ORCL.DBF startblock 2000001 endblock 2550000复制代码复制代码 2.3 Unload the object information复制代码 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~复制代码复制代码 Start the DUL utility with the appropriate ddl (Dul Description Language)复制代码 script. There are 3 scripts available to unload the USER$, OBJ$, TAB$ and复制代码 COL$ tables according to the database version.复制代码复制代码 Oracle6 :> dul8.exe dictv6.ddl复制代码 Oracle7 :> dul8.exe dictv7.ddl复制代码 Oracle8 :> dul8.exe dictv8.ddl复制代码复制代码 Data UnLoader: Release 8.0.5.3.0 - Internal Use Only - on Tue Jun 22 22:19:复制代码 Copyright (c) 1994/1999 Bernard van Duijnen All rights reserved.复制代码复制代码 Parameter altered复制代码 Session altered.复制代码 Parameter altered复制代码 Session altered.复制代码 Parameter altered复制代码 Session altered.复制代码 Parameter altered复制代码 Session altered.复制代码 . unloading table OBJ$ 2271 rows unloaded复制代码 . unloading table TAB$ 245 rows unloaded复制代码 . unloading table COL$ 10489 rows unloaded复制代码 . unloading table USER$ 22 rows unloaded复制代码 . unloading table TABPART$ 0 rows unloaded复制代码 . unloading table IND$ 274 rows unloaded复制代码 . unloading table ICOL$ 514 rows unloaded复制代码 . unloading table LOB$ 13 rows unloaded复制代码复制代码 Life is DUL without it复制代码复制代码 This will unload the data of the USER$, OBJ$, TAB$ and COl$ data dictionary复制代码 tables into SQL*Loader files , this can not be manipulated into dump files复制代码 of the import format. The parameter export_mode = false is hardcoded into复制代码 the ddl scripts and can not be changed to the value "true" since this will复制代码 cause DUL to fail with the error:复制代码复制代码 . unloading table OBJ$复制代码 DUL: Error: Column "DATAOBJ#" actual size(2) greater than length in column复制代码definition(1)复制代码 .............etc...............复制代码复制代码 2.4 Invoke DUL复制代码 ~~~~~~~~~~~~~~复制代码复制代码 Start DUL in interactive mode or you can prepare a scripts that contains all复制代码 the ddl commands to unload the necessary data from the database. I will复制代码 describe in this document the most used commands, this is not a complete list复制代码 of possible parameters that can be specified. A complete list can be found at复制代码 http://www.nl.oracle.com/support/DUL/ucg8.html section "DDL Description".复制代码复制代码 DUL> unload database;复制代码 => this will unload the entire database tables(includes sys'tables as well)复制代码复制代码 DUL> unload user <username>;复制代码 => this will unload all the tables owned by that particullarly user.复制代码复制代码 DUL> unload table <username.table_name>;复制代码 => this will unload the specified table owned by that username复制代码复制代码 DUL> describe <owner_name.table_name>;复制代码 => will represent the table columns with there relative pointers to the复制代码 datafile(s) owned by the specified user.复制代码复制代码 DUL> scan database;复制代码 => Scans all blocks of all data files.复制代码 Two files are generated:复制代码 1: seg.dat information of found segment headers (index/cluster/table)复制代码 (object id, file number, and block number).复制代码 2: ext.dat information of contiguous table/cluster data blocks.复制代码 (object id(V7), file and block number of segment header (V6),复制代码 file number and block number of first block,复制代码 number of blocks, number of tables)复制代码复制代码 DUL> scan tables;复制代码 => Uses seg.dat and ext.dat as input.复制代码 Scans all tables in all data segments (a header block and at least one复制代码 matching extent with at least 1 table).复制代码复制代码 2.5 Rebuild the database复制代码 ~~~~~~~~~~~~~~~~~~~~~~~~复制代码复制代码 Create the new database and use import or SQL*Loader to restore the data复制代码 retrieved by DUL. Note that when you only unloaded the data that table复制代码 structures, indexation, grants, PL/SQL and triggers will no longer exist in复制代码 the new database. To obtain an exactly same copy of the database as before复制代码 you will need to rerun your creation scripts for the tables, indexes, PL/SQL,复制代码 etc.复制代码复制代码 If you don't have these scripts then you will need to perform the steps复制代码 described in section 3 of this document.复制代码复制代码3. How to rebuild object definitions that are stored in the data dictionary复制代码~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~复制代码复制代码You want to rebuild PL/SQL(packages, procedures, functions or triggers), grants,复制代码indexes, constraints or storage clauses(old table structure) with DUL. This can复制代码be done but is a little bit tricky. You need to unload the relevant data复制代码dictionary tables using DUL and then load these tables into a healthy database,复制代码be sure to use a different user than sys or (system). Loading the data复制代码dictionary tables of the crashed database into the healthy database dictionary复制代码could corrupt the healthy database as well.复制代码复制代码Detailed explanation to retrieve for example pl/sql packages / procedures /复制代码functions from a corrupted database :复制代码复制代码 1) Follow the steps explained in the "Using DUL" section and unload the data复制代码 dictionary table "source$"复制代码复制代码 2) Create a new user into a healthy database and specify the desired default复制代码 and temporary tablespace.复制代码复制代码 3) Grant connect, resource, imp_full_database to the new user.复制代码复制代码 4) Import/load the table "source$" into the new created schema:复制代码复制代码 e.g.: imp80 userid=newuser/passw file=d:\dul\scott_emp.dmp复制代码 log=d:\dul\impemp.txt full=y复制代码复制代码 5) You can now query from the table <newuser.source$> to rebuild the pl/sql复制代码 procedures/functions from the corrupted database. Scripts can be found on复制代码 WebIv to generate such PL/SQL creation scripts.复制代码复制代码The same steps can be followed to recreate indexes, constraints, and storage复制代码parameters or to regrant privileges to the appropiate users. Please notice that复制代码you always need to use a script of some kind that can recreate the objects and复制代码include all the features of the crashed database version. For example : when复制代码the crashed database is of version 7.3.4 and you have several bitmap indexes,复制代码if you would use a script that supports version 7.3.2 or prior, then you won't复制代码be able to recreate the bitmap indexes succesful !复制代码复制代码4. How to unload data when the segment header block is corrupted复制代码~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~复制代码复制代码When DUL can't retrieve data block information on the normal way, it can scan复制代码the database to create its own segment/extent map. The procedure of scanning复制代码the database is necessary to unload the data from the datafiles.复制代码(to illustrate this example I copied an empty block ontop of the segment header复制代码block)复制代码复制代码 1) Create an appropiate "init.dul" (see 2.1) and "control.dul" (see 2.2) file.复制代码复制代码 2) Unload the table. This will fail and indicate that there is a corruption in复制代码 the segment header block:复制代码复制代码 DUL> unload table scott.emp;复制代码 . unloading table EMP复制代码 DUL: Warning: Block is never used, block type is zero复制代码 DUL: Error: While checking tablespace 6 file 10 block 2复制代码 DUL: Error: While processing block ts#=6, file#=10, block#=2复制代码 DUL: Error: Could not read/parse segment header复制代码 0 rows unloaded复制代码复制代码 3) run the scan database command :复制代码复制代码 DUL> scan database;复制代码 tablespace 0, data file 1: 10239 blocks scanned复制代码 tablespace 6, data file 10: 2559 blocks scanned复制代码复制代码 4) Indicate to DUL that it should use its own generated extent map rather than复制代码 the segment header information.复制代码复制代码 DUL> alter session set use_scanned_extent_map = true;复制代码 Parameter altered复制代码 Session altered.复制代码 DUL> unload table scott.emp;复制代码 . unloading table EMP 14 rows unloaded复制代码复制代码5. How to unload data when the datafile header block is corrupted复制代码~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~复制代码复制代码A corruption in the datafile header block is always listed at the moment you复制代码open the database this is not like a header segment block corruption (see point复制代码4) where the database can be succesfully openend and the corruption is listed复制代码at the moment you do a query of a table. Dul has no problems with recovering复制代码from such situations although there are other alternatives of recovering from复制代码this situation like patching the datafile header block.复制代码复制代码The error you will receive looks something like :复制代码 ORACLE instance started.复制代码 Total System Global Area 11739136 bytes复制代码 Fixed Size 49152 bytes复制代码 Variable Size 7421952 bytes复制代码 Database Buffers 4194304 bytes复制代码 Redo Buffers 73728 bytes复制代码 Database mounted.复制代码 ORA-01122: database file 10 failed verification check复制代码 ORA-01110: data file 10: 'D:\DATA\TRGT\DATAFILES\JUR1TRGT.DBF'复制代码 ORA-01251: Unknown File Header Version read for file number 10复制代码复制代码6. How to unload data without the system tablespace复制代码~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~复制代码复制代码If datafiles are not available for the system tablespace the unload can still复制代码continue but the object information can't be retrieved from the data dictionary复制代码tables USER$, OBJ$, TAB$ and COL$. So ownername, tablename and columnnames will复制代码not be loaded into the DUL dictionary. Identifying the tables can be an复制代码overwhelming task and a good knowledge of the RDBMS internals are needed here.复制代码First of all you need a good knowledge of your application and it's tables.复制代码Column types can be guessed by DUL, but table and column names will be lost.复制代码复制代码Any old system tablespace from the same database (may be weeks old) can be a复制代码great help !复制代码复制代码1) Create the "init.dul" file and the "control.dul" file as explained in above复制代码 steps 1 and 2. In this case the control file will contain all the datafiles复制代码 from which you want to restore but it doesn't require the system tablespace复制代码 information.复制代码复制代码2) Then You invoke dul and type the following command :复制代码复制代码 DUL> scan database;复制代码 data file 6 1280 blocks scanned复制代码复制代码 This will build the extent and segment map. Probably the dul command复制代码 interpreter will be terminated as well.复制代码复制代码3) reinvoke the dul command interpreter and do the following :复制代码复制代码 Data UnLoader: Release 8.0.5.3.0 - Internal Use Only - on Tue Aug 03 13:33:复制代码复制代码 Copyright (c) 1994/1999 Oracle Corporation, The Netherlands. All rights res复制代码 Loaded 4 segments复制代码 Loaded 2 extents复制代码 Extent map sorted复制代码 DUL> alter session set use_scanned_extent_map = true;复制代码 DUL> scan tables; (or scan extents;)复制代码复制代码 Scanning tables with segment header复制代码复制代码 Oid 1078 fno 6 bno 2 table number 0复制代码复制代码 UNLOAD TABLE T_O1078 ( C1 NUMBER, C2 UNKNOWN, C3 UNKNOWN )复制代码 STORAGE ( TABNO 0 EXTENTS( FILE 6 BLOCK 2));复制代码 Colno Seen MaxIntSz Null% C75% C100 Num% NiNu% Dat% Rid%复制代码 1 4 2 0% 0% 0% 100% 100% 0% 0%复制代码 2 4 10 0% 100% 100% 100% 0% 0% 0%复制代码 3 4 8 0% 100% 100% 100% 0% 0% 50%复制代码 "10" "ACCOUNTING" "NEW YORK"复制代码 "20" "RESEARCH" "DALLAS"复制代码 "30" "SALES" "CHICAGO"复制代码 "40" "OPERATIONS" "BOSTON"复制代码复制代码 Oid 1080 fno 6 bno 12 table number 0复制代码复制代码 UNLOAD TABLE T_O1080 ( C1 NUMBER, C2 UNKNOWN, C3 UNKNOWN, C4 NUMBER,复制代码 C5 DATE, C6 NUMBER, C7 NUMBER, C8 NUMBER )复制代码 STORAGE ( TABNO 0 EXTENTS( FILE 6 BLOCK 12));复制代码 Colno Seen MaxIntSz Null% C75% C100 Num% NiNu% Dat% Rid%复制代码 1 14 3 0% 0% 0% 100% 100% 0% 0%复制代码 2 14 6 0% 100% 100% 100% 0% 0% 21%复制代码 3 14 9 0% 100% 100% 100% 0% 0% 0%复制代码 4 14 3 7% 0% 0% 100% 100% 0% 0%复制代码 5 14 7 0% 0% 0% 0% 0% 100% 0%复制代码 6 14 3 0% 0% 0% 100% 100% 0% 0%复制代码 7 14 2 71% 0% 0% 100% 100% 0% 0%复制代码 8 14 2 0% 0% 0% 100% 100% 0% 0%复制代码 "7369" "SMITH" "CLERK" "7902" "17-DEC-1980 AD 00:00:00" "800" "" "20"复制代码 "7499" "ALLEN" "SALESMAN" "7698" "20-FEB-1981 AD 00:00:00" "1600" "300" "30"复制代码 "7521" "WARD" "SALESMAN" "7698" "22-FEB-1981 AD 00:00:00" "1250" "500" "30"复制代码 "7566" "JONES" "MANAGER" "7839" "02-APR-1981 AD 00:00:00" "2975" "" "20"复制代码 "7654" "MARTIN" "SALESMAN" "7698" "28-SEP-1981 AD 00:00:00" "1250" "1400" "30"复制代码复制代码 Note : it might be best that you redirect the output to a logfile since复制代码 commands like the "scan tables" can produce a lot of output.复制代码 On Windows NT you can do the following command :复制代码 C:\> dul8 > c:\temp\scan_tables.txt复制代码 scan tables;复制代码 exit;复制代码复制代码4) Identify the lost tables from the output of step 3; if you look carefully to复制代码 the output above then you will notice that the unload syntax is already given复制代码 but that the table name will be of the format t_0<objectno> and the column复制代码 names will be of the format C<no>; datatypes will not be an exact match of复制代码 the datatype as it was before.复制代码复制代码 Look especially for strings like "Oid 1078 fno 6 bno 2 table number 0" where:复制代码 oid = object id, will be used to unload the object复制代码 fno = (data)file number复制代码 bno = block number复制代码复制代码5) Unload the identified tables with the "unload table" command :复制代码复制代码 DUL> unload table dept (deptno number(2), dname varchar2(14),复制代码 loc varchar2(13)) storage (OBJNO 1078)复制代码 Unloading extent(s) of table DEPT 4 rows复制代码