Re: Oracle Streams / Change Data Capture Question

andrewah_at_hotmail.com wrote:
> Hi,
>
> I wondered if you could advise me on what Oracle feature to look into
> to help me decide upon a design issue?
>
> we have a corporate database - our main system - which is effectively
> the single repository for data in the organisation.
>
> A need for a publically available extranet has arisen, whose
> underlying database schema will contain a replica of some of the main
> system's data.
>
> About 10 tables have to be populated in the extranet's database. Their
> data will be derived from data stored in the main system. Some, but
> not all of these tables have a direct equivalent in the source system
> - ie the data that some tables will store is derived from a number of
> tables in our source system.
>
> If records in two of the tables in the main system are flagged to be
> pushed to the website's database by business users [which could happen
> at any time], it is possible to derive what the remainder of the data
> destined for the website's database should be.
>
> Clearly, it would be very simple to implement a push of data from the
> source to the destination database just by putting triggers on the two
> tables previously mentioned to capture changes to their records, using
> some queries to bring the data together, and then send these resulting
> datasets off to the destination system.
>
> The question I have is whether or not a feature like 'Oracle Streams'
> or 'Change Data Capture' would be useful in this situation? Is it
> possible to configure either of these features to recognise that when
> a change is made to a particular record, that Oracle should not only
> propogate this change to a destination system, but also propogate a
> related dataset as well? A feature like Streams would certainly
> simplify catering for situations where reference data in the main
> system changed after it was pushed to the destination.
>
> Any information would be appreciated,
>
> Cheers,
>
> Andrew.
>

If you don't need real-time data, consider doing ETL from source to target. This can simplify the maintenance tasks involved on a Streams and CDC scenario.

For loading data from one database to another you can take a look at the free warehouse builder also from oracle.

cheers,
pedro Received on Sat Oct 06 2007 - 05:42:37 CDT