ASM Datafile Copy

How to copy ASM datafile from primary database to standby database on ASM using RMAN

There are two options to achieve the same solution. Assume in this scenario file # 15 on Standby has corruption or is reported NO LOGGING operation error.
Location of file # 15 is in Primary database at ‘+ps90hr_disk3/ps90hrpd/datafile/users.xxx.xxxxxx’ and that standby and primary databases are on two separate servers:

Option 1:
On the primary database server take a copy of the file and put it in a flat filesystem:

rman> copy datafile ‘+ps90hr_disk3/ps90hrpd/datafile/users.xxx.xxxxxx’ to ‘/tmp/users01.dbf’;

FTP or copy the datafile to the standby server at /tmp/users01.dbf.

On the Standby Catalog this copy using RMAN.

rman> catalog datafilecopy ‘/tmp/users01.dbf’;

Switch the datafile to point to copy on the standby and stop the recovery.

sql> alter database recover managed standby database cancel;

rman> switch datafile to copy; – (ex switch datafile 15 to COPY;)

Now we copy this datafile to the ASM disk group on the standby server.

rman> backup as copy datafile format ”;

Switch to point the Backup copy created in ASM disk group.

rman> switch datafile to copy;

example: rman> switch datafile 15 to COPY;

Start the recovery and you’re done.

sql> alter database recover managed standby database disconnect from session;

Option 2:

On the Primary Database:

1. take a copy of the file and put it on a flat filesystem as in Option 1.

rman> copy datafile ‘+ps90hr_disk3/ps90hrpd/datafile/users.xxx.xxxxxx’ to ‘/tmp/users01.dbf’;

FTP or copy the file users01.dbf from the primary to the standby server and put it in the same locatiion.

2. On the Standby Server:

sql> shutdown immediate;
sql> startup mount; (note we are not starting the recovery)

Please note that if dataguard broker is running on the stanby server then it might start the recovery automatically at step 2 so you would need to stop it if that happens)

3. select name from v$datafile where file# = ;

This will return the location of the file that is to be restored.

4. Catalog the datafilecopy on the Standby server using RMAN:

rman> catalog datafilecopy ‘/tmp/users01.dbf’;

5. Connect to the target and copy the datafile using RMAN:

rman> connect target
rman> copy datafile ‘/tmp/users01.dbf’ to ‘+ps90hr_disk3′;

This will report the new location/name of the original file #.

6. Using SQL*Plus on the Standby server:

sql> select name from v$datafile where file# = 15;

This will show the original name/location of the datafile. We would need to rename this file to the new filename show above from RMAN. However, we need to set standby_file_management=manual temporarily for this operation:

sql> alter system set standby_file_management=manual scope=spfile;
sql> alter database rename file ‘ to ‘;

example:

alter database rename file ‘+ps90hr_disk3/ps90hrpd/users.xxx.xxxxxx’ to ‘+ps90hr_disk3/ps90hrpd/datafile/users.xxx.xxxxxx’;

sql> alter system set standby_file_management = auto scope=spfile;