Recovering a lost datafile with no backup available How do you recover a lost datafile if:


the database is running in archivelog mode
there is no backup is available
If you switched your database to ARCHIVELOG mode, all previous backups are invalidated and thus cannot be used for recovery; therefore, you have to do the full backup of the database immediately after switching it to the ARCHIVELOG mode, to make archive redo logs useful.

If you have a full backup(s) of database made before failure and all archive logs, you can easily restore the data file(s) and archive logs from the backup(s) and recover the database or data file(s) to a point of failure applying restored archive logs to the restored data file(s).

If you however don't have backups of database and archive logs made after switching, then, generally, you're out of luck. Though as @Mat pointed out, sometimes you can give the survived bits of data (archive logs and control file) a go at recovering your lost data file.

What can be useful for you is Data Recovery Advisor (DRA, read "Data Recovery Advisor" section in Database Concepts for more info). It can help you to recover database, and is specifically designed for situations like yours when you're not sure how to recover the database or have the time restrictions for the system downtime. It can well save you a lot of time during recovery, reducing downtime caused by failures.

To determine if you can do something about your lost data file, start RMAN:

$ rman target=/


List failures captured by Oracle:

RMAN&gt; list failure;


(The information about failures listed is collected and stored in Automatic Diagnostic Repository (ADR), and is available even when the database is down. Read "Fault Diagnosability Infrastructure Overview" section in Database Administrator's Guide for more info.)

If some open failures are returned (they will actually be returned in your case since you lost one of data files), ask the DRA about advice:

RMAN&gt; advise failure;


Read the output of the last command thoroughly and implement the recommended or required manual actions (if there are). If there are no manual actions listed, and the DRA prepared the repair scirpt, ask the DRA to repair the failures:

RMAN&gt; repair failure;


The DRA is also available from Software and Support tab in EM Database Control.