We had this error in a test environment where they were trying to run a few things through workflow mailer in the Ebusiness Suite. It was quite a recent clone but for the most part, I prefer not to have workflow on without reason in test systems without the over-ride email address set.
I've been in the position in the past where a junior DBA has cloned, forgotten to do this or not reset the workflow accounts and workflow has sent out emails to customers and also connected to the live account and moved emails into the discard folder.
There was a direct hit for the error on Metalink [ID 886377.1] which was tried but didnt resolve the issue. On checking then if the workflow queue existed under applsys it wasnt found so it was time to rebuild the queue.
The below should bring back 3 objects under applsys, table, queue and synonym.
select owner, object_type, status
from dba_objects
where object_name='WF_NOTIFICATION_OUT';
I've rebuilt the out queue hundreds of times and its usually down to issues in the INBOX with spamming mail killing the workflow IMAP connection to the account and it 'hangs'. Another popular one is customers replying to a workflow email or copying the address in on a reply to their manager. Nothing is sure to stop the workflow mailer from working as quick.
To rebuild the out queue automatically you would run the below.
select distinct tablespace_name
from dba_indexes,dba_queues
where index_name like 'WF%N1'
and table_name=queue_table
and name like 'WF%';
connect to SQL*Plus as applmgr user
@$FND_TOP/patch/115/sql/wfntfqup.sql APPS APPSPW APPLSYS
this rebuilds the queue, now to rebuild the index.
connect as applsys , replace question marks with value from first select.
CREATE INDEX WF_NOTIFICATION_OUT_N1
ON WF_NOTIFICATION_OUT(CORRID)
STORAGE (INITIAL 1M NEXT 1M MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0)
TABLESPACE ?????????????;
If you want to rebuild it yourself as I did manually you will want to follow Metalink Note [400493.1]
No comments:
Post a Comment