Roads ? - where we're going, we don't need roads ...
Worst recovery case ... ever ! | ORA600
ORA600 content Content RSS
Oracle ORA600 News RRSS Oracle News RSS
ORA600 blog Blog RSS
ORA600 blog Blog Atom

User login

Worst recovery case ... ever !

Worst recovery case ... ever.

Oracle DMP files generated by 'exp' are not considered as a backup. In a sense that is correct.
If you run 'exp' on a live database, data in your dump file can be inconsistent. This can be an issue when you export for example tables with a parent-child relationship.
Or for example - while performing full exports and tables are using a surrogate primary key. Typically a sequence is used, but the sequences are exported at a different point of the export flow. This means that sequences can become out-of-sync with the table data. Anyway, some of these issues are handled by the CONSISTENT parameter.

The main reason DMP files became legacy is the fact that we live in a 24/7 world (or 25/8 for dba's & sysadmins) - and DMP files are just a snapshot of your data in time, and thus are basically useless from the moment the file hits your disk. They are only vaguely useful now when it comes to having a snapshot of your database object DDL.
I remember the days though, where we had databases that we could shut down during the night for a cold backup and a startup followed by an export ... those days are long gone.

However, somehow people are still very fond of the beloved exp/imp tools even as a backup.

I remember one case were I was asked to look into an import problem.
Import errors are mostly like this :
1. something is wrong with the database you are importing to - like permissions - and thus, those are easely solved
2. or something is wrong with the DMP file itself - and this is mostly the point where you feel your blood pumping, because, well, not much you can do about that ...
(unless of course you still have the source database, and you can redo to export)

DUDE contains a DMP api ... which means that it can generate 8.0.5 compatible DMP files. These dump files can be read by the Oracle imp tool, version 8.0 and above. What's even more - the API allows the generation of datatypes which didn't even exist in 8.0.5 and still it will work in 10g.
For example, a table containing the TIMESTAMP datatype, extracted by DUDE to a 8.0.5 DMP file, will import without problems using 10g imp.

Anyway - a couple of years ago, I was approached by a company that had lost their database *and* their online backups... all they had left was an export dump.
Now, if you've lost both your database and backup ... and all you have left is one export dump... well, then that dump file *becomes* your backup.
So close your eyes - imagine yourself in this situation  ... and then imagine encountering an IMP-00009 error half-way through your only dump.
I'm telling you - forget base-jumping - IMP-00009 in that situation gives you the same adrenaline high !
Now - the company approached me with the question - can you repair this ?
This is not the sort of thing you do on a daily bases - but I was intrigued so I agreed to have a look at it without garantuee.

It was quite obvious what had happened. They were running Oracle on MS Windows - I don't know for sure if they were using NTFS or FAT32 - but my guess would have been FAT as I did recognise the corruption.
It's something one might call cross-linked files where a FAT corruption results in 2 files sharing a number of clusters.
The symptoms are quite easy to spot - just open a file with a hex editor and you'll see parts of other files popping up in multiples of 512bytes, aligned at multiples of 512bytes.
I've seen it at least a handful of times at several sites running MS Windows where they experienced a sudden power-loss.
And on each occasion datafiles (or in this case, a dmp file) were partially over-written by parts of some random dll.
In case of - partitially - overwritten files - the conclusion is simple : data will be lost.

This is what the DMP looked like (over-simplified) :

| dmp header (see below)                                               |
| filler bytes                                                                     |
| CONNECT <user>                                                         
| TABLE "<name>"                                                     
| CREATE TABLE DDL                                                  
| INSERT INTO STATEMENT                                             
| NOF columns                                                        
| COLx type | COLx len                                              
| RECx data  1                                                       
| corruption - 512 bytes                                             
| RECx data  3                                                       
| RECx data  4                                                       
| corruption - 4096 bytes                                            
| RECx data  6                                                       
| RECx data  7                                                       
| STOP bytes                         

The header stores NLS characterset settings, version of dump file, type of export, etc.
NLS info might also be stored on a per column basis.
Anyway - the format is pretty straight forward (at least compared to database blocks).
The company was interested in only one table and this was a 3Gb DMP (full export) file.

Here's a snapshot of what the DMP would have looked like in a hexeditor (this is not the actual dump)

corrupt dump

You clearly see something is wrong - even if you don't know a thing about DMP internals - just look at the part that says 'This program requires microsoft windows' ... not likely Oracle would put that in their dumps ;-)

I actually managed to manually create a new DMP file with only that one table in it using a hex editor and cutting
and pasting only good parts - so the end result was a bit like this :

| dmp header (see below)                                               |
| filler bytes                                                                     |
| CONNECT <user>                                                        
| TABLE "<name>"                                                         
| CREATE TABLE DDL                                                      
| INSERT INTO STATEMENT                                             
| NOF columns                                                                
| COLx type | COLx len                                                   
| RECx data  1                                                                
| RECx data  3                                                                
| RECx data  4                                                                
| RECx data  6                                                                
| RECx data  7                                                                
| STOP bytes                                                                  

The dump file imported without spawning imp-00xxx errors - however, data was lost and it was quite hard to tell what the actual value of this dump file was for the company.
Especially as I had to cut away a large amount of data - I counted 30 corruptions of on average 8K and there were many more outside that one table.
It took me long enough too - about 6hours... it felt a bit like an ER surgeon cutting away damaged flesh.

I'm sure the handmade DMP file had its use for them - but they probably had a long way to go from there... cross-checking what they had lost, re-entering data and so on...

It was without a doubt the worst recovery case I had encountered ... ever ... no datafiles ... no backup ... corrupted DMP.


Technorati Tags: loan to pay off payday loans [url=]online payday loans[/url] easy to get loans online payday loans payday loan richmond va [url=]cash advance online[/url] cash advance taylor mi online single mother loans [url=]payday loans[/url] legit online loans for bad credit online payday loans paydayloan [url=]payday loans[/url] one hour cash advance payday loans online

what to tell doctor to get viagra prescription

viagra online free sample viagra without doctor prescription genuine viagra online uk [url=]viagra without a prescription[/url] buy cheap viagra online us


[url=]cheap viagra online[/url] viagra for sale online viagra girl commercial buy generic viagra payday loan for bad credit direct lender [url=]online payday loans[/url] unsecured personal loans for bad credit not payday loans cash advance online bad credit loans pa [url=]loans[/url] payday loans over the phone online cash advance payday loans fast [url=]cash advance online[/url] cash loans greenville sc payday loans monthly loan [url=]pay day loans[/url] payday loan in minutes online direct pay day loans [url=]online[/url] payday loan lenders online payday express

cialis utilisatio

generico cialis en espana [url=]generic cialis[/url] generic cialis cialis dose recommended


[url=]buy cialis online[/url] cialis order online cialis online australia buy cheap cialis

buy viagra cvs

viagra sales rep viagra without doctor buy viagra in bangladesh [url=]viagra without a prescription[/url] get rid viagra spam

Coemedy how to get a personal loan with no credit history [url=">cash advance online[/url] cash advance loans near me loans for bad credit

citalse private money lending [url=">cash advance[/url] quick pay day loans cash advance

oceargo payday loans garland tx [url=">cash advance online[/url] get a loan with no credit advance loans

Bleax loan services [url=">payday express[/url] easy pay day loans cash advance loans


[url=]buy viagra online[/url] chinese herb viagra which viagra is best buy generic viagra

best website buy cialis

what color are cialis pills cialis without a doctor cheapest cialis price [url=]cialis without doctor[/url] cialis best buy

buy viagra germany

viagra used [url=]generic viagra online[/url] online viagra what is best viagra pill

achetez viagra cialis levitra

viagra available in canada [url=]viagra cheap[/url] buy viagra viagra for sale in the phils

Omicy personal loans lenders [url=">payday express[/url] payday loans with savings account cash advance loans

angex quick easy money [url=">payday express[/url] microlending websites payday advance loans

ordesee payday loan advance [url=">cash advance loans[/url] america loan payday advance loans

buy cheapest cialis

buy cialis levitra and viagra cialis without doctor cheapest cialis 20mg [url=]cialis without prescription[/url] cialis sale us

Alumpiz cashloan [url=">payday advance loans[/url] pay advance loans online cash advance online

Clume american cash loans [url=">payday express[/url] online loans with no credit payday advance loans

cialis at liquidation price

cialis uk price [url=]generic cialis[/url] cialis cialis canadian online

risse payday loans in houston [url=">cash advance online[/url] advance check loans advance loans

brutt safe payday loans [url=">bad credit loans[/url] loans for bad credit no fees bad credit loans


[url=]buy cialis[/url] hot rod cialis pills buy alcohol cialis generic buy cheap cialis

buy cialis online usa

buy cialis generic canada cialis without a doctor's prescription where to buy generic cialis [url=]cialis without doctor[/url] how do i order cialis

prescription sample cialis

comprar cialis 20 generico [url=]buy cialis online[/url] buy cialis foro cialis

buy cialis generic online

should buy cialis online cialis without prescription order cialis at online pharmacy [url=]cialis without doctor[/url] buy cialis online in australia

cialis generico 60mg

paypal to buy cialis pills [url=]order cialis[/url] buy cialis online high quality cialis

buy cialis nz

where is the best place to buy generic cialis cialis without prescription canadian pharmacy buy cialis professional [url=]cialis without a doctor prescription[/url] cialis c5 pill


[url=]cheap cialis[/url] cialis online cialis discount online generic cialis online


[url=]cheap viagra online[/url] synthetic viagra 1 viagra pill generic viagra online


[url=]buy viagra[/url] what happens if a female takes viagra viagra not working generic viagra online


[url=]personal loans[/url] get cash cash advance online reviews cash advance


[url=]payday loans[/url] direct pay day lender loans short term payday loans


[url=]viagra generic[/url] viagra before and after video is viagra funded by the government viagra online


[url=]generic viagra online[/url] new viagra commercials when did viagra come out viagra generic

cialis super active 20mg pills

buy cheap viagra cialis cialis prices buy cialis fda [url=]cialis cost[/url] buy daily cialis


[url=]buy viagra[/url] viagra singles herbal viagra ingredients buy viagra online


[url=]payday express[/url] loan online application very poor credit personal loans cash advance


[url=]buy viagra online[/url] viagra 100mg price walmart viagra 4 hours warning buy viagra


[url=]buy generic viagra[/url] viagra 100mg canadian pharmacy viagra for sale ebay buy viagra online

See DUDE primer for info

Get Support


Belgium :
Kurt Van Meerbeeck
ORA600 bvba
Cell : +32 495 580714

Denmark :
Michael Môller
Service & Support Manager
Miracle AS
E-mail :
Cell: +45 53 74 71 27

Latin America

Brazil :
Cell : +55 47 88497639


South Africa :
Kugendran Naidoo
NRG Consulting
Cell : +27 82 7799275