Note: Technical Talk being moved to:

Thursday, 1 October 2009

Oracle - Media Recovery

A gem of a tip from my friend Mike N. which I was unaware of. Had a database in noarchivelog mode. Copied it across to a new server. Due to a minor mishap encountered "ORA-01113: file needs media recovery" on the target database.

To fix this, ran the command RECOVER DATABASE UNTIL CANCEL and when prompted to specify log, in the absence of any archivelogs, pointed it to the redo log files and was able to recover. Following this off with a ALTER DATABASE OPEN NORESETLOGS and saved myself the hassle of recopying all the datafiles again.

Another tip along the same lines provided by my mate Ken W. Once again, performed a RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL. Supplied the archivelogs I had available but attempting to do ALTER DATABASE OPEN RESETLOGS found it required another archivelog which I did not have. Therefore recreated the CONTROLFILE using syntax CREATE CONTROLFILE SET DATABASE "DBNAME" RESETLOGS FORCE LOGGING NOARCHIVELOG REUSE. Followed this up with a ALTER DATABASE OPEN RESETLOGS - problem solved.