How to transfer files from ASM to another ASM or filesystem or DBFS… 23 July 2011Posted by David Alejo Marcos in ASM, Exadata, Oracle 11.2.
Tags: ASM, Exadata, Oracle 11.2
I had a requirement of transferring files from our PROD ASM to our UAT ASM as DBFS is proving to be slow.
We are currently refreshing UAT schemas using Oracle Datapump to DBFS and then transferring those files to UAT using SCP.
DBFS does not provided us with the performance we need as datapump files are quite big. Same export onto ASM or NFS proves to be much, much faster.
We are currently testing exports to ASM, but, how to move dmp files from PROD ASM to UAT ASM?
The answer for us is using DBMS_FILE_TRANSFER. It is very simple to set up (steps below) and it has proved to be fast.
DBMS_FILE_TRANSFER will copy files from one ORACLE DIRECTORY to another ORACLE DIRECTORY. The directory can point to a folder on ASM, DBFS, Filesystem, etc, so the transfer is “heterogeneous”.
I decided to go for GET_FILE, so most of the work will be done on UAT, the other option is PUT_FILE.
The syntax is as follows:
DBMS_FILE_TRANSFER.GET_FILE ( source_directory_object IN VARCHAR2, source_file_name IN VARCHAR2, source_database IN VARCHAR2, destination_directory_object IN VARCHAR2, destination_file_name IN VARCHAR2);
source_directory_object: The directory object from which the file is copied at the source site. This directory object must exist at the source site.
source_file_name: The name of the file that is copied in the remote file system. This file must exist in the remote file system in the directory associated with the source directory object.
source_database: The name of a database link to the remote database where the file is located.
destination_directory_object: The directory object into which the file is placed at the destination site. This directory object must exist in the local file system.
destination_file_name: The name of the file copied to the local file system. A file with the same name must not exist in the destination directory in the local file system.
These are the steps for my test:
1.- Create directory on destination:
oracle@sssss (+ASM1)$ asmcmd ASMCMD> mkdir +DATA/DPUMP ASMCMD> mkdir +DATA/DPUMP/sid ASMCMD> exit
2.- Create directory on database
SQL> create directory SID_ASM_DPUMP_DIR as '+DATA/DPUMP/sid'; Directory created. SQL> grant read, write, execute on directory SID_ASM_DPUMP_DIR to public; Grant succeeded.
3.- Transfer file
SQL> set timing on time on 17:22:13 SQL> BEGIN 17:22:17 2 dbms_file_transfer.get_file ('SID_ASM_DPUMP_DIR', 17:22:17 3 'expdp.dmp', 17:22:17 4 'TESTSRC', 17:22:17 5 'SID_ASM_DPUMP_DIR', 17:22:17 6 'expdp.dmp'); 17:22:17 7 END; 17:22:17 8 17:22:17 9 / PL/SQL procedure successfully completed. Elapsed: 00:01:07.57 17:23:31 SQL>
4.- Check files is on destination:
ASMCMD [+DATA/DPUMP/sid] > ls -ls Type Redund Striped Time Sys Name N expdp.dmp => +DATA/sid/DUMPSET/FILE_TRANSFER_0_0.797.756840143
OK, so ls -ls does not work show us the size of the file on the destination directory. The reason is because it is an alias, you need to perform ls -s on the directory where the file is stored.
ASMCMD [+DATA/DPUMP/sid] > ls -ls +DATA/sid/DUMPSET/ Type Redund Striped Time Sys Block_Size Blocks Bytes Space Name DUMPSET MIRROR COARSE JUL 18 17:00:00 Y 4096 1784576 7309623296 14633926656 FILE_TRANSFER_0_0.797.756840143
So, we have managed to transfer 6.8GB of data between two remote servers in 1 minute 8 seconds… not bad.
As always, comments are welcome.
David Marcos Consulting Ltd.