Download Oracle 11g / 12 c Software

MySQL Tutorial

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$ sqlplusSQL> 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