Oracle DUL Reference document

Subject:  Using DUL to Recover From Database Corruption (with some examples)Creation Date:  29-JUN-1999  Table Of Contents~~~~~~~~~~~~~~~~~1. Introduction2. 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 database3. 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. References1. 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 andinclude 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 unloaded5. 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 106. 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 willnot 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.

http://www.fors.com/velpuri2/Backup%20and%20Recovery/UsingDUL