Skip to main content

Replicating Oracle data to Amazon Redshift

Striim provides a template for creating applications that read from Oracle and write to Redshift. See Creating an application using a template for details.

RedshiftWriter can continuously replicate one or many Oracle tables to an Amazon Redshift store. First, create a table in Redshift corresponding to each Oracle table to be replicated. Then load the existing data using DatabaseReader, for example:

CREATE SOURCE OracleJDBCSource USING DatabaseReader (
  Username:'Striim',
  Password:'****',
  ConnectionURL:'jdbc:oracle:thin:@192.168.123.14:1521/XE',
  Tables:'TPCH.H_CUSTOMER;TPCH.H_PART;TPCH.H_SUPPLIER'
)
OUTPUT TO DataStream;

CREATE TARGET TPCHInitialLoad USING RedshiftWriter (
  ConnectionURL: 'jdbc:redshift://mys3bucket.c1ffd5l3urjx.us-west-2.redshift.amazonaws.com:5439/dev',
  Username:'mys3user',
  Password:'******',
  bucketname:'mys3bucket',
/* for striimuser */
  accesskeyid:'********************',
  secretaccesskey:'****************************************',
  Tables:'TPCH.H_CUSTOMER,customer;TPCH.H_PART,part;TPCH.H_SUPPLIER,supplier'
)
INPUT FROM DataStream;

Theœ Tables property maps each specified Oracle table to a Redshift table, for example, TPCH.H_CUSTOMER to customer.

Once the initial load is complete, the following application will read new data using LogMiner and continuously replicate it to Redshift:

CREATE SOURCE OracleCDCSource USING OracleReader (
  Username:'miner',
  Password:'miner',
  ConnectionURL:'192.168.123.26:1521:XE',
  Tables:'TPCH.H_CUSTOMER;TPCH.H_PART;TPCH.H_SUPPLIER'
)
Output To LCRStream;

CREATE TARGET RedshiftTarget USING RedshiftWriter (
  ConnectionURL: 'jdbc:redshift://mys3bucket.c1ffd5l3urjx.us-west-2.redshift.amazonaws.com:5439/dev',
  Username:'mys3user',
  Password:'******',
  bucketname:'mys3bucket',
/* for striimuser */
  accesskeyid:'********************',
  secretaccesskey:'****************************************',
  Tables:'TPCH.H_CUSTOMER,customer;TPCH.H_PART,part;TPCH.H_SUPPLIER,supplier',
  Mode:'IncrementalLoad' )
INPUT FROM LCRStream;

Note that Redshift does not enforce unique primary key constraints. Use OracleReader's StartSCN or StartTimestamp property to ensure that you do not have duplicate or missing events in Redshift.

For for more information, see Redshift Writer.