My Oracle Support Banner

AQ PL/SQL Notification stopped working after Shutdown Abort (Doc ID 1472105.1)

Last updated on JANUARY 06, 2020

Applies to:

Oracle Database - Enterprise Edition - Version to [Release 10.2 to 11.2]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.


Messages no longer dequeued via AQ Pl/SQL Notification after a shutdown abort of the instance.

A query of the form

 msgid notify_msgid,
 to_char(n.enq_time,'DD-MON-YYYY hh24:mi:ss'),
 n.user_data.msg_id app_msgid,
 n.user_data.queue_name qname,
 nvl(utl_raw.cast_to_varchar2(n.user_data.payload),'null') payload
from sys.AQ_SRVNTFN_TABLE_1 n
where n.user_data.msg_id in (
 select n.user_data.msg_id msgid
 from sys.AQ_SRVNTFN_TABLE_1 n
 select msgid
 and n.user_data.queue_name = '"<owner>"."<APPLICATION QUEUE>"';

returns multiple rows indicating a mismatch between the notification queue and the application queue.

When traced the job_queue_processes which are handling the AQ$_PLSQL_NTFN* jobs are looping attempting to dequeue the same messages.




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

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