| Oracle® Streams Advanced Queuing User's Guide and Reference Release 10.1 Part Number B10785-01 |
|
|
View PDF |
| Oracle® Streams Advanced Queuing User's Guide and Reference Release 10.1 Part Number B10785-01 |
|
|
View PDF |
This chapter discusses topics related to managing Oracle Streams Advanced Queuing (AQ).
This chapter contains these topics:
For 8.1-compatible or higher queues, the compatible parameter of init.ora and the compatible parameter of the queue table should be set to 8.1 or higher to use the following features:
Queue-level access control
Nonpersistent queues
Database compatibility should be 8.1 or higher for creating non-persistent queues.
Support for Real Application Clusters environments
Rule-based subscribers for publish/subscribe
Asynchronous notification
Sender identification
Separate storage of history management information
Secure queues
Mixed case (upper and lower case together) queue names, queue table names, and subscriber names are supported if database compatibility is 10.0, but the names must be enclosed in double quote marks. So abc.efg means the schema is ABC and the name is EFG, but "abc"."efg" means the schema is abc and the name is efg.
This section contains these topics:
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 are 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 Java Message Service (JMS) API need EXECUTE privileges on DBMS_AQJMS and DBMS_AQIN.
This section contains these topics:
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
Run all procedures in DBMS_AQELM
Run all procedures in DBMS_AQJMS
You should avoid granting AQ_USER_ROLE, because this role does not provide sufficient privileges for enqueuing or dequeuing on 8.1-compatible or higher 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.
Oracle Streams AQ administrators of Oracle Database can create 8.1-compatible or higher queues. All 8.1 security features are enabled for 8.1-compatible or higher queues. Oracle Streams AQ 8.1 security features work only with 8.1-compatible or higher queues. When you create queues, the default value of the compatible parameter in DBMS_AQADM.CREATE_QUEUE_TABLE is 8.1.3 if the database compatibility is less than 10.0. If database compatibility is 10.1, then the default value of the compatible parameter is also 10.0.
The AQ_ADMINISTRATOR_ROLE role is supported for 8.1-compatible or higher queues. To enqueue/dequeue on 8.1-compatible or higher queues, users need EXECUTE rights on DBMS_AQ and either enqueue/dequeue privileges on target queues or ENQUEUE ANY QUEUE/DEQUEUE ANY QUEUE system privileges.
You can grant or revoke privileges at the object level on 8.1- compatible or higher queues. You can also grant or revoke various system-level privileges. Table 5-1 lists all common Oracle Streams AQ operations and the privileges needed to perform these operations for an 8.1-compatible or higher queue.
Table 5-1 Operations and Required Privileges for 8.1-compatible and Higher Queues
| Operation(s) | Privileges Required |
|---|---|
CREATE/DROP/MONITOR own queues |
Must be granted EXECUTE rights on DBMS_AQADM. No other privileges needed. |
CREATE/DROP/MONITOR any queues |
Must be granted EXECUTE rights on DBMS_AQADM and be granted AQ_ADMINISTRATOR_ROLE by another user who has been granted this role (SYS and SYSTEM are the first granters of AQ_ADMINISTRATOR_ROLE) |
ENQUEUE/ DEQUEUE to own queues |
Must be granted EXECUTE rights on DBMS_AQ. No other privileges needed. |
ENQUEUE/ DEQUEUE to another's queues |
Must be granted EXECUTE rights on DBMS_AQ and be granted privileges by the owner using DBMS_AQADM.GRANT_QUEUE_PRIVILEGE. |
ENQUEUE/ DEQUEUE to any queues |
Must be granted EXECUTE rights on DBMS_AQ and be granted ENQUEUE ANY QUEUE or DEQUEUE ANY QUEUE system privileges by an Oracle Streams AQ administrator using DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE. |
For an Oracle Call Interface (OCI) application to access an 8.1-compatible or higher queue, the session user must 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 is not checked against the session user's rights if the queue he intends to access is an 8.1-compatible or higher queue.
Oracle Streams 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 need 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 right to enqueue to the destination queue. However, if the login user in the database link also owns the queue tables at the destination, then no explicit Oracle Streams AQ privileges must be granted.
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.
|
Note: Oracle Streams AQ does not currently support the new Data Pumpexpdp and impdp utilities. Use the original exp and imp utilities for queue table export-import. |
|
Note: If there exists a queue table with the same name in the same schema in the database as in the export dump, then ensure that the database queue table is empty before importing a queue table with queues. Failing to do so has a possibility of ruining the metadata for the imported queue. |
This section contains these topics:
The export of queues entails the export of the underlying queue tables and related dictionary tables. Export of queues can only be accomplished at queue-table granularity.
A queue table that supports multiple recipients is associated with the following tables:
Dequeue index-organized table (IOT)
Time-management index-organized table
Subscriber table (for 8.1-compatible and higher queue tables)
A history index-organized table (for 8.1-compatible and higher 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 generates a note about the ROWIDs being made obsolete when importing the metadata tables. This message can be ignored, because the queuing system automatically corrects 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), then you should correct the problem and repeat the import.
Exporting operates in full database mode, user mode, and table mode. Incremental exports on queue tables are not supported.
In full database mode, queue tables, all related tables, system-level grants, and primary and secondary object grants are exported automatically.
In user mode, queue tables, all related tables, and primary object grants are exported automatically. However, doing a user-level export from one schema to another using the FROMUSER TOUSER clause is not supported.
Oracle does not recommend table mode. If you must export a queue table in table mode, then you must export all related objects that belong to that queue table. For example, when exporting an 8.1-compatible or higher multiconsumer queue table named 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)
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.
|
Note: Transportable tablespace export/import of tablespaces with queue tables across releases fails on import. The metadata import from the lower release fails with an error indicating that the tablespace is read only. The workaround is to make the tablespace read/write before importing the metadata. |
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 or higher queue tables)
A history IOT (for 8.1-compatible or higher queue tables)
These tables must be imported as well as the queue table itself.
You must not import queue data into a queue table that already contains data. The IGNORE parameter of the import utility must 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 are loaded from the dump file into the existing table. At the same time, the old queue table definition is lost and re-created. Queue table definition prior to the import is lost and duplicate rows appear in the queue table.
The Data Pump replace and skip modes are supported for queue tables. In the replace mode an existing queue table is dropped and replaced by the new queue table from the export dump file. In the skip mode, a queue table that already exists is not imported.
The truncate and append modes are not supported for queue tables. The behavior in this case is the same as the replace mode.
Example 5-1 shows how to create an Oracle Streams AQ Administrator named aqadm. The last two lines, which are optional, show how to grant this user EXECUTE privileges on the Oracle Streams AQ packages. This allows the user to run the package procedures from within a user procedure.
Example 5-1 Creating a User as an Oracle Streams AQ Administrator
CONNECT system/manager CREATE USER aqadm IDENTIFIED BY aqadm; GRANT AQ_ADMINISTRATOR_ROLE TO aqadm; GRANT CONNECT, RESOURCE TO aqadm; GRANT EXECUTE ON DBMS_AQADM TO aqadm; --optional GRANT EXECUTE ON DBMS_AQ TO aqadm; --optional
The procedure to create Oracle Streams AQ users who create and access queues within their own schemas is similar to "Creating a User as an Oracle Streams AQ Administrator", except you do not grant the AQ_ADMINISTRATOR_ROLE. Example 5-2 shows how to create an own-schema user named aquser1. The last two lines, which are optional, show how to grant this user EXECUTE privileges on the Oracle Streams AQ packages. This allows the user to run the package procedures from within a user procedure.
Example 5-2 Creating a User to Create and Access Queues in Own Schema
CONNECT system/manager CREATE USER aquser1 IDENTIFIED BY aquser1; GRANT CONNECT, RESOURCE TO aquser1; GRANT EXECUTE ON DBMS_AQADM to aquser1;
The procedure to create an Oracle Streams AQ user who does not create queues but uses a queue in another schema is identical to that for the own-schema user, as shown in Example 5-3 for user aquser2. But you must also grant object level privileges in the other schema. Example 5-4 does this for aquser2 in the aquser1 schema. However, this applies only to queues defined using 8.1-compatible or higher queue tables.
Example 5-3 Creating a User to Access Queues in Another Schema
CONNECT system/manager CREATE USER aquser2 IDENTIFIED BY aquser2; GRANT CONNECT, RESOURCE TO aquser2; GRANT EXECUTE ON DBMS_AQ TO aquser2;
For aquser2 to access the queue aquser1_q1 in aquser1 schema, aquser1 must run the following statements:
Oracle Enterprise Manager supports most of the administrative functions of Oracle Streams AQ. Oracle Streams AQ functions are found under the Distributed node in the navigation tree of the Enterprise Manager console. Functions available through Oracle 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
You must specify "Objects=T" in the xa_open string if you want to use the Oracle Streams AQ OCI interface. This forces XA to initialize the client-side cache in Objects mode. You are not required to do this if you plan to use Oracle Streams AQ through PL/SQL wrappers from OCI or Pro*C.
The large object (LOB) memory management concepts from the Pro* documentation are not relevant for Oracle Streams AQ raw messages because Oracle Streams AQ provides a simple RAW buffer abstraction (although they are stored as LOBs).
When using the Oracle Streams AQ navigation option, you must reset the dequeue position by using the FIRST_MESSAGE option 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, then you get an error message stating that the navigation is used out of sequence (ORA-25237).
|
See Also:
|
This section discusses restrictions on queue management.
This section contains these topics:
|
Note: Mixed case (upper and lower case together) queue names, queue table names, and subscriber names are supported if database compatibility is 10.0, but the names must be enclosed in double quote marks. Soabc.efg means the schema is ABC and the name is EFG, but "abc"."efg" means the schema is abc and the name is efg. |
For this release, only 32 remote subscribers are allowed for each remote destination database.
Oracle Streams AQ does not support data manipulation language (DML) operations on queue tables or associated index-organized tables (IOTs), if any. The only supported means of modifying queue tables is through the supplied APIs. Queue tables and IOTs can become inconsistent and therefore effectively ruined, if DML operations are performed on them.
Oracle Streams AQ does not support propagation from object queues that have REF attributes in the payload.
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, as shown in Example 5-5.
No Oracle Streams AQ PL/SQL calls resolve synonyms on queues and queue tables. Although you can create synonyms, you should not apply them to the Oracle Streams AQ interface.
Oracle Streams AQ currently does not support tablespace point-in-time recovery. Creating a queue table in a tablespace disables that particular tablespace for point-in-time recovery. Oracle Streams AQ does support regular point-in-time recovery.
Currently you can create nonpersistent queues of RAW and Oracle object 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 OCISubscriptionRegister.
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.
|
Caution: The queueaq$_prop_notify_X should never be stopped or dropped and the table aq$_prop_table_X should never be dropped for propagation to work correctly. |
This section contains these topics:
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 Database snapshot processes does 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 username specified in the connection string must have EXECUTE access on the DBMS_AQ and DBMS_AQADM packages on the remote database.
The scheduling algorithm places the restriction that at least two job queue processes be available for propagation. If there are jobs unrelated to propagation, 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, then 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. Because 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.
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 must be propagated and the number of destinations (rather than queues) to which messages must 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 action. 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 jobs unrelated to propagation, 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 fewer than half the number of job queue processes, then 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, then multiple active schedules are assigned to an acquired job queue process.
If the system is overloaded (all schedules are busy propagating), depending on availability, then additional job queue processes are acquired up to one fewer 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 is 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.
The scheduling algorithm has robust support for handling failures. Common failures that prevent message propagation include the following:
Database link failed
Remote database is not available
Remote queue does not exist
Remote queue was not started
Security violation while trying to enqueue messages into remote queue
Under all these circumstances the appropriate error messages are reported in the DBA_QUEUE_SCHEDULES view.
When an error occurs in a schedule, propagation of messages in that schedule is attempted again after a retry period of 30*(number of failures) seconds, with an upper bound of ten minutes. After sixteen consecutive retries, the schedule is disabled.
If the problem causing the error is fixed and the schedule is enabled, then the error fields that indicate the last error date, time, and message continue to show the error information. These fields are reset only when messages are successfully propagated in that schedule.
Client requests for enqueue, send and publish requests, use the following methods:
AQXmlSend—to enqueue to a single-consumer queue
AQXmlPublish—to enqueue to multiconsumer queues/topics
In message_header, the message_state attribute represents the state of the message filled in automatically during dequeue, as follows:
0 (the message is ready to be processed)
1 (the message delay has not yet been reached)
2 (the message has been processed and is retained)
3 (the message has been moved to the exception queue)
Propagation from object queues with BFILEs is supported in Oracle Database 10g. To be able to propagate object queues with BEFILEs, the source queue owner must have read privileges on the directory object corresponding to the directory in which the BFILE is stored. The database link user must have write privileges on the directory object corresponding to the directory of the BFILE at the destination database.
|
Note: Propagation of BFILES from object queues without specifying a database link is not supported. |
|
See Also: "CREATE DIRECTORY" in Oracle Database SQL Reference for more information on directory objects |
If you use 8.0-compatible queues and 8.1 or higher database compatibility, then the following features are not available:
Support for Real Application Clusters environments
Asynchronous notification
Secure queues
Queue level access control
Rule-based subscribers for publish/subscribe
Separate storage of history management information
To use these features, you should migrate to 8.1-compatible or higher queues.
To upgrade a 8.0-compatible queue table to an 8.1-compatible or higher queue table or to downgrade a 8.1-compatible or higher queue table to an 8.0-compatible queue table, use DBMS_AQADM.MIGRATE_QUEUE_TABLE.
DBMS_AQADM.MIGRATE_QUEUE_TABLE(
queue_table IN VARCHAR2,
compatible IN VARCHAR2)
Specifies name of the queue table that is to be migrated.
Set to 8.1 to upgrade an 8.0 queue table to 8.1 compatibility. Set to 8.0 to downgrade an 8.1 queue table to 8.0 compatibility.
You must set up the following data structures for the following example to work:
EXECUTE DBMS_AQADM.CREATE_QUEUE_TABLE ( queue_table => 'qtable1', multiple_consumers => TRUE, queue_payload_type => 'aq.message_typ', compatible =>'8.0');
Because the metadata tables contain ROWIDs of some rows in the queue table, the import and export processes generate a note about the ROWIDs being obsoleted when importing the metadata tables. This message can be ignored, because the queuing system automatically corrects 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), then you should correct the problem and repeat the import or export.
Access to Oracle Streams AQ operations in Oracle8 was granted to users through roles that provided execution privileges on the Oracle Streams AQ procedures. The fact that there was no control at the database object level when using Oracle8 meant that a user with the AQ_USER_ROLE could enqueue and dequeue to any queue in the system. For finer-grained access control, use 8.1-compatible or higher queue tables in an 8.1- compatible or higher database.
Oracle Streams AQ administrators of an 8.1-compatible or higher database can create queues with 8.0 compatibility. These 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, then the queue table must be converted to 8.1-compatible or higher by running DBMS_AQADM.MIGRATE_QUEUE_TABLE on the queue table.
|
See Also: PL/SQL Packages and Types Reference for more information onDBMS_AQADM.MIGRATE_QUEUE_TABLE |
If a database downgrade is necessary, then all 8.1-compatible or higher queue tables must be either converted back to 8.0 compatibility or dropped before the database downgrade can be carried out. During the conversion, all 8.1-compatible security features on the queues, like the object privileges, are dropped. When a queue is converted to 8.0-compatible, the 8.0-compatible security model applies to the queue, and only 8.0-compatible security features are supported.
The following Oracle Streams AQ security features and privilege equivalences are supported with 8.0-compatible queues:
AQ_USER_ROLE
The grantee is given the EXECUTE right of DBMS_AQ through the role.
AQ_ADMINISTRATOR_ROLE
EXECUTE right on DBMS_AQ
EXECUTE right on DBMS_AQ should be granted to developers who write Oracle Streams AQ applications in PL/SQL.
The procedure grant_type_access was made obsolete in release 8.1.5 for 8.0-compatible queues.
For an OCI application to access an 8.0-compatible queue, the session user must be granted the EXECUTE rights of DBMS_AQ.
A tablespace that contains 8.0-compatible multiconsumer queue tables should not be transported using the pluggable tablespace mechanism. The mechanism does work, however, with tablespaces that contain only single-consumer queues as well as 8.1 compatible or higher multiconsumer queues. Before you can export a tablespace in pluggable mode, you must alter the tablespace to read-only mode. If you try to import a read-only tablespace that contains 8.0-compatible multiconsumer queues, then you get an Oracle Streams AQ error indicating that you cannot update the queue table index at import time.
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.
|
 Copyright © 1996, 2003 Oracle Corporation All Rights Reserved. |
|