Release 8.1.5
A68005-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8i Application Developer's Guide - Advanced Queuing Release 8.1.5 A68005-01 |
|
This chapter describes the elements you need to work with and issues you will want to take into consideration in preparing the application environment.
You specify the parameter aq_tm_processes in the init.ora PARAMETER file if you want to perform time monitoring on queue messages. You can set the parameter in a range from 0 to 10 depending on how many queue monitor processes you require. Setting it to any other number will result in an error. If this parameter is set to 1, one queue monitor process will be created as a background process to monitor the messages. If the parameter is not specified, or is set to 0, the queue monitor process is not created.
Since the aq_tm_processes parameter is dynamic, you can alter the number of queue monitors while the instance is running. You do this by means of the syntax:
ALTER SYSTEM SET aq_tm_processes=<integer>;
Propagation is handled by job queue (SNP) processes. The number of job queue processes started in an instance is controlled by the init.ora parameter JOB_QUEUE_PROCESSES. The default value of this parameter is 0. In order for message propagation to take place, this parameter must be set to at least 1. The DBA can set it to higher values if there are many queues from which the messages have to be propagated, or if there are many destinations to which the messages have to be propagated, or if there are other jobs in the job queue.
The following data structures are used in both the operational and administrative interfaces:
The naming of database objects. This naming convention applies to queues, queue tables and object types.
object_name := VARCHAR2 object_name := [<schema_name>.]<name>
Names for objects are specified by an optional schema name and a name. If the schema name is not specified then the current schema is assumed. The name must follow object name guidelines in the Oracle8i SQL Reference with regard to reserved characters.The schema name, agent name and the object type name can each be up to 30 bytes long. However, queue names and queue table names can be a maximum of 24 bytes.
Defining queue types.
type_name := VARCHAR2 type_name := <object_type> | "RAW"
To identify a producer or a consumer of a message.
TYPE aq$_agent IS OBJECT ( name VARCHAR2(30), address VARCHAR2(1024), protocol NUMBER)
| Parameter | Description |
|---|---|
|
|
Name of a producer or consumer of a message.The name must follow object name guidelines in the Oracle8i SQL Reference with regard to reserved characters. |
|
|
Protocol specific address of the recipient. If the protocol is 0 (default) the address is of the form [schema.]queue[@dblink] |
|
|
Protocol to interpret the address and propagate the message. The default value is 0. |
All consumers that are added as subscribers to a multi-consumer queue must have unique values for the AQ$_AGENT parameter. This means that two subscribers cannot have the same values for the NAME, ADDRESS and PROTOCOL attributes for the AQ$_AGENT type. At least one of the three attributes must be different for two subscribers.
To identify the list of agents that will receive the message.
TYPE aq$_recipient_list_t IS TABLE OF aq$_agent INDEX BY BINARY_INTEGER;
To identify the list of agents for DBMS_AQ.LISTEN to listen for.
TYPE aq$_agent_list_t IS TABLE OF aq$_agent INDEX BY BINARY INTEGER;
To identify the list of subscribers that subscribe to this queue.
TYPE aq$_subscriber_list_t IS TABLE OF aq$_agent INDEX BY BINARY INTEGER;
When using enumerated constants such as INFINITE, TRANSACTIONAL, NORMAL_QUEUE are selected as values, the symbol needs to be specified with the scope of the packages defining it. All types associated with the administrative interfaces have to be prepended with dbms_aqadm. For example:
DBMS_AQADM.NORMAL_QUEUE
| Parameter | Options |
|---|---|
|
|
|
|
|
|
|
|
|
When using enumerated constants such as BROWSE, LOCKED, REMOVE, the PL/SQL constants need to be specified with the scope of the packages defining it. All types associated with the operational interfaces have to be prepended with dbms_aq. For example:
DBMS_AQ.BROWSE
Configuration information can be managed through procedures in the DBMS_AQADM package. Initially, only SYS and SYSTEM have the execution privilege for the procedures in DBMS_AQADM and DBMS_AQ. Any users who have been granted the EXECUTE rights to these two packages will be able to create, manage, and use queues in their own schema. The user would also need the MANAGE ANY QUEUE privilege in order to create and manage queues in other schemas.
AQ administrators of an 8.1 database are allowed to create queues with 8.0 or 8.1 compatibility. All 8.1 security features are enabled for 8.1 compatible queues. However, please note that AQ 8.1 security features work only with 8.1 compatible queues; 8.0 compatible queues are protected by the 8.0 compatible security features.
To create queues in 8.1 that can make use of the new security features, the compatible parameter in DBMS_AQADM.CREATE_QUEUE_TABLE must be set to '8.1' or above. If you want to use the new security features on a queue originally created in an 8.0 database, the queue table must be converted to 8.1 compatibility by running DBMS_AQADM.MIGRATE_QUEUE_TABLE on the queue table.
If a database downgrade is necessary, all 8.1 compatible 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 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 apply to the queue, and only 8.0 security features are supported.
The following table lists the AQ security features supported in each version of Oracle8 database and their equivalence privileges across different database version.
With Oracle 8.1, you can grant or revoke privileges at the object level on 8.1 compatible 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 8.1-compatible queue:
Access to AQ operations in Oracle 8.0 is granted to users through roles which 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. Since Oracle 8.1 offers a finer-grained access control, the function of roles changes when you develop applications in the 8.1 context.
Oracle 8.1 continues to support the AQ_AQMISTRATOR_ROLE. As in 8.0, the AQ_ADMINISTRATOR_ROLE has been granted all the required privileges to administer queues. The privileges granted to the role let the grantee:
AQ_USER_ROLE continues to work for queues that are created with 8.0 compatibility. However, you should avoid granting AQ_USER_ROLE in Oracle 8.1 since this role will not provide sufficient privileges for enqueuing or dequeuing on 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.
The procedure grant_type_access is made obsolete in release 8.1.5 for both 8.0-compatible and 8.1 compatible queues. All internal AQ objects are now accessible to PUBLIC.
For an OCI application to access an 8.0-compatible queue, the session user has to be granted the EXECUTE rights of DBMS_AQ. For an OCI application to access an 8.1-compatible 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 an 8.1-compatible queue.
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 either.
Queues are stored in database tables. The performance characteristics of queue operations are very similar to the underlying database operations.
To understand the performance characteristics of queues it is important to understand the tables and index layout for AQ objects.
Creating a queue table creates a database table with approximately 25 columns. These columns store the AQ meta data and the user defined payload. The payload can be of an object type or RAW. The AQ meta data contains object types and scaler types. A view and two indexes are created on the queue table. The view allows users to query the message data. The indexes are used to accelerate access to message data. Please refer to the create queue table command for a detailed description of the objects created.
The code path of an enqueue operation is comparable to an insert into a multi-column table with two indexes. The code path of a dequeue operation is comparable to a select and delete operation on a similar table. These operations are performed using PL/SQL functions.
Oracle Parallel Server (OPS) can be used to ensure highly available access to queue data. Queues are implemented using database tables. The tail and the head of a queue can be extreme hot spots. Since OPS does not scale well in the presence of hot spots it is recommended to limit normal access to a queue from one instance only. In case of an instance failure messages managed by the failed instance can be processed immediately by one of the surviving instances.
Queue operation scalability is similar to the underlying database operation scalability. If a dequeue operation with wait option is issued in a Multi-Threaded Server (MTS) environment the shared server process will be dedicated to the dequeue operation for the duration of the call including the wait time. The presence of many such processes could cause severe performance and availability problems and could result in deadlocking the shared server processes. For this reason it is recommended that dequeue requests with wait option be only issued via dedicated server processes. This restriction is not enforced.
To upgrade a 8.0-compatible queue table to an 8.1-compatible queue table or to downgrade a 8.1-compatible queue table to an 8.0-compatible queue table.
DBMS_AQADM.MIGRATE_QUEUE_TABLE( queue_table IN VARCHAR2, compatible IN VARCHAR2)
For the most current information regarding the interrelationship of different releases, please refer to "Compatibility" in Chapter 1, "Introduction".
EXECUTE DBMS_AQADM.MIGRATE_QUEUE_TABLE( queue_table => 'qtable1', compatible => '8.1');
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.
Queues are implemented on tables. 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.
For every queue table that supports multiple recipients, there is an index-organized table (IOT) and a time-management table that contain important queue metadata. For 8.1 compatible queue tables there is also a subscriber table, a history table and a rules table. This metadata is essential to the operation of the queue, so the user must export these tables as well as the queue table itself for the queues in this queue table to work after import. During full database mode and user mode export, all these tables are exported automatically.
Because these 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 as 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), the problem should be corrected and the import should be repeated.
Rules are associated with a queue table. When a queue table is exported, all associated rules, if any, will be exported automatically.
Export currently operates in three modes: full database mode, user mode, and table mode. The operation of the three export modes is described as follows.
This mode is supported. Queue tables, all related tables, system level grants, and primary and secondary object grants are exported automatically.
This mode is supported. Queue tables, all related tables and primary object grants are exported automatically.
This is not recommended. If there is a need to export a queue table in table mode, the user is responsible for exporting all related objects which belong to that queue table. For example, when exporting an 8.1 compatible multi-consumer queue table MCQ, you will also need to export the following tables:
AQ$_MCQ_I AQ$_MCQ_H AQ$_MCQ_S AQ$_MCQ_T
Incremental export on queue tables is not supported.
Similar to exporting queues, the import of queues entails the import of 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.
As explained earlier, for every queue table that supports multiple recipients, there is a index-organized table (IOT), a subscriber table, a history table, and a time-management table that contain important queue metadata. All these tables as well as the queue table itself, have to be imported for the queues in this queue table to work after the import.
Because these metadata tables contain rowids of some rows in the queue table, the import process will issue a note about the rowids being obsoleted when importing the metadata table. This message can be ignored, as 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), the problem should be corrected and the import should be rerun.
We suggest that you do not import queue data into a queue table that already contains data. We recommend that the DBA should always set the IGNORE parameter of the import utility 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.
In setting the number of JOB_QUEUE_PROCESSES, the DBA should aware that this need 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.
In this release, a new scalable scheduling algorithm has been incorporated for handling propagation. It has been designed to make optimal use of the available job queue processes and also minimize the time it takes for a message to show up at a destination once it has been enqueued into the source queue, thereby providing near OLTP behavior. This algorithm is capable of simultaneously handling an unlimited number of schedules. The algorithm also has robust support for handling 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 non-propagation related jobs such as replication jobs. Hence, it is very important to use the following guidelines to get the best results from this new algorithm.
The new algorithm uses the job queue processes as follows: (for this discussion an active schedule is one which has a valid current window)
The scheduling algorithm places the restriction that at least 2 job queue processes be available for propagation. If there are non-propagation related jobs then more number of job queue processes is needed. If heavily loaded conditions (when there are a large number of active schedules all of which have messages to be propagated) are expected then it is recommended to start a larger number of job queue processes keeping in mind that the job queue processes will be used for non-propagation related jobs as well. In a system which only has propagation jobs, then 2 job queue processes can handle all schedules but higher the number the faster the messages get propagated. Note that, since one job queue process can propagate messages from multiple schedules, it is not necessary to have the same number of job queue processes as the number of schedules.
The new 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, the time span between propagation attempts can be large in the tune of hours or even days. This happens only 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.
Enterprise manager supports GUIs for most of the administrative functions listed in the administrative interfaces section.
These include:
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 you picked up from the Pro* documentation is not relevant for AQ raw messages because AQ provides a simple RAW buffer abstraction (although they are stored as LOBs).
You must use AQ navigation option carefully when you are using AQ from XA. XA cancels cursor fetch state after an xa_end. Hence, if you want to continue dequeuing between services (i.e. xa_start/xa_end boundaries) you must reset the dequeue position by using the FIRST_MESSAGE navigation option. Otherwise, you will get an ORA-25237 (navigation used out of sequence).
To set a user up as an AQ administrator, you must the following steps
CONNECT system/manager CREATE USER aqadm IDENTIFIED BY aqadm; GRANT AQ_ADMINISTRATOR_ROLE TO aqadm; GRANT CONNECT, RESOURCE TO aqadm;
Additionally, you might grant execute 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.
If you want to create an AQ user who creates and accesses queues within his/her own schema, follow the steps outlined in the previous section except do not grant the AQ_ADMINISTRATOR_ROLE.
CONNECT system/manager CREATE USER aquser1 IDENTIFIED BY aquser1; GRANT CONNECT, RESOURCE TO aquser1;
Additionally, you might grant execute 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 might 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);
