Archivelog mode and datafile recovery (non-SYSTEM tablespace)
From Oracle FAQ
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.