Restoring datafiles
The restore process for datafiles has three basic steps: determining which datafiles need recovery, copying the relevant backup files, and then restoring the database to a workable state.
Determining the files to recover.
If your control file has not been corrupted by the media failure, you can run the following command in SQL*Plus to obtain a list of the datafiles that need recovery:
SELECT * FROM V$RECOVER_FILE;
This query will return a set of numbers that indicates the files needing recovery.
To determine which datafiles and tablespaces these file numbers represent, run the following command:
SELECT d.NAME, t.NAME AS Tablespace_Name
FROM V$DATAFILE d, V$TABLESPACE
WHERE d.TS# = t.TS# AND
d.FILE# IN (filenumbers);where filenumbers is a comma-separated list of the file numbers returned from the previous query.
Copying backup files.
Once you have determined the datafiles that need recovery, you need to copy the backups of these files. To accomplish this, take the tablespace of the damaged datafile offline and copy the backup to the appropriate location.
If the default location is no longer available as a result of the media failure, you will have to modify the control file to recognize these new locations.
Restoring the database.
Once the backup copy of the datafile is in place, you must restore the tablespace that contains it. You can restore the database using SQL*Plus by following these steps:
Ensure that the database has been shut down:
SQL>
SHUTDOWN IMMEDIATE;or
SQL> ...