Database file recovery

[Richard Bosley]

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

[Sanjay_6]

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.

Regds

[Steven E. Protter]

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.

SEP

[Jean-Luc Oudart]

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.

Regards
Jean-Luc

[Patti Johnson]

Richard,

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.

Patti

[Yogeeraj_1]

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
yogeeraj

regards
Yogeeraj

[Hein van den Heuvel · Accepted solution]

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,
Hein.