Oracle DUL Data recovery UnLoader what you need to know

DISCLAIMER

• DUL is written by Bernard van Duijnen, from Oracle Support – Netherlands
• DUL is NOT an Oracle product
• DUL is NOT a supported Oracle product
• DUL is strictly for Oracle Support and internal use only
• DUL engagements are only initiated through the PSF as the result of a recovery effort
• DUL exposes Oracle’s source code and MUST be strictly controlled

Use the export mode (DUL version 3) or write a Pro*C program to load LONG RAW data.

 

Life without DUL

• Current recovery options
  • -restore and rollforward
  • -export/import
  • -use SQL*Loader to re-load the data
  • -(parallel) create table as select (PCTS)
  • -Transportable Tablespace

 

• Diagnostic tools
  • -orapatch
  • -BBED (block browser/editor)
• Undocumented parameters
  • -_corrupted_rollback_segments, _allow_resetlogs_corruption  etc…

 

Current Limitations

 

• No alternatives in the case of loss of SYSTEM tablespace datafile(s)
• The database must be in ‘reasonably’ good condition or else recovery is not possible (even with the undocumented parameters!)
• Patching is very ‘cumbersome’ and is not always guaranteed to work
• Certain corruptions are beyond patching
• Bottom line – loss of data!!

The most common problem is the fact that customer’s backup strategy does not match their business needs.

Eg.  Customer takes weekly backups of the database, but in the event of a restore their business need is to be up and running within (say) 10 hours.   This is not feasible since the ‘rollforward’ of one week’s worth of archive logs would (probably) take more than 10 hours!!

 

Other Options without DUL

 

• Building a cloned database exporting data, and importing into the recovery database.
• Building a cloned database and using Transportable Tablespaces for recovery.

Solution

• DUL could be a possible solution
  • -DUL (?) – Bernard says ‘Life is DUL without it!’
  • -bottom line – salvage as much data as possible

 

Why DUL?

• Doesn’t need the database or the instance to be open
• Does not use recovery, archive logs etc…
• It doesn’t care about data consistency
  • -more tolerant to data corruptions
• Does not require the SYTEM tablespace to recover

 

Overview of DUL

 

• DUL is a utility that can unload data from “badly damaged” databases.
• DUL will scan a database file, recognize table header blocks, access extent information, and read all rows
• Creates a SQL*Loader or Export formatted output
  • -matching SQL*Loader control file is also generated

 

Overview – DUL will…

• Read the Oracle data dictionary if the SYSTEM tablespace files are available
• Analyze all rows to determine

-number of columns, column datatypes and column lengths

If the SYSTEM tablespace datafiles are not available DUL does its own analysis, more on this later…

 

Overview – DUL

• DUL can handle all row types

-normal rows, migrated rows, chained rows, multiple extents, clustered tables, etc.

• The utility runs completely unattended, minimal manual intervention is needed.
• Cross platform unloading is supported

 

DUL can open other datafile(s) if there are extents in that datafile(s).

Although DUL can handle it, LONG RAW presents a problem for SQL*Loader – we’ll talk about this shortly…

For cross platform unloading the configuration parameters within “init.dul” will have to be modified to match those of the original platform and O/S rather than the platform from which the unload is being done.

DUL unloads in the physical order of the columns. The cluster key columns are always unloaded first.

 

DUL Concepts

 

• Recovers data directly from Oracle data files

-the Database (RDBMS) is bypassed

• Does dirty reads, it assumes that every transaction is committed
• Does not check if media recovery has been done
• DATABASE CORRUPT – BLOCKS OK
• Support for Locally Managed Tablespaces

 

DUL does not require that media recovery be done.

Since DUL reads the data directly from datafiles,  it  reads data that is committed as well as uncommitted.  Therefore the data that is salvaged by DUL can potentially be logically corrupt.  It is upto the DBA and/or the Application programmers to validate the data.

 

 

Compatibility

 

• The database can be copied from a different operating system than the DUL-host
• Supports all database constructs:

-row chaining, row migration, hash/index clusters, longs, raws, rowids, dates, numbers, multiple free list groups, segment high water mark, NULLS, trailing NULL columns etc…

• DUL should work with all versions 6 , 7, 8 and 9，10,11,12

 

DUL9i/92

• The main new features are:

–  Support for Oracle version 6, 7, 8 and 9

–  Support for Automatic Space Managed Segments

–  New bootstrap procedure: just use ‘bootstrap;’.   No more

       dictv6,7 or 8.ddl files

–  LOB are supported in SQL*Loader mode only

–  (Sub)Partitioned tables can be unloaded

–  Unload a single (Sub)Partition

–  Improved the scan tables

–  The timestamp and interval datatypes

–  Stricter checking of negative numbers

–  (Compressed) Index Organized Tables be unloaded

–  Very strict checking of row status flags

–  Unload index to see what rows you are missing

–  Objects, nested tables and varrays are not supported (internal 

        preparation for varray support )

 

The latest version is DUL92. The main new features are:

• fix for problem with startup when db_block_size = 16K
• fix for scan database and Automatic Space Managed Segments
• fix for block type errors high block types; new max is 51
• Support for Automatic Space Managed Segments
• phase zero of new unformat command
• internal preparation for varray support
• Bug fix in the stricter checking of negative numbers
• Bug fix in the unloading of clustered tables

 

Limitations

• The database can be corrupted, but an individual data block used must be 100% correct

-blocks are checked to make sure that they are not corrupt and belong to the correct segment

• DUL can and will only unload table/cluster data.

-it will not dump triggers, constraints, stored procedures nor create scripts for tables or views

-But the data dictionary tables describing these can be unloaded

 

Note: If during an unload a bad block is encountered, an error message is printed in the loader file and to standard output. Unloading will continue with the next row or block.

 

 

Configuring DUL

• There are two configuration files for DUL
  • -init.dul
  • -control.dul
• Configuration parameters are platform specific.

If you do decide that DUL is the only way to go, then here is how to go about configuring and using DUL.  Good Luck!!

 

 

init.dul

• Contains parameters to help DUL understand the format of the database files
• Has information on
  • -DUL cache size
  • -Details of header layout
  • -Oracle block size
  • -Output file format
  • -Sql*Loader format and record size.
  • -etc…

 

 

Sample init.dul file for Solaris looks like:
# The dul cache must be big enough to hold all entries from the Dictionary dollar tables.
dc_columns = 200000
dc_tables = 20000
dc_objects = 20000
dc_users = 40
# OS specific parameters
big_endian_flag = true
dba_file_bits = 6
align_filler = 1
db_leading_offset = 1
# Database specific parameters
db_block_size = 2048
# Sql*Loader format parameters
ldr_enclose_char = "
ldr_phys_rec_size = 81

 

 

control.dul

• Used to translate the file numbers to file names
• Each entry on a separate line, first the file_number then the data_file_name

-A third optional field is an extra positive or negative byte offset, that will be added to all fseek() operations for that datafile.

 

Sample “control.dul”

 

# AIX version 7 example with one file on raw device
   1 /usr/oracle/dbs/system.dbf
   8 /dev/rdsk/data.dbf 4096

   # Oracle8 example with a datafile split in multiple parts, each part smaller than 2GB
   0  1 /fs1/oradata/PMS/system.dbf
   1  2 /tmp/huge_file_part1 startblock 1 endblock 1000000
   1  2 /tmp/huge_file_part2 startblock 1000001 endblock 2000000
   1  2 /mnt3/huge_file_part3 startblock 2000001 endblock 2550000

 

Using DUL

• Case 1:

-SYSTEM tablespace available

• Case 2:

-Using DUL without the SYSTEM tablespace

 

Case1: Data dictionary usable

• Straight forward method
• Execute ‘dul’ from os prompt then ‘bootstrap’ from DUL
• Don’t need to know about the application tables structure, column types etc…

 

Case2: Without the SYSTEM tablespace

• Needs an in depth knowledge about the application and the application tables

The unloaded data does not have any value, if you do not know from which table it came from

• Column types can be guessed by DUL but table and column names are lost

The guessed column types can be wrong

Note:

1) Any old SYSTEM tablespace from the same database but weeks old can be of great help!

2) If you recreate the tables (from the original CREATE TABLE scripts) then the structural information of a “lost” table can be matched to the “seen” tables scanned with two SQL*Plus scripts. (fill.sql andgetlost.sql)

Steps to follow:

1.configure DUL for the target database. This means creating a correct init.dul and control.dul.

2.SCAN DATABASE; : scan the database for extents and segments.

3.SCAN TABLES; : scan the found segments for rows.

4.SCAN EXTENTS; : scan the found extents.

5.Identify the lost tables from the output of step 3.

6.UNLOAD the identified tables.

 

• DUL will not find “last” columns that only contain NULL’s

Trailing NULL columns are not stored in the database

• Tables that have been dropped can be seen

When a table is dropped, the description is removed from the data dictionary only

• Tables without rows will go unnoticed

 

DUL startup steps

During startup DUL goes through the following steps:

• the parameter file “init.dul” is processed
• the DUL control file (default “control.dul”) is scanned
• try to load dumps of the USER$, OBJ$, TAB$ and COL$ if available into DUL’s data dictionary cache
• try to load seg.dat and col.dat.
• accept DDL-statements or run the DDL script specified as first argument

 

Availability

Available on most common platforms

• Solaris
• AIX
• Windows
• Linux

 

DuL with Dictionary

• Configure init.dul and control.dul
• Load DuL
• Bootstrap
• Unload database, user, table

DuL without Dictionary

• Configure init.dul and control.dul (control will include

the datafiles needing to be recovered only).

• Load DuL
• alter session set use_scanned_extent_map = true
• scan database
• scan tables
• Using the found table definitions construct an uload

statement:

 

 

unload table dul2.emp (EMPLOYEE_ID number(22), FIRST_NAME varchar2(20),
LAST_NAME varchar2(25),
EMAIL varchar2(25),PHONE_NUMBER varchar2(20), HIRE_DATE date, JOB_ID varchar2 (10),
SALARY number(22), COMMISSION_PCT number(22),MANAGER_ID number(22),
DEPARTMENT_ID number(22))
storage (dataobjno 28200);