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:



Howto's
See DUDE primer for info

Get Support

Europe

Belgium :
Kurt Van Meerbeeck
ORA600 bvba
E-mail
dude@ora600.be
Cell : +32 495 580714

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


North America

USA :
Tim Gorman
Evdbt Inc
E-mail
tim@evdbt.com
Cell : +1 303 885 4526

Canada :
Pythian
E-mail
dude@pythian.com
Contact


Latin America

Brazil :
HBtec
E-mail
dude@hbtec.com.br
Cell : +55 47 88497639
Contact


Africa

South Africa :
Kugendran Naidoo
NRG Consulting
E-mail
k@nrgc.co.za
Cell : +27 82 7799275


East Asia Pacific

Australia
Andre Araujo
Pythian Australia
E-mail
dude@pythian.com
Cell : +61 2 9191 7427 ext. 1270