Oracle Database – How to recover from a lost datafile with no backup

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;