Sunday, April 27, 2008

Beware the hidden parameters... magic comes at a cost!

I was reading a series of posts on Oracle-L which had to do with a database that would not come up due to undo tablespace corruption.

There are some undocumented ways to respond to undo corruption. An example of one way is mentioned in the following blog (you can find this solution many places, but this is just one example I found):

(note there are other solutions for other specific conditions, but the same basic principles exist - I also have not taken painstaking measures to review the technical content of this blog entry, so use it at your own risk!!).

Now, the point of this post is this. The magic UNDO solution suggested here, using hidden parameters is all well and good, you might well get your database open again. I've been in places where this method was used just fine, and the database opened.

Unfortunately, afterwards folks didn't want to consider the potential negative ramifications to the database having used this method. What is the problem you ask? Well think about what UNDO is all about. Undo is the before image of a block that has been changed. It's used during recovery to rollback any uncommitted changes. The implication here then is that your data may well be inconsistent after you open the database using the method outlined above.

Want more? Think about the system tablespace and the Oracle metadata. IT used UNDO also, and so the SYSTEM tablespace data may well be inconsistent. Thus, you essentially have a corrupted SYSTEM tablespace. Not a good situation.

So, if you are going to use this big hammer, consider these implications. What I'd do is if you manage to get your database open, use exp or expdp to do a backup, recreate the thing and then load the data into the database. Your data may still be corrupt, but at least the system tablespace will be pure.

You can go fix your own data yourself.

Finally, this can all be magically solved if you introduce a sound and TESTED BACKUP STRATEGY before you have the failure. If you have a sound backup strategy I can almost assure you that you will never run into this kind of situation.

Oh yeah, I wrote a book on just that topic, backing up your database with RMAN....

1 comment:

Ben Prusinski said...

Good point on the dangers with undocumented parameters and utilities. And I completely agree about the requirement for a tested backup and recovery strategy.

Amazing how many companies are one step from going out of business to the lack of a proven backup and recovery strategy.

Ben Prusinski

Subscribe in a reader