How to Restore and Recover a Lost Datafile Using Archived Redo Logs

Oracle database recovery becomes very powerful when the database is running in ARCHIVELOG mode. In this article, we will see how to restore a lost or deleted datafile using archived redo logs, based on a practical demonstration.

This method works when all archive logs are available from the time the datafile was created.

Prerequisites

• Database must be in ARCHIVELOG mode
• All archived redo logs must be available
• Control file and other datafiles must be intact

Step 1: Verify Archive Log Mode

First confirm that the database is running in archive log mode:

SQL> archive log list;

If it shows Database log mode: Archive Mode, then recovery using archivelogs is possible.

Step 2: Create a Tablespace and Datafile (Test Scenario)

Create a new tablespace for testing:

SQL> CREATE TABLESPACE usek_tbs DATAFILE '/acfs/usek_tbs01.dbf' SIZE 10M;

Create a table and insert data into that

SQL> CREATE TABLE usek_tbl1 TABLESPACE usek_tbs AS SELECT * FROM dba_tables;

Step 3: Simulate Datafile Loss

Now remove the datafile from OS level:

$ rm -rf /acfs/usek_tbs01.dbf

The database will report errors because the datafile is missing.

Step 4: Take Datafile Offline

Bring the missing datafile offline:

SQL> ALTER DATABASE DATAFILE '/acfs/usek_tbs01.dbf' OFFLINE;

SQL> ALTER DATABASE OPEN;

Step 5: Recreate the Datafile

Create an empty datafile with the same name:

SQL> ALTER DATABASE CREATE DATAFILE '/acfs/usek_tbs01.dbf';

This creates a placeholder file where archived logs will be applied.

Step 6: Recover the Datafile Using Archivelogs

Now apply the archived redo logs:

SQL> RECOVER DATAFILE '/acfs/usek_tbs01.dbf';

Oracle will request and apply archived redo logs automatically until recovery is complete.

Step 7: Bring Datafile Online

After successful recovery:

SQL> ALTER DATABASE DATAFILE '/acfs/usek_tbs01.dbf' ONLINE;

The datafile is now fully restored with all committed data.

Why This Works

Archived redo logs contain every change made in the database. When the datafile is recreated, Oracle replays all changes from the archived logs and rebuilds the datafile to its most recent consistent state.

Important:
Recovery will fail if any archived redo log is missing. Always back up archived logs regularly to ensure successful recovery.

Conclusion

Restoring a datafile using archivelogs is an efficient recovery technique that avoids restoring the entire database. As a DBA, always ensure ARCHIVELOG mode is enabled in production systems and archive logs are properly backed up.

Please feel free to ask. Thank you 🙂

Toufique Khan