0 Replies Latest reply on Jul 27, 2012 7:02 AM by 952094

    Can Streams capture DML changes and populate a queue table with the changes

      I currently have a business requirement where we need to replicate our DB DML changes to a certain set of tables from one source DB to multiple destination DB's. The network between the source and destination DB's is highly unreliable and hence we cannot use any out of the box Oracle offerings for replication due to the bad network.

      Can we achieve the following through Oracle streams?

      1)     Use Oracle streams to capture the DML changes to a certain set of tables in Oracle DB and then push these DML changes to an Oracle AQ queue table. What format do we need to set on the AQ queue table for this? Should it be ANYDATA or it can be one related to jms messages so that we can achieve step 2 described below.

      2)     Our J2ee MDB on weblogic using foreign server, will read these messages from the AQ Queue table by de-queuing the queue table and propagate it to the destination database and en-queue the AQ-Queue table out there in the destination database environment. We have a way of doing this without depending on the network and doing a manual export and import of jms messages already ready and hence we want to leverage it.

      3)     On the destination side we will again use Oracle streams to take these messages from destination database AQ Queue table and persist the changes to the table and achieve data replication.

      Can anyone point to resources/online links which outline how we can do either all the steps or at least one of the three steps/code or provide steps to achieve all or any of the steps?