Maximum open cursors exceeded: PEGA_DATA_SPPC_DATA_UNIQUEID
Hello,
We are getting the below issue in our UAT environment. Its intermittent, and only happening in UAT environment. Please check the attached screenshot as well.
Unable to Obtain Unique ID: Problem encountered executing a stored procedure: code:1000 SQLState: 72000 Message: ORA- 01000: maximum open cursor exceeded
I have already checked the cursor configuration and its exactly same in all other environment. But the problem is happening in one environment only.
There is no mismatch in JDBC driver and DB version as well. Restarting the server solves the problem for some time and it comes again.
I have also followed up on other posts as well but most of them saying same thing to check the driver. Dont know what is causing the issue.
***Moderator Edit: Vidyaranjan | Updated Categories***
***Updated by Moderator: Marissa to add SR Details***
Accepted Solution
Pegasystems Inc.
IN
Hello!
On reviewing the corresponding SR, we see that the issue was resolved after upgrading to Pega 7.2.2.
Thank you,
The Toronto Dominion Bank
CA
2017-04-11 17:21:17,979 [ PegaRULES-Batch-3] [ STANDARD] [ PegaRULES:07.10] ( internal.async.AgentQueue) ERROR - Problem queue Pega-ProcessEngine #3: System-Queue-FlowDependency.pzProcessFlowDependency will restart in 180000 ms
2017-04-11 17:21:43,820 [ PegaRULES-Batch-3] [ STANDARD] [ PegaRULES:07.10] (l.access.ConnectionManagerImpl) ERROR - Not returning connection 102340838 for database "pegadata" to the pool as it previously encountered the following error
User ID: System
Last SQL: {call PEGA_DATA.sppr_sys_reservequeueitem_b ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) }
java.sql.SQLException: ORA-01000: maximum open cursors exceeded
ORA-06512: at "PEGA_DATA.SPPR_SYS_RESERVEQUEUEITEM_B", line 138
ORA-01000: maximum open cursors exceeded
ORA-06512: at line 1
2017-04-11 17:21:17,979 [ PegaRULES-Batch-3] [ STANDARD] [ PegaRULES:07.10] ( internal.async.AgentQueue) ERROR - Problem queue Pega-ProcessEngine #3: System-Queue-FlowDependency.pzProcessFlowDependency will restart in 180000 ms
2017-04-11 17:21:43,820 [ PegaRULES-Batch-3] [ STANDARD] [ PegaRULES:07.10] (l.access.ConnectionManagerImpl) ERROR - Not returning connection 102340838 for database "pegadata" to the pool as it previously encountered the following error
User ID: System
Last SQL: {call PEGA_DATA.sppr_sys_reservequeueitem_b ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) }
java.sql.SQLException: ORA-01000: maximum open cursors exceeded
ORA-06512: at "PEGA_DATA.SPPR_SYS_RESERVEQUEUEITEM_B", line 138
ORA-01000: maximum open cursors exceeded
ORA-06512: at line 1
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:879)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:204)
at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:1041)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1329)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3584)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3685)
at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4714)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1376)
at org.jboss.jca.adapters.jdbc.CachedPreparedStatement.execute(CachedPreparedStatement.java:297)
at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.execute(WrappedPreparedStatement.java:404)
at com.pega.pegarules.data.internal.access.DatabasePreparedStatementImpl.execute(DatabasePreparedStatementImpl.java:990)
at com.pega.pegarules.data.internal.access.DatabaseImpl.list(DatabaseImpl.java:6194)
at com.pega.pegarules.data.internal.access.DatabaseImpl.executeRDB(DatabaseImpl.java:6664)
at com.pega.pegarules.data.internal.access.DatabaseImpl.executeRDB(DatabaseImpl.java:6620)
at com.pega.pegarules.data.internal.access.DatabaseImpl.executeRDB(DatabaseImpl.java:6601)
at com.pega.pegarules.session.internal.async.agent.QueueManagerImpl$QueueIteratorImpl.next(QueueManagerImpl.java:1951)
at com.pega.pegarules.session.internal.async.agent.QueueManagerImpl$QueueIteratorImpl.next(QueueManagerImpl.java:1874)
at com.pega.pegarules.session.internal.async.agent.QueueProcessor.execute(QueueProcessor.java:454)
at com.pega.pegarules.session.internal.async.BatchRequestorTask.run(BatchRequestorTask.java:884)
at com.pega.pegarules.session.internal.PRSessionProviderImpl.performTargetActionWithLock(PRSessionProviderImpl.java:1192)
at com.pega.pegarules.session.internal.PRSessionProviderImpl.doWithRequestorLocked(PRSessionProviderImpl.java:930)
at com.pega.pegarules.session.internal.PRSessionProviderImpl.doWithRequestorLocked(PRSessionProviderImpl.java:829)
at com.pega.pegarules.session.internal.async.BatchRequestorTask.run(BatchRequestorTask.java:617)
at com.pega.jsr166backport.java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:991)
at com.pega.jsr166backport.java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:532)
at java.lang.Thread.run(Thread.java:745)
2017-04-11 17:21:43,820 [ PegaRULES-Batch-3] [ STANDARD] [ PegaRULES:07.10] ( async.agent.QueueManagerImpl) ERROR - Problem with browse of System-Queue-ServiceLevel
com.pega.pegarules.pub.database.DatabaseException: There was a problem getting a list: code: 1000 SQLState: 72000 Message: ORA-01000: maximum open cursors exceeded
ORA-06512: at "PEGA_DATA.SPPR_SYS_RESERVEQUEUEITEM_B", line 138
ORA-01000: maximum open cursors exceeded
ORA-06512: at line 1
From: (BEA25D3B2EE3658D9A39E4FA4383F0A47)
SQL: {call PEGA_DATA.sppr_sys_reservequeueitem_b ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) }
SQL Inserts: <PEGA_DATA.pr_sys_queue_sla> <System-Queue-ServiceLevel> <Pega-ProCom:ServiceLevelEvents> <Scheduled> <c1b8c841417ef4862c3be3c648559237> <> <20170411T212143.816 GMT> <true> <> <(OUTPUT PARAMETER)>
Caused by SQL Problems.
Problem #1, SQLState 72000, Error code 1000: java.sql.SQLException: ORA-01000: maximum open cursors exceeded
ORA-06512: at "PEGA_DATA.SPPR_SYS_RESERVEQUEUEITEM_B", line 138
ORA-01000: maximum open cursors exceeded
ORA-06512: at line 1
at com.pega.pegarules.data.internal.access.ExceptionInformation.createExceptionDueToDBFailure(ExceptionInformation.java:167)
at com.pega.pegarules.data.internal.access.DatabaseImpl.list(DatabaseImpl.java:6271)
at com.pega.pegarules.data.internal.access.DatabaseImpl.executeRDB(DatabaseImpl.java:6664)
at com.pega.pegarules.data.internal.access.DatabaseImpl.executeRDB(DatabaseImpl.java:6620)
at com.pega.pegarules.data.internal.access.DatabaseImpl.executeRDB(DatabaseImpl.java:6601)
at com.pega.pegarules.session.internal.async.agent.QueueManagerImpl$QueueIteratorImpl.next(QueueManagerImpl.java:1951)
at com.pega.pegarules.session.internal.async.agent.QueueManagerImpl$QueueIteratorImpl.next(QueueManagerImpl.java:1874)
at com.pega.pegarules.session.internal.async.agent.QueueProcessor.execute(QueueProcessor.java:454)
at com.pega.pegarules.session.internal.async.BatchRequestorTask.run(BatchRequestorTask.java:884)
at com.pega.pegarules.session.internal.PRSessionProviderImpl.performTargetActionWithLock(PRSessionProviderImpl.java:1192)
at com.pega.pegarules.session.internal.PRSessionProviderImpl.doWithRequestorLocked(PRSessionProviderImpl.java:930)
at com.pega.pegarules.session.internal.PRSessionProviderImpl.doWithRequestorLocked(PRSessionProviderImpl.java:829)
at com.pega.pegarules.session.internal.async.BatchRequestorTask.run(BatchRequestorTask.java:617)
at com.pega.jsr166backport.java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:991)
at com.pega.jsr166backport.java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:532)
at java.lang.Thread.run(Thread.java:745)
Caused by: java.sql.SQLException: ORA-01000: maximum open cursors exceeded
ORA-06512: at "PEGA_DATA.SPPR_SYS_RESERVEQUEUEITEM_B", line 138
ORA-01000: maximum open cursors exceeded
ORA-06512: at line 1
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:879)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:204)
at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:1041)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1329)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3584)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3685)
at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4714)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1376)
at org.jboss.jca.adapters.jdbc.CachedPreparedStatement.execute(CachedPreparedStatement.java:297)
at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.execute(WrappedPreparedStatement.java:404)
at com.pega.pegarules.data.internal.access.DatabasePreparedStatementImpl.execute(DatabasePreparedStatementImpl.java:990)
at com.pega.pegarules.data.internal.access.DatabaseImpl.list(DatabaseImpl.java:6194)
... 14 more
Pegasystems Inc.
IN
Hi,
As Kevin suggested, you may go ahead and create an SR with the support team. Once you do that, please let us know the SR number so that we can tag it to the thread for the benefit of the engineer.
Thanks!
Accepted Solution
Pegasystems Inc.
IN
Hello!
On reviewing the corresponding SR, we see that the issue was resolved after upgrading to Pega 7.2.2.
Thank you,