Oracle 9i Recovery (Datafile Recovery)

2) DATAFILE RECOVERY

Recover Datafile is performed either from the MOUNT state (after a shutdown), with the datafile ONLINE or OFFLINE, or from the OPEN state, with the datafile OFFLINE. A bad datafile must be taken offline before the database can be opened.

The basic steps for Recover Datafile from the OPEN state (except for SYSTEM):

c:\> sqlplus "/ as sysdba"
SQL> alter database datafile '/u03/oradata/PROD/devl_PROD_01.dbf' offline;
SQL> alter database open;
The database is now available to the users, except for that datafile.
Copy the datafile from backup.
SQL> Copy /u03/oradata/prod1/devl_PROD_01.dbf /u03/oradata/PROD
SQL> Recover automatic datafile '/u03/oradata/PROD/devl_PROD_01.dbf';
SQL> alter database datafile '/u03/oradata/PROD/devl_PROD_01.dbf' online;

The basic steps for the MOUNT state are:

C:\> copy /u03/oradata/prod1/devl_PROD_01.dbf /u03/oradata/PROD
C:\> sqlplus "/ as sysdba"
SQL> startup mount
SQL> recover automatic datafile '/u03/oradata/PROD/devl_PROD_01.dbf';
SQL> select * from v$datafile;
SQL> alter database datafile '/u03/oradata/PROD/devl_PROD_01.dbf' online;
SQL> alter database open;