Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Mar 29, 2003

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

By DatabaseJournal.com Staff

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.


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


    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)

Oracle Archives

Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM