Martins Blog

Trying to explain complex things in simple terms

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:

The 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 (11.2.0.3.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…

About these ads

One Response to “DBMS_FILE_TRANSFER potentially cool but then it is not”

  1. vijay said

    cool! sounds very good however, for non-asm databases

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 2,075 other followers

%d bloggers like this: