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

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.
  1. 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
  1. 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);
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google