Archivelog mode and datafile recovery (non-SYSTEM tablespace)

From Oracle FAQ
Jump to: navigation, search

If you are running your database in Archive Log mode, and you loose a datafile (maybe due to user errors or disk failure), follow these steps:

  • If the datafile belongs to optional tablespaces like tools, users, etc. and doesn't contain any meaningful data, you can simply drop the tablespace including contents and recreate your tablespace.
  • If the datafile holds key organizational data, you need to recover the data.
  • If you database was up and running when this took place, do a shutdown abort.
SQL> shutdown abort;
  • Mount the database in restricted mode.
SQL> startup restrict mount;
  • Check the list of files for which media recovery will be needed.
SQL> select * from v$recovery_log;
  • Copy an intact copy of the datafile from secondary storage (backup media).
  • Copy all the archive log files to the required destination.
  • Recover the database:
SQL> recover database;
  • In case of complete recovery:
SQL> alter database open;
  • In case of a partial recovery:
SQL> alter database open resetlogs;
  • Take a backup immediately.