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

    Login

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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top