Transfer Datafile from ASM to File System

This article is about transferring database files from ASM to O/S File System, two methods are used here are:

1. DBMS_FILE_TRANSFER.copy_file.

2. 'ASMCMD cp' Command.

DBMS_FILE_TRANSFER

                            There are three procedures within the dbms_file_transfer package:

• COPY_FILE: This is useful for copying files locally on the database server.
   
• GET_FILE: This is useful when a file on a remote database is to be transferred to a local file system through the local database connection.
   
• PUT_FILE: Reads a local file and contacts a remote database to create a copy of the file in the remote file system

       Can move files to and fro between ASM and OS File System, valid from 10gR2. Here COPY_FILE procedure will be used to transfer the datafile name EXAMPLE.dbf, the complete path and name of datafile at ASM is 

        +DATA/orcl/datafile/example.267.758826135

Source directory is at ASM (+DATA/orcl/datafile)

And Destination Directory is at O/S File System (/u01/app/oracle/dbhome_1)

Log in as user with DBA role and execute the following statements:

SQL> create directory source as '+DATA/orcl/datafile/';

SQL> create directory dest as '/u01/app/oracle/dbhome_1'

SQL> grant read on directory source to dba;

SQL> grant write on directory dest to dba;

and finally run 

SQL> begin 

                  dbms_file_transfer.copy_file('source','example.267.758826135','dest','EXAMPLE.dbf'); 

          end;

          After successful run of above procedure, file existence at O/S FS can be verified using 'ls'

 

ASMCMD 'cp'

                This is the simplest way to move database file either way between ASM and File System, introduces at 11g.

Again 

Source directory is at ASM (+DATA/orcl/datafile)

And Destination Directory is at O/S File System (/u01/app/oracle/dbhome_1)

at asmcmd prompt simply run the below line and you will be done.

ASMCMD> cp +DATA/appdbtst/datafile/example.267.758826135 /u01/app/oracle/dbhome_1/example.dbf

The remaining procedures (GET_FILE and PUT_FILE) are similar in the way source and destination are specified but enable further transfer to a remote DB specified by dblink. Both source and target databases should have directories defined. Restrictions related to dbms_file_transfer package are:

• Files are supposed to be multiple of 512 bytes
• Files should be less than 2TB
• There is no character set conversion
• Oracle should have permissions to the file and after copy file are owned by oracle user.
• File transfer operation can be monitored from v$session_longops