How to Recover Datafile From Standby DB

Goal

Problem Statement

How to recover the primary database's datafile using a copy of a standby database's datafile.

and

How to recover the standby database's datafile using a copy of a primary database's datafile.
Solution
Recovering the Primary's Datafile

How to recover the primary database's datafile using a copy of a standby database's datafile.

1) copy the standby database's datafile

2) ftp the file to the primary site

3) catalog the datafile copy on the primary site

4) on the primary site restore and recovery the datafile copy

This procedure will work for all file systems - cooked, raw or ASM.
Example

Through this example we will be using datafile 25.

1) On standby database, copy datafile from ASM to a cooked file system:

       9i:  RMAN> copy datafile 25 to '/tmp/df25.cpy';

       10g: RMAN> backup as copy datafile 25 format '/tmp/df25.cpy';


2) FTP the file to primary server on cooked system

On primary database
~~~~~~~~~~~~~~~~~~~
3) catalog this datafile copy:
       SQL> alter database datafile 25 offline;
       RMAN> catalog datafilecopy '/tmp/df25.cpy';

4) Confirm that datafile exists:
       RMAN> list copy of datafile 25;

5) Restore the datafile:
       RMAN> restore datafile 25;

6) Recover the datafile:
       RMAN> recover datafile 25;


recover datafile 7 until time '09-AUG-2010 14:00:00';

recover datafile 7 until time to_date('09-aug-2010 14:00:00','dd-mon-rrrr hh24:mi:ss')

7) Place the datafile online:
       SQL> alter database datafile 25 online;


Recovering the Standby's Datafile

If recovering the standby, reverse the steps. That is:

1) copy the primary database's datafile

2) ftp the file to the standby site

3) catalog the datafile copy on the standby site

4) stop Redo Apply on the Physical Standby Database

    SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

5) on the standby site restore and recovery the datafile copy