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]
DUL Introduction
- Developed by Bernard Van Duijnen
- Is a stand-alone disaster recovery utility, and requires no other Oracle software to run
- Unloads data from data files of a crashed database
- Creates SQL*Loader control files and data files
A useful utility has been developed in the Netherlands, called the Data Recovery Unloader (DUL). DUL is a stand-alone C program that directly retrieves rows from tables in data files. The RDBMS is not necessary.
DUL is intended to retrieve data from the database that cannot otherwise be retrieved. The database may be corrupted, but an individual data block to be used by DUL must be 100% correct.
DUL unloads data from files containing table and cluster data. It creates no scripts for triggers, procedures, tables, and views; it can only read the definitions from the data dictionary tables.
DUL can create SQL*Loader files or export files.
When to Use DUL
You can use DUL in disaster scenarios to provide your customer more current data than the last backup or export.
Before using DUL, you will need to learn more about the ACID principle (Atomicity, Consistency, Isolation, and Durability), Oracle’s transaction internals, block structures (headers and footers), file structures, and OS file systems. These topics are discussed in more detail in the DSI402 and DSI402e courses.
Also, make sure that your database is really unrecoverable. Recovery is discussed in the DSI403 and DSI403e courses. Maybe your database can also be rescued by using utilities like BBED and ORAPATCH, as discussed in DSI401.
DUL Requirements
You need the most current data files, preferably all of them from the same time frame. You need a knowledgeable resource to discuss OS, database, and schema details. You also need a new instance or location to put everything back together, with a lot of free disk space (up to three times the size of your database.)
Good Things to Have
- Data file listing
- DDL scripts to re-create objects
- Any export of the instance; ancient is acceptable
- Idea of how much data is to be retrieved
- Patience and luck
DUL Features
DUL Features
- Available on all Oracle versions since version 6, on many platforms
- Not an alternative to standard recovery methods
- Not a supported utility
- Used as a last resort only, as the ultimate solution for an otherwise dead-end situation
- Reads data dictionary if the files are available, but can also work without dictionary files
- Can unload individual tables and schemas, or the complete database
- Supports cross-platform unloading
DUL version 3 is implemented and tested for Oracle7. The most recent version is DUL version 9, which is implemented for Oracle9i Release 9.0.1.
DUL is available on the following platforms: Sequent/ptx, VAX/VMS, Alpha VMS, MVS, HP9000/8xx, IBM AIX, SCO Unix, Linux, Alpha OSF/1, Intel Windows NT, and Windows95.
DUL is not a supported utility, that is, there is no guarantee for bug fixes.
DUL should be used as a last resort after all other recovery alternatives have been exhausted. This includes the use of destructive events. However, to use these destructive events, the database must be in reasonably good shape to be opened and to remain open while an export is being performed. There are examples of databases that were restored from two disparate backups taken two weeks apart. In such situations it is often very difficult to successfully export, even if the database can be opened.
If the system tablespace files are available, DUL reads the data dictionary information and unloads the user$, obj$, tab$, and col$ tables. Old system tablespace backup files from the same database are better than nothing, and can also be used.
If the system data files are not available, DUL scans the data files for segments and extents, and scans the found segments for rows. Then you unload the found segments with the UNLOAD TABLE command. To do this, you must have a good knowledge of the database structure, such as column and table storage information.
Databases can be unloaded on platforms other than the DUL host platform. Simply copy the data files, and modify the configuration files appropriately.
DUL Support
The following standard constructs are supported:
- Chained or migrated rows
- Hash or index clusters
- NULLs, trailing NULL columns
- LONGs, RAWs, DATEs, NUMBERs, ROWIDs (extended and restricted)
- Multiple free list groups
- Unlimited extents
- Partitioned tables, index-organized tables
- Multibyte character sets
- LOBs
DUL supports the standard data types for Oracle9i, including features such as index- organized tables and partitioned tables.
Multibyte character sets are supported, but with workarounds for the DUL command parser. In particular, it is problematic to specify table names and user names in a multibyte character set. DUL is essentially a single-byte application. The command parser does not understand multibyte characters.
The current version of DUL also supports LOBs, internal ones (CLOBs, BLOBs) better than external ones (BFILEs, CFILEs).
Note: Internal LOBs are only supported with the SQL*Loader format.
One of the most problematic scenarios is having LOBs without a system tablespace.
DUL Restrictions and Dangers
- The database can be corrupt, but individual blocks must be
- DUL unloads LONG RAWs, but LONG RAW data cannot be loaded using SQL*Loader.
- There is no support for:
- MLSLABELs
- VARRAYs, objects, and nested tables
- DUL performs dirty reads;
no data consistency or validity guaranteed.
The database may be corrupted, but an individual data block that is used must be correct. During all unloading, blocks are checked to verify that they are not corrupted and they belong to the correct segment. If a corrupted block is detected, an error message is generated to the loader file.
There is no suitable format in SQL*Loader to preserve all LONG RAWs. Use the export mode instead, or write a Pro*C program to load the data.
Multilevel security labels (MLSLABELs) are not supported. VARRAYs, objects, and nested tables are not yet supported.
DUL performs dirty reads from the data files. Data will likely be logically inconsistent, and therefore will have to be revalidated.
DUL Expectations
It is important to understand what the limitations and dangers of DUL are. Be sure to set clients’ expectations about what the service is, and what their role is. Make sure they can get back to recovery operations, preferably semi-production status.
“If you are there, it is a bad situation already, so your help is their best hope.”
Before you start using DUL, understand what brought you to this procedure. Be aware that all transactional integrity is thrown out the window; file and consistency checks are ignored.
Look at the current backup and recovery strategies, and find out how they failed.
Configuring DUL
Two configuration files are necessary:
- init.dul
- control.dul
here are two configuration files for DUL:
- dul contains all configuration parameters, such as size of caches, details of header layout, Oracle block size, and output file format.
- In the dul file, the data file names and the Oracle file numbers must be specified.
Note: The following slides explain the usage of DUL version 9.0.1.0.0.
init.dul Parameters
Database configuration parameters:
- db_block_size
- compatible
Dictionary cache information:
- dc_columns
- dc_tables
- dc_objects
- dc_users
Database Configuration Parameters
The init.dul file contains database configuration parameters, such as
db_block_size and compatible.
The compatible parameter identifies the database version; valid values are 6, 7, 8, or 9. This parameter must be specified. Most other DUL parameters have default values.
Dictionary Cache Information
The DUL cache must be large enough to hold all entries from the col$, tab$, obj$, and user$ dictionary tables. If the DUL cache is configured too small, earlier releases of DUL did not start, and reported the following error:
- DUL: Error: parameter dc_xxxxxx too low
This meant that the dc_xxxxxx parameter had to be be increased and DUL to be restarted. However, the current release of DUL will automatically raise the dc_xxxxxx parameter in such cases, give a warning, and continue operation.
Note: To give the dc_xxxxxx parameters a historical perspective: Oracle version 6 had more than twenty different dc_ initialization parameters. These parameters were replaced in Oracle7 by the new shared_pool_size parameter.
init.dul Parameters
OS-specific parameters: Other common parameters:
- osd_big_endian_flag control_file
- osd_dba_file_bits ldr_enclose_char
- osd_c_struct_alignment ldr_phys_rec_size
- osd_file_leader_size buffer
- osd_word_size export_mode
- filesize_in_mb
OS-Specific Parameters
osd_big_endian_flag (Boolean)
Determines whether or not the platform is byte-swapped. Current versions of DUL set the default according to the machine it is running on. HP, Sun, and mainframes generally are big endian (true); Intel and DEC are little endian (false).
osd_dba_file_bits
The number of bits in a dba used for the low order part of the file number. For example, you should set osd_dba_file_bits on Sun Sparc Solaris to 10 (Oracle8i and higher).
osd_c_struct_alignment
The C structure member alignment (0,16, or 32), and must be set to 32 for most ports
osd_file_leader_size
Contains the number of bytes/blocks added before the real Oracle file header block
osd_word_size
The size of a machine word; always 32, except for MS-DOS (16)
Other Common Parameters
control_file
The name of the DUL control file (default: control.dul)
ldr_enclose_char
Contains the character to enclose fields in SQL*Loader mode
ldr_phys_rec_size
The physical record size for the generated loader data file:
0: No fixed records, each record is terminated with a newline
>2: Fixed record size
buffer
Indicates the row output buffer size (default: 64 KB)
export_mode
This boolean parameter determines whether the output mode is the export format or SQL*Loader format (default: FALSE; that is, SQL*Loader mode).
filesize_in_mb
This parameter specifies the maximum output file size. Output files will be split by DUL if necessary (default value is 0; that is, the output file will not be split).
init.dul Example
control.dul
- This file contains the mapping of file numbers to file names (see v$datafile).
- The format for Oracle8i and Oracle9i files is:
ts# relative_file# data_file_name [optional extra leader offset] [startblock block#] [endblock block#]
- Each entry is located on a separate line and can specify a part of a data file using the startblock and endblock
A DUL control file (default name: control.dul) is used to translate the file numbers to file names. The order is important. The first three fields (tablespace number, relative file number, and data file number) are mandatory; the other three fields are optional. The optional extra leader offset is an extra byte offset to be added to all lseek() operations for that data file. For example, this makes it possible to skip over the extra block for AIX on raw devices or unload from fragments of a data file.
You can use the following script [03_control_dul.sql] to generate a valid DUL control file:
SQL> connect / as sysdba
SQL> startup mount
SQL> set trimspool on pagesize 0 linesize 256 feedback off
SQL> column name format a200
SQL> spool control.dul
SQL> select ts#, rfile#, name from v$datafile;
SQL> spool off
Example
| 0 | 1 | D:\ORACLE\ORADATA\LHAAN\SYSTEM01.DBF |
| 1 | 2 | D:\ORACLE\ORADATA\LHAAN\UNDOTBS01.DBF |
| 2 | 3 | D:\ORACLE\ORADATA\LHAAN\CWMLITE01.DBF |
| 3 | 4 | D:\ORACLE\ORADATA\LHAAN\DRSYS01.DBF |
| 4 | 5 | D:\ORACLE\ORADATA\LHAAN\EXAMPLE01.DBF |
| 5 | 6 | D:\ORACLE\ORADATA\LHAAN\INDX01.DBF |
| 7 | 7 | D:\ORACLE\ORADATA\LHAAN\TOOLS01.DBF |
| 8 | 8 | D:\ORACLE\ORADATA\LHAAN\USERS01.DBF |
Using DUL
- Create appropriate dul and control.dul files.
- Invoke DUL to unload dictionary
- Unload the data
- Rebuild the database
- Validate the data
During startup, DUL goes through the following steps:
- The init.dul parameter file is processed.
- The DUL control file (default control.dul) is
- Try to load dumps of the user$, obj$, tab$, and col$ tables (if available) into DUL’s data dictionary
- Try to load SEG.dat and EXT.dat.
- Accept DDL statements or run the DDL script specified as first
Note: DUL assumes all files to be located in the current (working) directory.
DUL Output
The DUL output depends on the export_mode parameter in the control.dul file. If you have the DDL for the objects, you should use them to re-create the objects in your new instance. Note that the export format produces more compact files, which might be important in case of huge output files.
SYSTEM Tablespace
When unloading data from the wrecked data files, you have two options:
- Available system tablespace data file
- No system tablespace data file
Note: An old system tablespace data file is better than none at all!
With System Tablespace Files
- Create dul and control.dul files.
- Start DUL and unload the dictionary:
$ dul
DUL> bootstrap;
- Unload the objects, for example:
DUL> unload table <username.table>;
DUL> unload user <username>;
DUL> unload database;
Commonly Used DUL Commands
In case you have experience with earlier releases of DUL, you should know that the current version of DUL automatically generates and runs the dictv7.ddl and dictv8.ddl dictionary scripts when you issue the bootstrap command, so you don’t need to run those scripts anymore.
The most commonly used DUL commands are:
DUL> unload table table hr.employees
. unloaded 107 rows
DUL> unload user hr;
About to unload HR’s tables …
. unloading table REGIONS 4 rows unloaded
. unloading (index organized) table COUNTRIES 25 rows unloaded
. unloading table LOCATIONS 23 rows unloaded
. unloading table DEPARTMENTS 27 rows unloaded
. unloading table JOBS 19 rows unloaded
DUL> unload database;
This unloads the entire database.
Without System Tablespace Files
1. Create init.dul and control.dul files.
2. Start DUL and scan the database for segment headers and extents:
DUL> scan database;
3. Restart DUL and scan the found tables for column statistics:
DUL> scan tables;
4. Identify the scanned tables.
5. Unload the identified tables.
In this case, you need in-depth knowledge about the application and the application tables; the unloaded data will be meaningless if you do not know from which table it came. These are some potential challenges:
- Data types can be guessed by DUL, but table and column names are lost; the guessed column types can be
- DUL does not find trailing columns that only contain NULL values, because trailing NULL columns are not stored in the
- Tables that have been dropped can be seen. When a table is dropped, only the table definition is removed from the data
- Empty tables (without rows) will go
Step 2
DUL> scan database;
DUL: Warning: Recreating file “EXT.dat” DUL: Warning: Recreating file “SEG.dat”
DUL: Warning: Recreating file “COMPATSEG.dat” tablespace 0, data file 1: 83199 blocks scanned
tablespace 1, data file 2: 51199 blocks scanned
tablespace 2, data file 3: 5119 blocks scanned
tablespace 3, data file 4: 5119 blocks scanned
tablespace 4, data file 5: 39039 blocks scanned
tablespace 5, data file 6: 6399 blocks scanned
tablespace 7, data file 7: 2559 blocks scanned
tablespace 8, data file 8: 11199 blocks scanned Loaded 3131 entries from EXT.dat
Sorted 3131 entries
Loaded 2170 entries from SEG.dat Loaded 1 entries from COMPATSEG.dat
As you can see, the following two files are generated by the scan database
command:
- dat : Information about found segment headers
- dat : Information about contiguous table or cluster data blocks
Step 3
The third step tells DUL that it should use its own generated extent map rather than the segment header information, and scan all tables in all data segments using the commands:
DUL> alter session set use_scanned_extent_map=true;
Parameter altered DUL> scan tables; You can also use:
DUL> scan extents;
Step 4
Hopefully, the output from the previous step looks familiar. Use this information and your knowledge of the data to identify the lost tables. Note that this is the most difficult (and mainly manual) part of using DUL in the absence of the SYSTEM tablespace data files.
Note: at the end of this lesson, you will see a link pointing to two fully documented DUL cases, including all DUL screen output and corresponding explanations.
Step 5
Unload the identified tables, for example, with the following command:
DUL> unload table departments
- (department_id number
- ,department_name varchar2(30)
- ,manager_id number
- ,location_id number)
- storage (objno 31444);
. unloading table DEPARTMENTS 27 rows unloaded
Rebuilding the Database
- Rebuild phase:
- Load the database by using
- Load the database by using SQL*Loader.
- Validation phase:
- Data consistency check
- Back up your database
- Root cause analysis: what happened, and how to prevent it from happening again
To rebuild the database, load the unloaded information with the Import utility or the SQL*Loader utility.
Load the Database by Using Import
If you use the export mode to unload the data, DUL generates a separate import file for each table with minimal information in it. That is, grants, storage clauses, or triggers are not included. The output file name generated by DUL is ownername_tablename.dmp; for example, HR_EMPLOYEES.dmp.
Load the Database by Using SQL*Loader
For SQL*Loader output formats, the columns are separated by spaces and enclosed in double quotes. The output file names generated by DUL are:
ownername_tablename.dat for the data file
ownername_tablename.ctl for the control file
Now What?
When you have finished, these are typically the remaining steps:
- Data should be checked by the customer for consistency
- Once data is validated, it should be backed
- Root cause analysis is sometimes requested. A clear description of the environment and the facts behind the event is
DUL Case Studies
Click the link below to visit two case studies about using DUL:
- In the presence of syste m tablespace files
- In the absence of system tablespace files
Note: to open the case studies in a different browser window, use right-click and select the appropriate choice.
Leave a Reply