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)

Previous Next

[1] [2] [3] [4] [5] [6] Next