Follow these 3 Steps
To restore and recover damaged or missing data files:
Step 1: If the database is open, query V$RECOVER_FILE to determine which data files must be recovered and why they must be recovered.
If you are planning to perform complete recovery rather than point-in-time recovery, then you can recover only those data files that require recovery, rather than the whole database. For point-in-time recovery, you must restore and recover all data files, unless you perform TSPITR .
You can also use Flashback Database, but this procedure affects all data files and returns the entire database to a past time.
You can query the V$RECOVER_FILE view to list data files requiring recovery by data file number with their status and error information.
SELECT FILE#, ERROR, ONLINE_STATUS, CHANGE#, TIME
FROM V$RECOVER_FILE;
Warning: You cannot use V$RECOVER_FILE with a control file restored from backup or a control file that was re-created after the time of the media failure affecting the data files. A restored or re-created control file does not contain the information needed to update V$RECOVER_FILE accurately.
You can also perform useful joins by using the data file number and the V$DATAFILE and V$TABLESPACE views to get the data file and tablespace names. Use the following SQL*Plus commands to format the output of the query:
COL DF# FORMAT 999
COL DF_NAME FORMAT A35
COL TBSP_NAME FORMAT A7
COL STATUS FORMAT A7
COL ERROR FORMAT A10
COL CHANGE# FORMAT 99999999
SELECT r.FILE# AS df#, d.NAME AS df_name, t.NAME AS tbsp_name,
d.STATUS, r.ERROR, r.CHANGE#, r.TIME
FROM V$RECOVER_FILE r, V$DATAFILE d, V$TABLESPACE t
WHERE t.TS# = d.TS#
AND d.FILE# = r.FILE#;
The ERROR column identifies the problem for each file requiring recovery.
Step 2:
Query the V$ARCHIVED_LOG and V$RECOVERY_LOG views to determine which archived redo log files are needed.
V$ARCHIVED_LOG lists file names for all archived redo logs, whereas V$RECOVERY_LOG lists only the archived redo logs that the database needs to perform media recovery. The latter view also includes the probable names of the files based on the naming convention specified by using the LOG_ARCHIVE_FORMAT parameter.
Warning: V$RECOVERY_LOG is only populated when media recovery is required for a data file. Thus, this view is not useful for a planned recovery, such as recovery from a user error. If a data file requires recovery, but no backup of the data file exists, then you need all redo generated starting from the time when the data file was added to the database.
Step 3:
If all archived logs are available in the default location, then continue.
If some archived logs must be restored, and if sufficient space is available, then restore the required archived redo log files to the location specified by LOG_ ARCHIVE_DEST_1.
The database locates the correct log automatically when required during media recovery. For example, you might enter a command such as the following on Linux or UNIX:
% cp /disk2/arch/* $ORACLE_HOME/oradata/trgt/arch
If sufficient space is not available, then restore some or all of the required archived redo log files to an alternative location.
