Roads ? - where we're going, we don't need roads ...
ORA600 | Last resort Oracle recovery
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.


There is nothing more important than backups

I was reading Pythian's blog on 'what makes a good dba' this week.

I could not agree more about the next part :

"There is nothing more important than backups.

End of story. Performance tuning and building an awesome server are cool, but this is the bottom line: our data is sacred. Before anything else, you have to make regular backups and you have to make certain that those backups work properly. If you want to know why, read this sad story: CouchSurfing Deletes Itself, Shuts Down."

It's not only the importance of backups, it's the ability to recover from them - I can tell at least a handful of stories were a backup was available (or thought was available) but one was not able to recover.

There's nothing better than stories like these to point out were a dba's priorities should be.

Here's the full story on - here is an article as featured on

It seems in the end the users didn't mind re-entering their data ... don't except the same from your enterprise end-users Wink



Consuming WS-Security enabled webservices in PL/SQL

updated - see also 

As a DBA, I sure do a lot of non-DBA stuff these days - the next story is a perfect example.

For the last 2,5years I've been spending about 60% of my time at a customer's site. Because I have a java background and know a thing or two about Oracle application server and networking, they give me projects you normally don't give to a DBA.
For example :
- setting up linux based clustered load balancers (instead of using blue coat or big-ip f5)
- setting up linux/apache based clustered reverse proxies/ssl terminators
- build a SAML v2 extension to Oracle SSO server
- Oracle SSO server integration with  Belgian e-ID passports
(mental note - blog about above topics)
People might say those are not a dba's task - and I guess that's true but I need a challenge now and then to keep the job interesting ...

Anyway - I was in a meeting with this customer a couple of weeks ago and I got a new challenge.
They asked me if I would be able to consume webservices from within PLSQL.
So I said sure - no problem - piece of cake - bring it on.
They prefer PLSQL - as they have a lot of PLSQL experienced developers in-house, some VB/.net developers and no java
developers. So it would either be PL/SQL or .net.

Verify your source

Always verify your source - order your '404 page not found' menu here.

Let's hope they don't have a bouncer called '301 permanent redirect'.


Life is dul without a hammer

I sometimes have to smile when I hear someone talking about best practises on backup and recovery. Don't get me wrong - they all make very valid points. The fact is that the real world is sometimes very different.

In the real world there is only one valid point - money.
In the real world, the IT departement is there to support the business and not the other way around. This sometimes means that IT budget is tight, resulting in all kinds of strange and funny things but ultimately may also - indirectly - lead to data loss.

RMAN is nice - but what if you're still running on an unsupported Oracle 7.3 on an old platform and a so very very tired hardware, all because there is no budget. Or what if you just blew all the IT budget on nice and shiny hardware and forgot to calculate that RMAN MML part for your backup solution. Or what if you do not have the luxury of a big tape library and still have to switch tapes by hand on each and every server - who is doing the tape management anyway ... it's a silly job ... but you better keep that person motivated and happy !

Most of the recovery cases I will cover here are the result of human error ... and those are sometimes the result of budget constraints.
The same goes for this case - the hammer case.
This was a recovery case where I actually went on site.
It was ooooold hardware – a mid ‘90’s RS6000 with a DAS storage cabinet using SCSI disks.

Remember the RS6000 F30, F50 and 220's - that will give you an idea. Oh while we are on memory lane - remember the time where you had to carry around 10 different scsi cables and terminators at all time.
I always had a bunch of scsi cables laying around in the trunk of my car - together with meters of ethernet coax cable, terminators, a couple RANs, db/de9/25 connectors, gender changers, null modem and parallel cables and of course the good old soldering iron and multimeter.
But I never ever carried a rubber hammer around !

So when entering the server room I immediately noticed this rubber hammer hanging on a small cord next to the storage cabinet – (naturally)I asked what it is was used for ?

Apparently, when the machine was rebooted or shutdown – the disks would cool down and their head(s) would stick to the platters.
So when they started the machine again, they would listen to the disks and once they heard the struggling sound of the heads trying to free themselves, they would hit the disks with the hammer.
That seemed to always get the job done Wink

What happened ? One of the disks had finally failed ........ Duh.

The disk had crashed while migrating AIX to a higher level - you know ... exportvg, migrate/reinstall os, importvg.
The mapping of the filesystems was quite simple - one JFS would map to one LV on one PV or disk. All disks/PVs belonged to one VG. No RAID. They had about 7 data PVs/scsi disks, and all database files where spread around those PV's.

So a disk had crashed, making it impossible to perform an importvg.
No problem - we have a backup - let's just make a new VG of the remaining 6 disks - and rebuild the database from scratch.

Their backup solution was a user per user export. Not a best practise.
And to top it off - the backup script was hardcoded. Not a best practise.
You know what's next right ? - a new user was added a couple of months before and the backup script was not altered accordingly.

Bad luck - but wait. The new user was sitting on that crashed disk in a seperate tablespace !

Now, we were able to replace the PCB on the SCSI disk and it would spin up ! (how lucky can you be)
So the actual data on the disk was still intact, but because the disk was once part of a VG, made by a logical volume manager (LVM), you couldn't just pop in the disk in a machine and see the data.
So it’s not like a FAT or NTFS filesystem on windows where you hook up a disk to the server and voila, there are your files - we had to get an expert on site who had to extract the datafiles from the disk, wading through all the physical partitions. This guy knew his stuff but I can still see him sitting there breaking a sweat.

Once we got the datafiles, they were orphaned from their database – so we used DUDE's heuristic scanner to get the data out of the datafiles into flatfiles. One of the senior developers then quickly identified which flatfile was which table and using sqlldr we were able to load the data back in. There were only a 120 tables or so.

Later on I wrote a routine that would allow us to identify deleted records within the base dictionary tables – this makes it possible to map objectid's to table/column/usernames even if the objects were dropped – very much like the FEDS tool.

Unloading without a SYSTEM tablespace is always time-consuming as one needs to identify the flat files. Without SYSTEM, there's no dictionary and without a dictionary there are no table/column names and datatypes.

In conclusion – if you see a hammer attached to a storage device ... Walk away.


AYTS 2008 - Are you the smartest ?

I had a subject for a next blog entry already picked out when this mail came along :

Finally found some time for finishing off my ayts-movies and write a trip report - here is the result :


Joris is a employee of Ordina Belgium (so I guess he must be an ex-collegue of Dimitri Gielis). And Joris won the Oracle AYTS competition representing Ordina.
Don't worry - AYTS is not another product in Oracle's portfolio or the latest mutation of some J2EE framework.

AYTS is a competition organised by Oracle Belgium for all their local partners.
I hear they are now extending the contest to other countries too, like The Netherlands (and I think also Germany and Switserland - but I might be wrong about that tho).
AYTS or 'Are You The Smartest?' is the brain child of Yves Van Looy, a belgian based (pre)sales consultant and is basically a clever incentive to get the partners up to speed with the latest Oracle developments/products/technologies

Here is how it goes down - 10 or so Oracle partners enroll a number of software engineers, dba's, consultants,...
There are 4 technical and 1 commercial session - each session takes about 1/2 day and covers one topic.
For those who know the workshops/masterclasses on the last day of the UKOUG - it's a bit like that ...
At the beginning of each session you need to take an exam covering the previous topic. The exams are multiple choice
and open book. This sounds easy but I can assure you, it's not. There have been cases were people got negative scores on exams Wink 
At the end of the competition, there is a graduation ceremony where the winner for each Oracle Partner is rewarded with a price.

This year's topics were :
- ADF & Coherence by Filip Huysmans
- Oracle Identity & Access Management (IAM) by Antonio Mata
- Oracle WebCenter Suite 11g by Filip Huysmans
- Oracle 11g new features
- Oracle pricing/licencing by Yves Van Looy

Last year's 2007 topics were OBI, SOA Suite, IAM, JDEV/ADF and also a sales/pricing session.

It's a broad range of topics - developers will be more interested in the ADF & Webcenter content but will also get a taste of new RDBMS features... while for people like me - who are more into architecture and rdbms technology - there's the other stuff. So I can finally talk about ADF without sounding stupid.

Anyway - how brilliant is that !
All parties win :
- Oracle, who gets the word out to hundreds of people in the field, lowering the threshold to jump on these new technologies
- those people in the field who gain more knowledge
- and the Oracle partners who get more motivated employees

Now what's the incentive - what does make people like me follow these sessions in my own time and in the evening ?
Well - first price is a 4day VIP trip to San Francisco & Las Vegas. This year 14 Oracle Partners entered - so there
were 14 winners.
For me it was the second time I've entered and the first time I've won for AXI.
I knew some other guys that won - like Jorgen Bosman - an ex-collegue from Ferranti and still a dear friend
since I've left Ferranti in '98. Jorgen is also one of those guys who likes to write code for fun...just have a look at this page.
(oh he's a 2 for 2'r - 2 times entered and 2 times a winner)
Needless to say he became my roommate.
Another winner was Geert De Paep. His name should ring a bell - he's the man behind DBA Village.

I've been to SF & Vegas many, many times but it was still awesome !
Here's a small agenda :
day 1 : staying at Hyatt hotel near Fisherman's Wharf - visit pier 39 - check out golden gate
day 2 : visit Oracle HQ,  ride segways through SF, fancy dinner
day 3 : Vegas - room at Bellagio, great seats at Cirque du Soleil 'O'
day 4 : Grand Canyon helicopter ride with champagne lunch in the canyon
day 5 : flight home

The Oracle HQ visit was great as we got to meet Wim Coeckaerts who talked about Oracle Enterprise Linux & Oracle VM.
In short OEL is basically RHEL with all the trade marked logo's stripped away. So you're basically running RHEL. The difference is the support which is through metalink.
This is how I understood OEL's history. Basically, Oracle has a couple of big whale customers (Amazon) running Oracle on RHEL. When bugs were discovered Oracle would actually fix the bugs in RHEL and send them to RedHat... sometimes RedHat included the patch, sometimes they didn't. I guess ,Oracle basically got fed up with that and released OEL and Unbreakable Linux support program.
He pretty much convinced me to switch OEL for our future production systems. And I definitely will have a go at Oracle VM.
In fact we - at AXI - have already ordered 2 new 1U servers to test the RAC/ASM/OEL and Oracle VM combo.

Oh - one juicy detail - see if you can find Wim in one of the youtube clips... he definitely has changed his apperance Wink
We also had a presentation about ADF 11g by Shay Shmeltzer. To be honest - I still write my own java programs in Ultraedit. When I see an
ADF guru like Shay jumping around JDeveloper, it all looks so simple. But every time I need to do something in JDev myself (that's about once a year)
I'm completely lost. I mean... even when opening a new project ... there's like a million choices Yell
The irony is that I used to be a be a beta tester of the first JDev release in '98 (it was called project Walhalla back then) - things were still
simple in the 'old' days. It was just Borland JBuilder with some extra Corba features build in and no xml whatsoever.

Anyway - have a look at Joris' blog entry at It's in dutch. If you don't understand it you can at least look at the dramatic edited youtube clips (also courtesy of Joris)

In fact, the dramatic undertone of the clips made me decided to put together a clip myself - it's a snippet of me and Jorgen
getting ready to play some black jack. For dramatic effect we placed a score by Hans Zimmer on it ... looks just like Rain Man Cool

(ps Jorgen and I did tripple our money within the hour playing black jack ... only I lost 2/3 the next day)


UKOUG 2008 and more

Speaker comfirmation mails for the UKOUG 2008 are dripping in.
I see that Marco Gralike made the cut.

I'm glad to announce am 'in' too. From the many responses on the Oaktable list,
it looks like a lot of oakies will be there including my US partner Daniel Fink from Optimal DBA.
I see that Carel-Jan Engel will also be there, after missing the event last year - as a Citroën freak, he'll be happy to know I'll be trading in my Alfa 156TI for the new Citroën C5 this year. (for the Alfa freaks, I think Alfa blew it with the design of the 159)

While we're on the subject - I will also be presenting at the Oracle Technology Days on september the 9th in Brussels.
For people in the Benelux, I would like to mention that Doug Burns will be giving his Oracle 10g performance analysis course in october at the Oracle university in Brussels.
Doug is a very passionate speaker and tutor and if you're in need of a tuning course and you are in the benelux area, then don't miss out on it !
And there's more - my south african partner, and dear friend Kugendran Naidoo from NRGC will be presenting a backup & recovery paper based on the 'DUDE where's my data?' presentation at the South African Oracle User Group convention (SAOUG) in Sun City in october. I was actually thinking about joining him but now with UKOUG in sight, he'll be on his own.

With all this said - I won't be going to OOW2008. I don't mind really (you'll know why reading my next blog post).
I had a great time at OOW2007 - I did miss out on some sessions but for good reason. We went out to Sonoma county for
some wine tasting.  We ended up at Francis Ford Coppola's winery.
Here's a picture of me and my collega's at the tasting - one of the guys ended up buying 18 bottles of wine... which he eventually had to check in as luggage on the way back, because for some reason no shipping company would ship the wine to belgium.

axi at oow2007 
The fact that we look like the Daltons is completely unintentional !

A new ORA600 website - a new blog, DUDE !

I've been spending a lot of time lately redoing the website.

When I started this site, it was just a way to provide some technical details about the jDUL/DUDE tool. I needed a quick jumpstart of a website. The easiest way to do that, is to buy a Frontpage html template (which are offered by a gazillion sites), and upload that to a cheap hosting/provider. Actually, I only did the 'History' page because I otherwise had to throw one of the template's pages out. I think I all did that within 30minutes and in total costed me 25$.

It all went so fast because the website was not a priority - I had to get things moving. In fact, the whole company (ORA600) was setup within half a day (I was pretty chuffed about that - when starting a small company in Belgium there's almost no red tape anymore). For the first 2,5years I did all the financials myself... which I utterly hated. (someone give me an MBA for this Laughing). Anyway ... the jDUL/DUDE tool ... that was and still is the focus.

When looking back, it's - ahum - a miracle we got ORA600 off the ground. It was the opening of the Miracle brewery that triggered the startup...

Back then, I had no interest whatsoever in the Oracle community. I've lost my mother in 2003 (after loosing my dad 5y earlier). I did not have any brothers and sisters - and so I had decided that I should enjoy life and not wait for retirement. I had thought about it for a long time when I told my boss at AXI that I wanted to work only 4 days a week anymore. Mind you, I was only 28, 29y old - I expected him to say that I could look for another job (and I had a job in mind - the banking and insurance business is always throwing with large amounts of vacation days Cool). But no, he accepted and I haven't regretted it yet (this year is my 10th year at AXI). Just to say - I wasn't really eager to start a company on the side.

What I said above about the Oracle community is not entirely true. I wrote some open source tools - and got a paper/presentation approved for the Oracle Openworld in Berlin, 2001. The topic of the presentation was about Oracle Parallel Server and single instance databases. My luck was that 2001 was the year Oracle launched RAC - so my paper got selected (I was smart enough to put some buzzwords like cache fusion in the abstract). In actual fact, the conclusion of the presentation was that you only needed OPS/RAC if one big SMP box is not big enough and if you used it, watch out for scalability issues in your application. Funny enough, by the time I started doubting my own theory, I stumbled upon a paper called 'You probably don't need RAC' by Mogens Norgaard. I had no idea who he was, but I contacted him because I couldn't find the  RAC benchmark by Mario Broodbakker he mentions in the paper.

Another year later, 2004, I buy this book 'Oracle Insights : Tales of the Oak Table' from Amazon (I used to buy a *lot* of books back then). I've already had a quite complete jDUL version - I used it now and then on databases from people who contacted me. The problem was - I needed more people I could trust to test jDUL. The tool itself was controversial and I couldn't just give it to anyone. Now I'm really bad with names, but after reading that book, I realised (it took me a while) I had already had exchanged some mails with this Mogens character. So I thought, why not let him and his buddies play around with jDUL. I contacted him and he was interested - in fact, he cc'd Peter Gram (also from Miracle and ex-Oracle), who had tried to create a DUL like tool in the past. Those were not the words I was looking for and just blew things off. (mind you that jDUL is written in java and can be easely decompiled to it's source code - I was and still am very protective about the source code).

Fast forward another - 2005. I get a call at my office - Mogens Norgaard. He asks if I can recover a database for him and asks about the rate. Back then I agreed on recoveries, only if you uploaded the datafiles and within a limit of 15Gb.  I did the recoveries basicaly for free as they were excellent test cases. Mogens refuses the 'free' part and says he can not accept that. I found that a bit strange at the moment - I now know better, and people knowing Mogens, know he's made out of a different kind of wood then most other people. In the end we agreed that he either would give me a free seat to Miracle's next masterclass (which was Steve Adams in Utrecht - i think) or we would discuss later on how we could colaborate.

I probably would have taken the free masterclass if it wasn't for the grand opening of the Miracle Brewery. My cousin Wim is a micro brewer. In Belgium, everybody has a cousin who either makes beer or wine as a hobby or (semi-)professional. And I used to work for a brewery (Primus Haacht) as a student. (it's not part of InBev mind you!) Needless to say we were both interested in seeing the brewery. And as Mogens promised to set us up at Lasse's place it was only natural that Wim, me and Els (then girlfriend - now wife) would go on a roadtrip. I had this gps system back then - a pioneer AVIC X1. I trusted it blindly - unfortunately, the brewery is located in a street called 'strandvej' or basically 'beachroad' ... apparently, there are a lot streets called 'strandvej' near the beach even within the same village... I think Lasse will still remember the trouble he had guiding us in !

wim at miracle brewery

Anyway - the opening itself was pretty cool - lots of beer of course. And the beer was quite good. Much to our surprise, because it's not normal that a recipe is good on the first batch. So kudos to Lasse. As you can see from the picture above - my cousing Wim had the time of his life Cool. I had no idea what Mogens looked like and we only got to talk business later in the evening. I think we were both drunk and the conversation was quite short (I only needed an answer to one question really). Anyway I had met Lasse and Mogens face to face and that was enough for me to go for it. (beer is an excellent catalyst) So that day was quite defining and quite unexpected.


That's the reason I went with the simple web presence - it was all quite sudden. So after 3 years, I think it's fair to say it was time for a make over. I took the original Frontpage theme and turned it into a Drupal theme. Drupal is a CMS with its origine in Belgium. It's database driven - I could have used OracleXE, but I didn't (I could have even used APEX, but I didn't). I went with mysql instead. Maybe I'll blog about it later. The major reason for going the Drupal road is that it's :

  • open source
  • there are a million modules available to do just about anything
  • php based - and I needed to write a whole partner module for generating licenses and customer/partner management
  • most people use Wordpress Wink

The last thing that was missing was a blog ... this whole web 2.0 thing sure got fired up the last 3years so maybe it's time I jump that wagon too. You'll probably find some recovery war stories here in the future... but what you probably will not find here are whole replicas of Oracle manuals (that's not why I read blogs). Anyway - I won't be writing any hardcore dry technical content ... I do that enough already during the day.

I guess that's enough for now - time to get back to the real world.





See DUDE primer for info

Product is discontinued.