How to Drop Tablespace and Recover Database When Accidentally Delete Datafile

 You may get also some missing datafiles after database recovery process. In any case, missing a or a few datafiles will cause Oracle database to fail to startup, rendering the whole DB inaccessible. The solution to the missing datafiles is to drop the affected tablespace where has incomplete datafiles, and then recreate the tablespace and import the data into the tablespace from backup

1.    Run SQL*Plus.

2.    Connect to database as SYSDBA with this query:

CONNECT / AS SYSDBA

3.    Mount the database instead of starting it up:

STARTUP MOUNT;

4.    Issue the following command to bring the missing datafile offline so that Oracle won’t trying to connect and access the datafile anymore:

ALTER DATABASE DATAFILE ‘<datafile name with complete path>’ OFFLINE DROP;

Repeat the command for every datafiles that unaccounted for.

5.    Now start the database proper:

ALTER DATABASE OPEN;

6.    As the tablespace has damaged, drop it to recreate from fresh backup.

DROP TABLESPACE <tablespace name> INCLUDING CONTENTS;

7.    Ensure the other datafiles for the tablespace has been deleted, if not, remove them manually from the operating system.

=========================================================================

To determine and identify all datafiles that link to a tablespace, use the following query, with tablespace name in capital letter:

SELECT file_name, tablespace_name
FROM dba_data_files
WHERE tablespace_name =’<tablespace name>’;

If a tablespace contains multiple datafiles, and you just want to drop one or some of the datafiles and keep the remaining datafiles together with the objects and contents, the objects or data must be exported for the affected table space. Once exported, the tablespace can be dropped with above “DROP TABLESPACE” command. Then, recreate the tablespace with the datafile(s) required (that you initially want to keep), and then import the objects into the recreated tablespace.

If one or more datafiles is missing after a recovery process or accidental deletion, you can use ALTER DATABASE DATAFILE <datafile name> OFFLINE DROP command to make the datafile offline so that database can starts up after which the troubled tablespace can be dropped.

====================================================================

8.    Continue with the recovery process.

--------------------------------------------------------------------------------------------------------------