3 Oracle Streams Staging and Propagation

The following topics contain conceptual information about staging messages in queues and propagating messages from one queue to another:

See Also:

Introduction to Message Staging and Propagation

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. Even after a message is consumed, it can remain in the queue if you also have configured an Oracle Streams propagation to propagate, or send, the message to one or more other queues or if message retention is specified for the queue. Message retention applies to messages captured by a synchronous capture or enqueued explicitly, but it does not apply to messages captured by a capture process.

Queues

A queue is an abstract storage unit used by a messaging system to store messages. This section includes the following topics:

See Also:

ANYDATA Queues and Typed Queues

A queue of ANYDATA type can stage messages of almost any type and is called an ANYDATA queue. A typed queue can stage messages of a specific type. Oracle Streams clients always use ANYDATA queues.

In an Oracle Streams replication environment, logical change records (LCRs) must be staged in ANYDATA queues. In an Oracle Streams messaging environment, both ANYDATA queues and typed queues can stage messages. Publishing applications can enqueue messages into a single queue, and subscribing applications can dequeue these messages.

Two types of messages can be encapsulated into an ANYDATA object and staged in an ANYDATA queue: LCRs and user messages. An LCR is an object that contains information about a change to a database object. A user message is a message of a user-defined type created by users or applications. Both types of messages can be used for information sharing within a single database or between databases.

ANYDATA queues can stage user messages whose payloads are of ANYDATA type. An ANYDATA payload can be a wrapper for payloads of different data types.

By using ANYDATA wrappers for message payloads, publishing applications can enqueue messages of different types into a single queue, and subscribing applications can dequeue these messages, either explicitly using a messaging client or an application, or implicitly using an apply process. If the subscribing application is remote, then the messages can be propagated to the remote site, and the subscribing application can dequeue the messages from a local queue in the remote database. Alternatively, a remote subscribing application can dequeue messages directly from the source queue using a variety of standard protocols, such as PL/SQL and OCI.

You can wrap almost any type of payload in an ANYDATA payload. To do this, you use the Convertdata_type static functions of the ANYDATA type, where data_type is the type of object to wrap. These functions take the object as input and return an ANYDATA object.

Oracle Streams includes the features of Oracle Streams Advanced Queuing (AQ), which supports all the standard features of message queuing systems, including multiconsumer queues, publish and subscribe, content-based routing, internet propagation, transformations, and gateways to other messaging subsystems.

See Also:

Persistent Queues and Buffered Queues

Oracle Streams supports the following message modes:

  • Persistent messaging: Messages are always stored on disk in a database table called a queue table. This type of storage is sometimes called persistent queue storage.

  • Buffered messaging: Messages are stored in memory but can spill to a queue table under certain conditions. This type of storage is sometimes called buffered queue storage. The memory includes Oracle Streams pool memory that is associated with a queue that contains messages that were captured by a capture process or enqueued by applications.

Buffered queues enable Oracle to optimize messages by buffering them in the System Global Area (SGA) instead of always storing them in a queue table. Buffered messaging provides better performance, but it does not support some messaging features, such as message retention. Message retention lets you specify the amount of time a message is retained in the queue table after being dequeued.

If the size of the Oracle Streams pool is not managed automatically, then you should increase the size of the Oracle 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. Oracle 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.

LCRs that were captured by a capture process are always stored in a buffered queue, but LCRs that were captured by a synchronous capture are always stored in a persistent queue. Other types of messages might or might not be stored in a buffered queue. When an application enqueues a message, the enqueue operation specifies whether the enqueued message is stored in the buffered queue or in the persistent queue. 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.

Note:

Although buffered and persistent messages can be stored in the same queue, it is sometimes more convenient to think of a queue having a buffered portion and a persistent portion, referred to here as "buffered queue" and "persistent queue." Also, both ANYDATA queues and typed queues can include both a buffered queue and a persistent queue.

See Also:

Queues and Oracle Streams Clients

Oracle Streams clients always use ANYDATA queues. The following sections discuss how queues interact with Oracle Streams clients:

See Also:

Queues and Capture Processes

A capture processes can only enqueue LCRs into a buffered queue. LCRs enqueued into a buffered queue by a capture process can be dequeued only by an apply process. Captured LCRs cannot be dequeued by applications or users.

Queues and Synchronous Capture

A synchronous capture can only enqueue LCRs into a persistent queue. LCRs captured by synchronous capture can be dequeued by apply processes, messaging clients, applications, and users.

Queues and Propagations

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

Queues and Apply Processes

A single apply process can either dequeue messages from a buffered queue or a persistent queue, but not both. Apply processes can dequeue and process captured LCRs in a buffered queue. To dequeue captured LCRs, the apply process must be configured with the apply_captured parameter set to TRUE. Apply processes cannot dequeue buffered LCRs or buffered user messages. To dequeue persistent LCRs or persistent user messages, the apply process must be configured with the apply_captured parameter set to FALSE.

Queues and Messaging Clients

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

Note:

The DBMS_AQ and DBMS_AQADM packages support buffered messaging.

See Also:

Oracle Streams Advanced Queuing User's Guide for more information about using the DBMS_AQ and DBMS_AQADM packages

Secure Queues

Secure queues are queues for which Oracle Streams Advanced Queuing (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 Oracle Streams, secure queues can be used to ensure that only the appropriate users and Oracle Streams clients enqueue messages and dequeue messages.

Secure Queues and the SET_UP_QUEUE Procedure

All ANYDATA queues created using the SET_UP_QUEUE procedure in the DBMS_STREAMS_ADM package are secure queues. When you use the SET_UP_QUEUE procedure to create a queue, any user specified by the queue_user parameter is configured as a secure queue user of the queue automatically, if possible. The queue user is also granted ENQUEUE and DEQUEUE privileges on the queue. To enqueue messages and dequeue messages, a queue user must also have EXECUTE privilege on the DBMS_STREAMS_MESSAGING package or the DBMS_AQ package. The SET_UP_QUEUE procedure does not grant either of these privileges. Also, a message cannot be enqueued unless a subscriber who can dequeue the message is configured.

To configure a queue user as a secure queue user, the SET_UP_QUEUE procedure creates an Oracle Streams AQ agent with the same name as the user name, if one does not already exist. The user must use this agent to perform queue operations on the queue. If an agent with this name already exists and is associated with the queue user only, then the existing agent is used. SET_UP_QUEUE then runs the ENABLE_DB_ACCESS procedure in the DBMS_AQADM package, specifying the agent and the user.

If you use the SET_UP_QUEUE procedure in the DBMS_STREAMS_ADM package to create a secure queue, and you want a user who is not the queue owner and who was not specified by the queue_user parameter to perform operations on the queue, then you can configure the user as a secure queue user of the queue manually. Alternatively, you can run the SET_UP_QUEUE procedure again and specify a different queue_user for the queue. In this case, SET_UP_QUEUE skips queue creation, but it configures the user specified by queue_user as a secure queue user of the queue.

If you create an ANYDATA queue using the DBMS_AQADM package, then you use the secure parameter when you run the CREATE_QUEUE_TABLE procedure to specify whether the queue is secure or not. The queue is secure if you specify TRUE for the secure parameter when you run this procedure. When you use the DBMS_AQADM package to create a secure queue, and you want to allow users to perform queue operations on the secure queue, you must configure these secure queue users manually.

Secure Queues and Oracle Streams Clients

When you create a capture process or an apply process, an Oracle Streams AQ agent of the secure queue associated with the Oracle Streams process is configured automatically, and the user who runs the Oracle Streams process is specified as a secure queue user for this queue automatically. Therefore, a capture process is configured to enqueue into its secure queue automatically, and an apply process is configured to dequeue from its secure queue automatically. In either case, the Oracle Streams AQ agent has the same name as the Oracle Streams client.

For a capture process, the user specified as the capture_user is the user who runs the capture process. For an apply process, the user specified as the apply_user is the user who runs the apply process. If no capture_user or apply_user is specified, then the user who invokes the procedure that creates the Oracle Streams process is the user who runs the Oracle Streams process.

When you create a synchronous capture, an Oracle Streams AQ agent of the secure queue with the same name as the synchronous capture is associated with the user specified as the capture_user. If no capture_user is specified, then the user who invokes the procedure that creates the synchronous capture is the capture_user. The capture_user is specified as a secure queue user for this queue automatically. Therefore, the synchronous capture can enqueue into its secure queue automatically.

If you change the capture_user for a capture process or synchronous capture or the apply_user for an apply process, then the specified capture_user or apply_user is configured as a secure queue user of the queue used by the Oracle Streams client. However, the old capture user or apply user remains configured as a secure queue user of the queue. To remove the old user, run the DISABLE_DB_ACCESS procedure in the DBMS_AQADM package, specifying the old user and the relevant Oracle Streams AQ agent. You might also want to drop the agent if it is no longer needed. You can view the Oracle Streams AQ agents and their associated users by querying the DBA_AQ_AGENT_PRIVS data dictionary view.

When you create a messaging client, an Oracle Streams AQ agent of the secure queue with the same name as the messaging client is associated with the user who runs the procedure that creates the messaging client. This messaging client user is specified as a secure queue user for this queue automatically. Therefore, this user can use the messaging client to dequeue messages from the queue.

A capture process, a synchronous capture, an apply process, or a messaging client can be associated with only one user. However, one user can be associated with multiple Oracle Streams clients, including multiple capture processes, synchronous captures, apply processes, and messaging clients. For example, an apply process cannot have both hr and oe as apply users, but hr can be the apply user for multiple apply processes.

If you drop a capture process, synchronous capture, apply process, or messaging client, then the users who were configured as secure queue users for these Oracle Streams clients remain secure queue users of the queue. To remove these users as secure queue users, run the DISABLE_DB_ACCESS procedure in the DBMS_AQADM package for each user. You might also want to drop the agent if it is no longer needed.

Note:

No configuration is necessary for propagations and secure queues. Therefore, when a propagation is dropped, no additional steps are necessary to remove secure queue users from the propagation's queues.

See Also:

Transactional and Nontransactional Queues

A transactional queue is a queue in which 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 messages in a group. A nontransactional queue is one in which each message is its own transaction. That is, an apply process performs a COMMIT after each message it applies. In either case, the messages can be LCRs or user messages.

The SET_UP_QUEUE procedure in the DBMS_STREAMS_ADM package always creates a transactional queue. The difference between transactional and nontransactional queues is important only for messages that were enqueued by an application, a synchronous capture, or an apply process. An apply process always applies captured LCRs 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.

Table 3-1 Apply Process Behavior for Transactional and Nontransactional Queues

Message Type Transactional Queue Nontransactional Queue

Captured LCRs

Apply process preserves the original transaction.

Apply process preserves the original transaction.

Persistent LCRs or Persistent User Messages

Apply process applies a user-specified group of messages as one transaction.

Apply process applies each message in its own transaction.

When it is important to preserve the transactions executed at the source database, use transactional queues to store the messages. Ensure that LCRs captured by synchronous captures are stored in transactional queues.

See Also:

Commit-Time Queues

You can control the order in which messages in a persistent queue are browsed or dequeued. Message ordering in a queue is determined by its queue table, and you can specify message ordering for a queue table during queue table creation. Specifically, the sort_list parameter in the DBMS_AQADM.CREATE_QUEUE_TABLE procedure determines how messages are ordered. Each message in a commit-time queue is ordered by an approximate commit system change number (approximate CSCN), which is obtained when the transaction that enqueued each message commits.

Commit-time ordering is specified for a queue table, and queues that use the queue table are called commit-time queues. When commit_time is specified for the sort_list parameter in the DBMS_AQADM.CREATE_QUEUE_TABLE procedure, the resulting queue table uses commit-time ordering.

For Oracle Database 10g Release 2 and later, the default sort_list setting for queue tables created by the SET_UP_QUEUE procedure in the DBMS_STREAMS_ADM package is commit_time. For releases prior to Oracle Database 10g Release 2, the default is enq_time, which is described in the section that follows. When the queue_table parameter in the SET_UP_QUEUE procedure specifies an existing queue table, message ordering in the queue created by SET_UP_QUEUE is determined by the existing queue table.

Note:

A synchronous capture always enqueues into a commit-time queue to ensure that transactions are ordered properly.

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 at an Oracle database messages that originated at a non-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 messages:

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.

See Also:

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. For example, a database trigger can fire to enqueue messages. Figure 3-1 shows how enqueue time ordering does not support transactional dependency ordering during dequeue of such messages.

Figure 3-1 Transactional Dependency Violation During Dequeue

Description of Figure 3-1 follows
Description of "Figure 3-1 Transactional Dependency Violation During Dequeue"

Figure 3-1 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-2 shows how enqueue time ordering does not support consistent browse of messages in a queue.

Figure 3-2 Inconsistent Browse of Messages in a Queue

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

Figure 3-2 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.

Note:

The sort_list parameter in CREATE_QUEUE_TABLE can be set to the following: 
priority, commit_time

In this case, ordering is done by priority first and commit time second. Therefore, this setting does not ensure transactional dependency ordering and browse read consistency for messages with different priorities. However, transactional dependency ordering and browse read consistency are ensured for messages with the same priority.

See Also:

"Creating an ANYDATA Queue" for information about creating a commit-time queue

Message Propagation Between Queues

You can use Oracle Streams to configure message propagation between two queues. These queues can reside in the same database or in different databases. Oracle Streams uses Oracle Scheduler jobs to propagate messages.

A propagation is always between a source queue and a destination queue. Although propagation is always between two queues, a single queue can participate in many propagations. That is, a single source queue can propagate messages to multiple destination queues, and a single destination queue can receive messages from multiple source queues. Also, a single queue can be a destination queue for some propagations and a source queue for other propagations. However, only one propagation is allowed between a particular source queue and a particular destination queue.

Figure 3-3 shows propagation from a source queue to a destination queue.

Figure 3-3 Propagation from a Source Queue to a Destination Queue

Description of Figure 3-3 follows
Description of "Figure 3-3 Propagation from a Source Queue to a Destination Queue"

You can create, alter, and drop a propagation, and you can define propagation rules that control which messages are propagated. The user who owns the source queue is the user who propagates messages, and this user must have the necessary privileges to propagate messages. These privileges include the following:

  • EXECUTE privilege on the rule sets used by the propagation

  • EXECUTE privilege on all custom rule-based transformation functions used in the rule sets

  • Enqueue privilege on the destination queue if the destination queue is in the same database

If the propagation propagates messages to a destination queue in a remote database, then the owner of the source queue must be able to use the database link used by the propagation, and the user to which the database link connects at the remote database must have enqueue privilege on the destination queue.

A propagation can propagate all of the messages in a source queue to a destination queue, or a propagation can propagate only a subset of the messages. A single propagation can propagate messages in both the buffered queue portion and persistent queue portion of a queue. Also, a single propagation can propagate LCRs and user messages. You can use rules to control which messages in the source queue are propagated to the destination queue and which messages are discarded.

Depending on how you set up your Oracle Streams environment, changes could be sent back to the site where they originated. You must ensure that your environment is configured to avoid cycling a change in an endless loop. You can use Oracle Streams tags to avoid such a change cycling loop.

The following sections describe propagations in more detail:

See Also:

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. For user messages, you can create rules to control propagation behavior for specific types of messages. 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.

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.

See Also:

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.

See Also:

Ensured Message Delivery

A captured LCR 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.

Any other type of message is propagated successfully to a destination queue when the enqueue into the destination queue is committed. Other types of messages include buffered LCRs, buffered user messages, persistent LCRs, and buffered user messages.

When a message is successfully propagated between two 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 become larger than an optimal size. When a source queue increases, it uses more System Global Area (SGA) memory and might use more disk space.

There are two common reasons for a source queue to become larger:

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

  • Suppose a source queue is propagating messages captured by a capture process or synchronous capture 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 or synchronous capture 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.

    See Also:

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 Oracle 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-4 shows an example of a directed networks environment.

Figure 3-4 Example Directed Networks Environment

Description of Figure 3-4 follows
Description of "Figure 3-4 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 Oracle Streams environment.

Queue Forwarding and Apply Forwarding

An intermediate database in a directed network can propagate messages using either queue forwarding or apply forwarding. Queue forwarding means that the messages being forwarded at an intermediate database are the messages received by the intermediate database. The source database for a message is the database where the message originated.

Apply forwarding means that the messages being forwarded at an intermediate database are first processed by an apply process. These messages are then recaptured by a capture process or a synchronous capture at the intermediate database and forwarded. When you use apply forwarding, the intermediate database becomes the new source database for the messages. Either a capture process recaptures the messages from the redo log generated at the intermediate database, or a synchronous capture configured at the intermediate database recaptures the messages.

Consider the following differences between queue forwarding and apply forwarding when you plan your Oracle Streams environment:

  • With queue forwarding, a message is propagated through the directed network without being changed, assuming there are no capture or propagation transformations. With apply forwarding, messages are applied and recaptured at intermediate databases and can be changed by conflict resolution, apply handlers, or apply transformations.

  • With queue forwarding, a destination database must have a separate apply process to apply messages from each source database. With apply forwarding, fewer apply processes might be required at a destination database because recapturing of messages at intermediate databases can result in fewer source databases when changes reach a destination database.

  • With queue forwarding, one or more intermediate databases are in place between a source database and a destination database. With apply forwarding, because messages are recaptured at intermediate databases, the source database for a message can be the same as the intermediate database connected directly with the destination database.

A single Oracle Streams environment can use a combination of queue forwarding and apply forwarding.

Advantages of Queue Forwarding

Queue forwarding has the following advantages compared with apply forwarding:

  • Performance might be improved because a message is captured only once.

  • Less time might be required to propagate a message from the database where the message originated to the destination database, because the messages are not applied and recaptured at one or more intermediate databases. In other words, latency might be lower with queue forwarding.

  • The origin of a message can be determined easily by running the GET_SOURCE_DATABASE_NAME member procedure on the LCR contained in the message. If you use apply forwarding, then determining the origin of a message requires the use of Oracle Streams tags and apply handlers.

  • Parallel apply might scale better and provide more throughput when separate apply processes are used because there are fewer dependencies, and because there are multiple apply coordinators and apply reader processes to perform the work.

  • If one intermediate database goes down, then you can reroute the queues and reset the start SCN at the capture site to reconfigure end-to-end capture, propagation, and apply.

    If you use apply forwarding, then substantially more work might be required to reconfigure end-to-end capture, propagation, and apply of messages, because the destination database(s) downstream from the unavailable intermediate database were using the SCN information of this intermediate database. Without this SCN information, the destination databases cannot apply the changes properly.

Advantages of Apply Forwarding

Apply forwarding has the following advantages compared with queue forwarding:

  • An Oracle Streams environment might be easier to configure because each database can apply changes only from databases directly connected to it, rather than from multiple remote source databases.

  • In a large Oracle Streams environment where intermediate databases apply changes, the environment might be easier to monitor and manage because fewer apply processes might be required. An intermediate database that applies changes must have one apply process for each source database from which it receives changes. In an apply forwarding environment, the source databases of an intermediate database are only the databases to which it is directly connected. In a queue forwarding environment, the source databases of an intermediate database are all of the other source databases in the environment, whether they are directly connected to the intermediate database or not.

See Also:

Binary File Propagation

You can propagate a binary file between databases by using Oracle 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 Oracle Streams has the same restrictions as the procedure DBMS_FILE_TRANSFER.PUT_FILE.

See Also:

Oracle Database Administrator's Guide, and Oracle Database PL/SQL Packages and Types Reference for more information about transferring files with the DBMS_FILE_TRANSFER package

Propagation Jobs

An Oracle Streams propagation is configured internally using Oracle Scheduler. Therefore, a propagation job is a job that propagates messages from a source queue to a destination queue. Like other Oracle Scheduler jobs, propagation jobs have an owner, and they use slave 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 slave 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.

This section contains the following topics:

Note:

The source queue owner performs the propagation, but the propagation job is owned by the user who creates it. These two users might or might not be the same.

See Also:

Propagation Scheduling and Oracle Streams Propagations

A propagation schedule specifies how often a propagation job propagates messages from a source queue to a destination queue. Each queue-to-queue propagation has its own propagation job and propagation schedule, but queue-to-dblink propagations that use the same propagation job have the same propagation schedule.

A default propagation schedule is established when a new propagation job is created by a procedure in the DBMS_STREAMS_ADM or DBMS_PROPAGATION_ADM package.

The default schedule has the following properties:

  • The start time is SYSDATE().

  • The duration is NULL, which means infinite.

  • The next time is NULL, which means that propagation restarts as soon as it finishes the current duration.

  • The latency is three seconds, which is the wait time after a queue becomes empty to resubmit the propagation job. Therefore, the latency is the maximum wait, in seconds, in the propagation window for a message to be propagated after it is enqueued.

You can alter the schedule for a propagation job using the ALTER_PROPAGATION_SCHEDULE procedure in the DBMS_AQADM package. Changes made to a propagation job affect all propagations that use the propagation job.

See Also:

Propagation Jobs and RESTRICTED SESSION

When the restricted session is enabled during system startup by issuing a STARTUP RESTRICT statement, propagation jobs with enabled propagation schedules do not propagate messages. When the restricted session is disabled, each propagation schedule that is enabled and ready to run will run when there is an available slave process.

When the restricted session is enabled in a running database by the SQL statement ALTER SYSTEM ENABLE RESTRICTED SESSION, any running propagation job continues to run to completion. However, any new propagation job submitted for a propagation schedule is not started. Therefore, propagation for an enabled schedule can eventually come to a halt.

Oracle Streams Data Dictionary for Propagations

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

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

The Oracle Streams data dictionary information contained in these internal messages in a queue might or might not be propagated by a propagation. Which Oracle Streams data dictionary information to propagate depends on the rule sets for the propagation. When a propagation encounters Oracle 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 Oracle Streams data dictionary information for the table is propagated.

When Oracle Streams data dictionary information is propagated to a destination queue, it is incorporated into the Oracle 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 Oracle Streams data dictionary to be populated. In this way, the Oracle 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.

See Also:

Combined Capture and Apply Optimization

In Oracle Database 11g Release 1 (11.1) and later, for improved efficiency, a capture process can act as a propagation sender to transmit logical change records (LCRs) directly to a propagation receiver under specific conditions. The propagation receiver enqueues the LCRs into the buffered queue portion of the destination queue, and an apply process dequeues the LCRs. This configuration is called combined capture and apply.

The following sections describe combined capture and apply in more detail:

See Also:

Chapter 22, "Monitoring the Oracle Streams Topology and Performance" for information about monitoring the topology and performance of an environment that uses combined capture and apply

Combined Capture and Apply Requirements

Combined capture and apply can be used when the capture process and apply process reside on the same database or on different databases.

When the capture process and apply process reside on the same database, combined capture and apply is possible only if all of the following conditions are met:

  • The database must be an Oracle Database 11g Release 1 (11.1) or later database.

  • The capture process and apply process must use the same queue.

  • The queue must have a single publisher, and it must be the capture process.

  • The queue must have a single consumer for the buffered queue, and it must be the apply process. The queue can have one or more other apply processes that are consumers for the persistent queue.

When the capture process and apply process reside on the different databases, or on different instances in the same database, combined capture and apply is possible only if all of the following conditions are met:

  • The database running the capture process and the database running the apply process each must be an Oracle Database 11g Release 1 (11.1) or later database.

  • The capture process queue must have a single publisher, and it must be the capture process.

  • A propagation must be configured between the capture process queue and the apply process queue. There can be no intermediate queues (no directed network).

  • The capture process queue must have a single consumer, and it must be the propagation between the capture process queue and the apply process queue.

  • The apply process queue must have a single publisher, and it must the propagation between the capture process queue and the apply process queue.

  • The apply process queue must have a single consumer, and it must be the apply process. The queue can have one or more other apply processes that are consumers for the persistent queue.

Note:

Combined capture and apply is not possible with synchronous capture.

See Also:

How to Use Combined Capture and Apply

After you meet the requirements for combined capture and apply, you do not need to perform any other configuration tasks to use it. The capture process automatically detects that combined capture and apply is possible when it is started. After it is establishes a connection with the propagation receiver, it sends captured LCRs directly to the propagation receiver.

If combined capture and apply is used, and you change the configuration so that it no longer meets the requirements of combined capture and apply, then the capture process detects this change and restarts. After the capture process restarts, it no longer uses combined capture and apply.

If combined capture and apply is not used, and you change the configuration so that it meets the requirements of combined capture and apply, then combined capture and apply is used automatically when the capture process is restarted. In this case, you must restart the capture process manually. It is not restarted automatically.

See Also:

"Combined Capture and Apply Requirements"

How to Determine Whether Combined Capture and Apply Is Being Used

Check the following dynamic performance views to determine whether combined capture and apply is used:

  • For the capture process, combined capture and apply is used when the OPTIMIZATION column is greater than zero in the V$STREAMS_CAPTURE view.

  • For the apply process, combined capture and apply is used when the PROXY_SID column is not NULL in the V$STREAMS_APPLY_READER view.

See Also:

How to Manage the Flow of LCRs From the Capture Process to the Apply Process

The capture process uses the propagation rule sets to determine which LCRs to send to the apply process. You can modify the propagation rule sets to control which LCRs are sent. In addition, rule-based transformations that are configured for the rules in the positive propagation rule set are run when a rule evaluates to TRUE.

To stop the flow of LCRs, either stop or unschedule the propagation. To start the flow of LCRs, either start or schedule the propagation. However, changes to the parameters in the propagation schedule are ignored. In addition, the following views contain no information about the propagation when the propagation is replaced by combined capture and apply:

  • DBA_QUEUE_SCHEDULES

  • USER_QUEUE_SCHEDULES

Instead, query the following views for information about the propagation:

  • V$PROPAGATION_SENDER

  • V$PROPAGATION_RECEIVER

When combined capture and apply is used, you can manage capture processes and apply processes normally. Specifically, you control capture process and apply process behavior in the following ways:

  • Changes must satisfy the capture process rules sets to be captured by the capture process.

  • LCRs must satisfy the apply process rule sets to be applied by the apply process.

  • Rule-based transformations that are configured for the rules in the positive rule set of a capture process or apply process are run when a rule evaluates to TRUE.

  • LCRs are sent to apply handlers for an apply process when appropriate.

  • Update conflict resolution handlers are invoked when appropriate during apply.

See Also:

Combined Capture and Apply and Point-in-Time Recovery

When you use combined capture and apply in a single-source replication environment, the Oracle Streams clients handle point-in-time recovery of the destination database automatically. The Oracle Streams client include the capture process, propagation, and apply process that form the combined capture and apply configuration.

In a single-source replication environment that uses combined capture and apply, complete these general steps to perform point-in-time recovery on the destination database:

  1. Stop the capture process and apply process, and disable the propagation.

  2. Perform the point-in-time recovery on the destination database.

  3. Ensure that the capture process has access to the archived redo log files for the previous point in time.

  4. Start the apply process.

  5. Enable the propagation.

  6. Start the capture process.

When you follow these steps, the capture process determines its start SCN automatically, and no other steps are required.

See Also:

Oracle Streams Replication Administrator's Guide for more information about performing point-in-time recovery in an Oracle Streams replication environment