Archive for the ‘Streams’ Category

Oracle Streams Basics (10g)

What Is Streams?

Oracle Streams captures database changes at a source database, stages the changes, propagates the changes to one or more destination databases, and then applies the changes at the destination database(s). Using Oracle Streams, enterprise systems can capture, propagate, and apply information as follows:

  • Within an Oracle database
  • Between two Oracle databases
  • Among multiple Oracle databases
  • Between an Oracle database and a non-Oracle database

Oracle9i Database introduced the Change Data Capture (CDC) feature.

Oracle 10g introduces Asynchronous Change Data Capture, which instead of using triggers uses the database log files to capture changes and apply them to collection tables. Asynchronous requires both source and destination databases to be Oracle 10g.


Streams Features

  • Distinct capture/propagation/apply services
  • Message Queueing (via AQ)
  • Publish Subscribe
  • Rules based Capture/Transform/Apply
  • Database integration
  • Easy configuration
  • Flexible
  • Inline transformations


Streams Overview

There are three basic tasks of a stream

  • Capture
  • Staging /Propagation
  • Apply / Consumption

Capture Process:

  • A capture process captures a database change(DML or DDL changes from Redo logs) and converts it into a specific message format called a logical change record (LCR) or user messages.
  • After capturing an LCR, a capture process enqueues a message containing the LCR into a queue(local or remote).
  • Captured LCRs can be sent to queues in the same database or other databases by propagations.
  • A capture process running on source database is a local capture process while if it runs on a remote database, the capture process is a downstream capture process. (Check Local and Downstream Capture for Details

Further Readings:  Local and DownStream Capture(10g) , Summary of Information Capture Information in Oracle 11g

Staging Process:

  • Oracle Streams uses queues to stage messages. Staged messages can be consumed or propagated, or both.
  • Staged messages can be consumed by an apply process, a messaging client, or a user application.
  • A running apply process implicitly dequeues messages, but messaging clients and user applications explicitly dequeue messages.

Further Readings: Message Propagation between Queues,

Propagation Process:

  • Oracle Streams uses  Oracle scheduler jobs to propagate messages between two queues.
  • A propagation is always between a source queue and a destination queue.
  • Only one propagation is allowed between a particular source queue and a particular destination queue.
  • A single source queue can propagate messages to multiple destination queues, and a single destination queue can receive messages from multiple source queues. A single propagation can propagate LCRs and user messages.
  • A propagation either propagates or discards messages based on rules defined.
  • A propagation can be queue-to-queue or queue-to-database link. Propagation to the remote queue uses database links over Oracle Net Services.

Apply/Consumption Process:

  • An apply process dequeues messages (LCR or user messages) from a specific queue
  • An apply process either applies each message directly or passes it as a parameter to an apply handler. 
  • Auser-enqueued message dequeued by an apply process is of type ANYDATA and can contain any message, including an LCR or a user message.
  • An apply process can only dequeue messages from an ANYDATA queue, not a typed queue.

Further Readings: Message Processing with Apply Process

Streams Process Architecture

In simple five steps the whole Streams process is as follows:

  • Capture changes at a database(source/target)
  • Enqueue events in a queue
  • Propagate events from one queue to another
  • Dequeue events
  • Apply events at a database

Streams Rules

  • Rule is a database object that enables a client to perform an action when an event occurs and a condition is satisfied.
  • Rules are used to control which information to share and where to share it.
  • Rules can be used during capture, propagate and apply process.
  • Rule consists of Rule condition and optionally evaluation context and Action extent . It can be defined in three levels:
    1. Table
    2. Schema
    3. Global

Database Configuration

Following parameters should be set at each site:


DB parameters that must be set at source site:


DB parameters specific to propagation:


Databases capturing changes via Streams must be in archived mode whereas databases applying changes are not required in archived mode.

Oracle Streams Administration Tools

Oracle-Supplied PL/SQL Packages











Streams Data Dictionary Views

Streams Static Data Dictionary Views

Streams Dynamic Performance Views

Oracle Enterprise Manager Console

Oracle provides a Streams tool in the Oracle Enterprise Manager Console to help configure , administer and monitor Streams environments.