Skip to Content

++ 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
RECOVER DATABASE...

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…

KR Lars
p.s.
Of course SAP has a note for this error:
Go ahead and check this one: #4162 – Missing “end backup”

To report this post you need to login first.

1 Comment

You must be Logged on to comment or reply to a post.

  1. Paul Hardy
    As we all know most SAP error messages generated from the R3 system are “self explanatory” i.e. they tell you nothing so at least do not point you in the wrong direction.
    One message that confuses the users is the “cost centre does not exist” message in KS03. The user starts panicking and logs a help desk call saying their cost centre has vanished. What the error message should say is “cost centre X does not exist in controlling area Y”. The current controlling area is cunningly not on the screen but hidden in a menu option. The long text says this but most users do not bother looking for long texts as nine times out of ten the long text is the same as the short text on SAP error messages.
    By far the WORST message a user can get is the text at the start of the short dump (the “what can you do” section ), which suggests that they print it out and then contact their administrator. So, every so often I find a hundred page pile of paper sitting on my desk because a report has timed out.

    I wonder if SDN could do some sort of endeavour to get a list of the most misleading standard SAP error messages, or badly designed standard screen features ( e.g. the use of the “refresh” icon for “delete” in MIRO ). That could then we forward to the so called SAP “usability” lab so they could do something about them. But I am not holding my breath!

    (0) 

Leave a Reply