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
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.
Oracle9i (9.2.0) Enterprise edition.
The preliminary steps in creating an Oracle Streams environment
in brief are as follows:
the initialization parameters for the databases involved in the Streams setup.
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;
logging though is optional and depends on configuration. Refer to PART-1 of the series for information on use of
a Streams administrator user at the source and destination database
respectively, granting appropriate privileges.
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.)
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)
the Capture, Propagate and Apply rules in the Streams administrator
schemas of all the databases involved.
appropriate privileges to the Objects / schema for which the apply rules
have been setup, to apply the DDL or DML changes.
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
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)