How to rebuild WF_DEFERRED_TABLE_M Queue



Some times we need to rebuild the WF_DEFERRED_TABLE_M queue due to corruption. Here are the steps to perform it.

  1. First take a pre snapshot of all the objects related to that queue

Login to server and source the EBS environment

spool WF_DEFERRED_TABLE_M_objects_pre.lst

select object_name, object_type, status, owner
from dba_objects
where object_name like ‘%WF_DEFERRED_TABLE_M%’;

spool off

2) Now we will proceed with the steps to rebuild the queue

a) Please stop the workflow services and the workflow background engine programs.Please make sure no workflow program are running
i.e CM should be down

adcmctl.sh stop apps/appspass

sqlplus apps/appspass
b)  Create manual backup the queue table

create table wf_deferred_table_bk as select * from WF_DEFERRED_TABLE_M;
select count(*) from WF_DEFERRED_TABLE_M;

 

c. Drop the queue using the following. Please make sure you use uppercase for the APPLSYS APPS:

sqlplus apps/appspass @wfqued.sql APPLSYS appspass

d. Recreate the queue using the following:

sqlplus apps/appspass @wfquec2.sql APPS APPLSYS appspass

e.
Recreate the index:

sqlplus apps/appspass @wfqidxc2.sql APPLSYS appspass APPLSYSX

 

f. Make sure the histograms are added back:

sqlplus apps/appspass @wfhistc.sql APPLSYS

 

g. Re-populate WF_DEFERRED_TABLE_M. This may take a long time and make sure the Workflow Background Engine is not running::

sqlplus apps/appspass @wfbkgbld.sql APPLSYS

This will take some time like 1 hour

 

h) Verify
select count(*) from WF_DEFERRED_TABLE_M;
It should have same rows as earliar

3) Now take the post snapshot of all the queue related objects

spool WF_DEFERRED_TABLE_M_objects_post.lst

select object_name, object_type, status, owner
from dba_objects
where object_name like ‘%WF_DEFERRED_TABLE_M%’;

spool off

4) Please compare pre and post list to make sure everything is alright

5) Compile all the invalid objects with the adadmin

 


Leave a Reply