How Drop Tablespace and Recover Oracle Database When Accidentally Delete Datafile

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. However, the steps are not so straight forward.

• Run SQL*Plus.


• Connect to database as SYSDBA with this query:
  CONNECT / AS SYSDBA



• Mount the database instead of starting it up: 
  STARTUP MOUNT;



• 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.


• Now start the database proper:
  ALTER DATABASE OPEN;


• As the tablespace has damaged, drop it to recreate from fresh backup.
  DROP TABLESPACE <tablespace name> INCLUDING CONTENTS;


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


• Continue with the recovery process.

More Info