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.
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;
|
|
