Tips and Tricks: Database Recovery
Author: Ron Shaffer
May 2007
Situation
A health care industry provider is using Oracle 9i for their Tivoli database. There are 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 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.  The backups 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, the transactions in the rollback segments could afford to be lost.  A hidden parameter was used that allowed the database to be opened even though it was in an inconsistent state. Although the database was opened, data could not be exported because of the file that required media recovery. It was decided to drop the tablespace and recreate it to allow data exports. A second hidden parameter in Oracle was used to make this happen. A new undo tablespace was created and performed a full database export. This export was stored and protected from any harm as it was complete and error free. The export was used to create a new 10G release 2 database for the Tivoli application and configured RMAN backups and archive logging for failure protection.
Tips & Tricks
- Always run OLTP databases in archivelog mode for recovery
- Always use RMAN as your Oracle backup solution
- File level backups of an open Oracle database are inconsistent and can cause data loss on recovery. Some exceptions apply but use RMAN to eliminate the risk.
