Transfer Oracle ASM binary Files One File System to another File System Using DBMS_FILE_TRANSFER

advertisements

_____________________________________________________________________________________________________________________

There are 3 procedures with DBMS_FILE_TRANSFER package to help this file transfer from one ASM file system to another file system.

1. COPY_FILE

This procedure copies the file from source directory object to destination directory object.
The file system can be either ASM disk group or normal file system.

Syntax:

Exec DBMS_FILE_TRANSFER.COPY_FILE(

   source_directory_object       IN  VARCHAR2,

   source_file_name              IN  VARCHAR2,

   destination_directory_object  IN  VARCHAR2,

   destination_file_name         IN  VARCHAR2);

Example:

SQL> create directory sou_dir_obj as '+RECO/PROD/backups';

Directory created.

SQL> create or replace directory des_dir_obj as '/tmp';

Directory created.

SQL> exec DBMS_FILE_TRANSFER.COPY_FILE( 'sou_dir_obj', '7tmpbf0j_1_1', 'des_dir_obj', '7tmpbf0j_1_1');

PL/SQL procedure successfully completed.

2. GET_FILE

This procedure copies the files across database from ASM binary files to local file system and vice versa. GET_FILE procedure copies the file from the remote server to the local server using database link which can access the remote file system.

Syntax:

Exec DBMS_FILE_TRANSFER.GET_FILE

   source_directory_object      in  VARCHAR2,   

   source_file_name             in  VARCHAR2, 

   source_database_link         in  VARCHAR2, 

   destination_directory_object in  VARCHAR2,

   destination_file_name        in  VARCHAR2);

rem create directory object for remote database file system

SQL> create directory sou_dir_obj as '+RECO/PROD/backups';

Directory created.

rem create direcotry object for local database file system

SQL> create or replace directory des_dir_obj as '/tmp';

Directory created.

rem create database link to the remote database from the local database

SQL> create database link prod connect to system identified by welcome using 'prod';

Database link created.

Rem execute the command for file copy

SQL> exec DBMS_FILE_TRANSFER.GET_FILE ( 'sou_dir_obj' , '7rmpbebd_1_1','prod','des_dir_obj','bkup_7rmpbebd_1_1' );

PL/SQL procedure successfully completed.

SQL> !ls -ltr /tmp/bkup_7rmpbebd_1_1

-rw-r----- 1 oracle asmdba 173761024 Nov 28 01:34 /tmp/bkup_7rmpbebd_1_1

3. PUT_FILE

This procedure reads the local file or ASM and copies the files to the remote file system using database link.

Syntax:

Exec 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, 

   destination_database          IN  VARCHAR2);

_____________________________________________________________________________________________________________________

Website Stats