Audience
Documentation Accessibility
To reach Oracle Support Services, you must use a telecommunications relay service (TRS) to call Oracle Support. An Oracle Support Services engineer will handle technical issues and provide customer support according to the Oracle service request process.
Related Documents
Conventions
This chapter describes new features of the Oracle Database 11g Release 1 (11.1) and provides pointers to additional information.
Notification Enhancements
The Automatic Workload Repository (AWR) has also been improved to display queues with more persistent message operations, allowing easier diagnosis of AQ performance issues. All queues in the queue table, primary object grants, related objects like views, IOT, rules are automatically exported.
Transition from Job Queue Processes to Database Scheduler
Messaging Gateway Enhancements
This not only enables MGW to scale, but also enables task grouping and isolation, which is important when MGW is used in a complicated application integration environment. Simplified Messaging Gateway throughput job configuration An enhanced PL/SQL API consolidates the propagation subscriber and propagation schedule into a new propagation job.
Introduction to Oracle® Database
What Is Queuing?
The transformation is represented by an SQL function that takes the source data type as input and returns an object of the target data type. You can arrange transformations to occur when a message is enqueued, dequeued, or propagated to a remote subscriber.
Oracle® Database Leverages Oracle Database
Oracle® Database supports enqueue, dequeue, and propagate operations where the queue type is an abstract data type, ADT. The queue table is controlled by user-defined instance queue monitors.
Oracle® Database in Integrated Application Environments
This can result in pings between the application accessing the queue table and the queue monitor monitoring it. Specifying instance affinity prevents this, but does not prevent the application from accessing the queue table and its queues from other instances.
Oracle® Database Client/Server Communication
Multiconsumer Dequeuing of the Same Message
The address must be the name of another queue in the same database or another installation of Oracle Database (identified by the database link), in which case the message is propagated to the specified queue and can be queued by a consumer with the specified name. If the recipient name is NULL, then the message is propagated to the queue specified in the address and can be dequeued by the subscribers of the queue specified in the address.
Oracle® Database Implementation of Workflows
The applications at the top and bottom of the illustration are connected by arrows with three unmarked queues in the middle. In the lower right, application D, the consumer is connected to the rightmost queue with a downward-pointing arrow labeled Dequeue (message 3).
Oracle® Database Implementation of Publish/Subscribe
Publish/subscribe posts has a wide distribution mode called broadcast and a more narrow mode called multicast. Publish/subscribe describes a situation where a publisher application queues messages anonymously (no recipients specified).
Buffered Messaging
Sorting between persistent and buffered messages queued in the same session is not currently supported. Dequeuing applications can choose to remove only persistent messages, only buffered messages, or both.
Asynchronous Notifications
If the client tries to start a different thread on a different IP address using a different environment handle, Oracle Streams AQ returns an error. Customers can also register to receive only the first message, after which the registration will be automatically deleted.
Views on Registration
An example where purging by notification is useful is a client waiting for queues to start. In this case, only the first message is useful; subsequent messages provide no additional information.
Event-Based Notification
Previously, this client had to log out when queuing started; now you can configure the registration to disappear automatically. Message properties delivered by a PL/SQL or OCI notification determine whether the message is buffered or persistent.
Notification Grouping by Time
TIME, REPEAT_COUNT, GROUPING CLASS, GROUPING VALUE, GROUPING TYPE in the AQ$_REGISTRATION_INFO and the OCI subscription handle. The notification descriptor received by the AQ notification client provides information about the group of message identifiers and the number of notifications in the group.
Enqueue Features
All messages belonging to a group must be created in the same transaction, and all messages created in one transaction must belong to the same group. The priority, delay, and expiration properties of a group message are determined solely by the message properties specified for the first message in the group, regardless of what properties are specified for subsequent messages in the group.
Dequeue Features
Expired messages from multi-consumer queues cannot be removed from the queue by the intended recipients of the message. This allows dequeuers to complete the dequeue operation by not locking the message in the queue table.
Propagation Features
With queue-to-queue propagation, the frequency of each schedule can be adjusted independently of the others. Specifying the destination queue name results in queue-to-queue propagation, a new feature in Oracle®.
Message Format Transformation
Other Oracle® Database Features
The Oracle® Database retention feature can be used to automatically purge messages after the user-specified duration after use. The Oracle® Database servlet connects to the Oracle Database server and performs operations on user queues.
Interfaces to Oracle® Database
Oracle® Database Demonstrations
AQDemoServlet.java Servlet to place Oracle® Database XML files (for Jserv) AQPropServlet.java Servlet for Oracle® Database HTTP propagation aqxmldrp.sql Clean AQ XML demo.
Basic Components
Object Name
Type Name
AQ Agent Type
AQ Recipient List Type
AQ Agent List Type
AQ Subscriber List Type
AQ Registration Information List Type
AQ Post Information List Type
AQ Registration Information Type
NTFN_GROUPING_CLASS_TIME - Notifications grouped by time, i.e. the user specifies a time value and a single notification is published at the end of that time. If ntfn_grouping_start_time is not specified when grouping is used, the default is the current timestamp with timezone.
AQ Notification Descriptor Type
AQ Message Properties Type
NTFN_FLAGS is set to 1 if the notifications have already been removed after a set amount of time; otherwise the value is 0.
AQ Post Information Type
AQ$_NTFN_MSGID_ARRAY Type
Enumerated Constants in the Oracle® Database Administrative Interface
Enumerated Constants in the Oracle® Database Operational Interface
AQ Background Processes
Queue Monitor Processes
Job Queue Processes
Oracle® Database: Programmatic Interfaces
Programmatic Interfaces for Accessing Oracle® Database
Using PL/SQL to Access Oracle® Database
Using OCI to Access Oracle® Database
Using OCCI to Access Oracle® Database
Using Visual Basic (OO4O) to Access Oracle® Database
Using Oracle Java Message Service (OJMS) to Access Oracle® Database
Accessing Standard and Oracle JMS Applications
Using Oracle® Database XML Servlet to Access Oracle® Database
Comparing Oracle® Database Programmatic Interfaces
Oracle® Database Administrative Interfaces
Oracle® Database Operational Interfaces
Table 3–8 Comparison of Oracle® Database Programmatic Interfaces: Operational Interface—Messages Received from a Queue/Topic Use Cases. Table 3–9 Comparison of Oracle® Database Programmatic Interfaces: Operational Interface—Register to receive messages asynchronously from a queue/topic Use Cases.
Managing Oracle® Database
Oracle® Database Compatibility Parameters
Queue Security and Access Control
Oracle® Database Security
Administrator Role
User Role
You as the application developer grant 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 exit queues in your own schema, other than the EXECUTE right on DBMS_AQ.
Access to Oracle® Database Object Types
Queue Security
Queue Privileges and Access Control
OCI Applications and Queue Access
Security Required for Propagation
Queue Table Export-Import
Exporting Queue Table Data
This message can be ignored because the queuing system automatically corrects the out-of-date ROWIDs as part of the import operation. However, if another problem occurs during the import (such as not enough space to revert segments), you must resolve the problem and restore the .
Importing Queue Table Data
Because the metadata tables contain the ROWIDs of some rows in the queue table, the import process generates a note that the ROWIDs are out of date when importing the metadata tables.
Data Pump Export and Import
Oracle Enterprise Manager Support
Using Oracle® Database with XA
Restrictions on Queue Management
Subscribers
DML Not Supported on Queue Tables or Associated IOTs
Propagation from Object Queues with REF Payload Attributes
Collection Types in Message Payloads
Synonyms on Queue Tables and Queues
Synonyms on Object Types
Virtual Private Database
Managing Propagation
EXECUTE Privileges Required for Propagation
The username specified in the connection string must have EXECUTE access to the DBMS_AQ and DBMS_AQADM packages on the remote database.
Propagation from Object Queues
Optimizing Propagation
If the number of active schedules is less than half of the number of job queue processes, then the number of acquired job queue processes corresponds to the number of active schedules. If the system is overloaded (all schedules are propagating), depending on availability, additional job queue processes are obtained to one less than the total number of job queue processes.
Handling Failures in Propagation
If the number of active schedules is more than half the number of job queue processes, after reaching half the number of job queue processes, more active schedules are assigned to an acquired job queue process. If none of the active schedules handled by a process have messages to propagate, that job queue process is released.
Oracle® Database Performance and Scalability
Persistent Messaging Performance Overview
Oracle® Database and Oracle Real Application Clusters
Oracle® Database in a Shared Server Environment
Persistent Messaging Basic Tuning Tips
Memory Requirements
Using Storage Parameters
I/O Configuration
Running Enqueue and Dequeue Processes Concurrently in a Single Queue Table
Running Enqueue and Dequeue Processes Serially in a Single Queue Table
Creating Indexes on a Queue Table
Other Tips
In a shared server environment, the shared server process is dedicated to the dequeue operation for the duration of the call, including the wait time. The presence of many such processes can cause serious performance and scalability problems and can result in deadlocking of the shared server processes.
Propagation Tuning Tips
It is recommended that dequeue with a wait time be used only with dedicated server processes.
Buffered Messaging Tuning
Performance Views
Internet Access to Oracle® Database
Overview of Oracle® Database Operations over the Internet
Oracle® Database Internet Operations Architecture
A web browser or other HTTP client can act as an Oracle® Database client program and send IDAP-compliant XML messages to the Oracle® Database servlet, which interprets the incoming XML messages.
Internet Message Payloads
Configuring the Web Server to Authenticate Users Sending POST Requests
Client Requests Using HTTP
User Sessions and Transactions
Oracle® Database Servlet Responses Using HTTP
If a session ID is passed by the client in the HTTP headers, this operation is performed in the context of that session. If no transaction is active in the HTTP session, a new database transaction is started.
Oracle® Database Propagation Using HTTP and HTTPS
If any of these users have privileges to access the queue/thread specified in the request, then the Oracle® database server superuser creates a session on behalf of this user. Subsequent requests in the session are part of the same transaction until an explicit COMMIT or ROLLBACK request is made.
Deploying the Oracle® Database XML Servlet
ORACLE_HOME/jlib/jssl-1_1.jar ORACLE_HOME/jlib/jta.jar ORACLE_HOME/jlib/orai18n.jar. ORACLE_HOME/jlib/orai18n-collation.jar ORACLE_HOME/jlib/orai18n-mapping.jar ORACLE_HOME/jlib/orai18n-utility.jar ORACLE_HOME/lib/http_client.jar ORACLE_HOME/lib/lclasses12.zip lib/xmlparserv2.jar ORACLE_HOME/lib/xschema.jar ORACLE_HOME/lib/xsu12.jar ORACLE_HOME/rdbms/jlib/aqapi.jar ORACLE_HOME/rdbms/jlib/aqxml.jar ORACLE_HOME/rdbmsACLE/jlib.jar/jrdbmsACLE/jlib.jar/ jlib/xdb.jar.
Internet Data Access Presentation (IDAP)
SOAP Message Structure
SOAP Envelope
SOAP Header
SOAP Body
SOAP Method Invocation
HTTP Headers
In the latter two cases, additional notice holders may be present in the results of the extension request.
Results from a Method Request
Request and Response IDAP Documents
IDAP Client Requests for Enqueue
It specifies the name of the queue to which the message will be moved if the number of failed attempts to delete the queue has exceeded max_. If recorded, the transaction is committed at the end of the request.
IDAP Client Requests for Dequeue
If the specified exception queue does not exist at the time of the move, the message is moved to the default exception queue associated with the queue table, and a warning is logged in the warning log. It can contain different elements depending on the load type of the target queue/topic.
IDAP Client Requests for Registration
FIRST_MESSAGE retrieves the first message available that matches the search criteria. If the previous message belongs to a message group, Oracle® Database retrieves the next available message that matches the search criteria and belongs to the message group. NEXT_TRANSACTION skips the rest of the current transaction group and fetches the first message of the next transaction on group.
IDAP Client Requests to Commit a Transaction
MESSAGE is the default and retrieves the next available message that matches the search criteria. This option can only be used if message grouping is enabled for the current queue.
IDAP Client Requests to Roll Back a Transaction
IDAP Server Response to an Enqueue Request
IDAP Server Response to a Dequeue Request
IDAP Server Response to a Register Request
IDAP Commit Response
IDAP Rollback Response
IDAP Notification
IDAP Response in Case of Error
Notification of Messages by e-mail
Troubleshooting Oracle® Database
Debugging Oracle® Database Propagation Problems
Check to see that the queue table sys.aq$_prop_table_instno exists in DBA_. Check that the consumer trying to remove a message from the destination queue is a broadcast message receiver.
Oracle® Database Error Messages
In the case of real application clusters (RAC), this queue table and queue pair must exist for each RAC node in the system. This means that the fastest subscriber of the sender's message is unable to keep pace with the rate at which messages are queued.
Oracle® Database Administrative Interface
Managing Queue Tables
Creating a Queue Table
DBMS_AQADM.CREATE_QUEUE_TABLE( . queue_table => 'test.obj_qtab', queue_payload_type => 'test.message_typ');. DBMS_AQADM.CREATE_QUEUE_TABLE( . queue_table => 'test.example_qtab', queue_payload_type => 'test.message_typ', storage_clause => 'tablespace-voorbeeld');.
Altering a Queue Table
Dropping a Queue Table
Purging a Queue Table
If block is TRUE, an exclusive lock on all queues in the queue table is held during the queue table cleanup. See also: "DBMS_AQADM" in Oracle Database PL/SQL Packages and Types Reference for more information about DBMS_AQADM.PURGE_.
Migrating a Queue Table
If a schema was created by an import of an export repository from a lower version or has Oracle® database queues upgraded from a lower version, then attempts to drop it with DROP USER CASCADE will fail with ORA-24005.
Managing Queues
Creating a Queue
DBMS_AQADM.CREATE_QUEUE( . queue_name => 'test.multiconsumer_queue', queue_table => 'test.multiconsumer_qtab');. DBMS_AQADM.CREATE_QUEUE( . queue_name => 'test.another_queue', queue_table => 'test.multiconsumer_qtab');.
Altering a Queue
Starting a Queue
Stopping a Queue
Dropping a Queue
Managing Transformations
Creating a Transformation
Modifying a Transformation
All references to the attributes of the source type must be preceded by source.user_data. You must also have EXECUTE rights on the user-defined types that are the source and destination types of the transformation, and have EXECUTE rights on any PL/SQL function used in the transformation function.
Dropping a Transformation
Granting and Revoking Privileges
Granting Oracle® Database System Privileges
You must also have EXECUTE privileges on the user-defined types that are the source and destination types of the transformation, and EXECUTE privileges on any PL/SQL function used in the transformation function. admin_option => FALSE);.
Revoking Oracle® Database System Privileges
Granting Queue Privileges
Revoking Queue Privileges
Managing Subscribers
Adding a Subscriber
If the queue_to_queue parameter is set to TRUE, then the added subscriber is a queue-to-queue subscriber. When queue-to-queue propagation is set up between a source queue and a destination queue, queue-to-queue subscribers receive messages through this propagation plan.
Altering a Subscriber
Removing a Subscriber
Managing Propagations
Scheduling a Queue Propagation
You can query runtime propagation errors in the LAST_ERROR_MSG column of the USER_QUEUE_SCHEDULES view. If the date function specified in the next_time parameter of DBMS_AQADM.SCHEDULE_PROPAGATION results in a short interval between windows, the number of unsuccessful retries can quickly reach 16, disabling the schedule. queue_name => 'test.multiconsumer_queue', destination => 'another_db.world' . destination_queue => 'target_queue');.
Verifying Propagation Queue Type
Altering a Propagation Schedule
Enabling a Propagation Schedule
Disabling a Propagation Schedule
DBMS_AQADM.DISABLE_PROPAGATION_SCHEDULE( . queue_name => 'test.multiconsumer_queue', destinacioni => 'onether_db.world');.
Unscheduling a Queue Propagation
Managing Oracle® Database Agents
Creating an Oracle® Database Agent
Altering an Oracle® Database Agent
Dropping an Oracle® Database Agent
Enabling Database Access
The SYS.AQ$INTERNET_USERS view contains a list of all Oracle® Database Internet agents and the names of the database users whose privileges have been granted to them.
Disabling Database Access
Adding an Alias to the LDAP Server
Deleting an Alias from the LDAP Server
Oracle® Database & Messaging Gateway Views
DBA_QUEUE_TABLES: All Queue Tables in Database
USER_QUEUE_TABLES: Queue Tables in User Schema
ALL_QUEUE_TABLES: Queue Tables Queue Accessible to the Current User
DBA_QUEUES: All Queues in Database
USER_QUEUES: Queues In User Schema
ALL_QUEUES: Queues for Which User Has Any Privilege
DBA_QUEUE_SCHEDULES: All Propagation Schedules
USER_QUEUE_SCHEDULES: Propagation Schedules in User Schema
QUEUE_PRIVILEGES: Queues for Which User Has Queue Privilege
AQ$Queue_Table_Name: Messages in Queue Table
SENDER_NAME VARCHAR2(30) - The name of the agent queuing the message (only valid for 8.1 compatible queue tables). CONSUMER_NAME VARCHAR2(30) - The name of the agent receiving the message (only valid for 8.1-compliant multi-consumer queue tables).
AQ$Queue_Table_Name_S: Queue Subscribers
AQ$Queue_Table_Name_R: Queue Subscribers and Their Rules
DBA_QUEUE_SUBSCRIBERS: All Queue Subscribers in Database
USER_QUEUE_SUBSCRIBERS: Queue Subscribers in User Schema
ALL_QUEUE_SUBSCRIBERS: Subscribers for Queues Where User Has Queue Privileges
DBA_TRANSFORMATIONS: All Transformations
DBA_ATTRIBUTE_TRANSFORMATIONS: All Transformation Functions
USER_TRANSFORMATIONS: User Transformations
USER_ATTRIBUTE_TRANSFORMATIONS: User Transformation Functions
DBA_SUBSCR_REGISTRATIONS: All Subscription Registrations
USER_SUBSCR_REGISTRATIONS: User Subscription Registrations
AQ$INTERNET_USERS: Oracle® Database Agents Registered for Internet Access
G)V$AQ: Number of Messages in Different States in Database
G)V$BUFFERED_QUEUES: All Buffered Queues in the Instance
G)V$BUFFERED_SUBSCRIBERS: Subscribers for All Buffered Queues in the Instance
G)V$BUFFERED_PUBLISHERS: All Buffered Publishers in the Instance
G)V$PERSISTENT_QUEUES: All Active Persistent Queues in the Instance
G)V$PERSISTENT_QMN_CACHE: Performance Statistics on Background Tasks for Persistent Queues
G)V$PERSISTENT_SUBSCRIBERS: All Active Subscribers of the Persistent Queues in the Instance
G)V$PERSISTENT_PUBLISHERS: All Active Publishers of the Persistent Queues in the Instance
G)V$PROPAGATION_SENDER: Buffer Queue Propagation Schedules on the Sending (Source) Side
G)V$PROPAGATION_RECEIVER: Buffer Queue Propagation Schedules on the Receiving (Destination) Side
G)V$SUBSCR_REGISTRATION_STATS: Diagnosability of Notifications
V$METRICGROUP: Information about the Metric Group
G)V$STREAMSMETRIC: Streams Metrics for the Most Recent Interval
G)V$STREAMSMETRIC_HISTORY: Streams Metrics Over Past Hour
G)V$QUEUEMETRIC: Queue Metrics for the Most Recent Interval
G)V$QUEUEMETRIC_HISTORY: Queue Metrics Over Past Hour
DBA_HIST_STREAMSMETRIC: Streams Metric History
DBA_HIST_QUEUEMETRIC: Queue Metric History
MGW_GATEWAY: Configuration and Status Information
AGENT_NAME VARCHAR2 Message Gateway agent ping status name AGENT_PING VARCHAR2. MGWADM.STARTUP, the job queue has run and the Message Gateway agent is starting.
MGW_AGENT_OPTIONS: Supplemental Options and Properties
MGW_LINKS: Names and Types of Messaging System Links
MGW_MQSERIES_LINKS: WebSphere MQ Messaging System Links
MGW_TIBRV_LINKS: TIB/Rendezvous Messaging System Links
MGW_FOREIGN_QUEUES: Foreign Queues
MGW_JOBS: Messaging Gateway Propagation Jobs
MGW_SUBSCRIBERS: Information for Subscribers
MGW_SCHEDULES: Information about Schedules
SOURCE VARCHAR2 Propagation source START_DATE DATE Reserved for future use START_TIME VARCHAR2 Reserved for future use Table 9–17 (continued) MGW_SCHEDULES View properties.
Oracle® Database Operations Using PL/SQL
Using Secure Queues
Enqueuing Messages
The relative_msgid attribute specifies the identifier of the message referenced by the out-of-sequence operation. The value specified in the queue options is used to set the message delivery method.
Enqueuing an Array of Messages
Example 10–9 Queuing a message, setting the delay and timeout DECLARE . enqueue_options DBMS_AQ.enqueue_options_t;. message_properties DBMS_AQ.properties_message_t;. Example 10–10 Queuing a message, defining a DECLARE transformation. enqueue_options DBMS_AQ.enqueue_options_t;. message_properties DBMS_AQ.properties_message_t;.
Listening to One or More Queues
Use the ENQUEUE_ARRAY function to enqueue an array of payloads using a corresponding array of message properties. The message IDs are returned to an array of RAW(16) entries of type DBMS_AQ.msgid_array_t.
Dequeuing Messages
DBMS_AQ.DEQUEUE( . message_properties => message_properties, payload => message, . msgid => message_handle);. Example 10–17 Dequeuing messages for a RED from a multi-consumer queue SET SERVEROUTPUT ON. dequeue_options DBMS_AQ.dequeue_options_t;. message_properties DBMS_AQ.message_properties_t;.
Dequeuing an Array of Messages
The message identifiers are returned in an array of RAW(16) entries of type DBMS_AQ.msgid_array_t. In addition, the navigation attribute of dequeue_options provides two options specific to DBMS_AQ.DEQUEUE_ARRAY.
Registering for Notification
If you register for e-mail notifications, you must set the host name and port name for the SMTP server that the database uses to send e-mail notifications. When registering for HTTP notifications, you may want to set the host name and port number for the proxy server and a list of non-proxy domains that the database will use to post HTTP notifications.
Unregistering for Notification
If necessary, set the send-from email address set by the database as sent from field. An internal queue called SYS.AQ_SRVNTFN_TABLE_Q stores the messages to be processed by the job queue processes.
Posting for Subscriber Notification
The post_list parameter specifies the list of anonymous subscriptions to which you want to post. The name attribute specifies the name of the anonymous subscription to which you want to post.
Adding an Agent to the LDAP Server
To receive notifications from other applications through DBMS_AQ.POST, the namespace must be DBMS_AQ.NAMESPACE_ANONYMOUS.
Removing an Agent from the LDAP Server
Introducing Oracle JMS
General Features of JMS and Oracle JMS
JMS Connection and Session
ConnectionFactory Objects
Using JNDI to Look Up ConnectionFactory Objects
The user can first login to Oracle database and then let the database update the LDAP entry. To connect to LDAP through the database server, the parameters for the registerConnectionFactory method include a JDBC connection (to a user with AQ_ADMINISTRATOR_ROLE), the name of the.
JMS Connection
After the database is configured to use an LDAP server, the JMS administrator can use ConnectionFactory, QueueConnectionFactory, and. The user logging on to the database must have the AQ_ADMINISTRATOR_ROLE to perform this operation.
