learn-oracle
free Oracle DBA tutorial Oracle Jobs
Ask A Question
SQL Statement Tuning
Backup and Recovery Concepts
Oracle 11g New Features
Oracle E Suite & Others
Oracle Data Guard
Oracle DBA FAQ





Managing AQ


Previous Chapter | Next Chapter

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

* Oracle Enterprise Manager Support

* Using Advanced Queuing with XA

* Restrictions on Queue Management

* Propagation Issues

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.

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
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, provided that you wish 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.

OCI 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 and/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 anOracle9i 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.

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$__I (the dequeue IOT)

AQ$__T (the time-management IOT)

AQ$__S (the subscriber table)

AQ$__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 recreated. 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 GUIs for most of the administrative functions listed in the administrative interfaces section. These include:

* Queues as part of the schema manager to view properties

* Create, start, stop, and drop queues

* Schedule and unschedule propagation

* Add and remove subscribers

* View propagation schedules for all queues in the database

* View errors for all queues in the database

* View the message queue

* Grant and revoke privileges

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

* 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 OCISubcriptionRegister.

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.

Previous Chapter | Next Chapter Discuss Article

More Articles

1. What Is Advanced Queuing?
2. General Features of Advanced Queuing
3. Enqueue, Dequeue, Propagation Features of Advanced Queuing
4. Elements of Advanced Queuing
5. Basic Components of Advanced Queuing

More Tutorials on Oracle dba ...

Source :Oracle Documentation

Liked it ? Want to share it ? Social Bookmarking

Add to: Mr. Wong Add to: BoniTrust Add to: Newsider Add to: Digg Add to: Del.icio.us Add to: Reddit Add to: Jumptags Add to: StumbleUpon Add to: Slashdot Add to: Netscape Add to: Furl Add to: Yahoo Add to: Spurl Add to: Google Add to: Blinklist Add to: Technorati Add to: Newsvine Information




Want to share or request Oracle Tutorial articles to become a Oracle DBA. Direct your requests to webmaster@oracleonline.info