How to Copy ASM datafile From Primary Database to Standby Database on ASM using RMAN

How to Copy ASM datafile From Primary Database to Standby Database on ASM using RMAN

Requirement is to copy one datafile (file#20)from primary to standby.

We encountered NO LOGGING/Corruption errors in standby database and want to copy the current/good backup of datafile in Primary which is on ASM to Standby database which is on ASM
There are three options to achieve the same.

Option 1

On Primary database :-

Step 1:- Take an copy of the file to normal file system

RMAN > Copy datafile ''  to '' ;

for example

RMAN>copy datafile '+DATA/orcl/datafile/users.20.658960000'  to '/u01/stage/tmp/backup_file_20.dbf'

transfer the file backup_file_20.dbf to standby

use Ftp or copy (scp) to transfer the datafile  backup_file_20.dbf  to standby server at
/u01/stage/tmp/backup_file_20.dbf

On Standby database :-

Step 2:- On Standby Catalog this copy using Rman

RMAN> Catalog datafilecopy '/u01/stage/tmp/backup_file_20.dbf' ;

Step 3:- Switch the datafile to point copy on standby

Stop the recovery

SQL> Alter database recover managed standby database cancel ;

RMAN > switch datafile to COPY;

For example :-

RMAN> Switch datafile 20 to COPY;
datafile 20 switched to datafile copy "/u01/stage/tmp/backup_file_20.dbf"

Step 4 :- Now we copy this to ASM disk group on standby

RMAN> Backup as copy datafile   format  '';

For example :-

Rman> Backup as copy datafile 20 format '+DATA' ;
.....................................
.....................................
output filename=+DATA/orcl/datafile/users.20.658960000 tag=TAG20080701T174316 r ecid=20 stamp=658953191

Step 5 :- Switch to point Backup copy created in ASM disk group

RMAN>switch datafile  to COPY;

For example :-

Rman>Switch datafile 20 to Copy ;


Option 2

On Primary database :-

Step 1:- Take an copy of the file to normal file system


RMAN>copy datafile '+DATA/orcl/datafile/users.20.658960000'  to '/u01/stage/tmp/backup_file_20.dbf'

transfer the file backup_file_20.dbf to standby

Ftp or copy (scop) the datafile backup_file.dbf  to standby server at

/u01/stage/tmp/backup_file_20.dbf

On Standby database :-

Step 2

SQL> Shutdown immediate;

SQL> startup mount

Ensure recovery is stopped. Ensure no MRP process is listed in below query. If recovery is in progress stop it

SQL> select PROCESS,STATUS from v$managed_standby
  2  ;
PROCESS                     STATUS
--------------------------- --------------------
ARCH                        CLOSING
ARCH                        CLOSING
ARCH                        CONNECTED
ARCH                        CLOSING
RFS                         IDLE
RFS                         IDLE
RFS                         IDLE
RFS                         IDLE
RFS                         IDLE
RFS                         IDLE
10 rows selected.

 to stop recovery issue below command on standby

SQL> recover managed standby database cancel;

Step 3

notedown the file location

SQL> Select name from v$datafile where file#=20

+DATA/orcl/datafile/users.20.658933000

Step 4

Catalog datafilecopy on Standby using Rman

RMAN > Catalog datafilecopy '/u01/stage/tmp/backup_file_20.dbf' ;

Step 5
Rman > Connect target

RMAN> copy datafilecopy '/u01/stage/tmp/backup_file_20.dbf'  to '+DATA'
.....................................
.....................................
output filename=+DATA/orcl/datafile/users.20.658960000 tag=TAG20080701T174316 r ecid=20 stamp=658953191 <== note down this name
This will report the new location/name of the original file  20.
In this example  its +DATA/orcl/datafile/users.20.658960000

Step 6

Go to sqlplus on standby database

SQL> Select name from v$datafile where file#=20

For example

SQL> Select name from v$datafile where file#=20
'+DATA/orcl/datafile/users.20.658933000'

This will show the original name/location of the datafile. We would need to rename this to the new file
name showen from above rman command to update the information in controlfile about this new datafile
We would issue rename command to point to the new location of the file.

SQL>Alter system set standby_file_management=manual scope=spfile ;

SQL> Alter database rename  file '' to 'reported in step5 after copy datafile command;'

For example

SQL> Alter database rename file '+DATA/orcl/datafile/users.20.658933000' to
'+DATA/orcl/datafile/users.20.658960000';

SQL> Alter system set standby_file_management=auto scope=spfile ;

After the activity is over, remove the transient copy i.e.

RMAN> delete datafilecopy '/u01/stage/tmp/backup_file_20.dbf' ;

Option 3

On Primary database :-

Step1

backup affected datafile (in my case its file#20)

RMAN> BACKUP DATAFILE 20 FORMAT '/u01/stage/tmp/backup_file_20_%U';

Transfer the backup piece to standby

On Standby database :-

Step2

RMAN> catalog start with '/u01/stage/tmp/' noprompt;

Step3

SQL> recover managed standby database cancel;

Step4

RMAN> restore datafile 20;

Step5

SQL> recover managed standby disconnect from session;

The options assume that you have enough archivelogs availiable to recover the datafile to the current point in time
If you have space crunch and you are running 12cR2 and all the above 3 steps does not fit for you then you can check my post