Feb 02 2008
DUL & Desperation: The Trials and Tribulations of Corruption
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/jDUL
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. - OracleRecovery
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. - MyDUL
Different from AUL, MyDUL is a non-commercial utility written by Jerry Sun. I am not sure whether it is still publicly available. - CLOUT
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. - FlashUnload
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.
[…] Oracle Data Unloading Tools […]
Hi Jonah:
What’s your opinion about dd ? I know that’s more work to do but with dd you can extract data even on raw or ASM devices, of course, trying to know where are the segments using data dictionary
Best regards
D.
Hey David,
First, thanks for visiting my blog and asking a question :)
Second, as to using dd to extract the data, while it is possible, it’s pretty much no different than having the corrupted data files themselves. As you mentioned toward the end, it all comes down to knowing where and what the data is, and exporting it in such a way that it can be recovered and imported again.
Did this answer your question, or did I misunderstand what you were asking?
-Jonah
Yes, answered OK :)
But as you mentioned in your list - a lot of useful tools -, all of them seems to be products that involve a process of negotiation, payment and reception/use of the tool and datafiles well located. That’s ok for me, but I was only trying to know if a ‘quick&dirty’ first attempt to deal with corrupted devices ( without another solution as restore form backup ) with dd - I think about corrupted ASM ( ¿? yes, yes… very singular ) when no physical datafile location is so evident as on filesystem - fits in your list. Take it as a forensic approach.
May be some day I will post how I recovered a little Oracle Datawarehouse having only 98% of system tablespace ( datafile ) without any previous backup and armed with the linux command line ( hexa editor, cat, grep…) and a lot of luck. Was funny ! :)
Best regards
D.
( sorry if my english is not good - sometimes is worse! )
Regarding your site, very interesting info here, thanks to share it
David,
Gotcha now. Yes, dd would be OK at dealing with lower-level corruption or raw/ASM volumes. You still have to translate the bytes into usable data, but dd would be a good way to acquire it.
Also, from what I’ve heard, Oracle’s DUL can fix corrupted ASM volumes. Kurt and I once discussed adding ASM support to DUDE, but he’s a pretty busy guy :)
Similarly, you should post your story; it would be an interesting read!
-Jonah
[…] you all enjoyed it. From the few people I’ve talked to, the DUL topic seems to be one of those, like it or not with no middle ground types of things. Regardless, if […]
This one also (when it will be available)
http://sourceforge.net/projects/oflashunload/
[…] added two new entries to my DUL utilities list, K2Tor and […]
Here is a link to the tool that reads data directly from data files with high speed: http://www.wisdomforce.com/products-FastReader.html
Andrew,
Thanks for that link. I knew there was one I was missing and I could never remember what it was. Thank you!
-Jonah
You might like this one:
http://web.archive.org/web/20071213113331/www.fors.com/velpuri2/
http://web.archive.org/web/20070114105104/www.fors.com/velpuri2/Backup+and+Recovery/
Hai is there any free tools available for bulkunload including blob data unloading………..
how can i bulk unload table data to csv…,
on orcle client machine…………….
thans
saven
juniorDev, while it isn’t a DUL utility, for a fast export to CSV on a running system I recommend ociuldr:
http://www.dbatools.net/mytools/unload_oracle_with_ociuldr.html
If you were looking for a free DUL utility, you’re pretty much out of luck.