DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Thursday, April 25, 2013

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

ISSUE:XML reports  complete in warning

This is a freshly cloned instance and all managers and OPP services
were up and running.
However users were reporting of XML reports completing in error.
 
A check of the OPP logs revealed the following error:

[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:
SQL> select count(*) from applsys.FND_CP_GSM_OPP_AQTBL ;

  COUNT(*)
----------
   1039973

Take a back up of the table :

SQL> create table applsys.FND_CP_GSM_OPP_AQTBL_bkp2 as select * from applsys.FND_CP_GSM_OPP_AQTBL;

Table created.

SQL> show user
USER is "APPS"
SQL> conn applsys/devapps
Connected.
SQL> show user
USER is "APPLSYS"


SQL> exec dbms_aqadm.purge_queue_table('FND_CP_GSM_OPP_AQTBL', null, null);


PL/SQL procedure successfully completed.

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

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

Connect as APPS

bash-3.00$ sqlplus apps/devapps

SQL*Plus: Release 10.1.0.5.0 - Production on Tue Sep 11 11:12:45 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL>@$FND_TOP/patch/115/sql/afopp002.sql
Enter value for 1: APPLSYS
Enter value for 2: devapps
Connected.

PL/SQL procedure successfully completed.

SQL> exec fnd_cp_opp_ipc.remove_all_subscribers();




ISSUE SOLVED !!

1 comment:

Sridevi K said...

Regards
Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, SQL, PL/SQL, D2K at training@oracleappstechnical.com | +91 - 9581017828.