Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

How do you recover a lost datafile if:

  • the database is running in archivelog mode
  • there is no backup is available
share|improve this question

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> 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> 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> repair failure;

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

share|improve this answer

It is possible to recover a lost or corrupt datafile when your database is in archivelog mode, with two restrictions listed in the Re-Creating Data Files When Backups Are Unavailable docs:

  • All archived log files written after the creation of the original data file are available
  • The control file contains the name of the damaged file (that is, the control file is current, or is a backup taken after the damaged data file was added to the database)

Note that you cannot recover a lost or corrupt SYSTEM datafiles this way.

The linked documentation explains how to recover if you meet these criteria. Essentially:

  1. You re-create a datafile to replace the lost one - this requires information from the control files, reason for the second point above.
  2. You recover the datafile from the archived redo logs. This requires all the redo logs since the datafile was created, reason for the first point (and quite logical: there's nowhere else that data could be recovered from if you don't have backups).
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.