Assume we have lost a datafile '/u03/oracle/test/users01.dbf' because the disk mounted on /u03 is corrupt.

Now we have a database with all datafiles intact in '/u02' mount point except one datafile located in '/u03' mount point is not available.

We have RMAN backups and can recover this file completely through RMAN, but since the original location of the datafile is not available and it takes a day or two to attach a new disk and format it.

In this kind of situation we can minimize the downtime by restoring the file to another location and then recover the datafile.

Let's see how to do it using RMAN

Case Study

Recover from the loss of a datafile by changing location.

Assuming the following

• We lost a particular datafile say '/u03/oracle/test/users01.dbf'
• All other datafiles are OK
• Log files are OK
• Control Files are OK
• Database is running in archive log mode
• We were taking backups of this database using RMAN
• The original location of the lost datafile '/u03' is not available (because disk is corrupt)

Solution

To restore the file to a different location and  to recover it through RMAN do the following

Step 1.

Start and mount the database by starting SQL Plus

$ export ORACLE_SID=test
$ sqlplus
SQL> startup

When we try to open, Oracle will show an error that a datafile is missing as shown below

From the above the above output we can see that '/u03/oracle/test/users01.dbf' datafile is not found

We have also found that the disk mounted on '/u03' is damage and doesn't get mount. Now we need to restore the file to a different location say '/u02' mount point and then perform complete recovery on it.

To achieve this please proceed to next step

Step 2

Exit from SQL Plus and start RMAN by typing the following command

$ rman target / nocatalog

 

Step 3

Now type the following script at RMAN prompt to recover the loss datafile

RMAN> run {
        set newname for datafile '/u03/oracle/test/users01.dbf' to
                '/u02/oracle/testdb/test/users01.dbf';
        restore datafile '/u03/oracle/test/users01.dbf';
        switch datafile '/u03/oracle/test/users01.dbf';
        recover datafile '/u02/oracle/testdb/test/users01.dbf';
}

 In the above script

set newname...

This command tells RMAN to change location while restoring file

restore datafile ....

Line will restore the datafile to new location.

switch datafile....

Command in this line changes the location of the datafile from '/u03..' to '/u02..' in control file

recover datafile ....

Line will apply transactions from archive files to perform complete recovery

The output is shown below when we execute the above script

Step 4

We can now go to SQL Plus and open the database and check status of the datafile