ORACLE STREAMS and ETL. May 21,  2004 John Garmany

 

ORACLE STREAMS

An oracle stream, a new feature in oracle9i release 2, is an information-sharing technology, which allows the propagation and management of data, event, and transactions within oracle databases or between oracle and non-oracle databases. Oracle streams is flexible in the sense that it allows user-intervention- users can specify what information goes into the stream, the route of flow, what happens to events in the stream and how the stream terminates. It is used to capture events such as DML (insert, update and delete) and DDL (alter, drop, rename) operations.
However, very fundamental to the working of oracle stream are three elements/components namely

• Capture

• Staging

• Apply

Figure 2. Architecture of oracle stream.

The capture process

The capture process is responsible for the identification of data to capture such as database changes (DDL and DML) and application generated messages. We can have either implicit capture in which the server captures DML and DDL events at a source database using oracle's default rules or explicit capture, in which a customized configuration is used to capture data using procedures.

Furthermore, the change process formats the retrieved data into events called logical change records (LCR) and they are then placed in a queue- staging environment for onward processing. Logical change records are of two types- DDL LCR and row LCR. DDL LCR refers to changes made in the database objects by issuing ALTER, RENAME, CREATE or DROP commands. Row LCR on the other hand, refers to the modification of a single row of a table by a single DML statement. This implies that updating 10 rows in a table using a single DML statement generates 10 row LCRs.

The staging process

The staging area is a queue and acts as a temporary repository for logical change records (LCR) until they are subscribed to. The subscriber (a user application or another staging area or default apply process) has control over the contents of the staging area. Therefore, the subscriber can decide which records are propagated or consumed from the queue. For events propagation from a queue to take place, a user must be the owner of the queue and appropriate privileges are needed, not only on the source queue but also on the target queue. Moreover, a particular target queue can accept events from more than one source queues

The apply process

The apply process is responsible for applying the changes to the target database. This is possible in two ways namely- Default consumption (implicit) and customized consumption (explicit). In default consumption, the apply engine is used to apply the changes to the database directly. Per adventure if a conflict occurs, the apply engine resolves it by invoking resolution (data transmission) routines.

In customized consumption, the logical change records are passed as arguments to a user-defined function for processing. If the customized procedure process DML LCRs, DDL LCRs and enqueued messages, they are called DML handlers, DDL handlers and message handlers respectively.