Q

Restoring deleted datafiles

I have a problem opening the database because I accidentally deleted one of my datafiles from a tablespace which contains two datafiles.
An Oracle database will not open unless all of the database datafiles can be found. Since you deleted these datafiles, you cannot open the database without some work.

If you have a backup of the database, and I hope you do, then restore the missing datafiles from the backup. If you only have a cold backup and you are not running in Archive Mode, then you will have to restore the entire database and you will lose any changes since that backup was taken. If you are running in Archive Mode, then restore just the missing datafiles and then perform the following:

STARTUP MOUNT
RECOVER DATABASE;
ALTER DATABASE OPEN;

If you do not have a backup, then you have lost all of the data in these datafiles. To open the database, perform the following actions:

STARTUP MOUNT
ALTER DATABASE DATAFILE '/directory/filename' OFFLINE DROP;
do the above for all of your missing datafiles
ALTER DATABASE OPEN;

This was first published in February 2004
This Content Component encountered an error

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close