Ask the Expert

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.

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: