Pre-requisites
You have inadvertantly lost a datafile at the OS level and there are no current backups. You are in archivelog mode. You have ALL Archivelogs available since the datafile was created initially (creation date).
ORA-1110, lost datafile, file not found.
The Solution
Since there are no backups, the database cannot be opened without this file unless dropped and the tablespace dropped. If this is an important file and tablespace, this is not a valid option.
These files have to be recreated and recovered. Recreating the file and recovering it rewrites it to the OS and brings it up to date. Follow the steps given below to recover the datafile:
1. Connect to the database using sqlplus and shutdown the database.
SQLPLUS> shutdown immediate #(If this hangs, issue shutdown abort)
2. Mount the database:
SQL> startup mount
Check for the datafile which needs recovery:
SQL> select * from v$recover_file;
Example output:
FILE# ONLINE ERROR CHANGE# TIME ---------- ------- ------------------ ---------- -------------------- 11 OFFLINE FILE NOT FOUND 0 01/01/88 00:00:00
(Noting the file number that was reported in the error)
3. Note the file number that was reported in the output above and run the below query:
SQL> select * from v$datafile where FILE#=11;
Example output:
FILE# STATUS ENABLED CHECKPOINT BYTES CREATE_BYT NAME ---------- ------- ---------- ---------- ---------- ---------- -------- 11 RECOVER READ WRITE 4.9392E+12 0 10240 /tmp/sample.dbf
Note the status is RECOVER and the CREATE_BYTE size as well as NAME.
6. Recreate the datafile.
SQL> alter database create datafile '/tmp/sample.dbf' as '/tmp/sample.dbf' size 10240 reuse
Note that the file “created” and the file created “as” are the same file. The “size” needs to be the same size as it was when it was created.
7. Check to see that it was successful.
> select * from v$datafile where FILE#=11;
8. Bring the file online.
SQL> alter database datafile '/tmp/sample.dbf' online;
9. Recover the datafile.
SQL> Recover database;
10) Now open the database:
SQL> alter database open;