6
Frequently Asked Questions

This section answers some of the most commonly asked questions about Advanced Queuing.

General Questions

How are messages that have been dequeued but are still retained in the queue table accessed?

Access them using SQL. Messages in the queue table (either because they are being retained or because they have not yet been processed). Each queue has a view that you can use (see "Selecting the Number of Messages in Different States for the Whole Database" ).

Message retention means the messages are there, but how does the subscriber access these messages?

Typically we expect the subscriber to access the messages using the dequeue interface. If, however, you would like to see processed or waiting messages, you can either dequeue by message id or use SQL.

Can the sort order be changed after the queue table is created?

You cannot change the sort order for messages after you have created the queue table.

How do I dequeue from an exception queue?

The exception queue for a multiconsumer queue must also be a multiconsumer queue.

Expired messages in multiconsumer queues cannot be dequeued by the intended recipients of the message. However, they can be dequeued in the REMOVE mode once (only once) using a NULL consumer name in dequeue options. Messages can also be dequeued from an exception queue by specifying the message ID.

Expired messages can be dequeued only by specifying message ID if the multiconsumer exception queue was created in a queue table without the compatible parameter or with the compatible parameter set to '8.0'

What does the latency parameter mean in scheduling propagation?

If a latency less than 0 was specified in the propagation schedule, the job is rescheduled to run after the specified latency. The time at which the job actually runs depends on other factors, such as the number of ready jobs and the number of job_queue_processes. It may also be affected by the value for job_queue_interval. Please refer to the MANAGING JOB QUEUES chapter of the Oracle9i Database Administrator's Guide for more information on job queues and SNP background processes.

How can I control the tablespaces in which the queue tables are created?

You can pick a tablespace for storing the queue table and all its ancillary objects via the storage_clause parameter in DBMS_AQADM.CREATE_QUEUE_TABLE. However, once you pick the tablespace, all IOTs and indexes created for that queue table will go to the specified tablespace. Currently, you do not have a choice to split them between different tablespaces.

How do you associate Oracle Parallel Server instance affinities with queue tables?

In 8.1 you can associate OPS instance affinities with queue tables. If you are using q1 and q2 in different instances, you can use alter_queue_table (or even create queue table) on the queue table and set the primary_instance to the appropriate instance_id.

Can you give me some examples of a subscriber rule containing - message properties - message data properties.

Yes, here is a simple rule that specifies message properties - rule = 'priority 1';
here are example rules that specify a combination of message properties and data attributes: rule = 'priority 1 AND tab.userdata.sal 1000' rule = '((priority between 0 AND 3) OR correlation = 'BACK_ORDERS') AND tab.userdata.customer_name like ''JOHN DOE'')'

Note that user data properties or attributes apply only to object payloads and must be prefixed with tab.userdata in all cases. Check documentation for more examples.

Is registration for notification (OCI) the same as starting a listener?

No. Registration is an OCI client call to be used for asynchronous notifications (that is, push). It provides a notification from the server to the client when a message is available for dequeue. A client side function (callback) is invoked by the server when the message is available. Registration for notification is both non-blocking and non-polling.

What is the use of non-persistent queues?

To provide a mechanism for notification to all users that are currently connected. The non-persistent queue mechanism supports the enqueue of a message to a non-persistent queue and OCI notifications are used to deliver such messages to users that are currently registered for notification.

Is there a limit on the length of a recipient list? Or on the number of subscribers for a particular queue?

Yes, 1024 subscribers or recipients for any queue.

How can I clean out a queue with UNDELIVERABLE messages?

You can dequeue these messages by msgid. You can find the msgid by querying the queue table view. Eventually the messages are moved to the exception queue (you must have the AQ background process running for this to happen). You can dequeue these messages from the exception queue with a normal dequeue.

Is it possible to update the message payload after it has been enqueued?

Only by dequeuing and enqueuing the message again. If you are changing the message payload, it is a different message.

Can asynchronous notification be used to invoke an executable every time there is a new message?

Notification is possible only to OCI clients. The client does not have to be connected to the database to receive notifications. The client specifies a callback function which will be executed for each message. Asynchronous Notification cannot be used to invoke an executable, but it is possible for the callback function to invoke a stored procedure.

Does propagation work from multiconsumer queues to single-consumer queues and vice versa?

Propagation from a multiconsumer queue to a single consumer queue is possible. The reverse is not possible (propagation is not possible from a single consumer queue).

Why do I sometimes get ORA-1555 error on dequeue?

You are probably using the NEXT_MESSAGE navigation option for dequeue. This uses the snapshot created during the first dequeue call. After that the other dequeue calls generate more undo which fills up the rollback segment and hence generates 1555.

The workaround is to use the FIRST_MESSAGE option to dequeue the message. This will re-execute the cursor and get a new snapshot. This might not perform as well, so we suggest you dequeue them in batches: FIRST_MESSAGE for one, and NEXT_MESSAGE for the next, say, 1000 messages, and then FIRST_MESSAGE again, and so on.

What are the different subscriber types recorded on the subscriber table?

The subscriber_types and their values are:

1 - Current Subscriber. The subscribers name, address and protocol are in the same row.

2 - Ex subscriber - A subscriber that unsubscribed but had agent entries in the history aq$_queuetable_h IOT.

4 - Address - Used to store addresses of recipients. The name is always NULL. The address is always non-NULL.

8 - Proxy for Propagation - The name is always NULL.

database proxy to local queues, address=NULL, protocol=0

database proxy to remote queues, address=dblink address, protocol=0

3rd party proxies, address = NULL, protocol = 3rd party protocol.

After a message has been moved to an exception queue, is there a way, using SQL or otherwise, of identifying which queue the message resided in before moving to the exception queue?

No, AQ does not provide this information. To get around this, the application could save this information in the message.

What is the order in which messages are dequeued if many messages are enqueued in the same second?

When the enq_time is the same for messages, there is another field called step_no that will be monotonically increasing (for each message that has the same enq_time). Hence this helps in maintaining the order of the messages. There will be no situation when both enq_time and step_no are the same for more than one message enqueued from the same session.

What happened to OMB? When should we use AQ and when should we use Oracle MessageBroker?

In Oracle9i, OMB functionality is provided in the Oracle database. So, if you are using the Oracle9i database, use the functionality offered by the database.

You do not need OMB.

With Oracle8i, use OMB in the following scenarios:

• To integrate with Tibco and MQ Series

• To use HTTP framework

Use JMS functionality directly from the database in other scenarios.

Can I use AQ with Virtual Private Database?

Yes, you can specify a security policy with AQ queue tables. While dequeuing, use the dequeue condition (deq_cond) or the correlation ID for the policy to be applied. You can use "1=1" as the dequeue condition. If you do not use a dequeue condition or correlation ID, the dequeue will result in an error.

How do I clean up my retained messages?

The Advanced Queuing retention feature can be used to automatically clean up messages after the user-specified duration after consumption.

I have an application in which I inserted the messages for the wrong subscriber. How do I clean up those messages?

You can do a dequeue with the subscriber name or by message ID. This consumes the messages, which will be cleaned up after their retention time expires.

I'm running propagation between multiple Oracle databases. For some reason, one of the destination databases has gone down for an extended duration. How do I clean up messages for that destination?

To clean up messages for a particular subscriber, you can remove the subscriber and add the subscriber again. Removing the subscriber removes all the messages for that subscriber.

JMS Questions

Why do the JMS dbms_aqadm.add_subscriber and dbms_aqadm.remove_subscriber calls sometimes hang when there are concurrent enqueues or dequeues happening on the same queue to which these calls are issued?

Add_subscriber and remove_subscriber are administrative operations on a queue. Though AQ does not prevent applications from issuing administrative and operational calls concurrently, they are executed serially. Both add_subscriber and remove_subscriber will block until pending transactions that have enqueued or dequeued messages commit and release the resources they hold. It is expected that adding and removing subscribers will not be a frequent event. It will mostly be part of the setup for the application. The behavior you observe will be acceptable in most cases. The solution is to try to isolate the calls to add_subscriber and remove_subscriber at the setup or cleanup phase when there are no other operations happening on the queue. That will make sure that they will not stay blocked waiting for operational calls to release resources.

Why do the TopicSession.createDurableSubscriber and TopicSession.unubscribe calls raise JMSException with the message "ORA - 4020 - deadlock detected while trying to lock object"?

CreateDurableSubscriber and unsubscribe calls require exclusive access to the Topics. If there are pending JMS operations (send/publish/receive) on the same Topic before these calls are issued, the ORA - 4020 exception is raised.

There are two solutions to the problem:

1. Try to isolate the calls to createDurableSubscriber and unsubscribe at the setup or cleanup phase when there are no other JMS operations happening on the Topic. That will make sure that the required resources are not held by other JMS operational calls. Hence the error ORA - 4020 will not be raised.

2. Issue a TopicSession.commit call before calling createDurableSubscriber and unsubscribe call.

Why doesn't AQ_ADMINISTRATOR_ROLE or AQ_USER_ROLE always work for AQ applications using Java/JMS API?

In addition to granting the roles, you would also need to grant execute to the user on the following packages:

• grant execute on sys.dbms_aqin to <userid>

• grant execute on sys.dbms_aqjms to <userid>

Why do I get java.security.AccessControlException when using JMS MessageListeners from Java stored procedures inside Oracle8i JServer?

To use MessageListeners inside Oracle8i JServer, you can do one for the following

1. GRANT JAVASYSPRIV to <userid>

Call dbms_java.grant_permission ('JAVASYSPRIV', 'SYS:java.net.SocketPermission', '*', 'accept,connect,listen,resolve');

Internet Access Questions

What is IDAP?

IDAP is Internet Data Access Presentation. An IDAP document encapsulates the AQ operation request and response in XML. IDAP is used to perform AQ operations such as enqueue, dequeue, send notifications, register for notifications, and propagation over the Internet standard transports-HTTP(s) and email. In addition, IDAP encapsulates transactions, security, transformation, and the character set ID for requests.

Which Web servers are supported for AQ Internet access functionality? Do I have to use Apache or can I use any Web server? Which servlet engines are supported for AQ Internet access? Can I use Tomcat?

Internet access functionality for AQ is supported on Apache. This feature is certified to work with Apache, along with the Tomcat or Jserv servlet execution engines. However, the code does not prevent the servlet from working with other Web server and servlet execution engines that support Java Servlet 2.0 or higher interfaces.

How do I get transactional behavior while using email for AQ operations?

When you send IDAP messages via SMTP, each request is a separate transaction. The IDAP request must contain <AQXmlCommit/> as part of the message request to ensure that the operation is committed.

How does an Internet agent tie to an AQ agent stored in Oracle Internet Directory?

You can create an alias to an AQ agent in Oracle Internet Directory (OID). You can use these AQ agent aliases in the IDAP document sent over the Internet to perform AQ operations. Using aliases prevents exposing the internal name of the AQ agent.

Can I use my own authentication framework for authentication?

Yes, you can use your own authentication framework for authentication. HTTP POST requests to the AQ Servlet for AQ operations must be authenticated by the Web server. For example, in Apache, the following can be used to restrict access (using basic authentication) to servlets installed under aqserv/servlet. In this example, all users sending POST requests to the servlet are authenticated using the users file in /apache/htdocs/userdb.

<Location /aqserv/servlet>

<Limit POST>


AuthName "Restrict AQ Servlet Access"
AuthType Basic
AuthUserFile /apache/htdocs/userdb/users
require valid-user


</Limit>

</Location>

You say IDAP is transport independent. Can I use my own transport to perform AQ operations using IDAP?

Yes, IDAP is transport independent. The IDAP document is sent over HTTP(s) or email. You can use IDAP to perform AQ operations over any transport. The IDAP client request can be sent over any transport to the AQ Servlet. The AQ Servlet parses the IDAP document and performs the AQ operation. The response for the AQ operation is also in IDAP. It can be sent to the client over your transport. In fact, you can implement AQ propagation over your transport.

Oracle Internet Directory (OID) Questions - Global Agents, Global Events, and Global Queues

Which events can be registered in OID?

All types of events--system events, user events, and notifications on queues--can be registered with OID. System events are database startup, database shutdown, and system error events. User events include user log on and user log off, DDL statements (create, drop, alter), and DML statement triggers. Notifications on queues include OCI notifications, PL/SQL notifications, and email notifications.

How do I use agent information stored in an OID?

You can create aliases for an AQ agent in OID. These aliases can be specified while performing AQ operations-enqueue, dequeue, and notifications. This is specifically useful while performing AQ operations over the Internet when you do not want to expose an internal agent name. An alias can be used in an AQ operation (IDAP request).

Transformation Questions

What happens to enqueue, dequeue, or propagation if the transformation mapping raises an error?

Enqueue and dequeue of the message will raise the error to the application. If the error occurs during the dequeue operation, the retry count of the message is incremented. If the retry count exceeds max_retries, the message is moved to the exception queue. If the error occurs during propagation, it is handled in a manner similar to dequeue; propagation of the message will fail. It will be attempted again and the message will be moved to the exception queue when retry count exceeds max_retries for the queue.

How do you do transformation of XML data?

Transformation of XML data can be done in one of the following ways:

• Using the extract operator supported on XMLType to return an object of XMLType after applying the supplied XPath expression.

• Creating a PL/SQL function that transforms the XMLType object by applying an XSLT transformation to it, using the package XSLPROCESSOR.

Performance Questions

What is the maximum number of queues that a table can have without affecting performance?

Performance is not affected by the number of queues in a table.

When messages are moved from one queue to another using propagation, is there any optimization to move the messages in batches, rather than one at a time?

Yes, if it is optimized, propagation happens in batches.

If the remote queue is in a different database, we use a sequencing algorithm to avoid the need for a two-phase commit.

When a message needs to be sent to multiple queues in the same destination, it is sent multiple times. If the message needs to be sent to multiple consumers in the same queue at the destination, it is sent only once.

When is it useful to create indexes on a queue table? How do I create them?

Creating an index on the queue table is useful in the following scenarios:

1. Dequeuing using correlation ID: To expedite dequeue, an index can be created on the column corr_id of the underlying queue table AQ$_<QueueTableName>.

2. Dequeue using a condition: Assume this condition to the where-clause for the SELECT on the underlying queue table. An index on <QueueTableName> can be created to expedite the performance this SELECT statement.

What is the performance of Java (JMS) versus the PL/SQL API for AQ?

We do not have a specific performance evaluation of JMS versus the PL/SQL API. In general, the PL/SQL API is slightly better than the JMS API. The performance of the JMS and PL/SQL APIs in version 8.1.7 and higher should be comparable.

Installation Questions

How do I set up Internet access for AQ? What components are required?

See Chapter 17 for a full discussion. The following summarizes the steps required to set up Internet access for AQ queues:

1. Set up the AQ Servlet: If you are using a servlet execution engine that supports the Java Servlet 2.2 specification (such as Tomcat), you must create a servlet that extends the oracle.AQ.xml.AQxmlServlet class. If you are using a servlet execution engine that supports the Java Servlet 2.0 specification (like Apache Jserv), you must create a servlet that extends the oracle.AQ.xml.AQxmlServlet20 class. Implement the init() method in the servlet to specify database connection parameters.

2. Set up user authentication: Configure the Web server to authenticate all the users that send POST requests to the AQ Servlet. Only authenticated users are allowed to access the AQ Servlet.

3. Set up user authorization: Register the AQ agent name that will be used to perform AQ operations using DBMS_AQADM.CREATE_AQ_AGENT. Map the AQ agent to the database users using DBMS_AQADM.ENABLE_DB_ACCESS.

4. Now clients can write IDAP requests and send to the AQ Servlet via HTTP POST.

How do I set up email notifications?

Here are the steps for setting up your database for email notifications:

1. Set the SMTP mail host: Invoke DBMS_AQELM.SET_MAILHOST as an AQ administrator.

2. Set the SMTP mail port: Invoke DBMS_AQELM.SET_MAILPORT as an AQ administrator. If not explicit, set defaults to 25.

3. Set the SendFrom address: Invoke DBMS_AQELM.SET_SENDFROM.

4. After setup, you can register for email notifications using the OCI or PL/SQL API.

How do I perform AQ operations via email?

See Chapter 17 for a full discussion. Currently, these operations are supported by Oracle Email Server 5.5 and higher. In summary, follow the steps for setting up Internet access for AQ. In addition, do the following:

1. Create an AQ Internet agent to access the servlet using SMTP. This agent's digital certificate should be registered in LDAP. The certificate location must be specified when the agent is registered using the DBMS_AQADM.CREATE_AQ_AGENT procedure.

2. Set up the Web server: Configure the Web server to receive requests from a user called ORACLE_SMTP_AGENT. This user will be used to access the AQ Servlet. Also specify setEmailServerAddr or setEmailServerHost in the init() method of the AQ Servlet.

3. Set up Oracle Email Server:
   1. Run $ORACLE_HOME/admin/emailrule.sql to create an AQ schema on the email server database.

   2. Create an email account for the destination database in which the AQ operations are to be performed.

   3. Set up an email rule for the destination database, so that it can route the AQ requests to the AQ Servlet on the web server. This can be done using the DBMS_AQST.REGISTER_DB procedure.

4. Now clients can write IDAP requests and send to the AQ Servlet via email.

How do I set up AQ propagation over the Internet?

See Chapter 17 for a full discussion. In summary, follow the steps for setting up Internet access for AQ. The destination databases need to be set up for Internet access, as follows:

1. At the source database, create the dblink with protocol as http, and host and port of the Web server running the AQ Servlet with the username password for authentication with the Web server/servlet runner. For example, if the Web server is running on machine webdest.oracle.com and listening for requests on port 8081, then the connect string of the database is (DESCRIPTION=(ADDRESS=(PROTOCOL=http)(HOST=webdest.oracle.com)(PORT=8081)) If SSL is used, specify https as the protocol in the connect string. The database link is created as follows: create public database link propdb connect to john identified by welcome using '(DESCRIPTION=(ADDRESS=(PROTOCOL=http)(HOST=webdest.oracle.com)(PORT=8081))'; where user John with password Welcome is used to authenticate with the Web server, and is also known by the term AQ HTTP agent.

2. If SSL is used, create an Oracle wallet and specify the wallet path at the source database execute dbms_aqadm.set_aq_propagationwallet('/home/myuid/cwallet.sso', 'welcome');

3. Deploy the AQ Servlet at the destination database: Create a class AQPropServlet that extends oracle.AQ.xml.AQxmlServlet20 (if you are using a Servlet 2.0 execution engine like Apache Jserv) or extends oracle.AQ.xml.AQxmlServlet (if you are using a Servlet 2.2 execution engine like Tomcat). This servlet must connect to the destination database. The servlet must be deployed on the Web server in the path aqserv/servlet.

   NOTE: In 9i, the propagation servlet name and deployment path are fixed, that is, they must be AQPropServlet and the aqserv/servlet respectively.

4. At the destination database: Set up the authorization and authentication for the Internet user performing propagation, in this case, John.

5. Start propagation at the source site by calling dbms_aqadm.schedule_propagation('src_queue', 'propdb').