An effective simple method for exchanging data between
Oracle databases
Introduction
When offering data from one Oracle database to other a
number of issues need to be considered
Shall the sink database have direct access to the source
database?
For purposes of decoupling, and as a layer providing any
needed adaptation between the two databases direct access to the source DB
should normally be avoided. The method described in the following uses temporary
tables at the sink database as the way to hide the tables of the source
database. The structure of the data and any needed conversions are implemented
using SQL
Are we doing batch processing or is it important that
the sink database has online access to the source database?
Many solutions call for large extracts from the source
database which needs to be made available to the sink database at regular
intervals. Sometimes archives of each data transfer or queues of datasets may be
relevant, or the same data needs to be distributed to several destinations. The
below method accommodates for these requirements by having multiple tables saved
in a single highly compressed file in an efficient manner.
When online access is needed by the sink database to the
source database Oracle views at the source database offered through Oracle net
to the sink is the way to go. Such interfaces are often characterized by a high
number of queries each with a low volume of data. The method described in the
following is not relevant in this case.
Firewalls and network configuration?
Establishing a network connection between the source and
the sink database may sometimes be difficult. Firewalls have to be opened,
configurations at the sink and server has to be updated. The method described in
the following only requires the ability to move a file between the source and
the sink hosts. As the file is effectively compressed even dumps of several
Gbytes of DB data can be moved as files sized in number of 100Mbytes.
What will it cost to develop, maintain and manage the
software components introduced?
Data exchange between system can be done in many ways, e.g.
on a common XML bus or though proprietary communication protocols. Several
layers of conversion components are often seen. The method described here
depends on the Oracle tools exp (exporter), imp (an importer), Oracle sqlplus
and gzip. The glue between these are simple sh scripts which can easily be
maintained and developed.
How is it done?
The method is based on a common set of temporary tables
containing the extract in both the source and the sink DB
Defining the extracts
Before defining the extract you must remember to clean up
from last run
DROP TABLE tempExtractTableNo1;
DROP TABLE tempExtractTableNo2;
.....
DROP TABLE tempExtractTableNoN;
The actual extracts are defined by use of the SQL
construction
CREATE TABLE tempExtractTableNo1 as SELECT <.... query
defining 1st extract>;
CREATE TABLE tempExtractTableNo2 as SELECT <.... query
defining 2nd extract>;
......
CREATE TABLE tempExtractTableNoN as SELECT <.... query
defining Nth extract>;
Exporting and compressing the extracts
If you just use the Oracle utility exp you will end up with
huge files. Fortunately both exp and imp are able to write respectively read
from a namedPipe (in Tru64/Linux term) or fifo(in HPUX terms). Combine this with
the gzip utility and we suddenly have a very space efficient tool. Note also
that we have observed that the execution of Oracle imp as well as exp runs
faster when working on a named pipe connected to gzip/gunzip than when working
directly on the disc.
mknod myNamedPipe p (on Tru64/Linux)
mkfifo myNamedPipe (on HPUX)
gzip <myNamedPipe >extractFile.dmp.gz&
exp sourceUsr/sourcePassword@sourceDatabase FILE=myNamedPipe
TABLES=’tempExtractTableNo1,tempExtractTableNo2,...tempExtractTableNoN’
wait
rm myNamedPipe
At this point the extractFile.dmp.gz contains the extract.
This file can be stored and distributed or queued as needed.
Importing the extracts
At some point in time the data may be needed at the sink
DB. As with the export clean up of previously imported tables is needed.
mknod myNamedPipe p (on Tru64/Linux)
mkfifo myNamedPipe (on HPUX)
gunzip <extractFile.dmp.gz >myNamedPipe &
imp sinkUsr/sinkPassword@sourceDatabase FILE=myNamedPipe
TABLES=’*’
wait
rm myNamedPipe
References and statistics
This method has come out of the FOCUS project delivered to
a Telecom Company. The method has been applied when exporting data from the
Service Controller to the Inventory system.
In this setup it has been possible to perform the following
(on an export of a complete IMS database)
Number of rows in temporary
tables: 8476874
Number of blocks in temporary
tables: 417280blocks (3.2 GB in 8Kb blocks)
Size of .dmp uncompressed 2.9 GB
Size of .dmp compressed 114Mb
Time to create temporary tables
and perform export: 4h36min
Time to import temporary tables:
2h04min
Server details: Alpha Server DS20
500 MHz DEC6600, 1 CPU, 2.4GB memory
Note that the above export was performed with the parameter
DIRECT set to the default value ‘N’. Further performance may be gained by
setting it to ‘Y’ if this is possible in your setup. Consult the Oracle Database
Utilities manual for details on the DIRECT parameter.
Excluding the temporary table generation the export with
DIRECT=N (default) takes 41m, with DIRECT=Y takes 7m15s.
Note
Although the final extract is compressed, the temporary
extract tables are not. This means that the source and sink database must be
sized accordingly.
If one wants to use this method in an environment where
extracts are requested not just by a single sink system, one extract at a time,
but by several sink systems, some times at the same time, the usage of a single
set of temporary extract tables will pose a problem. A solution may be to use a
pool of tables or a DB user pr. sink. Distribution of the same set of data to
multiple sink hosts can of course be handled by simple file copying.
Examples scripts
The following examples scripts are not intended to be ready
to use in a solution. They are only intended to serve as working examples which
documents the method and can be used for a specific purpose. Just select and
copy the text of each script, paste it into your preferred editor and save. As
stated in the scripts the schemas collected from are the HR and SCOTT users of
the sample databases provided as a part of an Oracle installation.
#! /bin/sh
# The
following export uses data from the HR user of the example data provided as a
part of ORACLE
# Please
refer to the "Sample Schemas" document provided by ORACLE.
sqllogin='hr/hr@asakb'
timeStamp=`date
+%Y%m%d%H%M%S`
namedPipe=/tmp/`basename
$0`_${timeStamp}.dmp
exportFile='employeestat_'${timeStamp}.dmp.gz
tables='export_employees_stat_pr_job,export_employees_stat_pr_dep'
sqlplus ${sqllogin}
<< eof
drop table
export_employees_stat_pr_job;
create
table
export_employees_stat_pr_job as
select
job_title, count_Of_Employees, total_Salary
from
jobs,
(select
job_id,
count(*) count_Of_Employees, sum(salary) total_Salary
from
employees group by job_id) stats
where
jobs.job_id=stats.job_id;
drop table
export_employees_stat_pr_dep;
create
table
export_employees_stat_pr_dep as
select
department_name, count_Of_Employees, total_Salary
from
departments,
(select
department_id, count(*) count_Of_Employees, sum(salary) total_Salary
from
employees group by department_id) stats
where
departments.department_id=stats.department_id;
commit;
eof
if [ `uname`
!= "HP-UX" ]
then
mknod ${namedPipe}
p
else
# OK for
OSF and Linux
mkfifo
${namedPipe}
fi
gzip <${namedPipe}
>${exportFile} &
exp ${sqllogin}
FILE=${namedPipe} TABLES=\(${tables}\)
wait
rm ${namedPipe}
Figur
1: gzipexp.sh
#! /bin/sh
# The
following imports an extract into the SCOTT user of the example data provided
as a part of ORACLE
# Please
refer to the "Sample Schemas" document provided by ORACLE.
# First
parameter is the dmp.gz file
sqllogin='scott/tiger@asakb'
timeStamp=`date
+%Y%m%d%H%M%S`
namedPipe=/tmp/`basename
$0`_${timeStamp}.dmp
exportFile=${1}
tables='export_employees_stat_pr_job,export_employees_stat_pr_dep'
sqlplus ${sqllogin}
<< eof
drop table
export_employees_stat_pr_job;
drop table
export_employees_stat_pr_dep;
eof
if [ `uname`
!= "HP-UX" ]
then
mknod ${namedPipe}
p
else
# OK for
OSF and Linux
mkfifo
${namedPipe}
fi
gunzip <${exportFile}
>${namedPipe} &
imp ${sqllogin}
FILE=${namedPipe} TABLES=\(${tables}\)
wait
rm ${namedPipe}
Figur
2: gzipimp.sh
Acknowledgement
Thanks to Nandi who was the first reviewer on the document
and my manager who performed the second round of review.
Summary
This knowledge brief describes a simple way to provide
extracts from one Oracle database to other.
The main benefits of this
method are:
- Decouples the structure of the extracts from the layout
of the source DB.
- Extracts can be queued for later batch processing at the
sink DB.
- Simple SQL defines structure of extracts.
- Each extract is heavily compressed.
- Extracts are speedily imported and exported.
- Standard Oracle tools are used.
- Interface between source and sink DB is based on a file
transfer pr. extract (no Oracle Client/Server setup)
This article provides ready to use import and export
example scripts.
| 
