Rebuild Workflow Queue table if the workflow Queue table corrupted or email not sent

Rebuild Workflow Queue table if the workflow Queue table corrupted or email not sent




First, Verify or set the 'WF: Workflow Mailer Framework Web Agent' profile value to physical host name and port number, DO NOT enter the Load Balancer URL 

Example

WF: Workflow Mailer Framework Web Agent=http://erpdev01.blogspot.com:8008


Step 1: shutdown workflow services

Step 2: Execute below scripts(you may get an error that 'queue does not exist'  or 'table does not exist', ' does not exist'  and you can ignore that part)

sqlplus apps/****

update WF_NOTIFICATIONS set mail_status = 'SENT' where mail_status in ('MAIL','INVALID') and Status In ('OPEN', 'CANCELED') and begin_date < sysdate-30;


sqlplus apps/**** @$FND_TOP/sql/wfevqcln.sql WF_DEFERRED oracle.apps.wf.notification.%

sqlplus apps/**** @$FND_TOP/sql/wfevqcln.sql WF_DEFERRED oracle.apps.wf.notification.% 0

sqlplus apps/****

drop table APPLSYS.AQ$WF_NOTIFICATION_OUT_BAK;

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');


exec dbms_aqadm.stop_queue(queue_name => 'APPLSYS.WF_NOTIFICATION_OUT', wait => FALSE);
tip:  you may get an error that 'queue does not exist'  and you can ignore that part

exec dbms_aqadm.drop_queue_table( queue_table => 'APPLSYS.WF_NOTIFICATION_OUT', force => TRUE);
tip:  you may get an error that 'queue does not exist'  and you can ignore that part

sqlplus apps/**** @$FND_TOP/patch/115/sql/wfjmsqc2.sql applsys ****

sqlplus apps/**** @$FND_TOP/patch/115/sql/wfmqsubc2.sql APPLSYS ****

sqlplus apps/**** @$FND_TOP/patch/115/sql/wfntfqup APPS **** APPLSYS

Create alert_restore.sql file with below sql content and execute with apps user

set serveroutput on size 100000;
declare
x_out_queue varchar2(80) := 'APPLSYS.WF_NOTIFICATION_OUT';
x_enqueue_options dbms_aq.enqueue_options_t;
x_message_properties dbms_aq.message_properties_t;
x_msgid RAW(16);
i number := 0;
p_event SYS.AQ$_JMS_TEXT_MESSAGE;

cursor msg is
select * from APPLSYS.AQ$WF_NOTIFICATION_OUT_BAK
where msg_state in ('READY','WAIT');

begin

for m1 in msg loop

x_out_queue := 'APPLSYS.'||m1.queue;
x_message_properties.correlation := m1.corr_id;
--x_message_properties.original_msgid := m1.msg_id;
x_message_properties.priority := m1.msg_priority;

x_message_properties.recipient_list(1) := sys.aq$_agent(m1.consumer_name,
null,
0);
if (m1.msg_state = 'WAIT') and (m1.delay > sysdate) then
x_message_properties.delay := ((sysdate - m1.delay)*86400);
else
x_message_properties.delay := 0;
end if;
--dbms_output.put_line('msg.msgid = ' || m1.msg_id);

DBMS_AQ.ENQUEUE(
queue_name => x_out_queue,
enqueue_options => x_enqueue_options,
message_properties => x_message_properties,
payload => m1.user_data,
msgid => x_msgid); /* OUT*/

--dbms_output.put_line('x_msgid = ' || x_msgid);

if i = 20 then
i := 0;
commit;
else
i := i + 1;
end if;
end loop;

commit;
exception
when others then
dbms_output.put_line('sqlerrm = ' || sqlerrm);
end;
/



select tablespace_name, index_name from dba_indexes where index_name like 'WF_%_N1';

sqlplus apps/**** @$FND_TOP/patch/115/sql/wfqidxc APPLSYS **** APPS_TS_QUEUES

sqlplus applsys/**** @$FND_TOP/patch/115/sql/wfqidxc2 APPLSYS ***** APPS_TS_QUEUES



sqlplus apps/**** @$FND_TOP/patch/115/sql/wfhistc APPLSYS

step 3: start the workflow services








appsdbahelp

17+ years of experience in Oracle Database, Oracle Cloud Infrastructure(OCI), Oracle EBS on Cloud, Oracle E-Business Suite, DevOps tools, Oracle WebLogic, Oracle Application Server, Oracle Access Manager and various Operating System flavors including Redhat Linux, UNIX (Solaris, HP-UX) and Windows. Expert in Oracle9i/10g/11g/12c/19c database administration, upgrade, configuration and tuning. Experience in Oracle E-Business Suite technological stack, including architecture, installation, configuration, maintenance, tuning, cloning and patching procedures. Expert in Oracle Cloud Infrastructure(OCI), Oracle EBS On Cloud and Oracle EBS Cloud Manager Experience with Oracle Cloud Solution and Expert of Oracle ERP/Oracle HCM Cloud deployment Experience in Terraform, JSON and chef cloud infrastructure automation framework Knowledge of ASM, Data Guard, Real Application Cluster, Exadata and Exalogic Knowledge of Oracle Enterprise Manager(OEM) Grid Control, Oracle WebLogic, Oracle Internet Directory, Oracle Access Manager and Apache Ability to analyze problem, develops solutions and bring program/project execution to completion.

Post a Comment

Previous Post Next Post