how to rebuild the wf_java_deferred queue

Followings steps are to be followed to rebuild the queue

(1) Check to see what records will be backed up on the wf_queue_temp_jms_table backup table.

select wfjd.corr_id corrid, msg_state state, count(*) COUNT
from applsys.aq$wf_java_deferred wfjd
where msg_state IN('READY', 'WAIT')
group by corr_id, wfjd.msg_state;

(2) Shut down the Workflow Agent Listener Service and backup the records on the WF_JAVA_DEFERRED queue to the apps.wf_queue_temp_jms_table backup table.

System Administrator > Oracle Applications Manager > Workflow > Service Components > Workflow Agent Listener Service

sqlplus apps/ @wfaqback.sql

For Example:

sqlplus apps/apps @$FND_TOP/sql/wfaqback.sql WF_JAVA_DEFERRED

(3) Make sure all the records are in the wf_queue_temp_jms_table table.

select CORR_ID corrid, QUEUE queue, count (*)
from apps.wf_queue_temp_jms_table
group by CORR_ID, QUEUE;

(4) Set aq_tm_processes =0.

alter system set aq_tm_processes=0;

(5) Note the name of the tablespace containing the index on CORRID that will need to be recreated later.

SELECT index_name, tablespace_name
FROM all_indexes
WHERE index_name = 'WF_JAVA_DEFERRED_N1';

(6) Drop the WF_JAVA_DEFERRED queue and queue_table.

declare
begin
dbms_aqadm.stop_queue(queue_name => 'APPLSYS.WF_JAVA_DEFERRED', wait =>
FALSE);
end;
/

If it hangs, we can use force

declare
begin
dbms_aqadm.drop_queue_table(queue_table => 'APPLSYS.WF_JAVA_DEFERRED', force
=> TRUE);
end;
/

(7) Recreate the WF_JAVA_DEFERRED queue.

sqlplus / @wfbesqc.sql

For Example:

sqlplus apps/apps @$FND_TOP/patch/115/sql/wfbesqc.sql APPLSYS APPS

(8) Add the subscribers.

sqlplus APPSusr/<pass> @wfbesqsubc.sql

Example Syntax:

sqlplus apps/<pass> @$FND_TOP/patch/115/sql/wfbesqsubc.sql APPLSYS APPS

(9) Recreate the index (Please ignore any ORA-00955 errors about object already exist as this adds index for other objects.):

sqlplus APPSusr/<pass> @FND_TOP/patch/115/sql/wfbesqidxc.sql APPLSYS APPS tablespace_name

Example Syntax:

sqlplus apps/apps @$FND_TOP/patch/115/sql/wfbesqidxc.sql APPLSYS APPS APPS_TS_QUEUES

(10) Put the data for the WF_JAVA_DEFERRED back into the queue.

sqlplus apps/<pass> @wfaqrenq.sql

For Example:

sqlplus apps/apps @$FND_TOP/sql/wfaqrenq.sql WF_JAVA_DEFERRED

(11) Confirm that all records are back in the queue.

select wfjd.corr_id corrid, msg_state state, count(*) COUNT
from applsys.aq$wf_java_deferred wfjd
where msg_state IN('READY', 'WAIT')
group by corr_id, wfjd.msg_state;

(12) Start the Workflow Agent Listener Service and confirm it is now processing the events on the queue.

System Administrator > Oracle Applications Manager > Workflow > Service Components > Workflow Agent Listener Service

select wfjd.corr_id corrid, msg_state state, count(*) COUNT
from applsys.aq$wf_java_deferred wfjd
where msg_state IN('READY', 'WAIT')
group by corr_id, wfjd.msg_state;

Hope you like the post. Please do provide the feedback to improve