CODEWITHSUNDEEP
oracle11g
Tikhon
Tikhon

Reputation: 1024

Windows Service Dequeuing with Oracle Advanced Queue

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, so I thought I'd post it here.

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

doug

Upvotes: 0

Views: 376

Answers (1)

Tikhon
Tikhon

Reputation: 1024

Oracle Advanced Queues Do Not Work With RAC

Oracle Bug 3830972

Bug 3830972 - DBMS_AQ.LISTEN ON RAC CLUSTER DOES NOT SEE NEW ENQUEUE MESSAGES Fixed in 10.2 ...

PROBLEM:

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 enqueue happened. If the listen is started after the message have been queued, they are noticed almost instantly.

DIAGNOSTIC ANALYSIS:

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 another node.

WORKAROUND:

If the listen is started after the messages have been queued, they are noticed almost instanly. ... Rediscovery Information: 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 nodes.

Workaround: 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.

Upvotes: 1

Related Questions