Becoming the Master of Disaster

When I arrived early on Monday to run a few more tests of our applications against the standby site, I was surprised to discover the instance had crashed. After investigation, I found out that all of the drives had failed on one of the standby site's two disk drive arrays. Since that array held drives that contained datafiles for the system rollback segments, the rollback segment tablespace was corrupted almost immediately. Further investigation revealed that the disk array had failed because the array had only one power supply, even though a second redundant power supply module could have been installed.

Even though this was a rather unexpected and reasonably unlikely failure, it could not have come at a better time. It caused me to review our entire disaster recovery plan for both the secondary and primary servers. I found out that none of the production servers had been outfitted with redundant power supplies for the disk arrays. And some further reevaluation of my disaster recovery scenarios proved that the loss of one of the arrays would have caused the loss of UNDO segments on the production database - because it turns out those datafiles weren't mirrored properly either.

Both the primary and standby sites are all repaired now, of course, and everything is copasetic. However, my cautionary tale underlines how a robust disaster recovery plan can be critical in preventing and surviving a potential disaster.

Developing disaster recovery scenarios.
A good disaster recovery planner isn't afraid to "think about the unthinkable." This entails developing the common disaster recovery scenarios that could happen to your database and server.

Based on my experiences over the past several years as an Oracle DBA, the most serious of these is media failure. A typical example of preventable media failure involves under-utilization of RAID-0+1 or RAID-1 redundancy for critical data files, log groups, and control files. Moreover, as I described in my earlier tale of woe, it is a good idea to remember those pesky and often-overlooked UNDO or rollback segments - it may be impossible to restart the database when those tablespaces are damaged or corrupted due to media failure.

Another set of disaster recovery scenarios with serious implications involves the partial or complete loss of the database server itself. This might include damage to the software needed to run the Oracle instance - for example, the loss of critical operating system files - as well as physical damage, such as a failed power supply, memory, or CPU module.

Hardware disasters can be more difficult to predict, and can be even harder to test, since realistically a "test to destruction" of the hardware might have to be performed to simulate some of the failures. However, even with robust modern service agreements available from major hardware suppliers, it could be hours or even days before the damaged server is repaired and ready to take the load of a production database again, so these scenarios should not be ignored.

Once you've uncovered potential single points of failure and have painted some grim pictures as to what might happen if those failures occurred, it's time to turn attention to the methods, practices, and hardware configurations that help prevent a disaster.

Alternate production server.
If you are using Oracle's DataGuard facilities to create and maintain either a logical or physical "hot standby" server site, then you've already got this angle covered. However, if you do not have an alternate server to which you could quickly restore your production database, the ability to recover from a serious hardware disaster will be much more in doubt.

One less robust alternative to a standby site is a quality-assurance (QA) database server. This server should ideally be a close match to the hardware for the production site to allow evaluation of the next set of application or database changes about to be released to production. On one occasion before getting our hot standby server in working order, I was forced to transfer our production database over to our QA site because we had noticed some "flaky" performance of the production server. As it turned out, we had guessed right - the production server's motherboard was facing an imminent failure, and failed shortly after the transfer of responsibility. Though the QA server had only half the memory and CPU power of the production site, having a QA server in my "back pocket" saved the day.