DBMS_FILE_TRANSFER potentially cool but then it is not

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 ( 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”

  8  /
ERROR at line 1:
ORA-19504: failed to create file
ORA-17502: ksfdcre:4 Failed to create file
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…


3 thoughts on “DBMS_FILE_TRANSFER potentially cool but then it is not

  1. Josef Kraitz (@josefkraitz)

    Of course it works without any issues for any combination of source and destination, ASM and non-ASM.

    In your case the error is coming from the destination, when you are trying to prescribe it, with the destination filename, the full filename, including the ASM file and incarnation numbers, which do not have any sense, with the same values on destinatoin, as they are on source.

    If you would have provided the destination filename (parameter destination_file_name) without the ASM file and incarnation numbers (it can be any valid ASM alias name, in fact, thus you can freely use this function to change names during the transfers), then the transfer would have succeeded, the file would have been created on destination with a different ASM file and incarnation numbers, and an ASM alias would have been created, for that file, same as the destination_file_name parameter which you have provided.


    1. Martin Bach Post author

      I think we are sort of thinking along the same lines but with different expectations. I have thought or better, hoped the package to be an equivalent of the OS cp command when it isn’t. That’s my only grief, as I said in the last paragraph I don’t really like ASM aliases. I should probably elaborate a little more.

Comments are closed.