Basic Datafile Recovery Option

The Recover Datafile option is used to recover the specified datafile up to the point of failure, synchronizing it with the other datafiles (complete recovery only). 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. Since the SYSTEM tablespace CANNOT be taken offline, you cannot use Recover Datafile from the OPEN state on SYSTEM.

The basic steps for the MOUNT state are: restore the datafile, mount, recover datafile (using automatic here instead of autorecovery), online the datafile if needed, and open, as shown below:

$ cp -p /u03/oradata/prod1/devl_PROD_01.dbf /u03/oradata/PROD
$ 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;

The basic steps for Recover Datafile from the OPEN state (except for SYSTEM) while the rest of the database is still up and running, are: offline the datafile, restore the datafile, recover datafile, and online the datafile, as shown below:

$ sqlplus “/ as sysdba”
SQL> alter database datafile ‘/u03/oradata/PROD/devl_PROD_01.dbf’ offline;
SQL> !cp -p /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;

Doing a Recover Datafile from the OPEN state (except for SYSTEM) can also be used to bring up your database for use before doing a recovery. The basic steps are: mount, offline the datafile, open, restore the datafile, recover datafile, and online the datafile, as shown below:

$ sqlplus “/ as sysdba”
SQL> startup mount
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.
SQL> !cp -p /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;

You can still access data in tables in a tablespace which has an offlined datafile, just so long as the data is in one of the other datafiles of the tablespace and the table header is not in the offlined datafile.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: