AQ - Message state not updated on queue table

User_A0ZGW

Hello all,

The message state is not getting updated to 2 on queue tables even though message is processed (state remains 0). The correct state is available on the AQ$ views. We'd encountered this behaviour two days back. Functionality is not impacted, however, I'd like to understand if we could potentially have some issues if state does not get updated on queue tables.

We are on DB 11.2.0.3.0.

Regards,

Nagendra

Raj__K

Hi, When the transaction is getting completed then the state should get updated in AQ table. Whether the transaction is getting completed successfully? The views reflect the data from the tables.

Have a look at - Doc ID 1356146.1 in My Oracle Support which is related to state not getting updated from READY state.

Rgrds, Raj

User_A0ZGW

Hello Raj,

Thanks for the reply. Messages are getting processed successfully. I do see the correct message state on the AQ$ view, but the same message has the state as 0 on the queue table. This has been happening for two days now and the number of messages on the queue table with state 0 is increasing, even though these messages have been dequeued and processed.I am concerned if this will affect the purge mechanism. This behaviour has been observed on all queues in our instance.

Regards,

Nagendra

Raj__K

Hi, As you are confirming that the transactions of these AQ message are not rolling back, the issue may with the infrastructure. Check whether any patches or any maintenance activity happened before few days.

Try restarting the AQ and SOA environment at the same time and monitor.

Are these message having an expiry time?

Rgrds, Raj