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
ANYDATAand can contain any message, including an LCR or a user message. - An apply process can only dequeue messages from an
ANYDATAqueue, 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:
- Table
- Schema
- Global
Database Configuration
Following parameters should be set at each site:
- COMPATIBLE
- PROCESSES
- SHARED_POOL_SIZE
- STREAMS_POOL_SIZE
- TIMED_STATISTICS
DB parameters that must be set at source site:
- LOG_ARCHIVE_DEST_n
- LOG_ARCHIVE_DEST_STATE_n
- REMOTE_ARCHIVE_ENABLE
- UNDO_RETENTION
DB parameters specific to propagation:
- GLOBAL_NAMES
- JOB_QUEUE_PROCESSES
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.
Mohib Alvi 