Alerts along with Other Notifications Are Getting Piled Up In The Out Queue And Not Sending Emails

(Doc ID 733539.1)

Last updated on JUNE 21, 2017

Applies to:

Oracle Workflow - Version 11.5.10.0 to 12.1.3 [Release 11.5.10 to 12.1]
Information in this document applies to any platform.
Checked for relevance on 22-JUN-2013


Symptoms

Using the Oracle Applications Release 11.5.10.2 with ATG.RUP5 patchset applied:

Oracle alerts that are defined to send notifications are not sending emails. Instead they are getting
piled up in applsys.aq$wf_notification_out table.

Restarting the workflow service will process the messages for a time but eventually the problem re-occurs.


.

Changes

NOTE: As of ATG RUP5 this script will halt if there are Oracle Alerts un-processed messages on the WF_NOTIFICATION_OUT_N1 queue, these messages cannot be recovered so they must be processed before the script will continue.

required for ATG RUP4 and above Oracle Alert users:

NOTE: APPLSYS is assumed to be the schema storing the Workflow tables. Please substitute the correct schema name if necessary. 

1. Backup the Oracle Alert messages that are unprocessed on the WF_NOTIFICATION_OUT. 

ASSUMPTION: The corrupt messages are not belonging to Oracle Alert. 

create table APPLSYS.AQ$WF_NOTIFICATION_OUT_BAK as select * from APPLSYS.AQ$WF_NOTIFICATION_OUT where CORR_ID like 
'APPS:ALR%' and msg_state in ('READY','WAIT'); 

2. Confirm that all Oracle Alert messages have been backup: 

set linesize 155; 
set pagesize 200; 
set verify off; 
SELECT COUNT(*), 
substr(corr_id,1,45) corr_id, 
msg_state 
FROM applsys.aq$wf_notification_out 
WHERE corr_id LIKE 'APPS:ALR%' 
AND msg_state IN('READY', 'WAIT') 
GROUP BY corr_id, 
msg_state; 

Compare: 

set linesize 155; 
set pagesize 200; 
set verify off; 
SELECT COUNT(*), 
substr(corr_id,1,45) corr_id, 
msg_state 
FROM applsys.aq$wf_notification_out_bak 
WHERE corr_id LIKE 'APPS:ALR%' 
AND msg_state IN('READY', 'WAIT') 
GROUP BY corr_id, 
msg_state; 

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