如何重建 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). Shutdown 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 tablspace containing 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; 
/ 

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/ @wfbesqsubc.sql 

Example Syntax: 

sqlplus apps/apps @$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/ @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/ @wfaqrenq.sql 

For Example: 

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

11). Confirm that all records are back on 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;