Wednesday, January 5, 2011

ASM to ASM file transfer

DBMS_FILE_TRANSFER package which was introduced in 10g could be used to transfer files from one ASM disk to another without an intermediary local file.
There are two methods for this, get_file and put_file. get_file could be used to copy a file from a remote location to the local server ASM and put_file would copy a local file to a remote ASM location.


1. Create a file in ASM. In this case a dumpfile was created in the ASM disk. This will be the remote server. The file created here will be requested by the local server demonstrating get_file operation. For example a ASM directory was created on remote server with
and a directory object created with
SQL> create directory remoteasmdir as '+FLASH/RAC11G1/REMOTEASM';
Directory created.
Grant the user (that's on the remote node) that will be used to create the database link (step 4) read,write access on this directory object
SQL> grant read,write on directory remoteasmdir to scott;
2. On the local database server connect to the ASM instance using asmcmd and create a destination directory for the file.
. oraenv
ORACLE_SID = [rac11g21] ? +ASM1
The Oracle base for ORACLE_HOME=/opt/app/11.2.0/grid is /opt/app/oracle
$ asmcmd

It's better to create the directories in uppercase as procedure converts directory object parameters to uppercase unless they are surrounded by double quotation marks.

3. Create the directory object and grant the user (on the local node) with the read,write privileges on that directory.
SQL> create directory destasmdir as '+DATA/RAC11G2/DESTASM';
Directory created.

SQL> grant read,write on directory destasmdir to scott;
Grant succeeded.
4. Create a TNS entry on the local database tnsnames.ora file for the remote database and create a database link using the TNS entry. The user used in the database link must have read/write permission on the remote servers directory (created on step 1)
   (ADDRESS = (PROTOCOL = TCP)(HOST = remote-loc)(PORT = 1521))
     (SID = remote-db)

SQL> create database link remoteloc connect to scott identified by tiger using 'REMOTELOC'
5. Execute the get_file function.
SQL> exec dbms_file_transfer.get_file('REMOTEASMDIR','SCOTT.DMP','REMOTELOC','DESTASMDIR','destasm.dmp');
Here REMOTEASMDIR is the remote directory object where the source file resides.
SCOTT.DMP is the file name. REMOTELOC is the name of the database link created above.
DESTASMDIR is the directory object on the local server where file will be stored once the transfer is completed. Last parameter is the destination file name which could be different from the source file name.

Only an alias name is created on the location specified on the procedure. Real file resides in a seperate location with a generated file name.
Type Redund Striped Time Sys Name
N destasm.dmp => +DATA/RAC11G2/DUMPSET/FILE_TRANSFER_0_0.276.739638379
To monitor the progress of a long file copy, query the V$SESSION_LONGOPS dynamic performance view. The size of the copied file must be a multiple of 512 bytes and the copied file must be less than or equal to two terabytes.


put_file allows to copy a file from local server to a remote server. The file that was copied using get_file will be copied back to the remote server under a different name. This procedure also uses the same database link and directory objects.
1.In put_file scenario DESTASM is the directory object name where the file resides
destasm.dmp is the name of the file that will be put into the remote server
ASMDUMPDIR is the direcotry object name on the remote server where file will be kept once copied
newasm.dmp is the name of the file at the remote location and
REMOTELOC is the database link to the remote database server.
exec dbms_file_transfer.put_file('DESTASMDIR','destasm.dmp','REMOTEASMDIR','newasm.dmp','REMOTELOC');