It’s hard to prevent accidents from happening, even though some of them may turn out to be really costly. This is especially true in the case of Oracle database – where the data objects are stored physically in data files on the hard disk itself.
This can lead to several potential mishaps – a novice administrator may accidentally delete some of the data files, or the data files may get corrupted or unreadable in the case of a hard disk failure, or it might also happen that certain data files are missing after a database recovery process has been carried out. In all of the cases, because of the missing data files, Oracle database will fail to startup, and as a result, the entire database will become inaccessible.
This problem can be solved by first dropping the affected tablespace containing the incomplete / missing data files from the startup process. Following this, the tablespace can be recreated and the data files of concern can be imported from backup. In this way, your database is restored to its original functioning state.
However, the steps involved in this entire process are not so straightforward. They are listed below and you should be able to manage to fix the problem if you follow them in order:
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:
4> Issue the following command to bring the missing data file offline so that Oracle won’t trying to connect and access the data file anymore:
ALTER DATABASE DATAFILE ‘<data file name with complete path>’ OFFLINE DROP;
Repeat the command for every data file that has been affected.
5> Now start the database:
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 data files for the tablespace has been deleted, if not, remove them manually from the operating system.
8> Continue with the recovery process.