3 Streams Staging and Propagation

Propagation Rules

A propagation either propagates or discards messages based on rules that you define. For LCRs, each rule specifies the database objects and types of changes for which the rule evaluates to TRUE. You can place these rules in a positive rule set or a negative rule set used by the propagation.

If a rule evaluates to TRUE for a message, and the rule is in the positive rule set for a propagation, then the propagation propagates the change. If a rule evaluates to TRUE for a message, and the rule is in the negative rule set for a propagation, then the propagation discards the change. If a propagation has both a positive and a negative rule set, then the negative rule set is always evaluated first.

You can specify propagation rules for LCRs at the following levels:

• A table rule propagates or discards either row changes resulting from DML changes or DDL changes to a particular table. Subset rules are table rules that include a subset of the row changes to a particular table.

• A schema rule propagates or discards either row changes resulting from DML changes or DDL changes to the database objects in a particular schema.

• A global rule propagates or discards either all row changes resulting from DML changes or all DDL changes in the source queue.

For non-LCR messages, you can create your own rules to control propagation.

A queue subscriber that specifies a condition causes the system to generate a rule. The rule sets for all subscribers to a queue are combined into a single system-generated rule set to make subscription more efficient.

Queue-to-Queue Propagations

A propagation can be queue-to-queue or queue-to-database link (queue-to-dblink). A queue-to-queue propagation always has its own exclusive propagation job to propagate messages from the source queue to the destination queue. Because each propagation job has its own propagation schedule, the propagation schedule of each queue-to-queue propagation can be managed separately. Even when multiple queue-to-queue propagations use the same database link, you can enable, disable, or set the propagation schedule for each queue-to-queue propagation separately. Propagation jobs are described in detail later in this chapter.

A single database link can be used by multiple queue-to-queue propagations. The database link must be created with the service name specified as the global name of the database that contains the destination queue.

In contrast, a queue-to-dblink propagation shares a propagation job with other queue-to-dblink propagations from the same source queue that use the same database link. Therefore, these propagations share the same propagation schedule, and any change to the propagation schedule affects all of the queue-to-dblink propagations from the same source queue that use the database link.

Queue-to-queue propagation connects to the destination queue service when one exists. Currently, a queue service is created when the database is a Real Application Clusters (RAC) database and the queue is a buffered queue. Because the queue service always runs on the owner instance of the queue, transparent failover can occur when RAC instances fail. When multiple queue-to-queue propagations use a single database link, the connect description for each queue-to-queue propagation changes automatically to propagate messages to the correct destination queue. In contrast, queue-to-dblink propagations require you to repoint your database links if the owner instance in a RAC database that contains the destination queue for the propagation fails.

Ensured Message Delivery

A user-enqueued message is propagated successfully to a destination queue when the enqueue into the destination queue is committed. A captured message is propagated successfully to a destination queue when both of the following actions are completed:

• The message is processed by all relevant apply processes associated with the destination queue.

• The message is propagated successfully from the destination queue to all of its relevant destination queues.

When a message is successfully propagated between two ANYDATA queues, the destination queue acknowledges successful propagation of the message. If the source queue is configured to propagate a message to multiple destination queues, then the message remains in the source queue until each destination queue has sent confirmation of message propagation to the source queue. When each destination queue acknowledges successful propagation of the message, and all local consumers in the source queue database have consumed the message, the source queue can drop the message.

This confirmation system ensures that messages are always propagated from the source queue to the destination queue, but, in some configurations, the source queue can grow larger than an optimal size. When a source queue grows, it uses more SGA memory and might use more disk space.

There are two common reasons for source-queue growth:

• If a message cannot be propagated to a specified destination queue for some reason (such as a network problem), then the message will remain in the source queue until the destination queue becomes available. This situation could cause the source queue to grow large. So, you should monitor your queues regularly to detect problems early.

• Suppose a source queue is propagating captured messages to multiple destination queues, and one or more destination databases acknowledge successful propagation of messages much more slowly than the other queues. In this case, the source queue can grow because the slower destination databases create a backlog of messages that have already been acknowledged by the faster destination databases. In such an environment, consider creating more than one capture process to capture changes at the source database. Doing so lets you use one source queue for the slower destination databases and another source queue for the faster destination databases.

Directed Networks

A directed network is one in which propagated messages pass through one or more intermediate databases before arriving at a destination database. A message might or might not be processed by an apply process at an intermediate database. Using Streams, you can choose which messages are propagated to each destination database, and you can specify the route that messages will traverse on their way to a destination database. Figure 3-2 shows an example of a directed networks environment.

Figure 3-2 Example Directed Networks Environment

Description of "Figure 3-2 Example Directed Networks Environment"

The advantage of using a directed network is that a source database does not need to have a physical network connection with a destination database. So, if you want messages to propagate from one database to another, but there is no direct network connection between the computers running these databases, then you can still propagate the messages without reconfiguring your network, as long as one or more intermediate databases connect the source database to the destination database.

If you use directed networks, and an intermediate site goes down for an extended period of time or is removed, then you might need to reconfigure the network and the Streams environment.

Binary File Propagation

You can propagate a binary file between databases by using Streams. To do so, you put one or more BFILE attributes in a message payload and then propagate the message to a remote queue. Each BFILE referenced in the payload is transferred to the remote database after the message is propagated, but before the message propagation is committed. The directory object and filename of each propagated BFILE are preserved, but you can map the directory object to different directories on the source and destination databases. The message payload can be a BFILE wrapped in an ANYDATA payload, or the message payload can be one or more BFILE attributes of an object wrapped in an ANYDATA payload.

The following are not supported in a message payload:

• One or more BFILE attributes in a varray

• A user-defined type object with an ANYDATA attribute that contains one or more BFILE attributes

Propagating a BFILE in Streams has the same restrictions as the procedure DBMS_FILE_TRANSFER.PUT_FILE.

Buffered Messages and Capture Processes

Messages enqueued into a buffered queue by a capture process can be dequeued only by an apply process. Captured messages cannot be dequeued by users or applications.

Buffered Messages and Propagations

A propagation will propagate any messages in its source queue that satisfy its rule sets. These messages can be stored in a buffered queue or stored persistently in a queue table. A propagation can propagate both types of messages if the messages satisfy the rule sets used by the propagation.

Buffered Messages and Apply Processes

Apply processes can dequeue and process messages in a buffered queue. To dequeue messages in a buffered queue that were enqueued by a capture process, the apply process must be configured with the apply_captured parameter set to true. To dequeue messages in a buffered queue that were enqueued by a user or application, the apply process must be configured with the apply_captured parameter set to false. An apply process sends user-enqueued messages to its message handler for processing.

Buffered Messages and Messaging Clients

Currently, messaging clients cannot dequeue buffered messages. In addition, the DBMS_STREAMS_MESSAGING package cannot be used to enqueue messages into or dequeue messages from a buffered queue.

When to Use Commit-Time Queues

A user or application can share information by enqueuing messages into a queue in an Oracle database. The enqueued messages can be shared within a single database or propagated to other databases, and the messages can be LCRs or user messages. For example, messages can be enqueued when an application-specific message occurs or when a trigger is fired for a database change. Also, in a heterogeneous environment, an application can enqueue messages that originated at a non-Oracle database into a queue in an Oracle database.

Other than commit_time, the settings for the sort_list parameter in the CREATE_QUEUE_TABLE procedure are priority and enq­_time. The priority setting orders messages by the priority specified during enqueue, highest priority to lowest priority. The enq_time setting orders messages by the time when they were enqueued, oldest to newest.

Commit-time queues are useful when an environment must support either of the following requirements for concurrent enqueues of user-enqueued messages:

• Transactional Dependency Ordering During Dequeue

• Consistent Browse of Messages in a Queue

Commit-time queues support these requirements. Neither priority nor enqueue time ordering supports these requirements because both allow transactional dependency violations and nonconsistent browses. Both settings allow transactional dependency violations, because messages are dequeued independent of the original dependencies. Also, both settings allow nonconsistent browses of the messages in a queue, because multiple browses performed without any dequeue operations between them can result in different sets of messages.

Transactional Dependency Ordering During Dequeue

A transactional dependency occurs when one database transaction requires that another database transaction commits before it can commit successfully. Messages that contain information about database transactions can be enqueued into a queue. For example, a database trigger can fire to enqueue messages. Figure 3-4 shows how enqueue time ordering does not support transactional dependency ordering during dequeue of such messages.

Figure 3-4 Transactional Dependency Violation During Dequeue

Description of "Figure 3-4 Transactional Dependency Violation During Dequeue"

Figure 3-4 shows how transactional dependency ordering can be violated with enqueue time ordering. The transaction that enqueued message e2 was committed before the transaction that enqueued messages e1 and e3 was committed, and the update in message e3 depends on the insert in message e2. So, the correct dequeue order that supports transactional dependencies is e2, e1, e3. However, with enqueue time ordering, e3 can be dequeued before e2. Therefore, when e3 is dequeued, an error results when an application attempts to apply the change in e3 to the hr.employees table. Also, after all three messages are dequeued, a row in the hr.employees table contains the wrong information because the change in e3 was not executed.

Consistent Browse of Messages in a Queue

Figure 3-5 shows how enqueue time ordering does not support consistent browse of messages in a queue.

Figure 3-5 Inconsistent Browse of Messages in a Queue

Description of "Figure 3-5 Inconsistent Browse of Messages in a Queue"

Figure 3-5 shows that a client browsing messages in a queue is not guaranteed a definite order with enqueue time ordering. Sessions 1 and 2 are concurrent sessions that are enqueuing messages. Session 3 shows two sets of client browses that return the three enqueued messages in different orders. If the client requires deterministic ordering of messages, then the client might fail. For example, the client might perform a browse to initiate a program state, and a subsequent dequeue might return messages in a different order than expected.

How Commit-Time Queues Work

The commit system change number (CSCN) for a message that is enqueued into a queue is not known until the redo record for the commit of the transaction that includes the message is written to the redo log. The CSCN cannot be recorded when the message is enqueued. Commit-time queues use the current SCN of the database when a transaction is committed as the approximate CSCN for all of the messages in the transaction. The order of messages in a commit-time queue is based on the approximate CSCN of the transaction that enqueued the messages.

In a commit-time queue, messages in a transaction are not visible to dequeue and browse operations until a deterministic order for the messages can be established using the approximate CSCN. When multiple transactions are enqueuing messages concurrently into the same commit-time queue, two or more transactions can commit at nearly the same time, and the commit intervals for these transactions can overlap. In this case, the messages in these transactions are not visible until all of the transactions have committed. At that time, the order of the messages can be determined using the approximate CSCN of each transaction. Dependencies are maintained by using the approximate CSCN for messages rather than the enqueue time. Read consistency for browses is maintained by ensuring that only messages with a fully determined order are visible.

A commit-time queue always maintains transactional dependency ordering for messages that are based on database transactions. However, applications and users can enqueue messages that are not based on database transactions. For these messages, if dependencies exist between transactions, then the application or user must ensure that transactions are committed in the correct order and that the commit intervals of the dependent transactions do not overlap.

The approximate CSCNs of transactions recorded by a commit-time queue might not reflect the actual commit order of these transactions. For example, transaction 1 and transaction 2 can commit at nearly the same time after enqueuing their messages. The approximate CSCN for transaction 1 can be lower than the approximate CSCN for transaction 2, but transaction 1 can take more time to complete the commit than transaction 2. In this case, the actual CSCN for transaction 2 is lower than the actual CSCN for transaction 1.

priority, commit_time

Streams Pool

The Streams pool is a portion of memory in the System Global Area (SGA) that is used by Streams. The Streams pool stores buffered queue messages in memory, and it provides memory for capture processes and apply processes. The Streams pool always stores LCRs captured by a capture process, and it stores LCRs and messages that are enqueued into a buffered queue by applications or users.

The Streams pool is initialized the first time any one of the following actions occur in a database:

• A message is enqueued into a buffered queue. Data Pump export and import operations initialize the Streams pool because these operations use buffered queues.

• A capture process is started.

• An apply process is started.

The size of the Streams pool is determined in one of the following ways:

• Streams Pool Size Set by Automatic Shared Memory Management

• Streams Pool Size Set Manually by a Database Administrator

• Streams Pool Size Set by Default

Buffered Queues

A buffered queue includes the following storage areas:

• Streams pool memory associated with a queue that contains messages that were captured by a capture process or enqueued by applications or users

• Part of a queue table that stores messages that have spilled from memory to disk

Queue tables are stored on disk. Buffered queues enable Oracle to optimize messages by buffering them in the SGA instead of always storing them in a queue table.

If the size of the Streams pool is not managed automatically, then you should increase the size of the Streams pool by 10 MB for each buffered queue in a database. Buffered queues improve performance, but some of the information in a buffered queue can be lost if the instance containing the buffered queue shuts down normally or abnormally. Streams automatically recovers from these cases, assuming full database recovery is performed on the instance.

Messages in a buffered queue can spill from memory into the queue table if they have been staged in the buffered queue for a period of time without being dequeued, or if there is not enough space in memory to hold all of the messages. Messages that spill from memory are stored in the appropriate AQ$_queue_table_name_p table, where queue_table_name is the name of the queue table for the queue. Also, for each spilled message, information is stored in the AQ$_queue_table_name_d table about any propagations and apply processes that are eligible for processing the message.

Captured messages are always stored in a buffered queue, but user-enqueued LCRs and user-enqueued non-LCR messages might or might not be stored in a buffered queue. For a user-enqueued message, the enqueue operation specifies whether the enqueued message is stored in the buffered queue or in the persistent queue. A persistent queue only stores messages on hard disk in a queue table, not in memory. The delivery_mode attribute in the enqueue_options parameter of the DBMS_AQ.ENQUEUE procedure determines whether a message is stored in the buffered queue or the persistent queue. Specifically, if the delivery_mode attribute is the default PERSISTENT, then the message is enqueued into the persistent queue. If it is set to BUFFERED, then the message is enqueued as the buffered queue. When a transaction is moved to the error queue, all messages in the transaction always are stored in a queue table, not in a buffered queue.

Propagation Jobs

A Streams propagation is configured internally using the DBMS_JOB package. Therefore, a propagation job is a job used by a propagation that propagates messages from a source queue to a destination queue. Like other jobs configured using the DBMS_JOB package, propagation jobs have an owner, and they use job queue processes (Jnnn) as needed to execute jobs.

The following procedures can create a propagation job when they create a propagation:

• The ADD_GLOBAL_PROPAGATION_RULES procedure in the DBMS_STREAMS_ADM package

• The ADD_SCHEMA_PROPAGATION_RULES procedure in the DBMS_STREAMS_ADM package

• The ADD_TABLE_PROPAGATION_RULES procedure in the DBMS_STREAMS_ADM package

• The ADD_SUBSET_PROPAGATION_RULE procedure in the DBMS_STREAMS_ADM package

• The CREATE_PROPAGATION procedure in the DBMS_PROPAGATION_ADM package

When one of these procedures creates a propagation, a new propagation job is created in the following cases:

• If the queue_to_queue parameter is set to true, then a new propagation job always is created for the propagation. Each queue-to-queue propagation has its own propagation job. However, a job queue process can be used by multiple propagation jobs.

• If the queue_to_queue parameter is set to false, then a propagation job is created when no propagation job exists for the source queue and database link specified. If a propagation job already exists for the specified source queue and database link, then the new propagation uses the existing propagation job and shares this propagation job with all of the other queue-to-dblink propagations that use the same database link.

A propagation job for a queue-to-dblink propagation can be used by more than one propagation. All destination queues at a database receive messages from a single source queue through a single propagation job. By using a single propagation job for multiple destination queues, Streams ensures that a message is sent to a destination database only once, even if the same message is received by multiple destination queues in the same database. Communication resources are conserved because messages are not sent more than once to the same database.

Secure Queues

Secure queues are queues for which AQ agents must be associated explicitly with one or more database users who can perform queue operations, such as enqueue and dequeue. The owner of a secure queue can perform all queue operations on the queue, but other users cannot perform queue operations on a secure queue, unless they are configured as secure queue users. In Streams, secure queues can be used to ensure that only the appropriate users and Streams clients enqueue messages into a queue and dequeue messages from a queue.

Transactional and Nontransactional Queues

A transactional queue is a queue in which user-enqueued messages can be grouped into a set that are applied as one transaction. That is, an apply process performs a COMMIT after it applies all the user-enqueued messages in a group. The SET_UP_QUEUE procedure in the DBMS_STREAMS_ADM package always creates a transactional queue.

A nontransactional queue is one in which each user-enqueued message is its own transaction. That is, an apply process performs a COMMIT after each user-enqueued message it applies. In either case, the user-enqueued messages might or might not contain user-created LCRs.

The difference between transactional and nontransactional queues is important only for user-enqueued messages. An apply process always applies captured messages in transactions that preserve the transactions executed at the source database. Table 3-1 shows apply process behavior for each type of message and each type of queue.

Streams Data Dictionary for Propagations

When a database object is prepared for instantiation at a source database, a Streams data dictionary is populated automatically at the database where changes to the object are captured by a capture process. The Streams data dictionary is a multiversioned copy of some of the information in the primary data dictionary at a source database. The Streams data dictionary maps object numbers, object version information, and internal column numbers from the source database into table names, column names, and column datatypes. This mapping keeps each captured message as small as possible, because the message can store numbers rather than names internally.

The mapping information in the Streams data dictionary at the source database is needed to evaluate rules at any database that propagates the captured messages from the source database. To make this mapping information available to a propagation, Oracle automatically populates a multiversioned Streams data dictionary at each database that has a Streams propagation. Oracle automatically sends internal messages that contain relevant information from the Streams data dictionary at the source database to all other databases that receive captured messages from the source database.

The Streams data dictionary information contained in these internal messages in a queue might or might not be propagated by a propagation. Which Streams data dictionary information to propagate depends on the rule sets for the propagation. When a propagation encounters Streams data dictionary information for a table, the propagation rule sets are evaluated with partial information that includes the source database name, table name, and table owner. If the partial rule evaluation of these rule sets determines that there might be relevant LCRs for the given table from the specified database, then the Streams data dictionary information for the table is propagated.

When Streams data dictionary information is propagated to a destination queue, it is incorporated into the Streams data dictionary at the database that contains the destination queue, in addition to being enqueued into the destination queue. Therefore, a propagation reading the destination queue in a directed networks configuration can forward LCRs immediately without waiting for the Streams data dictionary to be populated. In this way, the Streams data dictionary for a source database always reflects the correct state of the relevant database objects for the LCRs relating to these database objects.