++ 07.03.2011 ++ UPDATE ++ UPDATE ++ UPDATE ++
The mentioned error message had been improved with Oracle 10.2.0.5 (“Things change for the better” or “How I enhanced the Oracle DBMS software”). Thus, the content of this blog post is only valid for older versions of the Oracle database software.
++ UPDATE ++ UPDATE ++ UPDATE ++
There are dozens of support messages on the BC-DB-ORA component each year that have been opened because of only one error message:
ORA-01113: file string needs media recovery
Usually customers see this error when trying to startup a db instance that was shut down “the hard way” (that is shutdown abort, a power outtage or something similar).
The next thing that is usually done is typing in
That could be the worst thing to do! Why?
Let’s have a look how Oracle describes this error:
Cause: An attempt was made to online or open a database with a file that is in need of media recovery.
Action: First apply media recovery to the file.
Huh? What is wrong here? Haven’t I just written that a recovery is not the right thing to do?
Yes, I did.
The problem with this error message is: it tells us only the half of the truth.
It’s true – if there is a file that needs recovery then this error will appear.
But in most of the cases when this error occurs there is no need to recover anything.
All that has to be done is to update the fileheader.
And this is done by
ALTER DATABASE DATAFILE '' END BACKUP;
Unfortunately a datafile in ONLINE BACKUP mode looks very much the same to the Oracle startup procedure as a restored datafile does.
Both have lower system change numbers (SCN) in the datafile header than the current SCN.
To catch up redo-log information is necessary, but only for the restored file.
The file in ONLINE BACKUP mode just needs to get the datafile header ‘unfreezed’ so that it will be updated again. That’s an action of some minutes if not only seconds.
But due to the misleading message text, people try the recovery over and over again. If something goes wrong with it, restore is tried and so times flies…
In fact I once (and only once!) fell for this misleading error and supported a customer in trying the recovery.
The backup/recovery setup at the customer side was not well tested… in the end the customer had to work the whole weekend to get his database online again.
(To my excuse I was quite new in database support back then!)
All that just because the Oracle developers decided to write the error-message as they did.
Why couldn’t they change it to something like this:
ORA-01113: file string needs media recovery or is in online backup mode
They would save so much time of customers with that…
Of course SAP has a note for this error:
Go ahead and check this one: #4162 – Missing “end backup”