Change data capture implementation in Oracle Data warehouses – Part 2 – Oracle Streams implementation

In part-1 of the series, we
discussed the overall view of Oracle Streams and the process of information
sharing between databases. Part-2 of this article, presents a scenario to
demonstrate a simple and “straight-forward” configuration for Oracle Streams.

Oracle Streams Configuration

In this example scenario, we use two databases (both Oracle
9.2.0) “local.world” and “remote.world”. Any changes (DML) in the schema, DEMO@local.world, will be setup to be captured,
propagated and applied to the corresponding tables in the schema DEMO at ” remote.world.”

This configuration does take into consideration any DDL changes
that might take place in the DEMO@local.world
schema or replicate any changes that occur in DEMO@remote.world
schema. In other words, the capture, propagate and apply takes place in only
one direction from DEMO@local.world to DEMO@remote.world and does not include any
DDL changes.

However, it will give a brief idea as to how Oracle streams
is configured automatically using the Default replication rules implemented
using Oracle supplied PL/SQL package DBMS_STREAMS_ADM.

Requirements:

Oracle9i (9.2.0) Enterprise edition.

Preliminary steps

The preliminary steps in creating an Oracle Streams environment
in brief are as follows:

1. Verify
   the initialization parameters for the databases involved in the Streams setup.

2. Start
   the database (where changes are captured) in the ARCHIVELOG MODE and specify
   supplemental logging at the “source” database using

   ALTER DATABASE ADD SUPPLEMENTAL LOG DATA
   (PRIMARY KEY, UNIQUE INDEX) COLUMNS;

   Supplemental
   logging though is optional and depends on configuration. Refer to PART-1 of the series for information on use of
   Supplemental logging.

3. Create
   a Streams administrator user at the source and destination database
   respectively, granting appropriate privileges.

4. Establish
   a database link between the two databases from the source database (and also from the destination database if changes are to
   be propagated from the destination database to other subscriber databases.)

5. Create
   a Streams queue in the Streams administrator schema of both, the source and the
   destination databases respectively (and any other
   databases involved in the Streams setup)

6. Create
   the Capture, Propagate and Apply rules in the Streams administrator
   schemas of all the databases involved.

7. Grant
   appropriate privileges to the Objects / schema for which the apply rules
   have been setup, to apply the DDL or DML changes.

8. Set
   up the instantiation SCNs for the source database tables (where changes are
   captured) at the other databases tables (where changes are applied).

   For details on database object SCN
   instantiation in Streams environment, refer to http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96571/apply.htm#55587

9. Start the processes. (Note: The
   Apply/ propagation processes should exist and be associated with a rule that
   handles the events, before creating or starting a Capture process for the said
   event, to avoid loosing events)