Emre Baransel - Oracle Blog

Why do we need to transfer Oracle files between databases? There may be lots of cases depending on your imagination, and my case here is sending archivelogs in a dataguard configuration. Dataguard ships the archivelog files automatically, even if the standby database is read-write (Snapshot Standby Feature) in 11g. But in 10gR2, if you’re “Using a Physical Standby Database for Read/Write Testing and Reporting”, archivelog shipping stops while the physical standby database in Read-Write. What i want to do is adding an 11g feature to 10g and ship the archivelogs from primary to standby via a script.
What options do we have for this purpose?


DBMS_FILE_TRANSFER

DBMS_FILE_TRANSFER package provides three procedures to move Oracle files. COPY_FILE procedure moves files between directories within a database. However GET_FILE and PUT_FILE procedures contacts to a remote database and transfer files from or to remote database.
Note: When transferring files in use, you should bring them offline to guarantee consistency.
Let’s see the usage of PUT_FILE procedure:



Syntax:

DBMS_FILE_TRANSFER.PUT_FILE(
source_directory_object IN VARCHAR2,
source_file_name IN VARCHAR2,
destination_directory_object IN VARCHAR2,
destination_file_name IN VARCHAR2,
database_link_name IN VARCHAR2);

Don’t forget to grant READ on the source directory to source database user and grant WRITE on destination directory to destination database user.

Create the directory on the source database:

CREATE OR REPLACE DIRECTORY SOURCEDIR AS '+FRA/SID/ARCHIVELOG' ;
GRANT READ ON DIRECTORY SOURCEDIR TO "SOURCEUSER";

Create the directory on the destination database:

CREATE OR REPLACE DIRECTORY DESTDIR AS '+FRA/SID/ARCHIVELOG ' ;
GRANT WRITE ON DIRECTORY DESTDIR TO "DESTUSER";

Create the Database Link on the source database:

CREATE DATABASE LINK "TESTLINK " CONNECT TO DESTUSER IDENTIFIED BY PASSWORD USING 'INST2';

Start file transfer:

CONNECT sourceuser/password@inst1
BEGIN
SYS.DBMS_FILE_TRANSFER.PUT_FILE ( ' SOURCEDIR' , 'source_file_name' , ' DESTDIR ' , 'destination_file_name','TESTLINK' ) ;
END ;

In my dataguard case I didn’t need to create directory on the destination (standby) database. After creating the source directory on the primary side, I used the same directory name for the destination (of course after the archivelog was applied on standby). I executed the procedure as follows:

BEGIN
SYS.DBMS_FILE_TRANSFER.PUT_FILE ( 'ARCHDIR' , 'thread_1_seq_204982.1897.709557909' , ' ARCHDIR ' , 'thread_1_seq_204982','TESTLINK' ) ;
END ;

When you use the destination file name same with the source file name in an OMF environment you get the following error, so you must use a different destination file name.

ORA-19504: failed to create file "+FRA/…/thread_1_seq_204982.1897.709557909"
ORA-17502: ksfdcre:4 Failed to create file +FRA/…/thread_1_seq_204982.1897.709557909
ORA-15046: ASM file name '+FRA/… /thread_1_seq_204982.1897.709557909' is not in single-file creation form

FTP proxy Method
Another option for file transfer in ASM is FTP proxy Method. You must have Oracle XML DB installed for this option. XML DB uses the virtual folder /sys/asm to access ASM files. An example usage of this future is:

ftp> open server1 port1
ftp> user username1
Password required for USERNAME1
Password: password-for-username1
ftp> cd /sys/asm/FRA/SID/ARCHIVELOG
ftp> proxy open server2 port2
ftp> proxy user username2
Password required for USERNAME2
Password: password-for-username2
ftp> proxy cd /sys/asm/FRA/SID/ARCHIVELOG
ftp> proxy put thread_1_seq_204982.1897.709557909
ftp> proxy get thread_1_seq_204982.1897.709557909

Refer to Using FTP and Oracle XML DB Protocol Server and Commanding ASM By Arup Nanda for more information.

11g ASM cp Command
Next option is ASM cp command which is an new feature of 11g. (Not suitable for my 10g case)
Syntax for remote ASM copy is:

ASMCMD> cp [srcfile] username@hostname.SID.port:path

username can be any username in the ASM instance that has the system privilege. Port number is required if the listener doesn’t listen from default port 1521. Also don’t forget that ASM instance needs to be registered with the listener.

For example:

ASMCMD> cp +FRA/SID/ARCHIVELOG/thread_1_seq_204982.1897.709557909
sys@ORCL.+ASM: +FRA/SID/ARCHIVELOG/thread_1_seq_204982

Find more information about ASM cp command here.

RMAN Convert
Last option is RMAN but unfortunately not directly to ASM. You can copy an ASM file to file system, ftp the converted file to remote host then convert again into remote ASM. I’m not sure if anyone chose this way where we have simpler options, but it’s good to know this capability of RMAN.

Here is how we use RMAN Convert command to copy files between ASM and file system.:

RMAN> convert datafile "+DATAFILE/tbs_21.f" format "/tmp/conv_df_%U";