Performing Oracle User-Managed Database Recoveries - OCP: Oracle Database 12c Administrator Certified Professional - page 73

Missing Data Files
Don't expect that these errors indicating data files are missing will always show up in the
alert log. Sometimes they will (for example, on database startup), but often they won't
(for example, when a query fails because a data file is offline) If you want to monitor for
this problem reliably, then the V$RECOVER_FILE view is the way to go.
So, here is the general recovery process from such an error. In this case, we assume the
database is up and running:
1. Take the data file offline using the ALTER DATABASE DATAFILE OFFLINE command as
shown here:
alter database datafile 'C:\ORACLE\ORADATA\ORCL\USERS01.DBF' offline;
As an alternative, you can use FILE_ID as shown in this example:
alter database datafile 4 offline;
FILE_ID will appear in the error message, or you can use the FILE_ID column of DBA_
DATA_FILES or the FILE# column in the V$DATAFILE view.
2. Restore the missing data files.
3. Restore all archived redo logs that will be needed for recovery. This would be all archived
redo logs generated from the beginning of the backup image you restored in step 2.
When you are restoring backup files, never restore backed-up online redo
logs over the existing online redo logs. This is so important, in fact, that
when we talked about hot backups in Chapter 1, we did not even back up
the online redo logs. Restoring old online redo logs over your existing
ones will lead to data loss. Fair warning!
4. Recover the missing data files with the RECOVER DATAFILE or RECOVER TABLESPACE
command.
5. Bring the data files or the tablespace online with the ALTER DATABASE or ALTER
TABLESPACE command.
So, what do you do if your database was down and you discover the files are lost when
you start it up? That's simple too:
1. Log in as SYS and start up the database.
If a data file is missing, you will get an error message that looks something like this:
SQL> startup
ORACLE instance started.