How to Recover lost datafile with no backup

In this article, we are going to learn how to recover lost datafile with no backup. Suppose expediently you lost a datafile at the OS level and there is no current backup available. But the best point is that you are in archivelog mode and you have all archive logs available since the datafile was created. The below error will come.

ORA-1110, lost datafile, file not found.

Read: Drop Database Manually in Oracle

Because no backups exist, the database cannot be opened without this file until it is dropped along with the tablespace. This is not a viable choice if this is a critical file or tablespace.

These files will need to be retrieved and regenerated. The file is rewritten to the OS and brought up to date by recreating and recovering it. To restore the datafile, follow the procedures below:

Step 1: Connect to the Database

Connect to the database using sqlplus and shut immediate the database. If it hangs then run shut abort.

$sqlplus / as sysdba

SQL>shu immediate

Step 2: Mount the Database

SQL>startup mount

After mounting, checks the datafile name which needs recovery.

SQL> select * from v$recover_file;

Output from the above query:

FILE#      ONLINE  ERROR              CHANGE#    TIME                
---------- ------- ------------------ ---------- --------------------   
8 	   OFFLINE FILE NOT FOUND     0          01/04/2018 00:00:00 

Note down the file number.

Step 3: Fin tablespace details

Using the above file number run the below query to find out more about the tablespace.

SQL> select * from v$datafile where FILE#=8;  

Output from the above query:

FILE#      STATUS   ENABLED    CHECKPOINT BYTES      CREATE_BYT NAME             
---------- ------- ---------- ---------- ---------- ---------- --------
8          RECOVER READ WRITE 3.9342E+12          0      10240 /u04/datafile/ocpdata.dbf

Step 4: Recreate the Datafile

As per the above output, the file is in recovery mode, So create a new datafile with the same name and exact location with the same size.

SQL> alter database create datafile '/u04/datafile/ocpdata.dbf' as '/u04/datafile/ocpdata.dbf' size 10240 reuse

We have successfully created the datafile with the same name, size, and location.

Check the file status:

SQL> select * from v$datafile where FILE#=8;  

Step 5: Make it Online

Now it's time to make it online.

SQL> alter database datafile '/u04/datafile/ocpdata.dbf' online;

Step 6: Recover the datafile

SQL> Recover database;

Noet:

Here its applying the all archives in this empty datafile, so it's required the all archives logs must be available on the disk.

Step 7: Open the database

After complete recovery now it's time to open the database.

SQL> alter database open;

I hope now you learn how to Recover lost datafile with no backup, write your fillings in the comment box.

Read More interesting articles:

• How to check RMAN backup status and timings
• Store RMAN output in a logfile
• How to restore archive logs from RMAN backup
• Use RMAN in NOARCHIVE
• Restore the SPFILE from the controlfile Autobackup
• ORA-10456 cannot open the standby database
• Step by Step to configure Oracle 12c Data Guard Physical Standby
• Resolve huge archive gap between primary and standby
• How to Convert Physical Standby To Snapshot Standby Database?
• Oracle 19c Data Guard Configuration step by step