Data recovery UnLoader ORACLE DUL introduce

If you cannot recover the data by yourself, ask Parnassusdata, the professional ORACLE database recovery team for help.

Parnassusdata Software Database Recovery Team

Service Hotline:  +86 13764045638 E-mail: [email protected]

ORACLE PRM-DUL Download: http://zcdn.parnassusdata.com/DUL5108.zip

 

• Developing DUL
• The usage of DUL
• The development of DUL
• Case Study
• References
• Summary，Questions

DISCLAIMER

• DUL is written in C by Bernard van Duijnen from Dutch , the author is Dutch Oracle support member
• DUL is not Oracle product and is not supported by Oracle
• DUL is only for internal use (Oracle Support, etc.)
• DUL program exposes Oracle’s source code, so its use is strictly restricted
• DUL v3 began to support export mode, or write a Pro * c to load LONG RAW Data

Life without DUL

General Recovery:
– Restore and rollforward
– Export / import
– SQL * Loader reload the data
– CTAS and PCTS (parallel create table as select)
– Transportable Tablespace

 

• Diagnostic tools

– Ora patch

– BBED (block browser / editor)

• Undocumented parameters

– _corrupted_rollback_segments,

_allow_resetlogs_corruption etc …

 

Restrictions

• Under the condition of SYSTEM table space (data file) is corrupted, and no other options
• The database can not be restored even used the undocumented parameter!)
• Patching is very “troublesome” and is not guaranteed to solve the problem
• Some damage is unrecoverable (truncate, drop)
• Bottom line – data loss!

 

Other options

• Clone the database and export (exp) data, then import (imp) to the database requires recover
• clone a database, and transmit table space to recover

 

Solutions

DUL is a possible solution

– DUL (?) – Bernard said, ‘Life is DUL without it!’

– Bottom line – save data as much as possible

 

Warning- Last Resort of Recovery

• DUL is meant to be a last resort, not for normal production usage.
• Preferably use restore / rollforward, EXP, SQL * Plus and other options.
• When any other normal methods fail to meet the requirements, DUL is the last resort of recovery

Note: DUL does no logical check (dirty read), does not guarantee the consistency of the database

 

Why DUL?

• Do not require to open the database or startup an instance
• Do not require to do recovery, archive logs, etc.
• Do not require to care for data consistency

– so it can applied to more data corruption situations(truncate, drop, etc.)

• Do not require SYTEM tablespace

Overview of DUL

DUL is a tool to unload data from “serious corrupted” databases

DUL can scan the database file, restrcut table header, obtain extent information

DUL can create an output files of SQL * Loader or in Export format

– Relevant SQL * Loader’s control files are automatically generated

If SYSTEM tablespace files are available, read the data dictionary to analyze all rows

– Number of Columns, column data types, column length

 

• DUL all types of data rows

– Normal rows, migrated rows, chained rows, multiple extents, clustered tables, and so on.

• DUL execution, only requires a minimum of (necessary) human intervention
• Cross-platform support for unloading

 

Concepts of DUL

• Recovery data directly from Oracle data files

– Bypassing the database (RDBMS)

• Dirty read, which means that it considers all transaction are already submitted
• Do not check whether there have been media recovery
• database corruption, block corruption as well
• Support LMT (Locally Managed Tablespaces)
• Latest DUL 10.2 support ASM

Compatibility

• Cross-unloading: Data files can be copied to other hosts do unloading
• Supports all database structure:

– 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 versions 6, 7, 8 and 9, 10, respectively used for different versions of ORACLE database

 

DUL Support?

• Support for chained rows and migrated rows
• Support for hash and index cluster
• Support for NULL column
• support for LONG, RAW, DATE, NUMBER, ROWID
• Support for multiple free list group
• Support for multi-byte character sets
• Support for LOB type column, note that when tables of LOB type column are doing DUL, the output needs to be SQL * Loader format.
• Support for ASM since 10.2.1.16

 

DuL 92 and Dul 10

• 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
• Support for ASM since 10.2.1.16

 

Restrictions

Database can be corrupted, but each individual data block inside must remains complete.

– During unloading, blocks are checked to ensure they are intact and belong to the correct segment

DUL can only unload table or cluster data

– It can not export triggers, constraints, stored procedures, also cannot generate create table or view statements

– But the data dictionary tables can be unloaded

– If DUL encounters bad blocks during unloading, a prompt information will shows, unload will not terminate, it continues

 

• No specific support for data in multi-byte character sets
• DUL can unload (LONG) RAWs, but can not effectively use SQL * Loader to import data, export mode is generally recommended

– SQL * Loader can not load LONG RAW

 

FAQ

DUL and large files (files> 2GB)

– Since 8.0.6.7, DUL gives prompts of whether to support 32-bit i / o (don not support large file) or 64-bit i / o (support large file).

DUL support for raw devices

– DUL  support raw device but cares whether it’s raw device or not.

– For the early raw device, DUL cannot automatically skip header block (OS

block), but can configure offset to skip the header block (for example, the early AIX header block is 4k, tru64 64k, etc.)

– DUL do not use the size of the file header, so DUL will read the entire raw device (including empty blocks of documents trailer)

 

Configuring DUL

DUL requires two configure files

– init.dul

– control.dul

Configure parameters and relevant platforms

 

init.dul

• Include relevant parameters of datafile format
• Include following info:

– DUL cache size

– block header deployment

– Oracle block size

– output files format

– Sql*Loader format and record size

– …

 

# sample init.dul configuration parameters

# these must be big enough for the database in question

# the cache must hold all entries from the dollar tables.

dc_columns = 200000

dc_tables = 10000

dc_objects = 10000

dc_users = 40

# OS specific parameters

osd_big_endian_flag = false

osd_dba_file_bits = 6

osd_c_struct_alignment = 32

osd_file_leader_size = 1

# database parameters

db_block_size = 8192

# loader format definitions

LDR_ENCLOSE_CHAR = ”

LDR_PHYS_REC_SIZE = 81

#ADD PARAMETERS

export_mode=true # still needed with dul9

 

 

control.dul

It is used to convert the file number and file name

• The first column is file_number,
• The second column is the file name (full path and file name)
• The third column is optional, indicates offset, fseek () operations of all relevant data files need to add the offset.

 

control.dul example 1

1 /test/dul/orcl/system01.dbf

2 /test/dul/orcl/sysaux.dbf

3 /test/dul/orcl/user.dbf

4 /test/dul/orcl/index.dbf

5 /test/dul/orcl/test.dbf

 

# 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

– SYSTEM TableSpace not available

 

Case 1: Data dictionary available

• Simple and straight forward way
• process ‘dul’ on OS, then use ‘bootstrap’ in DUL
• Do not require knowledge of table structure, column type, etc.

 

Case 2: SYSTEM TableSpace not available

Requires further knowledge of your application and business table

• If you do not know the information of your applications and business table, then the unloaded data is useless
• DUL can guess the type of the column, but the unloaded data does not have table names and column names

– Column types may be wrong

– Try the approach similar to following:

– insert into lost_tables select t.obj#, u.name, o.name, t.cols,

NULL, NULLfrom sys.obj$ o, sys.tab$ t, sys.user$ uwhere

o.obj# = t.obj# and o.owner# = u.user# and upper(u.name)

like upper(‘&&USER_NAME’) and upper(o.name) like

upper(‘&&TABLE_NAME’) and o.type = 2 and t.clu# IS NULL;

– etc.

 

Case 2: SYSTEM TableSpace not available

DUL will lost Trailing NULL columns data

• Because Trailing NULL columns (if field of row data trailer is null) does not store in the database will export the dropped tables
• When the table is dropped, the relevant information will be deleted from the data dictionary ,the empty table will not be unloader out

 

DUL startup procedure

DUL startup procedure:

• Read “ini.dul”
• Scan DUL control file (default is “control.dul”)
• Try to load USER $, OBJ $, TAB $ and COL $ dump files if these files is available (already exists in the directory). If these files are available, DUL will load them into the cache of DUL data dictionary
• Try to load seg.dat and col.dat.
• Require DDL statement or run DDL scripts (startup parameters can be specified)

 

 

DUL procedures when data dictionary is available

 

• configure init.dul and control.dul
• perform DUL
• Bootstrap
• Unload database, user, table

 

DUL procedures when data dictionary is not available

• configure init.dul and control.dul (control.dul only contains the information of the files need to be recovered).
• perform DuL
• alter session set use_scanned_extent_map = true
• scan database
• scan tables
• use the unload statement table structure with definitions:

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);