How To Purge FND_AQ Tables

Symptoms(Doc ID 1156523.1)

1. The Internal standard manager take a long time to startup

2. The table AQ$_FND_CP_GSM_OPP_AQTBL table grow up enormously and has a lot of records
3. The following message is shown for a concurrent request of a bi publisher report:

    Post-processing of request xxx failed with the error message:
    ORA-04021: timeout occurred while waiting to lock object

Changes

Improper shutdown of the concurrent managers especially the OPP manager.

Cause

AQs tables are used to look for "subscriptions" by FNDSMs. That is, when ICM calls for FNDSM to start, they "subscribe" to this queue to identify its status. The time taken for the process cleanup prior to the internal manager start up with the regular CMs is correlated to the number of processes that were not stopped cleanly. In case of un-clean shutdown, the process to restart will be longer as manager spends extra cycles to perform housekeeping tasks.

Solution

1. It is highly recommended to always ensure the clean shutdown of the concurrent managers.
2. The Purge Concurrent Request and/or Manager Data Program request should run periodically. Purge concurrent request does not Purges AQ Tables. For maintaining a healthy level of records in fnd_concurrent_requests instead of running 'Purge Concurrent Program' with same parameters for all the applications you can choose to run it differently for different application where you can decide for which application you needs records to be kept for certain days and for which application you only need to keep for 1 day or so.
It is recommended to schedule a cron job or something which queries records in APPLSYS.FND_CP_GSM_OPP_AQTBL to monitor it and use DBMS_AQADM.PURGE_QUEUE_TABLE to purge the table as needed.
3. This is how to purge the FND_CP_GSM_OPP_AQTBL manually to clean up the table:

3.1. Check the number of records in the table :

SQL> select count(*) from applsys.FND_CP_GSM_OPP_AQTBL ;

 COUNT(*)
 ----------
 31759

3.2. Run the following as SYS:

DECLARE
po dbms_aqadm.aq$_purge_options_t;
BEGIN
po.block := FALSE;
DBMS_AQADM.PURGE_QUEUE_TABLE(
queue_table => 'APPLSYS.FND_CP_GSM_OPP_AQTBL',
purge_condition => NULL,
purge_options => po);
END;
/

3.3. Re-check again the number of records in the table

SQL> select count(*) from applsys.FND_CP_GSM_OPP_AQTBL ;

 COUNT(*)
 ----------
 0

In case the purge did not complete successfully after the second time, or did not purge all the queues, then you would have to recreate the queue. Recreate the queue using  $FND_TOP/patch/115/sql/afopp002.sql file as 'APPLSYS' user.  On running the script you will be prompted for username and password. Please note that this may take a longer length of time to complete.

3.4. Please run the Concurrent Manager Recovery feature to address any Concurrent Manager / Concurrent Processing issues within the Oracle Application Manager.

3.5. Start up the application services and retest.

Note: The following applsys.AQ$_FND_CP_GSM_OPP_AQTBL_x objects are also cleaned as part of the above purge.

applsys.AQ$_FND_CP_GSM_OPP_AQTBL_S
applsys.AQ$_FND_CP_GSM_OPP_AQTBL_T
applsys.AQ$_FND_CP_GSM_OPP_AQTBL_H
applsys.AQ$_FND_CP_GSM_OPP_AQTBL_I
applsys.AQ$_FND_CP_GSM_OPP_AQTBL_G
applsys.AQ$_FND_CP_GSM_OPP_AQTBL_L

For best maintenance and monitoring run the script: Concurrent Processing - CP Analyzer for E-Business Suite (Document 1411723.1)

R12 E-Business Suite Output Post Processor (OPP) Fails To Pick Up Concurrent Requests With Error 'Unable to find an Output Post Processor service to post-process request nnnnn'

E-Business Suite R12 Concurrent Processing, Output Post Processing related issues
Requests fail during Post Processing with the following errors:

'Unable to find an Output Post Processor service to post-process request xxxxx.

Check that the Output Post Processor service is running'

Cause

Incorrect data is present in the OPP queue table AQ$FND_CP_GSM_OPP_AQTBL_S, which occurs when the concurrent managers are not shutdown correctly.

A quick sql query shows orphaned OPP subscribers in APPLSYS.AQ$FND_CP_GSM_OPP_AQTBL_S, causing the select_random_subscriber routine to pick a non-running process.

Solution

To resolve the issue test the following steps in a development instance and then migrate accordingly:
1. Find the active processes for OPP:

SELECT fcp.concurrent_process_id
FROM fnd_concurrent_queues fcq, fnd_concurrent_processes fcp
WHERE concurrent_queue_name = 'FNDCPOPP'
AND fcq.concurrent_queue_id = fcp.concurrent_queue_id
AND fcq.application_id = fcp.queue_application_id
AND fcp.process_status_code = 'A';

Output example:
CONCURRENT_PROCESS_ID
---------------------
93557
93558
2. Find the OPP subscribers:

select name from APPLSYS.AQ$FND_CP_GSM_OPP_AQTBL_S;

The name in the table is the concurrent_process_id prefixed with 'OPP'.
The output should normally correspond to the output of first query: have the same number of records and have as name the concurrent_process_id of the running OPP processes prefixed by OPP
Output example:
NAME
------------------------------
OPP93558
OPP93557
OPP88933
OPP92625
3. If there are extra subscribers which are left from previous runs of OPP processes, unsubscribe them via the following command:

  exec fnd_cp_opp_ipc.unsubscribe('<concurrent_process_id>');

 Example:
  exec fnd_cp_opp_ipc.unsubscribe('88933');
  exec fnd_cp_opp_ipc.unsubscribe('92625');
4. Bounce the concurrent managers.

5. Retest some concurrent requests and confirm the post processing actions now complete successfully.