Skip Headers
Oracle® Streams Concepts and Administration
11g Release 1 (11.1)

B28321-03
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

11 Configuring Queues and Propagations

The following topics describe configuring queues, propagations, and messaging environments:

Each task described in this chapter should be completed by an Oracle Streams administrator that has been granted the appropriate privileges, unless specified otherwise.

See Also:

Configuring Queues

A queue stores messages in an Oracle Streams environment. Messages can be enqueued, propagated from one queue to another, and dequeued.

This section contains instructions for completing the following tasks related to queues:

Note:

You can configure an entire Oracle Streams environment, including queues, using procedures in the DBMS_STREAMS_ADM package or Oracle Enterprise Manager

Creating an ANYDATA Queue

An ANYDATA queue stores messages whose payloads are of ANYDATA type. Therefore, an ANYDATA queue can store a message with a payload of nearly any type, if the payload is wrapped in an ANYDATA wrapper. Each Oracle Streams capture process, apply process, and messaging client is associated with one ANYDATA queue, and each Oracle Streams propagation is associated with one ANYDATA source queue and one ANYDATA destination queue.

The easiest way to create an ANYDATA queue is to use the SET_UP_QUEUE procedure in the DBMS_STREAMS_ADM package. This procedure enables you to specify the following settings for the ANYDATA queue it creates:

  • The queue table for the queue

  • A storage clause for the queue table

  • The queue name

  • A queue user that will be configured as a secure queue user of the queue and granted ENQUEUE and DEQUEUE privileges on the queue

  • A comment for the queue

If the specified queue table does not exist, then it is created. If the specified queue table exists, then the existing queue table is used for the new queue. If you do not specify any queue table when you create the queue, then, by default, streams_queue_table is specified.

You can use a single procedure, the SET_UP_QUEUE procedure in the DBMS_STREAMS_ADM package, to create an ANYDATA queue and the queue table used by the queue. For SET_UP_QUEUE to create a new queue table, the specified queue table must not exist.

For example, run the following procedure to create an ANYDATA queue with the SET_UP_QUEUE procedure:

BEGIN
  DBMS_STREAMS_ADM.SET_UP_QUEUE(
    queue_table => 'strmadmin.streams_queue_table',
    queue_name  => 'strmadmin.streams_queue',
    queue_user  => 'hr');
END;
/

Running this procedure performs the following actions:

  • Creates a queue table named streams_queue_table. The queue table is created only if it does not already exist. Queues based on the queue table store messages of ANYDATA type. Queue table names can be a maximum of 24 bytes.

  • Creates a queue named streams_queue. The queue is created only if it does not already exist. Queue names can be a maximum of 24 bytes.

  • Specifies that the streams_queue queue is based on the strmadmin.streams_queue_table queue table.

  • Configures the hr user as a secure queue user of the queue, and grants this user ENQUEUE and DEQUEUE privileges on the queue.

  • Starts the queue.

Default settings are used for the parameters that are not explicitly set in the SET_UP_QUEUE procedure.

When the SET_UP_QUEUE procedure creates a queue table, the following DBMS_AQADM.CREATE_QUEUE_TABLE parameter settings are specified:

  • If the database is Oracle Database 10g Release 2 or later, the sort_list setting is commit_time. If the database is a release prior to Oracle Database 10g Release 2, the sort_list setting is enq_time.

  • The multiple_consumers setting is TRUE.

  • The message_grouping setting is transactional.

  • The secure setting is TRUE.

The other parameters in the CREATE_QUEUE_TABLE procedure are set to their default values.

You can use the CREATE_QUEUE_TABLE procedure in the DBMS_AQADM package to create a queue table of ANYDATA type with different properties than the default properties specified by the SET_UP_QUEUE procedure in the DBMS_STREAMS_ADM package. After you create the queue table with the CREATE_QUEUE_TABLE procedure, you can create a queue that uses the queue table. To do so, specify the queue table in the queue_table parameter of the SET_UP_QUEUE procedure.

Similarly, you can use the CREATE_QUEUE procedure in the DBMS_AQADM package to create a queue instead of SET_UP_QUEUE. Use CREATE_QUEUE if you require custom settings for the queue. For example, use CREATE_QUEUE to specify a custom retry delay or retention time. If you use CREATE_QUEUE, then you must start the queue manually.

Note:

A message cannot be enqueued unless a subscriber who can dequeue the message is configured.

Creating Oracle Streams Propagations Between ANYDATA Queues

A propagation sends messages from an Oracle Streams source queue to an Oracle Streams destination queue. In addition, you can use the features of Oracle Streams Advanced Queuing (AQ) to manage Oracle Streams propagations.

You can use any of the following procedures to create a propagation between two ANYDATA queues:

Each of these procedures in the DBMS_STREAMS_ADM package creates a propagation with the specified name if it does not already exist, creates either a positive rule set or negative rule set for the propagation if the propagation does not have such a rule set, and can add table rules, schema rules, or global rules to the rule set. The CREATE_PROPAGATION procedure creates a propagation, but does not create a rule set or rules for the propagation. However, the CREATE_PROPAGATION procedure enables you to specify an existing rule set to associate with the propagation, either as a positive or a negative rule set. All propagations are started automatically upon creation.

The following tasks must be completed before you create a propagation:

This section contains the following topics:

Note:

You can configure an entire Oracle Streams environment, including propagations, using procedures in the DBMS_STREAMS_ADM package or Oracle Enterprise Manager

See Also:

Example of Configuring a Propagation Using DBMS_STREAMS_ADM

The following example runs the ADD_TABLE_PROPAGATION_RULES procedure in the DBMS_STREAMS_ADM package to create a propagation:

BEGIN
  DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
    table_name              => 'hr.departments',
    streams_name            => 'strm01_propagation',
    source_queue_name       => 'strmadmin.strm_a_queue',
    destination_queue_name  => 'strmadmin.strm_b_queue@dbs2.example.com',
    include_dml             => TRUE,
    include_ddl             => TRUE,
    include_tagged_lcr      => FALSE,
    source_database         => 'dbs1.example.com',
    inclusion_rule          => TRUE,
    queue_to_queue          => TRUE);
END;
/

Running this procedure performs the following actions:

  • Creates a propagation named strm01_propagation. The propagation is created only if it does not already exist.

  • Specifies that the propagation propagates LCRs from strm_a_queue in the current database to strm_b_queue in the dbs2.example.com database.

  • Specifies that the propagation uses the dbs2.example.com database link to propagate the LCRs, because the destination_queue_name parameter contains @dbs2.example.com.

  • Creates a positive rule set and associates it with the propagation because the inclusion_rule parameter is set to TRUE. The rule set uses the evaluation context SYS.STREAMS$_EVALUATION_CONTEXT. The rule set name is system generated.

  • Creates two rules. One rule evaluates to TRUE for row LCRs that contain the results of DML changes to the hr.departments table. The other rule evaluates to TRUE for DDL LCRs that contain DDL changes to the hr.departments table. The rule names are system generated.

  • Adds the two rules to the positive rule set associated with the propagation. The rules are added to the positive rule set because the inclusion_rule parameter is set to TRUE.

  • Specifies that the propagation propagates an LCR only if it has a NULL tag, because the include_tagged_lcr parameter is set to FALSE. This behavior is accomplished through the system-created rules for the propagation.

  • Specifies that the source database for the LCRs being propagated is dbs1.example.com, which might or might not be the current database. This propagation does not propagate LCRs in the source queue that have a different source database.

  • Creates a propagation job for the queue-to-queue propagation.

Note:

To use queue-to-queue propagation, the compatibility level must be 10.2.0 or higher for each database that contains a queue involved in the propagation.

Example of Configuring a Propagation Using DBMS_PROPAGATION_ADM

The following example runs the CREATE_PROPAGATION procedure in the DBMS_PROPAGATION_ADM package to create a propagation:

BEGIN
  DBMS_PROPAGATION_ADM.CREATE_PROPAGATION(
    propagation_name   => 'strm02_propagation',
    source_queue       => 'strmadmin.strm03_queue',
    destination_queue  => 'strmadmin.strm04_queue',
    destination_dblink => 'dbs2.example.com',
    rule_set_name      => 'strmadmin.strm01_rule_set',
    queue_to_queue     => TRUE);
END;
/

Running this procedure performs the following actions:

  • Creates a propagation named strm02_propagation. A propagation with the same name must not exist.

  • Specifies that the propagation propagates messages from strm03_queue in the current database to strm04_queue in the dbs2.example.com database. Depending on the rules in the rule sets for the propagation, the propagated messages can be LCRs or user messages, or both.

  • Specifies that the propagation uses the dbs2.example.com database link to propagate the messages.

  • Associates the propagation with an existing rule set named strm01_rule_set. This rule set is the positive rule set for the propagation.

  • Creates a propagation job for the queue-to-queue propagation.

Note:

To use queue-to-queue propagation, the compatibility level must be 10.2.0 or higher for each database that contains a queue involved in the propagation.