Description :
The cloned instance and all managers and OPP services were up and running. However users were reporting of XML reports completed in Error.
Issue Log :
[OPPServiceThread0] java.sql.SQLException: ORA-24067: exceeded maximum number of subscribers for queue APPLSYS.FND_CP_GSM_OPP_AQ
ORA-06512: at “APPS.FND_CP_OPP_IPC”, line 85
ORA-06512: at line 1
at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:206)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1034)
at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:191)
at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:950)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1225)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3387)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3488)
at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:3857)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1374)
at oracle.apps.fnd.cp.opp.OPPAQMonitor.initAQ(OPPAQMonitor.java:558)
at oracle.apps.fnd.cp.opp.OPPAQMonitor.init(OPPAQMonitor.java:534)
at oracle.apps.fnd.cp.opp.OPPAQMonitor.initialize(OPPAQMonitor.java:89)
at oracle.apps.fnd.cp.opp.OPPServiceThread.init(OPPServiceThread.java:94)
at oracle.apps.fnd.cp.gs
f.BaseServiceThread.run(BaseServiceThread.java:135)
Solution:
check the FND_CP_GSM_OPP_AQTBL table data is there or not.
SQL> select count(*) from applsys.FND_CP_GSM_OPP_AQTBL ;
COUNT(*)
———-
1039973
Take a backup of the table :
SQL> create table applsys.FND_CP_GSM_OPP_AQTBL_bkp2 as select * from applsys.FND_CP_GSM_OPP_AQTBL;
Table created.
Connected as Applsys User.
SQL> conn applsys/*****
Connected.
Run the Bellow script to purge the table of FND_CP_GSM_OPP_AQTBL
SQL> exec dbms_aqadm.purge_queue_table(‘FND_CP_GSM_OPP_AQTBL’, null, null);
PL/SQL procedure successfully completed.
Check the Table have any data after purge:
SQL> select count(*) from applsys.FND_CP_GSM_OPP_AQTBL ;
COUNT(*)
———-
0
Connect as APPS user
sqlplus apps/******
SQL*Plus: Release 11.2.0.4.0 Production on Thu Feb 5 11:14:34 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Run the afopp002.sql script for regenerate.
SQL>@$FND_TOP/patch/115/sql/afopp002.sql
Enter value for 1: APPLSYS
Enter value for 2: ******
Connected.
PL/SQL procedure successfully completed.
Remove the subscribers of opp.
SQL> exec fnd_cp_opp_ipc.remove_all_subscribers();
PL/SQL procedure successfully completed.
Successfully solve the OPP issue and check with user its working fine.
