Feb 02 2008
Imagine, if you will, a situation where your Oracle database has become corrupted and your backup could either not be restored or was too out-of-date to be worth restoration. Unfortunately, this situation isn’t as hypothetical or as rare as one would think.
While poor backup strategies set the stage for this situation, hardware failures are an all-too-common occurrence which can wreak havoc on your database. So, what is a DBA to do when confronted with such a vexing situation? If the corruption is substantial or the data must be recovered, the only solution is Data Unloading (DUL).
What is Data Unloading?
Simply put, DUL is the process of extracting (unloading) data from Oracle data files directly; completely bypassing the Oracle Kernel. Unloading does not even require Oracle to be installed.
How is DUL different from exporting data?
While DUL does export data, it does not require a running server; as does EXP, EXPDP, etc. DUL is strictly an offline operation.
What about database security?
As DUL reads data files directly, user and role-level permissions are completely ineffective. Currently, the only way to protect data from an unloader is to use encryption. However, Oracle’s DUL (and soon DUDE) can read a file which uses Transparent Data Encryption (TDE).
Who can perform DUL?
Technically, anyone who knows the data type and file storage formats used by Oracle can extract the data. Though, DUL is generally performed by specialized utilities; a software category of which only a handful of tools exist.
Basically, these utilities first attempt to open the SYSTEM tablespace data files and retrieve the USER$, OBJ$, TAB$ and COL$ data for use in extracting the data. If this file is also corrupt, you may have to specify object information manually. Though, several of the utilities are able to scan the data files and heuristically determine objects to extract.
In all cases of corruption, one should generally never repair the database and continue to run on it; especially without first attempting to determine whether the corruption itself was hardware or software-related. In most cases, it’s hardware-gone-bad. As such, it’s generally recommended to dump the data, recreate the database on another server, and re-import it.
What tools exist for DUL?
As mentioned before, there are very few utilities available for unloading Oracle data files. If you should run into a situation where you need this type of utility or service, I’ve compiled a comprehensive list below:
- Bernard’s Data UnLoader
Oracle’s official DUL utility was initially written by Bernard van Duijnen as a standalone C application for Oracle Support in the Netherlands. DUL services from Oracle are offered only as a consulting-based engagement and are rumored to be quite expensive.
DUDE, Database Unloading by Data Extraction, is a Java-based DUL utility for Oracle 7-11g written by OakTable member Kurt Van Meerbeeck of ORA-600. DUDE is far-and-away more comprehensive than any other tool on the market and rivals Oracle’s own unloader. DUDE is offered as both a service and a time-limited product.
- AnySQL UnLoader (AUL)
AUL is a C-based DUL utility written by Oracle ACE Fangxin Lou. Similar to DUDE, AUL is offered as both a service and a product.
- Oracle Salvage
Oracle Salvage is a SQLite-based data unloader written in C by former Oracle kernel developer Scott Martin of Terlingua Software. It is offered as a product.
A MSVC++-based DUL utility from OfficeRecovery. It is offered only as a product.
- Recovery for Oracle
Recovery for Oracle is a Delphi-based DUL utility offered as both a service and a product from a guy in Poland.
- WisdomForce FastReader for Oracle
Another utility capable of performing parallel and selective data unload from Oracle.
Different from AUL, MyDUL is a non-commercial utility written by Jerry Sun. I am not sure whether it is still publicly available.
Clever Little Oracle Unloading Tool, my own proof-of-concept DUL utility, is not commercially available.
- K2Tor Pro
Available in both educational and real-life versions, K2Tor is able to describe the data file structures as well as perform data unloading.
Currently listed as an empty SourceForge project (with screenshots), I’m currently verifying that this utility was written by ex-Oracle RAC performance guru Stefan Roesch.