Problem solve Get help with specific problems with your technologies, process and projects.

Problems restoring database from cold backup

There are two possible causes for receiving the ORA-00314 error during recovery.

A question regarding a recovery situation I had ...

8.1.7 database running archive
hot backups daily
cold backup weekly

I had to restore the database from a cold backup one month earlier. I had all the archive logs and the current control file. I restored using the recover automatic parameter. The recovery went well, but I did not apply my last four archived logs and I received the following error:

*** Media Recovery
Log /scratch1/archive/oradata/aleph0/arch/arch_aleph0_1_36075.dbf
Thu Nov 20 11:08:39 2003
Errors in file /aleph/app/oracle/admin/aleph0/udump/aleph0_ora_422.trc:
ORA-00314: log 1 of thread 1, expected sequence# 36076 doesn't match 35711
ORA-00312: online log 1 thread 1: '/aleph/oradata/aleph0/redoaleph001.log'
Media Recovery failed with error 314
ORA-283 signalled during: ALTER DATABASE RECOVER automatic ***

It leads me to believe I should have saved the current online redo logs. I guess my question is, I thought the recovery would apply all my archive logs first then go to the redos -- it appears this is not the case because the last four archive logs did not get applied ... I had to resetlogs to get the database up. Very nervewracking and confusing. Any thoughts?

The cause of the ORA-00314 error is that the sequence numbers of the online redo logs do not match the the sequence numbers in the control file. There are two possible causes:

1. The control file was restored, but not the online redo logs. The control file is looking for online redo logs at the time of the backup.

2. The online redo logs were restored with the data files but the control file was not restored.

Again, the control file and online redo logs are not in sync. The recovery of the control file and redo logs depend on the database recovery intended. If a full restore of the database and recovery to the most current point in time is intended, then the control file and online redo logs should not be recovered unless they are lost. The current state of both the control file and redo logs will be used to perform a complete recovery. If a full restore of the database to the point in time of the backup is intended, both the control file and redo logs can be restored with the datafiles. This will allow the database to open from the point it was shutdown for the backups. This is the only time that recovery of the redo logs can assist recovery. If the redo logs were not recovered, the recovery to a point back in time would require a resetlog to open the database.

Since you say that it didn't apply the last four archived logs, I'm not sure what was up with that as ALL archive logs should've been applied and then the current redo logs would've been searched for to apply the transactions there to complete the "roll forward".

The message basically is saying that the sequence number of the online log does not match the information for that log in the controlfile. If you did not have the current redo logs that is what is up there. But if the last four archived log files weren't applied, I'm not sure what would cause that, unless they were corrupt somehow.

Dig Deeper on Oracle database backup and recovery

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.