Systems Engineering and RDBMS

DBMS_FILE_TRANSFER

Posted by decipherinfosys on June 25, 2007

Traditionally, DBAs depend upon the operating system commands to copy an “Oracle database file” within a database, or transfer a file between databases. However, after the introduction of DBMS_FILE_TRANSFER, they do not necessarily need to depend upon the operating system for transferring oracle database file within or across the databases, and this could prove very useful while using the transportable tablespace feature or an Automatic Storage Management (ASM) disk group as the source or destination for a file transfer.

We will demonstrate here how to use dbms_file_transfer to copy an “oracle database file” within a database by moving a tablespace’s data-files.

Pre requisite:

You need to create a directory object pointing to the source and destination directory.

SQL> CREATE DIRECTORY source_dir AS ‘/u01/oradata/decipher/’;

SQL> CREATE DIRECTORY dest_dir AS ‘/u01/oradata/decipher/’;

Grant the read/write privileges to the user who will run the COPY_FILE procedure.

SQL> GRANT EXECUTE ON DBMS_FILE_TRANSFER TO decipher;

SQL> GRANT READ ON DIRECTORY source_dir TO decipher;

SQL> GRANT WRITE ON DIRECTORY dest_dir TO decipher;

SQL>conn decipher/decipher@decipher

Step 1: Take the tablespace offline

SQL>ALTER TABLESPACE decipher_data OFFLINE NORMAL;

Note : DB must be running in archive log mode.

Step 2: Move the datafiles using DBMS_FILE_TRANSFER

BEGIN
DBMS_FILE_TRANSFER.COPY_FILE(
source_directory_object => ‘SOURCE_DIR’,
source_file_name => ‘decipher_data_001.ora’,
destination_directory_object => ‘DEST_DIR’,
destination_file_name => ‘decipher_data_001.ora’);
END;
/

Few important points to keep in mind:

  • On UNIX systems, the owner of a file created by the DBMS_FILE_TRANSFER package is the owner of the shadow process running the instance. Normally, this owner is ORACLE. A file created using DBMS_FILE_TRANSFER is always writable and readable by all processes in the database, but the non-privileged users who need to read or write such a file directly may need access from a system administrator. You need to ensure that such privileges are in place if you are going to use those users.
  • Do not use the DBMS_FILE_TRANSFER package to copy or transfer a file that is being modified by a database because doing so can result in an inconsistent file.
  • DBMS_FILE_TRANSFER can also be invoked as a remote procedure call. A remote procedure call lets you copy a file within a database even when you are connected to a different database. For example, you can make a copy of a file on database DB decipher, even if you are connected to database healthcare, by executing the following remote procedure call:

connect healthcare/healthcare@healthcare

BEGIN
DBMS_FILE_TRANSFER.COPY_FILE@decipher(
source_directory_object => ‘SOURCE_DIR’,
source_file_name => ‘decipher_data_001.ora’,
destination_directory_object => ‘DEST_DIR’,
destination_file_name => ‘decipher_data_001.ora’);
END;
/

  • DBMS_FILE_TRANSFER along with remote procedure calls enables you to copy a file between two databases, even if you are not connected to either database. For example, you can connect to the database healthcare and then transfer a file from database decipher to database finance or vice versa using either DBMS_FILE_TRANSFER.PUT_FILE or DBMS_FILE_TRANSFER.GET_FILE.

Step 3: Rename the filenames within the database

SQL>ALTER TABLESPACE decipher_data
RENAME DATAFILE ‘/u01/oradata/decipher/decipher_data_001.ora’,
‘/u01/oradata/ decipher/decipher_data_002.ora’
TO ‘/u02/oradata/decipher/decipher_data_001.ora’,
‘/u02/oradata/decipher/decipher_data_002.ora ‘;

Step 4: Bring tablespace back to online

SQL>ALTER TABLESPACE decipher_data ONLINE;

Sorry, the comment form is closed at this time.

 
%d bloggers like this: