Tips and Tricks: Database Recovery

Author: Ron Shaffer

May 2009

Situation
A client in the health care industry is using Oracle for their Tivoli database. They have approximately fifty-five million rows of audit data about their environment, applications, hardware, etc. stored within.  Like many database implementations, the system is installed, configured and as long as the dependent program runs, all is well. That is until the one day when the program does not run and the database will not start.

Problem
Diving into the system found that the header of the undo tablespace was corrupt and the database would not open. It would start and even mount, but it would not open. It failed while applying redo log entries to the undo tablespace file. It was current as of log sequence 67629 and Oracle needed to apply the current redo log sequence 67630 but it could not because the file header was corrupt. This requires media recovery. When presented with the backups, they were inconsistent, meaning the SCN for the data files were at different levels when the backup was taken. This usually happens because the database was open during the backup. The log sequence of the last backup of the undo tablespace file was 67598 and there were no archive logs. This means that the log sequence required for recovery was not available and the file could not be recovered.

Solution
The solution to the problem is a little challenging but very exciting. First, understand that because the undo tablespace was the problem, we could afford to lose the transactions in the rollback segments. We used a hidden parameter that allowed us to open the database even though it was in an inconsistent state. Even though we opened the database we could not export data because we still had a file that required media recovery. We decided we could drop the tablespace and recreate it to allow data exports. We used a second hidden parameter in Oracle to make this happen. We created a new undo tablespace and performed a full database export. This export was stored and protected from any harm as it was complete and error free. We used the export to create a new 10G release 2 database for the Tivoli application and configured RMAN backups and archive logging for failure protection.

Tips
1.    Always run OLTP databases in archivelog mode for recovery
2.    Always use RMAN as your Oracle backup solution
3.    File level backups of an open Oracle database are inconsistent and can cause data loss on recovery. Some exceptions apply, but use RMAN and don’t worry about it.
If you need more information, contact Gary Codeluppi at 937-912-3273 or visit the Ross Group Inc web site at www.rossgroupinc.com.