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, [email protected], 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 [email protected]
schema or replicate any changes that occur in [email protected]
schema. In other words, the capture, propagate and apply takes place in only
one direction from [email protected] to [email protected] 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 s
    etup)

  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
    )

Latest Articles