Forum Stats

  • 3,837,196 Users
  • 2,262,234 Discussions
  • 7,900,219 Comments

Discussions

Inconsistent AQ Notifications

User_BHZGL
User_BHZGL Member Posts: 8 Employee
edited May 12, 2016 2:07AM in Advanced Queueing

Dear All,

We have implemented a product feature using the Oracle (Multi Consumer) Queue on Oracle DB 11 g.
The queue is enabled with AQ Notification ( having one subscriber ) using the Java Interfaces to Oracle AQ, AQNotificationListener and AQNotificationRegistration from middle tier ( implemented in Oracle ADF ).

As part of this feature, UI operation invokes DB which generates a batch Job and enqueues couple of messages ( payload as Oracle Object Type) on the above Queue. On enqueue operation, Middle tier gets notified, middle tier has implemented a routine which reads all the messages from the Queue Table and processes them. After processing the messages from QT, it invokes back DB and DB dequeues all the records from the Queue.

This has been working consistently on several environments. On one of the environment which is also RAC enabled (with two nodes, Version - 11.2.0.4 ).
Middle-tier keeps on getting notifications for couple of executions and then, it suddenly stops getting notifications for enqueue operation.

As a result, Middle tier doesn't read the records from QT and records are not getting processed.
In addition, if user performs similar operation again, which enqueues another set of records, middle tier again starts getting notification.

The AQ notifications are getting lost suddenly, the root cause is not known.

We have verified that listener has been registered to the subscriber and it is listed in dba_subscr_registrations table. In addition, registration shows up middle tier host with port as 47632, this has been made sure that port 47632 from MT machine is accessible from DB.

As suggested from different articles, also tried the tracing the following DB events to capture if there are some issues during notification:
  24040  AQ tracing event
  10865   Control tracing of notification operations
  10850   Enable time manager tracing
  10852   Enable tracing for Enqueue Dequeue Operations
  26700   Enable traces for inter-instance IPC communication.

We didn't find any errors in Oracle background qmon, emon, job process and their respective slave process trace files.

Does anyone have any pointers or suggestions to find the root cause for missing AQ notifications.

Best Regards,
Sachin

This discussion has been closed.