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;
