I would like to publish this blog post in the form of a set of questions and answers that will help you resolve a situation if "Ready" state messages in WF_DEFERRED queue are in few millions and Workflow Deferred Agent listener is down from years which caused the pile up of records in WF_DEFERRED Queue.
Q1. How do I find out in first place WF_DEFERRED Queue is having millions of records in "Ready" state ?
The simplest way to find out this is by executing the script $FND_TOP/sql/wfver.sql. In the spool file that the script creates, look for
--Clip--
Notification Subsystem Queue Info This requires package WF_QUEUE to be valid ______________________________________________________________________________ WF_DEFERRED messages ready:8406878, waiting:16, expired:0, undeliverable:0, processed:2586
--End Clip--
The highlighted one indicates the number of messages in "Ready" state. If you see big numbers in this section of wfver.sql output, this is a matter of concern. Get ready to do an operation ;)
Q2. What happens if the number of messages in this queue are very high.
One of the obvious things that can happen when this queue is so big is the delay in processing of the messages. Just imagine if the Order Management related messages are not processed on time and delay of few hours in notifying the users.
Q3. What is the reason for this queue to grow up in such huge numbers ?
"Workflow Deferred Agent Listener" is the one which processes the messages in WF_DEFERRED queue. If the listener is down, you should try and bring it up. If the number of messages in WF_DEFERRED queue are very huge then this listener fails to come up or dies immediately after few minutes.
Q4. How do I bring up "Workflow Deferred Agent Listener" in this situation.
Believe me. It is not easy.
Step 1: Execute the following script to find out event wise "Ready" state messages.
--Script Begins here--
set linesize 100
col corrid format a50
col state format a15
col count format 99999999
select NVL(substr(wfe.corrid,1,50),'NULL - No Value') corrid, decode(wfe.state,0,'0 = Ready',1,'1 = Delayed','2=Retained','3=Exception',to_char(substr(wfe.state,1,12))) State, count(*) COUNT from applsys.wf_deferred wfe group by wfe.corrid, wfe.state ;
--Script Ends here--
Note: This script might take longtime depending up on the number of messages in WF_DEFERRED Queue.
You should see the output something like this.
CORRID STATE COUNT
------------------------------------------------------------ -------------------- ---------
APPS:oracle.apps.ar.adjustments.Adjustment.create 0 = Ready 72488
APPS:oracle.apps.ar.applications.CashApp.apply 0 = Ready 557692
APPS:oracle.apps.ar.applications.CashApp.unapply 0 = Ready 145695
APPS:oracle.apps.ar.applications.CreditMemoApp.app 0 = Ready 58107
APPS:oracle.apps.ar.applications.CreditMemoApp.una 0 = Ready 2512
APPS:oracle.apps.ar.batch.AutoInvoice.run 0 = Ready 32950
Step 2: This step is to process the events manually. The job of "Workflow Deferred Agent Listener" is being carried out manually by executing the following scripts.
--Script Begins here--
spool oracle.apps.ar.adjustments.Adjustment.create.log
set serveroutput on size 100000;
begin wf_log_pkg.wf_debug_flag := TRUE;
wf_event.listen(p_agent_name =>'WF_DEFERRED',p_correlation=>'oracle.apps.ar.adjustments.Adjustment.create');
end;
/
commit
/
--Script Ends here--
Execute the above script for all the events that are in the "Ready" state. Once after the count of "Ready" state messages comes down to some number, try starting "Workflow Deferred Agent Listener". This process is really time consuming, so plan out your vacation after the issue is resolved. You need a break !! :)
Q5. What do you as an Apps DBA need to do when users complain that OM notifications are stuck.
Use the following query to check to see whatever the users are saying is correct ;).
SQL> select message_type,count(1) from wf_notifications where status='OPEN' and mail_status='MAIL' group by message_type;
MESSAGE_ COUNT(1)
-------- ----------
FNDCMMSG 6
IBEALERT 32
JTFTASK 2441
OEOH 112
OEOL 462
OKCAUKAP 116
POAPPRV 121
REQAPPRV 40
MESSAGE_ COUNT(1)
-------- ----------
WFERROR 4524
Q6. What can you do to speed up the processing of sending notifications.
Execute the following script manually to speedup delivering of notifications.
--Script Begins here--
spool oracle.apps.wf.notification.log
set serveroutput on size 100000;
begin wf_log_pkg.wf_debug_flag := TRUE;
wf_event.listen(p_agent_name => 'WF_DEFERRED',p_correlation=>'oracle.apps.wf.notification.%');
end;
/
--Script Ends here--
Enjoy !!
Monday, October 15, 2007
WF_DEFERRED Queue is having few million messages in "Ready" State
Thursday, October 4, 2007
Worflow Background Process errors with ORA-00600 internal error code, arguments: [4414]
Workflow Background Process concurrent request fails with the following error message when the request is submitted with Order Management Item type.
Symptom:
ORA-00600: internal error code, arguments: [4414], [12226], [1], [12226], [1], [], [],
ORA-01403: no data found
Workflow Background Process concurrent request is a Workflow Background Engine. Workflow Backedground Process concurrent request needs to be scheduled at regular intervals as it processes deferred activities and timed out activities.
Generally the 'Workflow Background Process' fails with the following error after upgrading to Oracle Applications 11.5.10.2 and if the database is upgraded from 8.1.7.4 to 9i.
What does "ORA-600 [4414] " followed by an "ORA-01403: no data found" mean ?
If a queue has messages enqueued for rule based subscribers prior to the upgrade, then dequeueing these messages on the upgraded database results in an "ORA-600 [4414]" followed by an "ORA-01403: no data found" error.
Solution:
sqlplus "/ as sysdba"
SQL> exec sys.dbms_prvtaqis.upgrade_rulesub_msgs;
The package "
Wednesday, October 3, 2007
Package 'CSI_ITEM_INSTANCE_PVT_W' Invalid after 11.5.10.2 Upgrade
Symptom:
If the existing .pls in your environment has the following version & you have just upgraded to Oracle Apps 11.5.10.2, then read further.
$ strings -a ./patch/115/sql/csiviiws.pls | grep -i Header
REM $Header: csiviiws.pls 115.4.1159.2 2006/03/10 10:04:55 abhgupta ship $
/* $Header: csiviiws.pls 115.4.1159.2 2006/03/10 10:04:55 abhgupta ship $ */
procedure construct_inst_header_rec(p_inst_id NUMBER
SQL> alter package apps.CSI_ITEM_INSTANCE_PVT_W compile body ;
Warning: Package Body altered with compilation errors.
SQL> show error;
Errors for PACKAGE BODY APPS.CSI_ITEM_INSTANCE_PVT_W:
LINE/COL ERROR
-------- -----------------------------------------------------------------
997/5 PLS-00306: wrong number or types of arguments in call to
'GET_PARENT_SORT_ORDER'
997/5 PL/SQL: Statement ignored
2855/5 PL/SQL: Statement ignored
2855/22 PLS-00302: component 'VERSION_LABEL' must be declared
2856/5 PL/SQL: Statement ignored
2856/22 PLS-00302: component 'VERSION_LABEL_MEANING' must be declared
2998/5 PL/SQL: Statement ignored
2998/33 PLS-00302: component 'VERSION_LABEL' must be declared
2999/5 PL/SQL: Statement ignored
LINE/COL ERROR
-------- -----------------------------------------------------------------
2999/33 PLS-00302: component 'VERSION_LABEL_MEANING' must be declared
3279/5 PL/SQL: Statement ignored
3279/22 PLS-00302: component 'VERSION_LABEL' must be declared
3280/5 PL/SQL: Statement ignored
3280/22 PLS-00302: component 'VERSION_LABEL_MEANING' must be declared
3420/5 PL/SQL: Statement ignored
3420/33 PLS-00302: component 'VERSION_LABEL' must be declared
3421/5 PL/SQL: Statement ignored
3421/33 PLS-00302: component 'VERSION_LABEL_MEANING' must be declared
3568/5 PLS-00306: wrong number or types of arguments in call to
'ROSETTA_TABLE_COPY_IN_P22'
LINE/COL ERROR
-------- -----------------------------------------------------------------
3568/5 PL/SQL: Statement ignored
Solution:
Apply the patch:6264601. The patch:6264601 might make CSE_PROJ_ITEM_IN_SRV_PKG / CSE_PROJ_TRANSFER_PKG invalid as well. If at all these packages go invalid. No need to worry. Follow the Metalink Note:403993.1 to fix CSE_PROJ_ITEM_IN_SRV_PKG / CSE_PROJ_TRANSFER_PKG.
