Transferring Operating System Files from within Oracle

Thursday May 6th 2004 by James Koopmann
Share:

As new versions of Oracle are released, we are less likely to access the operating system. The ability to move data files from within Oracle is just one more great hook to the operating system that aids DBAs in their daily tasks.

As new versions of Oracle are released, we are less likely to access the operating system. The ability to move data files from within Oracle is just one more great hook to the operating system that aids DBAs in their daily tasks.

Within Oracle 10g we are given the opportunity to move operating system files without ever 'physically' logging into the operating system itself. This in itself provides us, as DBAs, many options when doing common database administrative tasks that we will typically do. These tasks range from simple storage management, where we need to free up space on a disk, to allowing us to move tablespaces and associated data files to other databases. If you have any scripts lying around that do file movement such as backups or cloning of databases this option should spark some interest from you.

DBMS_FILE_TRANSFER

As this package suggests it provides you the ability to move (transfer) operating system files from one location to another. The beauty of this package is that it allows you to move these operating system files either within the current database host environment or between database hosts. Table A gives you a brief description of the procedures that are included in this package and what they do.

DBMS_FILE_TRANSFER Procedures

Here are the current three procedures that are packaged in the DBMS_FILE_TRANSFER package and the associated definition.

COPY_FILE Procedure

Allows you to read an operating system file (source_file_name) within a directory (source_directory_object) on the local database host environment and create a copy (destination_file_name) in another directory (destination_directory_object) on the same local host system.

Syntax

DBMS_FILE_TRANSFER.COPY_FILE(
source_directory_object 		IN VARCHAR2,
source_file_name 			IN VARCHAR2,
destination_directory_object 	IN VARCHAR2,
destination_file_name 		IN VARCHAR2);

GET_FILE Procedure

Allows you to read in a file (source_file_name) within a directory (source_directory_object) from a remote database host system (source_database) and create a copy of that data file (destination_file_name) in a directory (destination_directory_object) on the current local host database system

Syntax

DBMS_FILE_TRANSFER.GET_FILE
source_directory_object 		IN VARCHAR2,
source_file_name 			IN VARCHAR2,
source_database 			IN VARCHAR2,
destination_directory_object 	IN VARCHAR2,
destination_file_name 		IN VARCHAR2);

PUT_FILE Procedure

Allows you to read in a file (source_file_name) within a directory (source_directory_object) from the current local database host system and create a copy of that data file (destination_file_name) in a directory (destination_directory_object) on a remote database host system (desination_database).

Syntax

DBMS_FILE_TRANSFER.PUT_FILE(
source_directory_object 		IN VARCHAR2,
source_file_name 			IN VARCHAR2,
destination_directory_object 	IN VARCHAR2,
destination_file_name 		IN VARCHAR2,
destination_database 		IN VARCHAR2);

Input Definitions

Here is a brief definition of the input for each of the procedures within the DBMS_FILE_TRANSFER package.

source_directory_object

This is a created directory object where a file resides from which you wish to copy a file. This is done through the CREATE DIRECTORY command and then read privileges must be granted to the user on this directory.

source_file_name

This is the name of a file that exists within the source_directory_object directory that you wish to copy either to the current host database system or to a remote destination host database system.

source_database

This is a database link that needs to be created with the CREATE DATABASE LINK command and is the source database where a source data file exists that you wish to copy to a remote system.

destination_directory_object

A source data file will be placed into this created directory object. This directory is created through the CREATE DIRECTORY command. Write privileges must be granted to the user on this directory.

destination_file_name

This is the name of the file you wish to copy the source file to, within the destination_directory_object directory. There must not be a file in this directory that matches the name you are trying to write.

destination_database

This database link needs to be created with the CREATE DATABASE LINK command and represents the destination database to which you are copying the data file.

Privileges Required

In order to read and write to the directories on the source and destination hosts you must grant the following to the users that will be executing the DBMS_FILE_TRANSFER procedures. If you are worried about giving someone complete access to the operating system files that might have sensitive information you will need to create extra directory structures and then grant privileges according to the type of information stored in those directories.

  • You must grant READ privileges for users on the DIRECTORY used in the source_directory_object if you wish them to copy files from this directory.
  • You must grant WRITE privileges for users on the DIRECTORY used in the destination_directory_object if you wish them to copy files into this directory.

Considerations

When using the DBMS_FILE_TRANSFER package there are a couple of considerations and usage notes of which you should be aware.

  1. The procedures contained within the DBMS_FILE_TRANSFER package will convert the parameters to uppercase unless you use double quotes around the variable.
  2. Not to worry, the file names are not converted to uppercase.
  3. When a file is copied, it is treated as a binary file.
  4. No character conversions are performed when copying files.
  5. File size must be a multiple of 512 bytes.
  6. File size cannot exceed 2 terabytes.


An Example



1.  Let's take a look at where our current files reside.





SQL> SELECT tablespace_name,file_name FROM dba_data_files;
TABLESPACE_NAME  FILE_NAME
---------------  -----------------------------------------------------
USERS            C:\ORACLE\PRODUCT\10.1.0\ORADATA\DATEN\USERS01.DBF
SYSAUX           C:\ORACLE\PRODUCT\10.1.0\ORADATA\DATEN\SYSAUX01.DBF
UNDOTBS1         C:\ORACLE\PRODUCT\10.1.0\ORADATA\DATEN\UNDOTBS01.DBF
SYSTEM           C:\ORACLE\PRODUCT\10.1.0\ORADATA\DATEN\SYSTEM01.DBF
CUSTOMER         C:\ORACLE\PRODUCT\10.1.0\ORADATA\DATEN\CUST01.DBF

2.  Create a DIRECTORY to our source directory

SQL> CREATE DIRECTORY DBFILES AS 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\DATEN';
Directory created.

3.  Grant READ Privileges to the source directory

SQL> GRANT READ ON DIRECTORY dbfiles TO jkoopmann;
Grant succeeded.

4.  Create a DIRECTORY to our destination directory

SQL> CREATE DIRECTORY DB_BACKUP AS 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\DATEN_BACKUP';
Directory created.

5.  Grant WRITE Privileges to the destination directory

SQL> GRANT WRITE ON DIRECTORY db_backup TO jkoopmann;
Grant succeeded.

6.  Connect as the user you wish to copy file as

connect jkoopmann/@daten
connected.

7.  If you want to copy an existing data file attached to a tablespace, you must put it into read-only mode or take it off-line. A note here to be aware of when working with existing data files that are being used within a database, you cannot copy the data files associated with the SYSTEM, TEMPORARY, or UNDO tablespaces. This is because they cannot be taken off-line or put into read-only mode since they need to be available for read-write access by processes. In addition, the new tablespace SYSAUX in Oracle10g cannot be put into read-only mode. Because of this we will work with the CUSTOMER tablespace that I have created and copy its data file.

i.	
	SQL> ALTER TABLESPACE CUSTOMER READ ONLY;
	Tablespace altered.

-OR-

ii.	
	SQL> ALTER TABLESPACE CUSTOMER OFFLINE;
	Tablespace altered.

8.  Run the COPY_FILE procedure to copy a file to the backup directory.

BEGIN
  DBMS_FILE_TRANSFER.COPY_FILE(
        source_directory_object       =>  'DBFILES',
        source_file_name              =>  'CUST01.DBF',
        destination_directory_object  =>  'DB_BACKUP',
        destination_file_name         =>  'CUST01.DBF');
END;
/
PL/SQL procedure successfully completed.

9.  Copy of data file is done, now end backup mode for tablespace.

i.	
	SQL> ALTER TABLESPACE CUSTOMER READ WRITE;
	Tablespace altered.

-OR-

ii.	
	SQL> ALTER TABLESPACE CUSTOMER ONLINE;
	Tablespace altered.

Monitoring

If you wish to monitor the process of copy the data files you can use the new V$SESSION_LONGOPS view. This view displays those operations in your database that take longer than 6 seconds to complete. Following is a SQL statement that you can use against this view. I have given two output lines that were produced from me copying the CUSTOMER.DBF data file. The first was after the job completed and the second line was when the copy was in-stream. Of importance is the SOFAR column, which shows the total units of work completed so far. As you recall, it was stated earlier that these copies happen in 512K block units. Therefore, you can multiply the SOFAR by 512K (524,288) to get the total bytes transferred at any given time. In addition, this is a handy view to check the time this operation started, the time left, and total elapsed time for the operation to complete.

SELECT opname,target,target_desc,
       sofar,totalwork,
       to_char(start_time,'MMDDYYYY:HH24:MI:SS') start_time,
       time_remaining,elapsed_seconds
FROM v$session_longops
/

 Time Elapsed
OPNAME                       TARGET_DESC  SOFAR TOTALWORK START_TIME        Left Seconds
---------------------------- ----------- ------ --------- ----------------- ---- -------
DBMS_FILE_TRANSFER.COPY_FILE CUST01.DBF  204816    204816 04282004:07:29:33    0      21
DBMS_FILE_TRANSFER.COPY_FILE CUST01.DBF  151552    204816 04282004:07:40:54    6      16

Conclusion

This new option allows us (and Oracle) to take further control of our databases in ways I do not think we have yet considered. With the need to transport tablespaces / data files to other systems, the need to detatch ourselves from the operating system, and the need for us to seamlessly start sharing information across platforms makes this seemingly small option a very powerfull utility. Start using wisely.

» See All Articles by Columnist James Koopmann

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved