Skip to main content

Replicating Oracle data to another Oracle database

The first step in Oracle-to-Oracle replication is the initial load.

  1. Use select min(start_scn) from gv$transaction to get the SCN number of the oldest open or pending transaction.

  2. Use select current_scn from V$DATABASE; to get the SCN of the export.

  3. Use Oracle's exp or expdp utility, providing the SCN from step 2, to export the appropriate tables and data from the source database to a data file.

  4. Use Oracle's imp or impdp 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.Recovering applications

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.