How to transfer files from ASM to another ASM or filesystem or DBFS…

I had a requirement of transferring files from our PROD ASM to our UAT ASM as DBFS is proving to be slow.

The problem:

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 solution:

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);

Where:

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 Alejo-Marcos.

David Marcos Consulting Ltd.