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

Last updated on DECEMBER 08, 2014

Applies to:

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

Symptoms

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

A query of the form

select
 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
 minus
 select msgid
 from PFPS.DSKS_MSG_IN_Q1_TAB)
 and n.user_data.queue_name = '"PFPS"."DSKS_MSG_IN_Q1"';

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.

Cause

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms