Replicating Oracle data to another Oracle database
The first step in Oracle-to-Oracle replication is the initial load.
Use
select min(start_scn) from gv$transaction
to get the SCN number of the oldest open or pending transaction.Use
select current_scn from V$DATABASE;
to get the SCN of the export.Use Oracle's
exp
orexpdp
utility, providing the SCN from step 2, to export the appropriate tables and data from the source database to a data file.Use Oracle's
imp
orimpdp
to import the exported data into the target database.
Once initial load is complete, the following sample application would continuously replicate changes to the tables SOURCE1 and SOURCE2 in database DB1 to tables TARGET1 and TARGET2 in database DB2 using Database Writer. The StartSCN value is the SCN number from step 1. In the WHERE clause, replace #########
with the SCN from step 2. Start the application with recovery enabled (see Recovering applications) so that on restart it will resume from the latest transaction rather than the StartSCN point.
CREATE SOURCE OracleCDC USING OracleReader ( Username:'striim', Password:'******', ConnectionURL:'10.211.55.3:1521:orcl1', Tables:'DB1.SOURCE1;DB1.SOURCE2', Compression:true StartSCN:'...' ) OUTPUT TO OracleCDCStream; CREATE CQ FilterCDC INSERT INTO FilteredCDCStream SELECT x FROM OracleCDCStream x WHERE TO_LONG(META(x,'COMMITSCN')) > #########; CREATE TARGET WriteToOracle USING DatabaseWriter ( ConnectionURL:'jdbc:oracle:thin:@10.211.55.3:1521:orcl1', Username:'striim', Password:'******', Tables:'DB1.SOURCE1,DB2.TARGET1;DB1.SOURCE2,DB2.TARGET2' ) INPUT FROM FilteredCDCStream;
The FilterCDC
CQ filters out all transactions that were replicated during initial load.
The following Oracle column types are supported:
BINARY DOUBLE
BINARY FLOAT
BLOB
CHAR
CLOB
DATE
FLOAT
INTERVAL DAY TO SECOND
INTERVAL YEAR TO MONTH
LONG
NCHAR
NUMBER
NVARCHAR
RAW
TIMESTAMP
TIMESTAMP WITH TIME ZONE
TIMESTAMP WITH LOCAL TIME ZONE
VARCHAR2
Limitations:
The primary key for a target table cannot be BLOB or CLOB.
TRUNCATE TABLE is not supported for tables containing BLOB or CLOB columns.