My Oracle Support Banner

Query returns 0 rows after successful row insert/message enqueue when using Tibco Application in a RAC environment (Doc ID 1412774.1)

Last updated on MARCH 04, 2019

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.1 to 11.2.0.3 [Release 11.2]
Information in this document applies to any platform.

Symptoms

Intermittently a query of the following form

SELECT /*+ index (staffo pk_staffo) */ o_reqid FROM <Schema>.<Table_1> WHERE o_queuename=:1 AND o_reqid=:2 AND o_reqidhost=:3

executed by a Tibco WIS process does not return rows which have previously been successfully inserted into the <Table_1>.

The Tibco application does the following

A BS process in customer application performs the following operations

1 - Insert unique row in a table called <Table_1>.
2 - Enqueue a message to an AQ, queue name of <Queue_Name>*, the message contains a reference to the unique record in the <Table_1>.
3 - Inserts a unique row in a table called <Table_2>
4 - Commits

Another process WISMDB dequeues messages from <Queue_Name>*, when it dequeues the message it relays the message contents to another process (via RPC), this process uses the information in the message to select the unique row from the <Table_1>, in the following way :-

1 - Process (WISMBD) dequeues the message from the AQ.
2 - The process (WISMBD) sends the message to another process (WIS) using RPC.
3 - The process (WISMBD) commits (or rollbacks) the dequeue.
4 - The WIS process decodes the unique reference from the message.
5 - The WIS process queries the <Table_1> using the unique reference
6 - The WIS process caches in memory the information from the <Table_1>.

Step 5 intermittently fails in a RAC cluster in 11.2. The issue never arises when the Tibco
application runs on a single instance or a single node in a RAC cluster or when the WISMDB and WIS processes run on the same node.

Ad-hoc LogMiner analysis indicates that the inserts and messages were enqueued and committed and the dequeue of the associated messages occurred very close to (in scn terms) but after the message enqueue.

Changes

Upgrade from 10.2 to 11.2.

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.