DBMS_FILE_TRANSFER potentially cool but then it is not
Posted by Martin Bach on August 24, 2013
This post is interesting for all those of you who plan to transfer data files between database instance. Why would you consider this? Here’s an excerpt from the official 12.1 package documentation:
DBMS_FILE_TRANSFER package provides procedures to copy a binary file within a database or to transfer a binary file between databases.
But it gets better:
The destination database converts each block when it receives a file from a platform with different endianness. Datafiles can be imported after they are moved to the destination database as part of a transportable operation without RMAN conversion.
So that’s a way not only to copy data files from one database to another but it also allows me to get a file from SPARC and make it available on Linux!
Before you are getting too excited though like I did here’s a catch. If the source file is in ASM you are kind of stuffed. Consider the below (18.104.22.168.0) example, created with a bog standard dbca “General Purpose” database. I have created the directories and db link and now want to get the files from database “source” to database “dbmsft”
begin dbms_file_transfer.get_file('dbmsft_src1','system.256.823861409','source','dbmsft_dst1','system.256.823861409'); dbms_file_transfer.get_file('dbmsft_src1','sysaux.257.823861409','source','dbmsft_dst1','sysaux.257.823861409'); dbms_file_transfer.get_file('dbmsft_src1','undotbs1.258.823861411','source','dbmsft_dst1','undotbs1.258.823861411'); dbms_file_transfer.get_file('dbmsft_src1','users.259.823861411','source','dbmsft_dst1','users.259.823861411'); dbms_file_transfer.get_file('dbmsft_src2','nonomf_01.dbf','source','dbmsft_dst2','nonomf_01.dbf'); end; 8 / begin * ERROR at line 1: ORA-19504: failed to create file "+DATA/dbmsft/datafile/system.256.823861409" ORA-17502: ksfdcre:4 Failed to create file +DATA/dbmsft/datafile/system.256.823861409 ORA-15046: ASM file name '+DATA/dbmsft/datafile/system.256.823861409' is not in single-file creation form ORA-06512: at "SYS.DBMS_FILE_TRANSFER", line 37 ORA-06512: at "SYS.DBMS_FILE_TRANSFER", line 132 ORA-06512: at line 2
And yes, that’s not a bug, it’s a feature, the same as with creating tablespaces. You simply cannot specify a fully qualified ASM file name when creating a file. You can of course use an alias name but that’s not pretty, is it? I hate mixing non OMF and OMF file names in ASM.
Need to check if that’s possible in 12c…