Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 

 

Oracle dbms_file_transfer tips


Oracle Database Tips by Donald Burleson
Using Oracle dbms_file_transfer

The dbms_streams_tablespace_adm package provides administrative procedures for copying tablespaces between databases and for moving tablespaces from one database to another. This package uses transportable tablespaces, Data Pump, and the Oracle dbms_file_transfer package.

Also see dbms_file_transfer.copy_file tips

Copy archived redo log files to the downstream database using the Oracle dbms_file_transfer package.

In the Down Streams Capture method, the archived redo log files from the source database are copied to the downstream database.  The Capture process captures changes from these files at the downstream database. The archived redo log files can be copied to the downstream database using a variety of means. They include log transport services, the Oracle dbms_file_transfer package, a File Transfer Protocol (FTP), or some other mechanism.

New features of the dbms_file_transfer package

Dave Moore notes these new dbms_file_transfer options:

There are three procedures within the dbms_file_transfer  package:

  • COPY_FILE: This is useful for copying files locally on the database server.
     
  • GET_FILE: This is useful when a file on a remote database is to be transferred to a local file system through the local database connection.
     
  • PUT_FILE: Reads a local file and contacts a remote database to create a copy of the file in the remote file system

Assume that the tablespace containing a data file with excessive IO was created as follows:

CREATE TABLESPACE ts_hotspot LOGGING
DATAFILE '/usr/oracle/databases/mid101so/datafiles/hotspot.dbf' SIZE 5120K REUSE
BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
SEGMENT SPACE MANAGEMENT AUTO;


The following SQL demonstrates how he can move the datafile from a hotspot to a different location without executing an OS command. As the DBA user, execute the following command.

CREATE DIRECTORY DEST_DIR AS '/tmp';
CREATE DIRECTORY SOURCE_DIR AS '/usr/oracle/databases/mid101so/datafiles';


As user SYS, create the necessary grants.

GRANT EXECUTE ON DBMS_FILE_TRANSFER to DBA
GRANT READ ON DIRECTORY SOURCE_DIR to DBA
GRANT WRITE ON DIRECTORY DEST_DIR to DBA


.As the DBA user, execute the following command.

ALTER TABLESPACE ts_hotspot READ ONLY;
BEGIN
dbms_file_transfer.copy_file( source_directory_object => 'SOURCE_DIR',
source_file_name => 'hotspot.dbf',
destination_directory_object => 'DEST_DIR',
destination_file_name => 'hotspot.dbf');
END;
/
ALTER TABLESPACE ts_hotspot OFFLINE;
ALTER TABLESPACE ts_hotspot RENAME datafile '/usr/oracle/databases/mid101so/datafiles/hotspot.dbf' TO '/tmp/hotspot.dbf';
ALTER TABLESPACE ts_hotspot ONLINE;
ALTER TABLESPACE ts_hotspot READ WRITE;


 

The dbms_file_transfer package, available in 10g and later, supports copying via the local file system or an ASM (Automated Storage Management) disk group as both the source and target for a binary file transfer between databases servers.

An example of using this package would be to copy BFILE objects across databases. Remember, the database stores just the BFILE attributes - the data resides outside the database. So use dbms_file_transfer to copy the actual BFILE data and not just the attributes held within the database. However, there are some limitations:

  • Maximum of 2 TB file size

  • File size must be multiple of 512 bytes

The process is very simple. First create a database directory to read from on the source and another one on the target for the write. Then make sure that both databases have grants on the directories and the dbms_file_transfer package for the schemas used to perform the task. Then use one of the following three procedures.

Copy_file reads a file from the local database directory and copies it to another local database directory. The DBA is able to copy any type file when working with local file systems, but ASM supports only moving database files since that is all that can be held within ASM.

Argument

Type

In / Out

Default Value

SOURCE_DIRECTORY_OBJECT

VARCHAR2

IN

SOURCE_FILE_NAME

VARCHAR2

IN

DESTINATION_ DIRECTORY_OBJECT

VARCHAR2

IN

DESTINATION_FILE_NAME

VARCHAR2

IN

Table 6.136: Copy_file Parameters

Get_file reads a file from the remote database directory and copies it to the source local database directory. The DBA can copy any type file when working with local file systems, but ASM supports only moving database files since that is all that can be held within ASM.

Argument

Type

In / Out

Default Value

SOURCE_DIRECTORY_OBJECT

VARCHAR2

IN

SOURCE_FILE_NAME

VARCHAR2

IN

SOURCE_DATABASE

VARCHAR2

IN

DESTINATION_ DIRECTORY_OBJECT

VARCHAR2

IN

DESTINATION_FILE_NAME

VARCHAR2

IN

Table 6.137: Get_file Parameters

Put_file reads a file from the local database directory and copies it to the target remote database directory.

Argument

Type

In / Out

Default Value

SOURCE_DIRECTORY_OBJECT

VARCHAR2

IN

SOURCE_FILE_NAME

VARCHAR2

IN

DESTINATION_ DIRECTORY_OBJECT

VARCHAR2

IN

DESTINATION_FILE_NAME

VARCHAR2

IN

DESTINATION_DATABASE

VARCHAR2

IN

Table 6.138: Put_file Parameters


The DBMS_FILE_TRANSFER Package

Another approach to migrate and copy files from the file system to the ASM is to use the Oracle supplied PL/SQL package DBMS_FILE_TRANSFER. The example below illustrates the use of the package to migrate a temporary tablespace from the file system to the Automatic Storage Management (ASM) using the following steps:

 

  Create and provide access to the source and target directories - SRCDIR

and TGTDIR. The source data file is located on the directory '/home/oracle.'

The target directory is the +DATA disk group.

 

SQL> CREATE DIRECTORY srcdir as '/home/oracle';

 

Directory created.

 

SQL> GRANT all ON DIRECTORY srcdir to system;

 

Grant succeeded.

 

SQL> CREATE DIRECTORY tgtdir as '+DATA';

 

Directory created.

 

SQL> GRANT all ON DIRECTORY tgtdir to system;

 

Grant succeeded.

 

SQL>

 

  Execute DBMS_FILE_TRANSFER.COPY_FILE procedure

 

SQL> BEGIN

  2     DBMS_FILE_TRANSFER.COPY_FILE(

  3          'SRCDIR', 'temp_1.dbf', 'TGTDIR', 'temp_1.f');

  4  END;

  5  /

 

PL/SQL procedure successfully completed.

 

SQL>

 

  Update the database data dictionary with the new file location. The tablespace must be either offline or the database is on MOUNT mode. Obtain the name

of the copied file by querying V$ASM_FILES.

 

SQL> SHUTDOWN IMMEDIATE

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> STARTUP MOUNT

ORACLE instance started.

 

Total System Global Area    551165952 bytes

Fixed Size                    2230232 bytes

Variable Size               352323624 bytes

Database Buffers            192937984 bytes

Redo Buffers                  3674112 bytes

Database mounted.

SQL> ALTER DATABASE RENAME FILE '/home/oracle/temp_1.dbf'

  2  TO '+DATA/temp_1.f';

 

Database altered.

 

SQL> ALTER DATABASE OPEN;

 

Database altered.

 

SQL>

 

  Verify the new location of the migrated temporary file.

 

SQL> SELECT tablespace_name, file_name

  2  FROM   dba_temp_files;

 

TABLESPACE_NAME      FILE_NAME

-------------------- --------------------------------------------------

TEMP_2             +DATA/t1c1/tempfile/copy_file.267.846281147

TEMP_1             +DATA/temp_1.f

TEMP               +DATA/t1c1/tempfile/temp.263.846267555

 

SQL>

 

 
 
 
Get the Complete
Oracle Utility Information 

The landmark book "Advanced Oracle Utilities The Definitive Reference"  contains over 600 pages of filled with valuable information on Oracle's secret utilities. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.
 

 


 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.