Last post Oct 18, 2012 02:28 PM by drdexter33
Oct 15, 2012 09:17 AM|drdexter33|LINK
I think this might be a longshot, but I'm going to try it anyway.
We’ve identified a problem we are having with an Oracle Advanced queue, however cannot seem to find a resolution with Oracle yet.
Here’s the issue:
• We've written a multi-threaded Windows service that uses a specific db connection to an Oracle schema called schema_X through an Oracle database instance called IVRSD1.
• When testing the service, a tester is connected to the same schema (schema_x) through a different Oracle instance called IVRSD2 using TOAD.
• The tester updates a table.
• This UPDATE triggers a call to a stored procedure which copies the respective updated record to a queue.
• Our windows service (using the IVRSD1 instance) should see the queue entry and dequeue it and process it.
• Since the tester is connected to the IVRSD2 instance and the service is connected to the IVRSD1 instance, it does not.
• If the tester stipulates the IVRSD1 instance (and is in turn connected to the IVRSD1 instance), the service will recognize that the queue has a record pushed onto it and it will process the record accordingly.
This seems to be a problem on the Oracle and we have an open ticket with Oracle support, but still cannot find a resolution.
Thanks in advance for any help
Oct 18, 2012 02:28 PM|drdexter33|LINK
AQ doesn't work with RAC clusters...
Bug 3830972 - DBMS_AQ.LISTEN ON RAC CLUSTER DOES NOT SEE NEW ENQUEUE MESSAGES
Fixed in 10.2
Using 2 node RAC configuration.
When using dbms_aq.listen() any enqueue made to a
queue after the listen has started is not noticed.
Enqueue happens on cluster node 1.
Both cluster nodes are listening to the queue where
If the listen is started after the message have
been queued, they are noticed almost instantly.
I believe dbms_aq.listen is not supported in RAC if
the enqueue and listen happen on different nodes because
a listen call will not be posted by enqueue happening on
If the listen is started after the messages have been queued,
they are noticed almost instanly.
To be seeing this bug the following must be true :
1. Running under a RAC environment
2. DBMS_AQ.LISTEN() on one node doesn't detect new messages enqueued on other
None, other than to have a "heartbeat" dummy message posted on each node
every now and then. This obviously requires application support and is
therefore unlikely to be of use. Another alternative is to always ensure that
messages are enqueued at the node that performs the listen call.
]] DBMS_AQ.LISTEN() might not have detected new messages enqueued on other
]] nodes in a RAC cluster. This has now been corrected.
Bug 3959559: IMPROVE DBMS_AQ.LISTEN() FUNCTIONALITY UNDER RAC
Status 11 - Code/Hardware Bug (Response/Resolution)
@ This bug has been filed to track the requirement that we need a longer term
@ resolution to the problem highlighted in bug 3830972. In that bug,
@ dbms_aq.listen() did not detect new messages enqueued at different RAC nodes.
@ Bug 3830972 has implemented an event to allow the detection of new messages
@ via a polling mechanism so that the change can be backported. However, we
@ might want to use KSR's ability to detect global messages, expose the polling
@ feature via the PLSQL API or resolve the issue in some other way.
-- Bug has been assigned but no further info in bug.