Streams advice

Hi all,

Oracle 10.2.0.3.0 RAC on multiple Linux boxes.

I am still at the RTFM (Oracle Streams Replication Admin Guide) stage of investigating the use of Streams to enhance a current brute force partial replication of data from an OLTP source database to a data warehouse reporting repository.

Both source and destination databases are on the same RAC cluster, but we will probably also want to additionally replicate to our dev copy of the destination database on a separate (same version and O/S) RAC cluster.

Current brute force replication involves a daily full copy of about of 3-4 gig of data, with only 2-3% of it likely to have actually changed. We are still at an early stage of establishing our DW and volumes are likely to increase substantially, so the current method is unlikely to scale well over time.

What appears to be our likely method is archive-log downstream capture (to minimise business hours load on the OLTP instances) and using the PREPARE_TABLE_INSTANTIATION options (as capture is required for only about half the columns of about a fifth of the tables in a single source schema).

If this proves to be successful, data from several other source Oracle RAC services will also be switched to Streams replication to the same destination.

Any early advice as to whether this appears appropriate, or any known gotchas, would be greatly appreciated.

Geoff M Received on Mon Aug 20 2007 - 21:06:29 CDT