How to Recreate a Corrupted Workflow Background Queue
WF_Deferred_Table_M
1 Stop all workflow agent listeners using OAM / Workflow page:
a. Logon as SYSDMIN go to Oracle Applications Manager / Workflow page.
b. Select each workflow agent listener and in the drop down select stop.
c. Verify that each agent listener is stopped.
2. Stop the underlying workflow service containers using one of these methods:
a. Using the script downloaded from Note 1124356.1:
sqlplus apps/<password> @atg_supp_wf_srv_ctl.sql
b. Or Use the OAM/Workflow/Containers – Stop All dropdown.
3. Suspend any running instances of the concurrent request “Workflow Background Process”
FNDWFBG . Use Oracle Application Manager/Concurrent Requests/ Running or Scheduled.
4. Compare and obtain a baseline of database objects using this script:
sqlplus select OWNER, OBJECT_NAME, OBJECT_TYPE, STATUS from dba_objects where object_name like '%WF_DEFERRED_TABLE_M%
When this is run in a test instance we see the following output:
APPLSYS WF_DEFERRED_TABLE_M TABLE VALID
APPLSYS AQ$_WF_DEFERRED_TABLE_M_S TABLE VALID
APPLSYS AQ$_WF_DEFERRED_TABLE_M_T TABLE VALID
APPLSYS AQ$_WF_DEFERRED_TABLE_M_N SEQUENCE VALID
APPLSYS AQ$_WF_DEFERRED_TABLE_M_H TABLE VALID
APPLSYS AQ$_WF_DEFERRED_TABLE_M_L TABLE VALID
APPLSYS AQ$_WF_DEFERRED_TABLE_M_G TABLE VALID
APPLSYS AQ$_WF_DEFERRED_TABLE_M_I TABLE VALID
APPLSYS AQ$_WF_DEFERRED_TABLE_M_E QUEUE VALID
APPLSYS AQ$_WF_DEFERRED_TABLE_M_V EVALUATION CXT VALID
APPLSYS WF_DEFERRED_TABLE_M_N1 INDEX VALID
APPLSYS AQ$ WF_DEFERRED_TABLE_M VIEW VALID
APPLSYS AQ$ WF_DEFERRED_TABLE_M_R VIEW VALID
APPLSYS AQ$ WF_DEFERRED_TABLE_M_S VIEW VALID
APPLSYS AQ$_WF_DEFERRED_TABLE_M_F VIEW VALID
APPS WF_DEFERRED_TABLE_M SYNONYM VALID
5. Check the current volume in the background queue:
SQL> select corrid,
decode(state, 0, '0 = Ready',
1, '1 = Delayed',
2, '2 = Retained',
3, '3 = Exception',
to_char(state)) State,
count(*) COUNT
from wf_deferred_table_m
group by corrid, state;
6. Drop the queue using the following. Use uppercase for the <APPLSYS> <APPS>
sqlplus <apps_user>/<apps_password> @$FND_TOP/patch/115/sql/wfqued.sql <APPLSYS> <APPS>
sqlplus <apps_user>/<apps_password> @$FND_TOP/patch/115/sql/wfqued.sql <APPLSYS> <APPS>
in case any error as below, please drop the view - drop view AQ$_WF_DEFERRED_TABLE_M_F and re-run the step 6
Error:
declare
*
ERROR at line 1:
ORA-20000: Oracle Server Error at Drop Deferred Queue Table = -4020 -
ORA-04020: deadlock detected while trying to lock object
APPLSYS.AQ$_WF_DEFERRED_TABLE_M_F
ORA-06512: at line 16
Solution
sqlplus applsys/appspass
drop view AQ$_WF_DEFERRED_TABLE_M_F;
re-run the step 6 (sqlplus <apps_user>/<apps_password> @$FND_TOP/patch/115/sql/wfqued.sql <APPLSYS> <APPS>)
a. Check to see if the synonym is dropped using query in #4. If it is still there and invalid then manually drop it:
DROP SYNONYM WF_DEFERRED_TABLE_M
This may also return an error (object does not exist) but if you run the query from #4 again you will find that the synonym is gone.
7. Recreate the queue using the following:
sqlplus <apps_user>/<apps_password> @wfquec2.sql APPSusr APPLSYS <applsys_pw>
Example VIS instance Syntax:
sqlplus <apps_user>/<apps_password> @$FND_TOP/patch/115/sql/wfquec2.sql APPS APPLSYS apps
in case any error as below, please restart the databases and re-run the step 7
declare
*
ERROR at line 1:
ORA-02289: sequence does not exist
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 7046
ORA-06512: at "SYS.DBMS_AQADM", line 549
ORA-06512: at line 10
8. Add the synonym back:
Sqlplus <apps user> <apps password>@$FND_TOP/patch/115/sql/afwfqgnt.sql APPS apps APPLSYS apps
9. Recreate WF_DEFERRED_TABLE_M_N1 index using the following script.
a. First, find the correct tablespace_name to use based on the following select:
select index_name, owner, table_name, tablespace_name
from dba_indexes
where index_name like 'WF_%_N1';
In our VIS instance it is APPS_TS_QUEUES since that is the tablespace name shared by other jms queue objects like WF_NOTIFICATION_OUT_N1 and WF_DEFERRED_N1.
b. Recreate the index:
sqlplus <apps_user>/<apps_password> @FND_TOP/patch/115/sql/wfqidxc2.sql <APPLSYS> <APPS> tablespace_name
Example Syntax:
sqlplus <apps_user>/<apps_password> @$FND_TOP/patch/115/sql/wfqidxc2.sql <APPLSYS> <APPS> APPS_TS_QUEUES
...If you get the error message “ORA-00955: name is already used by an existing object” ignore it.
10. Make sure the histograms are added back:
sqlplus <apps_user>/<apps_password> @$FND_TOP/patch/115/sql/wfhistc.sql <APPLSYS>
11. Re-populate WF_DEFERRED_TABLE_M. This may take a very long time. It’s best if all unneeded workflow data is purged first. Ensure the Workflow Background Engine is not running while this runs:
sqlplus <apps_user>/<apps_password> @$FND_TOP/sql/wfbkgbld.sql <APPLSYS>
Step 12: Start the workflow service component container, agent listener and workflow mailer.
Reference: How to Recreate a Corrupted Workflow Background Queue WF_Deferred_Table_M (Doc ID 1176723.1)