NaturalOracle DataGaurd tutorial Series Coming Soon!! Natural
For Any queries, please mail us at support@funoracleapps.com

ORA-24067: exceeded maximum number of subscribers for queue APPLSYS.FND_CP_GSM_OPP_AQ

No comments

XML Reports Failing 

While Running XML reports getting below error

Error:

 [6/14/18 12:02:58 PM] [main] Initialization Parameters: oracle.apps.fnd.cp.opp.OPPServiceThread:2:0:max_threads=5
[6/14/18 12:02:58 PM] [Thread-91] Service thread starting up.
[6/14/18 12:02:58 PM] [Thread-92] Service thread starting up.
[6/14/18 12:02:58 PM] [EXCEPTION] [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.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:590)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1973)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1119)
at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2191)
at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:2064)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2989)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:658)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:736)
at oracle.apps.fnd.cp.opp.OPPAQMonitor.initAQ(OPPAQMonitor.java:567)
at oracle.apps.fnd.cp.opp.OPPAQMonitor.init(OPPAQMonitor.java:543)
at oracle.apps.fnd.cp.opp.OPPAQMonitor.initialize(OPPAQMonitor.java:91)
at oracle.apps.fnd.cp.opp.OPPServiceThread.init(OPPServiceThread.java:94)
at oracle.apps.fnd.cp.gsf.BaseServiceThread.run(BaseServiceThread.java:144)


SOLUTION:


  • Stop OPP Manager



  • Connect to Apps User

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

  COUNT(*)
----------
   10


  • Take a back up of the table :


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

Table created.



  • Connect to Applsys User and run below


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


PL/SQL procedure successfully completed.


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

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


  • Connect as APPS user and run below


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

PL/SQL procedure successfully completed.

SQL> exec fnd_cp_opp_ipc.remove_all_subscribers();


Start OPP Manager and Validate.

No comments :

Post a Comment