Menu Bar

Monday, September 12, 2011

Oh No, my database went down during a hot backup!

"IT'S A TRAP!" - Admiral Ackbar, Star Wars Episode VI: Return of the Jedi

So you're doing a hot database backup in Oracle when doing a user managed backup.

You're set up so that you have a script that sets all of your tablespaces into backup mode via "ALTER TABLESPACE ... BEGIN BACKUP", copies your data files to the backup location and then does "ALTER TABLESPACE ... END BACKUP".

While a tablespace is in backup mode, Oracle copies whole data blocks into the redo logs instead of just changed rows and the SCN's in the data file headers are not updated. When a tablespace is taken out of backup mode, Oracle updates the headers of the affected data files to the latest database checkpoint.

So, for whatever reason, your system goes down mid backup. When you try to startup the database, you'll get the following error:

ORA-01113: file 1 needs media recovery

Why this error? Since during the hot backup Oracle isn't updating data file headers with SCN's every checkpoint, the data file headers are out of sync.

So, having issued a "startup" command, the database is mounted but not open (the error occurred during the open phase).

From here you can issue "recover database until cancel".

The database will prompt you for the next archive log file in the sequence, but it will likely not be in the archive log destination. Feed it the destination of the most recent online redo log and you should see a message indicating that media recovery is complete.

Finish it off with an "alter database open noresetlogs" and you have your database back.

UPDATE 11/06/11 - There's an addendum to this post, noting another method to correct this problem here.

Oracle 10g backup documentation