Archivelog mode and datafile recovery (non-SYSTEM tablespace)

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.