SECRETS of ORACLE STREAMS REVEALED

I recently had an exhausting experience to implement STREAMS for a HIGH END TRANSACTIONAL SYSTEM hosted on Oracle 10g RAC which was not less than a CHALLENGE.

Surprisingly I came across alot of issues which had not been published before and

The Business requirement was simple:
Make a SINGLE INSTANCE REPLICA of PRODUCTION SYSTEM running on TWO NODE RAC with near to real time REPLICATION of TWO SCHEMAS without impacting the PERFORMANCE of Production DB in any way. A challenge we took HEAD ON. Ultimately DOWNSTREAM CAPTURE using ARCHIVELOG SHIPPING was chosen as the OPTIMAL MECHANISM to acheive the target. Here under I will elaborate the  never before documented steps to succesfully implement this solution.

Oracle documentation specifies you to create THREE BASIC COMPONENTS for STREAMS :
1- CAPTURE PROCESS - To capture the changes from SOURCE DB
2- PROPAGATION PROCESS - To propagate the captured changes tp APPLY site
3 APPLY PROCESS - To apply captures changes on TARGET DB

The above processes are hosted on 2 QUEUES : CAPTURE QUEUE which hosts CAPTURE and PROPAGATION PROCESS and APPLY QUEUE which hosts APPLY PROCESS.

There is a fundamental FLAW with the above configuration for the CHOSEN DEPLOYMENT : DOWNSTREAM CAPTURE using ARCHIVE LOG SHIPPING for TWO SCHEMAS.

Since the CAPTURE and APPLY process will both be hosted on TARGET DB so you do not require a PROPAGATION PROCESS as the latter is used only in the case where CAPTRUE PROCESS in hosted on SOURCE DB, a configuration for REAL TIME replication using LOG WRITER. Hence for our configuration we will OMIT the PROPAGATION PROCESS. Further we DO NOT require seperate queues to HOST CAPTURE and APPLY PROCESS. A single queue is sufficient to host both CAPTURE and APPLY PROCESS.

The above change , once in place, will ensure a succesful replication for more than one SCHEMA, without this configuration STREAMS REPLICATION will NEVER WORK for DOWNSTREAM CAPTURE using ARCHIVE LOG CAPTURE.

Secondly , the scary part for DBAs is to figure out how RAC will behave with STREAMS , what happens if one node fails, what impact would that have on the STREAMS configuration. Well, good news is that RAC handles this anomaly itself.
You just need to add the TNS entry of the TARGET DB on all nodes of RAC CLUSTER

Wait for my next article wherein I will explain the steps required to configure the STREAMS and some inetresting solutions to challenges faced in PRODUCTION STREAMS DEPLOYMENT.