4
Managing AQ

This chapter discusses the following topics related to managing Advanced Queuing:

• Security
• Oracle 8.1-Style Queues
• Queue Table Export-Import
• Oracle Enterprise Manager Support
• Using Advanced Queuing with XA
• Restrictions on Queue Management
• Propagation Issues
• Oracle 8.0-Style Queues

Security

Configuration information can be managed through procedures in the DBMS_AQADM package. Initially, only SYS and SYSTEM have execution privilege for the procedures in DBMS_AQADM and DBMS_AQ. Users who have been granted EXECUTE rights to these two packages will be able to create, manage, and use queues in their own schemas. Users also need the MANAGE ANY QUEUE privilege to create and manage queues in other schemas.

Users of the JMS or Java AQ APIs will need EXECUTE privileges on DBMS_AQJMS (also available through AQ_ADMINSTRATOR_ROLE and AQ_USER_ROLE) and DBMS_AQIN.

Administrator Role

The AQ_ADMINISTRATOR_ROLE has all the required privileges to administer queues. The privileges granted to the role let the grantee:

• Perform any queue administrative operation, including create queues and queue tables on any schema in the database
• Perform enqueue and dequeue operations on any queues in the database
• Access statistics views used for monitoring the queue workload
• Create transformations using DBMS_TRANSFORM
• Execute all procedures in DBMS_AQELM
• Execute all procedures in DBMS_AQJMS

User Role

You should avoid granting AQ_USER_ROLE in Oracle9i and 8.1 since this role will not provide sufficient privileges for enqueuing or dequeuing on Oracle9i or 8.1-compatible queues.

Your database administrator has the option of granting the system privileges ENQUEUE ANY QUEUE and DEQUEUE ANY QUEUE, exercising DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE and DBMS_AQADM.REVOKE_SYSTEM_PRIVILEGE directly to a database user, if you want the user to have this level of control. You as the application developer give rights to a queue by granting and revoking privileges at the object level by exercising DBMS_AQADM.GRANT_QUEUE_PRIVILEGE and DBMS_AQADM.REVOKE_QUEUE_PRIVILEGE.

As a database user, you do not need any explicit object-level or system-level privileges to enqueue or dequeue to queues in your own schema other than the execute right on DBMS_AQ.

Access to AQ Object Types

All internal AQ objects are now accessible to PUBLIC.

Oracle 8.1-Style Queues

Compatibility

For 8.1-style queues, the compatible parameter of init.ora and the compatible parameter of the queue table should be set to 8.1 to use the following features:

• Queue-level access control
• Nonpersistent queues (automatically created when queue table compatible = 8.1)
• Support for Oracle Parallel Server environments
• Rule-based subscribers for publish-subscribe
• Asynchronous notification
• Sender identification
• Separate storage of history management information

Security

AQ administrators of an Oracle9i database can create 8.1-style queues. All 8.1 security features are enabled for 8.1-style queues. Note that AQ 8.1 security features work only with 8.1-style queues. When you create queues, the default value of the compatible parameter in DBMS_AQADM.CREATE_QUEUE_TABLE is 8.1.

Table 4-1 lists the AQ security features and privilege equivalences supported with 8.1-style queues.

Table 4-1 Security with 8.1-Style Queues

Privileges and Access Control

You can grant or revoke privileges at the object level on 8.1- style queues. You can also grant or revoke various system-level privileges. The following table lists all common AQ operations and the privileges need to perform these operations for an Oracle9i or 8.1-compatible queue:

Table 4-2 Operations and Required Privileges

LNOCI Applications

For an OCI application to access an 8.1-style queue, the session user has to be granted either the object privilege of the queue he intends to access or the ENQUEUE ANY QUEUE or DEQUEUE ANY QUEUE system privileges. The EXECUTE right of DBMS_AQ will not be checked against the session user's rights if the queue he intends to access is an Oracle9i or 8.1-compatible queue.

Security Required for Propagation

AQ propagates messages through database links. The propagation driver dequeues from the source queue as owner of the source queue; hence, no explicit access rights have to be granted on the source queue. At the destination, the login user in the database link should either be granted ENQUEUE ANY QUEUE privilege or be granted the rights to enqueue to the destination queue. However, if the login user in the database link also owns the queue tables at the destination, no explicit AQ privileges need to be granted.

Queue Table Export-Import

When a queue table is exported, the queue table data and anonymous blocks of PL/SQL code are written to the export dump file. When a queue table is imported, the import utility executes these PL/SQL anonymous blocks to write the metadata to the data dictionary.

Exporting Queue Table Data

The export of queues entails the export of the underlying queue tables and related dictionary tables. Export of queues can only be done at queue-table granularity.

Exporting Queue Tables with Multiple Recipients

A queue table that supports multiple recipients is associated with the following tables:

• A dequeue index-organized table (IOT)
• A time-management index-organized table
• A subscriber table (for 8.1-compatible queue tables)
• A history index-organized table (for 8.1-compatible queue tables)

These tables are exported automatically during full database mode and user mode exports, but not during table mode export. See "Export Modes".

Because the metadata tables contain rowids of some rows in the queue table, the import process will generate a note about the rowids being obsoleted when importing the metadata tables. This message can be ignored, since the queuing system will automatically correct the obsolete rowids as a part of the import operation. However, if another problem is encountered while doing the import (such as running out of rollback segment space), you should correct the problem and repeat the import.

Export Modes

Exporting operates in full database mode, user mode, and table mode, as follows. Incremental exports on queue tables are not supported.

• Full database mode--Queue tables, all related tables, system-level grants, and primary and secondary object grants are exported automatically.
• User mode--Queue tables, all related tables, and primary object grants are exported automatically.
• Table mode--This mode is not recommended. If you need to export a queue table in table mode, you must export all related objects that belong to that queue table. For example, when exporting an 8.1-compatible multiconsumer queue table MCQ, you must also export the following tables:

  AQ$_<queue_table>_I (the dequeue IOT)  
  AQ$_<queue_table>_T (the time-management IOT)
  AQ$_<queue_table>_S (the subscriber table)
  AQ$_<queue_table>_H (the history IOT)
  

Importing Queue Table Data

Similar to exporting queues, importing queues entails importing the underlying queue tables and related dictionary data. After the queue table data is imported, the import utility executes the PL/SQL anonymous blocks in the dump file to write the metadata to the data dictionary.

Importing Queue Tables with Multiple Recipients

A queue table that supports multiple recipients is associated with the following tables:

• A dequeue IOT
• A time-management IOT
• A subscriber table (for 8.1-compatible queue tables)
• A history IOT (for 8.1-compatible queue tables)

These tables must be imported as well as the queue table itself.

Import IGNORE Parameter

You should not import queue data into a queue table that already contains data. The IGNORE parameter of the import utility should always be set to NO when importing queue tables. If the IGNORE parameter is set to YES, and the queue table that already exists is compatible with the table definition in the dump file, then the rows will be loaded from the dump file into the existing table. At the same time, the old queue table definition and the old queue definition will be dropped and re-created. Hence, queue table and queue definitions prior to the import will be lost, and duplicate rows will appear in the queue table.

Creating AQ Administrators and Users

Creating a User as an AQ Administrator

To set a user up as an AQ administrator, do the following:

CONNECT system/manager 
CREATE USER aqadm IDENTIFIED BY aqadm;   
GRANT AQ_ADMINISTRATOR_ROLE TO aqadm;   
GRANT CONNECT, RESOURCE TO aqadm;   
   

Additionally, you can grant execute privilege on the AQ packages as follows:

GRANT EXECUTE ON DBMS_AQADM TO aqadm;   
GRANT EXECUTE ON DBMS_AQ TO aqadm;   
   

This allows the user to execute the procedures in the AQ packages from within a user procedure.

Creating Users AQUSER1 and AQUSER2

If you want to create AQ users who create and access queues within their own schemas, follow the steps outlined in "Creating a User as an AQ Administrator" except do not grant the AQ_ADMINISTRATOR_ROLE.

CONNECT system/manager 
CREATE USER aquser1 IDENTIFIED BY aquser1;   
GRANT CONNECT, RESOURCE TO aquser1;   
   

Additionally, you can grant execute privilege on the AQ packages as follows:

GRANT EXECUTE ON DBMS_AQADM to aquser1;   
GRANT EXECUTE ON DBMS_AQ TO aquser1;   
 

If you wish to create an AQ user who does not create queues but uses a queue in another schema, first follow the steps outlined in the previous section. In addition, you must grant object level privileges. However, note that this applies only to queues defined using 8.1 compatible queue tables.

CONNECT system/manager 
CREATE USER aquser2 IDENTIFIED BY aquser2;   
GRANT CONNECT, RESOURCE TO aquser2;   
   

Additionally, you can grant execute on the AQ packages as follows:

GRANT EXECUTE ON DBMS_AQADM to aquser2;   
GRANT EXECUTE ON DBMS_AQ TO aquser2;   
   

For aquser2 to access the queue, aquser1_q1 in aquser1 schema, aquser1 must execute the following statements:

CONNECT aquser1/aquser1 
EXECUTE DBMS_AQADM.GRANT_QUEUE_PRIVILEGE( 
  'ENQUEUE','aquser1_q1','aquser2',FALSE); 

Oracle Enterprise Manager Support

Oracle Enterprise Manager supports most of the administrative functions of Advanced Queuing. AQ functions are found under the Distributed node in the navigation tree of the Enterprise Manager console. Functions available through Enterprise Manager include:

• Using queues as part of the schema manager to view properties
• Creating, starting, stopping, and dropping queues
• Scheduling and unscheduling propagation
• Adding and removing subscribers
• Viewing propagation schedules for all queues in the database
• Viewing errors for all queues in the database
• Viewing the message queue
• Granting and revoking privileges
• Creating, modifying, or removing transformations

Using Advanced Queuing with XA

You must specify "Objects=T" in the xa_open string if you want to use the AQ OCI interface. This forces XA to initialize the client-side cache in Objects mode. You do not need to do this if you plan to use AQ through PL/SQL wrappers from OCI or Pro*C. The LOB memory management concepts from the Pro* documentation are not relevant for AQ raw messages because AQ provides a simple RAW buffer abstraction (although they are stored as LOBs).

When using the AQ navigation option, you must reset the dequeue position by using the FIRST_MESSAGE if you want to continue dequeuing between services (such as xa_start and xa_end boundaries). This is because XA cancels the cursor fetch state after an xa_end. If you do not reset, you will get an error message stating that the navigation is used out of sequence (ORA-25237).

Restrictions on Queue Management

See the following topics for restrictions on queue management:

• Collection Types in Message Payloads
• Synonyms on Queue Tables and Queues
• Tablespace Point-in-Time Recovery
• Nonpersistent Queues

  Note: Queue names and queue table names are converted to upper case. Mixed case (upper and lower case together) is not supported for queue names and queue table names.

Collection Types in Message Payloads

You cannot construct a message payload using a VARRAY that is not itself contained within an object. You also cannot currently use a NESTED Table even as an embedded object within a message payload. However, you can create an object type that contains one or more VARRAYs, and create a queue table that is founded on this object type.

For example, the following operations are allowed:

CREATE TYPE number_varray AS VARRAY(32) OF NUMBER; 
CREATE TYPE embedded_varray AS OBJECT (col1 number_varray);
EXECUTE DBMS_AQADM.CREATE_QUEUE_TABLE( 
  queue_table           =>     'QT', 
  queue_payload_type    =>     'embedded_varray'); 

Synonyms on Queue Tables and Queues

All AQ PL/SQL calls do not resolve synonyms on queues and queue tables. Although you can create a synonyms, you should not apply the synonym to the AQ interface.

Tablespace Point-in-Time Recovery

AQ currently does not support tablespace point-in-time recovery. Creating a queue table in a tablespace will disable that particular tablespace for point-in-time recovery.

Nonpersistent Queues

Currently you can create nonpersistent queues of RAW and ADT type.You are limited to sending messages only to subscribers and explicitly specified recipients who are local. Propagation is not supported from nonpersistent queues. When retrieving messages, you cannot use the dequeue call, but must instead employ the asynchronous notification mechanism, registering for the notification by mean of LNOCISubcriptionRegister.

Propagation Issues

Propagation makes use of the system queue aq$_prop_notify_X, where X is the instance number of the instance where the source queue of a schedule resides, for handling propagation run-time events. Messages in this queue are stored in the system table aq$_prop_table_X, where X is the instance number of the instance where the source queue of a schedule resides.

Execute Privileges Required for Propagation

Propagation jobs are owned by SYS, but the propagation occurs in the security context of the queue table owner. Previously propagation jobs were owned by the user scheduling propagation, and propagation occurred in the security context of the user setting up the propagation schedule. The queue table owner must be granted EXECUTE privileges on the DBMS_AQADM package. Otherwise, the Oracle snapshot processes will not propagate and generate trace files with the error identifier SYS.DBMS_AQADM not defined. Private database links owned by the queue table owner can be used for propagation. The user name specified in the connection string must have EXECUTE access on the DBMS_AQ and DBMS_AQADM packages on the remote database.

The Number of Job Queue Processes

The scheduling algorithm places the restriction that at least two job queue processes be available for propagation. If there are nonpropagation-related jobs, then more job queue processes are needed. If heavily loaded conditions (a large number of active schedules, all of which have messages to be propagated) are expected, you should start a larger number of job queue processes and keep in mind the need for nonpropagation jobs as well. In a system that only has propagation jobs, two job queue processes can handle all schedules. However, with more job queue processes, messages are propagated faster. Since one job queue process can propagate messages from multiple schedules, it is not necessary to have the number of job queue processes equal to the number of schedules.

Optimizing Propagation

In setting the number of JOB_QUEUE_PROCESSES, DBAs should be aware that this number is determined by the number of queues from which the messages have to be propagated and the number of destinations (rather than queues) to which messages have to be propagated.

A scheduling algorithm handles propagation. The algorithm optimizes available job queue processes and minimizes the time it takes for a message to show up at a destination after it has been enqueued into the source queue, thereby providing near-OLTP behavior. The algorithm can handle an unlimited number of schedules and various types of failures. While propagation tries to make the optimal use of the available job queue processes, the number of job queue processes to be started also depends on the existence of nonpropagation-related jobs such as replication jobs. Hence, it is important to use the following guidelines to get the best results from the scheduling algorithm.

The scheduling algorithm uses the job queue processes as follows (for this discussion, an active schedule is one that has a valid current window):

• If the number of active schedules is less than half the number of job queue processes, the number of job queue processes acquired corresponds to the number of active schedules.
• If the number of active schedules is more than half the number of job queue processes, after acquiring half the number of job queue processes, multiple active schedules are assigned to an acquired job queue process.
• If the system is overloaded (all schedules are busy propagating), depending on availability, additional job queue processes will be acquired up to one less than the total number of job queue processes.
• If none of the active schedules handled by a process has messages to be propagated, then that job queue process will be released.
• The algorithm performs automatic load balancing by transferring schedules from a heavily loaded process to a lightly load process such that no process is excessively loaded.

Handling Failures in Propagation

The scheduling algorithm also has robust support for handling failures. It may not be able to propagate messages from a queue due to various types of failures. Some of the common reasons include failure of the database link, non-availability of the remote database, non-existence of the remote queue, remote queue not started and security violation while trying to enqueue messages into the remote queue. Under all these circumstances the appropriate error messages will be reported in the DBA_QUEUE_SCHEDULES view. When an error occurs in a schedule, propagation of messages in that schedule is attempted periodically using an exponential backoff algorithm for a maximum of 16 times, after which the schedule is disabled. If the problem causing the error is fixed and the schedule is enabled, the error fields that indicate the last error date, time, and message will still continue to show the error information. These fields are reset only when messages are successfully propagated in that schedule. During the later stages of the exponential backoff, many hours or even days can elapse between propagation attempts. This happens when an error has been neglected for a long time. Under such circumstances it may be better to unschedule the propagation and schedule it again.

Propagation from Object Queues

Note that AQ does not support propagation from object queues that have BFILE or REF attributes in the payload.

Guidelines for Debugging AQ Propagation Problems

This discussion assumes that you have created queue tables and queues in source and target databases and defined a database link for the destination database. The notation assumes that you will supply the actual name of the entity (without the brackets).

To begin debugging, do the following:

1. Turn on propagation tracing at the highest level using event 24040, level 10.

   Debugging information will be logged to job queue trace files as propagation takes place. You can check the trace file for errors and for statements indicating that messages have been sent.

2. Check the database link to database 2.

   You can do this by doing select count(*) from @.

3. Check that the propagation schedule has been created and that a job queue process has been assigned.

   Look for the entry in dba_queue_schedules and aq$_schedules. Check that it has a 'jobno' in aq$_schedules, and that there is an entry in job$ or dbms_jobs with that jobno.

4. Make sure that at least two job queue processes are running.
5. Check for messages in the source queue with select count(*) from where q_name = '<queue_name>';
6. Check for messages in the destination queue with the same kind of select.
7. Check to see who is using job queue processes.

   Is it possible that the propagation job is being starved of processing time by other jobs?

8. Check to see that sys.aq$_prop_table_ exists in dba_queue_tables and that queue aq$_prop_notify_ exists in dba_queues (used for communication between job queue processes).
9. Check that the consumer attempting to dequeue a message from the destination queue is a recipient of the propagated messages.

   For 8.1-style queues, you can do the following:

           select consumer_name, deq_txn_id, deq_time, deq_user_id, 
             propagated_msgid from aq$ 
             where queue = '<queue_name>';
   
   

   For 8.0-style queues, you can obtain the same information from the history column of the queue table:

           select h.consumer, h.transaction_id, h.deq_time, h.deq_user,  
           h.propagated_msgid from t, table(t.history) h 
           where t.q_name = '<queue_name>'; 
   

   or

           select consumer, transaction_id, deq_time, deq_user, 
           propagated_msgid from 
           the(select cast(history as sys.aq$_dequeue_history_t) 
           from  where q_name = '<queue_name>'); 
   

Oracle 8.0-Style Queues

If you use 8.0-style queues and 8.1 or higher database compatibility, the following features are not available:

• Support for Oracle Parallel Server environments
• Asynchronous notification

To use these features, you should migrate to 8.1-style or higher queues.

Migrating To and From 8.0

To upgrade a 8.0-style queue table to an 8.1-style queue table or to downgrade a 8.1-style queue table to an 8.0-style queue table, use DBMS_AQADM.MIGRATE_QUEUE_TABLE. Table 4-3 lists the parameters for DBMS_AQADM.MIGRATE_QUEUE_TABLE.

Syntax

DBMS_AQADM.MIGRATE_QUEUE_TABLE( 


queue_table        IN       VARCHAR2,
compatible         IN       VARCHAR2) 

Table 4-3 DBMS_AQADM_MIGRATE_QUEUE_TABLE Parameters

Example: Upgrading an 8.0 Queue Table to an 8.1-Compatible Queue Table

EXECUTE DBMS_AQADM.CREATE_QUEUE_TABLE (
   queue_table            => 'qtable1', 
   multiple_consumers     => TRUE,   
   queue_payload_type     => 'aq.message_typ',
   compatible             =>'8.0');

EXECUTE DBMS_AQADM.MIGRATE_QUEUE_TABLE(
    queue_table  => 'qtable1',                                                                           
   compatible   => '8.1');

Importing and Exporting with 8.0-Style Queues

Because the metadata tables contain rowids of some rows in the queue table, the import and export processes will generate a note about the rowids being obsoleted when importing the metadata tables. This message can be ignored, since the queuing system will automatically correct the obsolete rowids as a part of the import operation. However, if another problem is encountered while doing the import or export (such as running out of rollback segment space), you should correct the problem and repeat the import or export.

Roles in 8.0

Access to AQ operations in Oracle 8.0 is granted to users through roles that provide execution privileges on the AQ procedures. The fact that there is no control at the database object level when using Oracle 8.0 means that, in Oracle 8.0, a user with the AQ_USER_ROLE can enqueue and dequeue to any queue in the system. For finer-grained access control, use 8.1-style queue tables in an 8.1- compatible or higher database.

AQ administrators of an Oracle9i or 8.1 database can create queues with 8.0 compatibility; 8.0-style queues are protected by the 8.0-compatible security features.

If you want to use 8.1 security features on a queue originally created in an 8.0 database, the queue table must be converted to 8.1 style by running DBMS_AQADM.MIGRATE_QUEUE_TABLE on the queue table.

If a database downgrade is necessary, all 8.1-style queue tables have to be either converted back to 8.0 compatibility or dropped before the database downgrade can be carried out. During the conversion, all Oracle9i or 8.1 security features on the queues, like the object privileges, will be dropped. When a queue is converted to 8.0 compatibility, the 8.0 security model applies to the queue, and only 8.0 security features are supported.

Security with 8.0-Style Queues

Table 4-4 lists the AQ security features and privilege equivalences supported with 8.0-style queues.

Table 4-4 Security with 8.0.x-Style Queues

Access to AQ Object Types

The procedure grant_type_access was made obsolete in release 8.1.5 for 8.0-style queues.

LNOCI Application Access to 8.0-Style Queues

For an OCI application to access an 8.0-style queue, the session user has to be granted the EXECUTE rights of DBMS_AQ.

Pluggable Tablespaces and 8.0-Style Multiconsumer Queues

A tablespace that contains 8.0-style multiconsumer queue tables should not be transported using the pluggable tablespace mechanism. The mechanism will work, however, with tablespaces that contain only single-consumer queues as well as 8.1 compatible multiconsumer queues. Before you can export a tablespace in pluggable mode, you have to alter the tablespace to read-only mode. If you try to import a read-only tablespace that contains 8.0-style multiconsumer queues, you will get an Oracle error indicating that you cannot update the queue table index at import time.

Autocommit Features in the DBMS_AQADM Package

The autocommit parameters in the CREATE_QUEUE_TABLE, DROP_QUEUE_TABLE, CREATE_QUEUE, DROP_QUEUE, and ALTER_QUEUE calls of the DBMS_AQADM package are deprecated for 8.1.5 and subsequent releases. Oracle continues to support this parameter in the interface for backward compatibility.