6 Configuring Queues and Propagations

The following topics describe configuring queues and propagations:

Creating an ANYDATA Queue
Creating Oracle Streams Propagations Between ANYDATA Queues

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:

Oracle Streams Concepts and Administration for more information about queues and propagations
"Configuring an Oracle Streams Administrator on All Databases"

Creating an ANYDATA Queue

A queue stores messages in an Oracle Streams environment. Messages can be enqueued, propagated from one queue to another, and dequeued. 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, synchronous capture, 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.

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

Complete the following tasks in "Tasks to Complete Before Configuring Oracle Streams Replication" you create an ANYDATA queue:
In SQL*Plus, connect to the database that will contain the queue as the Oracle Streams administrator.

See Oracle Database Administrator's Guide for instructions about connecting to a database in SQL*Plus.
Run the SET_UP_QUEUE procedure to create the queue:
```
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 in the strmadmin schema. 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 in the strmadmin schema. 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, then the sort_list setting is commit_time. If the database is a release before Oracle Database 10g Release 2, then 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:

You can configure an entire Oracle Streams environment, including queues, using procedures in the DBMS_STREAMS_ADM package or Oracle Enterprise Manager Cloud Control. See Chapter 2, "Simple Oracle Streams Replication Configuration".
A message cannot be enqueued unless a subscriber who can dequeue the message is configured.

See Also:

Oracle Streams Concepts and Administration
Oracle Database Advanced Queuing User's Guide
Oracle Database PL/SQL Packages and Types Reference for more information about the SET_UP_QUEUE, CREATE_QUEUE_TABLE, and CREATE_QUEUE procedures

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 Database 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.

This section contains the following topics:

Preparing to Create a Propagation
Creating a Propagation Using DBMS_STREAMS_ADM
Creating a Propagation Using DBMS_PROPAGATION_ADM

Note:

You can configure an entire Oracle Streams environment, including propagations, using procedures in the DBMS_STREAMS_ADM package or Oracle Enterprise Manager Cloud Control. See Chapter 2, "Simple Oracle Streams Replication Configuration".

See Also:

Oracle Streams Concepts and Administration
Oracle Database Advanced Queuing User's Guide for more information about configuring propagations with the features of Oracle Streams AQ and instructions about configuring propagations between typed queues

Preparing to Create a Propagation

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

Complete the following tasks in "Tasks to Complete Before Configuring Oracle Streams Replication":
- "Configuring an Oracle Streams Administrator on All Databases"
- "Configuring Network Connectivity and Database Links" if the propagation will send messages between databases
- "Setting Initialization Parameters Relevant to Oracle Streams"
- "Configuring the Oracle Streams Pool"
Create a source queue and a destination queue for the propagation, if they do not exist. Both queues must be ANYDATA queues. The examples in this chapter assume that the source queue is strmadmin.strm_a_queue and that the destination queue is strmadmin.strm_b_queue. See "Creating an ANYDATA Queue" for instructions.

Creating a Propagation Using DBMS_STREAMS_ADM

Complete the following steps to create a propagation using the ADD_TABLE_PROPAGATION_RULES procedure in the DBMS_STREAMS_ADM package:

Complete the tasks in "Preparing to Create a Propagation".
In SQL*Plus, connect to the database that contains the source queue for the propagation as the Oracle Streams administrator.

See Oracle Database Administrator's Guide for instructions about connecting to a database in SQL*Plus.
Run the ADD_TABLE_PROPAGATION_RULES procedure to create the 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 logical change records (LCRs) from strmadmin.strm_a_queue in the current database to strmadmin.strm_b_queue in the dbs2.example.com database. These queues must exist.
- 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. This database link must exist.
- 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 data manipulation language (DML) changes to the hr.departments table. The other rule evaluates to TRUE for DDL LCRs that contain data definition language (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.

See Also:

Oracle Streams Concepts and Administration for more information about queues, propagations, and rules
Chapter 10, "Oracle Streams Tags"

Creating a Propagation Using DBMS_PROPAGATION_ADM

Complete the following steps to create a propagation using the CREATE_PROPAGATION procedure in the DBMS_PROPAGATION_ADM package:

Complete the tasks in "Preparing to Create a Propagation".
In SQL*Plus, connect to the database that contains the source queue for the propagation as the Oracle Streams administrator.

See Oracle Database Administrator's Guide for instructions about connecting to a database in SQL*Plus.
Create the rule set that will be used by the propagation if it does not exist. In this example, assume that the rule set is strmadmin.strm01_rule_set. Optionally, you can also add rules to the rule set. See Oracle Streams Concepts and Administration for instructions.
Run the CREATE_PROPAGATION procedure to create the propagation:
```
BEGIN
  DBMS_PROPAGATION_ADM.CREATE_PROPAGATION(
    propagation_name   => 'strm02_propagation',
    source_queue       => 'strmadmin.strm_a_queue',
    destination_queue  => 'strmadmin.strm_b_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 strmadmin.strm_a_queue in the current database to strmadmin.strm_b_queue in the dbs2.example.com database. These queues must exist. 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. This database link must exist.
- Associates the propagation with the rule set named strmadmin.strm01_rule_set. This rule set must exist. 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.