Oracle recommends the use of RMAN to create a physical standby database. But in the manual and various MAA white papers they simply state that you should make the backups available to the target system and that the path to the backup files on the source and standby server must be the same.
Unfortunately they do not recommend how you should do this, especially when you have a FRA configured in ASM.
With the release of version 11 ASM Oracle introduced the ASMCMD cp command. I thought that solves the problem since the white paper (Oracle Database 11G Automatic Storage Management New Features Overview – published June 2007) states “The ASMCMD cp command allows you to copy files between ASM disk groups between 2 ASM servers”, but I could not get the command to copy my RMAN backupsets between ASM disk groups. I eventually logged SR 7173090.993 but even Oracle support was not sure and they wasted 2 days trying to make it work. Only after 2 days, did Oracle support say that the command does not work for copying backupsets between ASM disk groups (only data files).
OK what now?
I had a look around and decided that my only option is the cumbersome (but at least workable) DBMS_FILE_TRANSFER package.
First step is to locate the rman backupsets on the source server:
ASMCMD> pwd
+PCASDGF/CASOB1/BACKUPSET/2009_03_27
Now create the same directory structure on the remote server. On the standby server I first created an ASM Diskgroup with the same name as the FRA on production (+PCASDGF).
ASMCMD> mkdir +PCASDGF/CASOB1
ASMCMD> mkdir +PCASDGF/CASOB1/BACKUPSET
ASMCMD> mkdir +PCASDGF/CASOB1/BACKUPSET/2009_03_27
Use DBMS_FILE_TRANSFER to instantiate an ASM DataGuard database
DBMS_FILE_TRANSFER.GET_FILE contacts a remote database to read a remote file and then creates a copy of the file in the local file system. But before I can use this method I need a "stage" instance on the standby server. (I used DBCA to create a stage database - it took 20 minutes to create)
Prepare the newly created stage database to use DBMS_FILE_TRANSFER:
CONNECT sys/XXX
CREATE OR REPLACE DIRECTORY DEST_DIR AS '+PCASDGF/CASOB1/BACKUPSET/2009_03_27'
CREATE DATABASE LINK SOURCE_DB CONNECT TO system IDENTIFIED BY XXX USING 'CASOB1.VODACOM.CO.ZA';
On Primary:
CREATE OR REPLACE DIRECTORY SOURCE_DIR AS '+PCASDGF/CASOB1/BACKUPSET/2009_03_27';
Get a list of the backupsets on the source server:
ASMCMD> pwd
+PCASDGF/CASOB1/BACKUPSET/2009_03_27
ASMCMD> ls
annnf0_TAG20090327T115944_0.417.682603193
annnf0_TAG20090327T115944_0.418.682603193
...
Use an editor to create a SQL command (one per backupset) which looks like this:
execute dbms_file_transfer.get_file(source_directory_object => 'SOURCE_DIR',source_file_name => 'ncsdf0_TAG20090406T092911_0.1
367.683458159', destination_directory_object => 'DEST_DIR',destination_file_name => 'BACKUP1',source_database => 'SOURCE_DB');
execute dbms_file_transfer.get_file(source_directory_object => 'SOURCE_DIR',source_file_name => 'nnndf0_TAG20090406T092911_0.1
364.683458159', destination_directory_object => 'DEST_DIR',destination_file_name => 'BACKUP2',source_database => 'SOURCE_DB');
…
Note that the destination_file_name is not the same as the source file name. You will get an ORA-15046 if they are the same, this error is the result of the backupset being an Oracle-Managed file name.
So I execute these commands on the standby server from the stage instance. The progress of the command can be monitored using the V$SESSION_LONGOPS view. Once all the backupsets are copied over I was done with the stage database.
The next step is to create aliases for the new names of the backuppieces, so back on the source server issue:
ALTER DISKGROUP PCASDGF ADD ALIAS '+PCASDGF/CASOB1/BACKUPSET/2009_03_27/BACKUP1' for '+PCASDGF/CASOB1/BACKUPSET/2009_03_27/annnf0_TAG20090327T115944_0.417.682603193';
…
Once done, I can query V$ASM_ALIAS and there are two entries per backuppiece. One for the backuppiece and one for the alias.
The aim of doing this was to create a Data Guard instance, so before I can restore the backup I first have to prepare a Data Guard instance:
Log onto the standby server
# $export ORACLE_SID=casobc
# $orapwd=orapwcasobc password=XXX
sqlplus
SYS@casobc>create spfile from pfile; (I previously created a pfile from production with a few changes necessary for DG)
File created.
SYS@casobc>startup force nomount;
ORACLE instance started.
OK now I am ready to start the restore. First catalog the backuppieces in the source server/primary RMAN
Connect to RMAN on the primary server
RMAN> connect target sys/XXX@casob1.vodacom.co.za;
connected to target database: CASOB1 (DBID=1215042422)
RMAN> connect auxiliary /
connected to auxiliary database: CASOB1 (not mounted)
RMAN> catalog backuppiece '+PCASDGF/ CASOB1/BACKUPSET/2009_03_27/BACKUP1';
RMAN> catalog backuppiece '+PCASDGF/CASOB1/BACKUPSET/2009_03_27/BACKUP2';
…
Once done, I could list the backup in RMAN and see two entries, one for the backuppiece and one for the alias.
Eventually I am ready to use the RMAN backup to create a data guard instance.
RMAN> duplicate target database for standby ;
...
RMAN> exit
