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)