Simpler Navigation for Servers and Operating Systems
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
Showing results for 
Search instead for 
Did you mean: 

Database file recovery

Go to solution
Richard Bosley
Occasional Visitor

Database file recovery

Hi, We are running an Oracle database on windows 200 enterprise edition.

when we started the server today we got the following error message:-

ora-01113: file 2 needs media recovery
ora-01110: data file 2: 'E:\oracle\ordata\hps\undotbs01.dbf

We have a valid backup of this file. Are we able to copy this file directly from the backup copy to replace the damaged file? The data in the database will have changed since the last succesful backup was completed.

Look forward to your responses
Many Thanks
Honored Contributor

Re: Database file recovery

Hi Richard,

You'll probably have to a do a complete restore from the last backup and then apply the archive logs, if you have the database in archive mode.

restoring the single file from an old backup of the database may not help, since the various files are in sync with each others.

Hope this helps.

Steven E. Protter
Exalted Contributor

Re: Database file recovery

Before you restore any files, try a standard recovery with sqlplus.

Sometimes the database can correct certain erors itself.

Otherwise you will need to restore to your last cold backup and roll forward or perhaps your last hot backup if you take such backups.

Steven E Protter
Owner of ISN Corporation
Jean-Luc Oudart
Honored Contributor

Re: Database file recovery

Hi Richard,

You may want to read attached document "How to Recover from a Lost Datafile with Different Scenarios" Metalink doc id 198640.1

Also I would suggest you take a backup of current situation (cold backup) before you start running any recovery procedure.

fiat lux
Patti Johnson
Respected Contributor

Re: Database file recovery


Is your database in archivelog mode? If it is and you have a copy of the file from your last backup and all the archive redo logs created since that backup you should be able to copy the file from backup and roll forward without loosing data or restoring the entire database. If you are not in archivelog mode then you need to restore the entire database from the last complete cold backup, you will loose everything since then.

First try and determine why the file need recovery? Did the file get deleted? Is the directory still valid? Can you restore to the same location.

Before attempting any recovery it is best to determine the cause of the problem. Check out metalink article 183367.1, it's also possible that you did a shutdown while in backup mode. Check the alert log to determine what has happened.


Honored Contributor

Re: Database file recovery

hi richard,

you cannot just restore the backup of this file. It will not work. You need to perform a media recovery at the database level.

Hopefully if you are using RMAN, you can easily recover till the last change made prior to the shutdown.

if you are unsure, please contact Metalink for a better step-by-step assistance.

kind regards

No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Hein van den Heuvel
Honored Contributor

Re: Database file recovery

But hang-on, Was that the oly datafile reported with a problem?
The name of that datafile certainly strongly suggests that this is an undo tablespace.
So you might just be 'lucky'.
There is no user data in there.
You can quite possibly change your initXXX.ora file to only use a system undo segment for now. Then just create a whole new undo tablespace and some segments in that. As it is called undo, not rbs, may we assume you switched to automatic undo? That's fully manageable from the initXXX.ora file.

Good luck,