RelationalDBDesign RelationalDBDesign 

Advanced Queuing   «Prev 

Propagation: What is best suited for an Oracle DBA

Oracle has many replication mechanisms that require data propagation:
  1. Snapshot and Multimaster replication: Uses snapshots for data propagation.
  2. Streams Replication: Uses Oracle queues (Asynchronous change data capture) for data propagation. You define the propagation rules using the dbms_repcat package.
  3. Oracle Data Guard replication - Uses redo logs for data propagation.

Staging and Propagation

The captured LCRs must be made available to the subscribers and consumers. Oracle Streams achieves this by staging the LCRs for propagation.


All captured messages are stored in a staging area. The staging area is an in-memory buffer and is part of the system global area (SGA) of the database instance. Messages created by user applications are also stored in this staging area. The LCRs created by the synchronous capture process are not stored in the memory queue, but they are stored in a disk queue table. The messages remain in the staging area until consumed by the subscribers. The subscriber can read the contents of the staging area to select messages of their interest. The subscriber can be an application, a staging area, or the apply process of a different database. The applications can explicitly dequeue or read the message from the staging area to consume it. If the subscriber to this staging area is an apply process, then the messages will be dequeued and applied by the apply process.


Messages in one staging area can be propagated to another staging area in another database using database links over Oracle Net. Streams offers a great deal of flexibility in choosing how messages are routed. Rules can be applied during propagation to select which messages will be propagated to another staging area. You can modify existing propagation rules or define your own rules. In some cases, the propagation may not be required. The messages can be dequeued by the consumer from the same staging area where the capture process created the message. In this case, the publisher and consumer processes run in the same database.

Oracle 12c Performance Tuning
Before Propagation a message cue contains several messages

In this example, propagation is triggered by a specific call of the SCHEDULE_PROPAGATION procedure

The other queues automatically receive copies of the messages from the originating queue