Oracle Streams

Oracle Streams allows data propagation between Oracle Databases (homogeneous) and between Oracle and non-Oracle databases (heterogeneous environment).

Oracle Streams can be used for:

• Replication
• Message Queuing
• Loading data into a Data Warehouse
• Event Notification
• Data Protection

History

Oracle Streams was first shipped with Oracle 9i release 9.2.

Oracle Streams is only available with the Oracle Database Enterprise Edition.

Oracle 11gR2 is the last release supporting Streams. Oracle now recommends to use Golden Gate.

Components of Oracle Streams

Oracle Streams consists of the following components:

Capture

Database changes (DDL and DML) are captured from the redo logs and packaged into Logical Change Records or LCRs. The LCRs are then moved into the Staging environment. Data and events may be changed or formatted by a predefined set of rules before they are packaged into an LCR.

Your database must be in ARCHIVE log mode.

alter database archivelog;

Additionally, you may also need to enable supplemental logging for the database to ensure all changes are captured in the log files:

alter database add supplemental log data (primary key, unique index) columns;

You also need to enable force logging to ensure NOLOGGING operations are considered.

alter database force logging;

Create a queue table - used to queue captured changes:

BEGIN
  DBMS_STREAMS_ADM.SET_UP_QUEUE(
    queue_table    => 'strm_scott_qtable',
    storage_clause => 'storage (initial 10M next 10M maxextents unlimited)',
    queue_name     => 'strm_scott_queue',
    queue_user     => 'scott');
END;
/

Staging

LCRs are stored in the Staging environment until a subscriber picks them up to be used or consumed. The subscriber may be another staging environment or a user application.

Consumption

During Consumption, LCRs are picked up and applied to a database. Consumption allows the LCR to be modified before it is applied to the database.

Some Streams Notes

DECLARE iscn NUMBER;
BEGIN
  iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
  DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@A(
    source_object_name => 'schema.table',
    source_database_name => 'B',
    instantiation_scn => iscn);
END;
/

BEGIN
  DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION(table_name => \'schema.table\');
END;
/

External links

• Oracle Streams feature overview
• Oracle Golden Gate