It seems that real-life (and larger than life) database recovery stories always seem to lure in the crowds.
It's like a car accident - people slow down, have a look and hope it never happens to them (and causing additional traffic jams while doing so).

I mentioned in my previous blogentry that Daniel Fink will be presenting some real-life recovery stories at RMOUG on the 21th november, so I'm pretty sure it will generate some disaster tourism
One of the other ORA600 partners - South African's NRG Consulting - delivered a similar presentation last month at the South African Oracle Usergroup (SAOUG) :

DUDE Where is My Data - Database Recoveries and Data unloaders by Kugendran Naidoo

Kugendran is one of the brightest people I know - so I know he's a good presenter.
But Kugendran sure hit a home run that day.
He received the 'Best speaker award' for day 1 *and* the award for "Best Innovative Presentation 2008" :

Two thumbs up !  

On november 21st, Daniel Fink, my US partner and fellow Oaktable member, will be giving a presentation about his data recovery experiences at the RMOUG Quarterly Educational Workshop.
Dan has helped out several US based companies in some very complex recovery scenarios, where a data unloader like DUDE was the only option left.

Abstract :
Never a DUL Moment: How to Avoid Costly Data Recovery
Dan Fink

One of the worst situations is when you have a database in need of recovery...and find that you don't have a usable backup. One option is to use a Data Unloader (DUL), a costly tool/service that is able to extract data from a down and unusable database. However, the best option is to avoid this situation completely through recovery testing, database refreshes, and proper security. This presentation focuses on common reasons for data recovery (worst practices) and how to avoid them (best practices).

You'll find more info at the RMOUG website (www.rmoug.org) and here is an overview of the agenda, which looks very promising with speakers like Steven Feuerstein and Tim Gorman.

A couple of weeks ago, my wife and I, were driving on the E314 when suddenly we had a tyre blowout.
This is never a pleasant experience - especially in the middle of the night while driving to the airport to catch a flight !
I know now I'm ready to work the formula one pits, as me and the wife exchanged that wheel in a record time and 10minutes later we were driving again !
(ok - my hands were bleeding and my jacket looked like I came out of a coal mine)

That reminds of the next video made for the 2008 Miracle Oracle Open World (moow2008) by Carel-Jan Engel :

Ps - this does not apply to Alfa Romeo's - it's *OK* for an Alfa to have - several - warning lights flashing and still continue driving ...

In the spirit of Halloween - here are some other scary movies

OOW2008 highlight

I know, I know - many will consider the launch of HP Oracle Exadata storage server as the highlight of OOW2008.
I agree - it looks pretty exiting if you are running a datawarehouse firing a gazillion pq slaves at it. It put a smile
on my face to see that one of the beta testers was the ABSA bank in South Africa as I had worked on their datawarehouse 8years ago.
My buddy Kugendran was one of the lead DBA's.

Anyway - *the* highlight for me was the fact that Oracle had gotten their cool back.
For years now - I think, it started around the last OOW in Europe - it was Paris in 2003 - Oracle have produced lame white T-shirts and polo shirts.
I don't know who's in charge of their clothing line (if you can call it that) - but wake up and smell the coffee... no self-respecting DBA dares showing up at work with a white and red t-shirt with a massive bulls-eye logo to top it off.
Clearly, from a marketing perspective, the goal of giving away (who would buy them?) such shirts, is that people would wear them and thus, promote your product.
It got even worse when they thought - 'hey - this fleece is so white and bright, let's make it completely red!'.
Now - unless there's an Alfa Romeo logo on that fleece ... who would wear it?

As an experiment, Doug Burns, tried on one of these Alfa red fleeces at OOW2007.
Clearly, as shown on the picture below, some people were utterly shocked.
The girl's reaction - on the right - tells it all (and no - she is not dancing ... she's is starting a sprint)

A couple of weeks ago I wrote about consuming ws-security enabled webservices in PLSQL.

The problem was that, even using Oracle 11g and Jpublisher 11g, I was not able to generate a usertoken and password in the SOAP header according to the WS-Security standard.

My twisted solution was to put a WS proxy (or gateway if you like) in between the consumer (database) and the provider.
I would then place the proxy in the DMZ - and on behalf of the consumer :
- the proxy would set up an SSL connection to the provider
- receive the plsql/jpub generated XML
- inject a WS-Security header in the SOAP envelop
- adjust http headers (especially HOST & Content-Length)
- send the new SOAP message to the provider
- receive the response from the provider
- send the response to the consumer

It's not a real proxy in the sense that it proxies the complete http traffic. The proxy/gateway needs to alter the message and that wouldn't be possible if we would use SSL encryption straight out of the database :

Oracle rdbms --- http/s ----> proxy ---- http/s -----> WS provider (endpoint)

So instead we do something like this :

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 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 couchsurfing.com - here is an article as featured on wired.com.

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

updated - see also http://www.ora600.be/Consuming-WS-Security-enabled-webservices-in-PLSQL-part-2 

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.

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'.

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

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.