How to move standby database datafile to other location

Below are the steps to move Standby database datafiles to other location:

1) Login to the standby database. Export ORACLE_SID.

2)    Check the current settings of standby file management parameter:

Show parameter standby if it set to auto we need to set it to manual

SQL> alter system set standby_file_management=manual scope=both

3) Stop MRP process using below command:

SQL>alter database recover managed standby database cancel;

        Check if the MRP has stopped or not using below command:

        SQL>select process,status,sequence# from v$managed_standby;


4) Connect  to the RMAN on linux prompt.
rman target /

5) Copy datafile to the respective location which has enough free space. See below example:

        RMAN> COPY DATAFILE  '+DG12/test/datafile/test.123.353897'  TO  '+DG03';

6) Rename datafile using below command:

        SQL>ALTER DATABASE RENAME FILE '+DG12/test/datafile/test.123.353897'  TO              '+DG03';


7) Use RMAN to switch to file which we copied in step 5 using below command:

        RMAN>SWITCH DATAFILE '+DG12/test/datafile/test.123.353897'  TO COPY;

        you can also use datafile no to do this

        RMAN>SWITCH DATAFILE 20  TO COPY;

8) Start managed recovery process using below command:

        SQL>alter database recover managed standby database using current logfile disconnect from       session;

9) Verify standby is working fine by switching log files on primary database:
         SQl>alter system switch logfile;

10) As soon as you switched to copy old file gets deleted automatically in ASM. Verify file has       deleted or not.


11)   Make stand by file management auto using below command:

        SQL>alter system set standby_file_management=auto scope=memory both