ORA-01194: File # needs media recovery to be consistent

Posted on August 13, 2007. Filed under: Database Recovery |

ORA-01194: File 5 needs media recovery to be consistent

When you get the above error if you decide that you have to recover the datafile (or database), think twice. Always you may not have to recover the file. I had faced this scenario in one of the client places. Though the initial plan was to restore the datafile from the backup and recover, I had to change my mind.

Now the scenario…I was informed that a guy at the client site had restarted the database (on windows) and they could not open the database because of the datafile inconsistency. After some searching I could find that the problem was with the backup process!!

It happened so that when the hot backup (user-managed) of the database was being taken, that guy had shutdown the database. Now, while restarting the database a particular datafile was asking for media recovery. When checked I found that the datafile was still in backup mode. i.e. the database was shutdown when the backup was going on. Now, the solution is simple. Mount the database and use  alter database datafile 5 end backup command and open the database. There is absolutely no need to recover the datafile!! 🙂

So what exactly happened here?!? Well, nothing strange happened. It is a normal oracle behavior, nothing else. When the instance was restarted, the datafile which was in the backup mode will look old because the datafile header is freezed with older SCN. So it will (and it should :-)) ask for recovery. That’s it!

Generally on unix servers if you issue normal shutdown commands (except abort!!) on the database when the hot backup is going on, shutdown wont happen. It will throw an error saying that the datafile is in backup mode. So there is very less chance of facing this scenario on unix servers.

But in windows, it is general practice to shutdown the database using the OracleService on the services window. In this case even if the backup is happening database goes down with shutdown abort command internally. You won’t come to know that shutdown abort has happened.

You can even face this scenario when the instance crashes during backup (hot backup).You try to restart the instance without knowing that backup was active during the instance crash and end up with the same error.

So, don’t panic… coz Sometimes somethings goes unnoticed…… Take it easy!!!


OraclePitStop (contribued by Yadu)


Make a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

3 Responses to “ORA-01194: File # needs media recovery to be consistent”

RSS Feed for OraclePitStop – Lets share some knowledge Comments RSS Feed

Nice tip Yadhu. Thanks for sharing.

Thanks for sharing this

Thank you for your website 🙂


Where's The Comment Form?

Liked it here?
Why not try sites on the blogroll...

%d bloggers like this: