ORA-01000 Maximum open cursors exceeded (after HFix-29955)
After installing HFix-29955, which fixes the error ORA-01000: Maximum number of open cursors exceeded, the error persists.
DatabaseException caused by prior exception: java.sql.SQLException: ORA-00604: error occurred at recursive SQL level 1
2017-06-20 15:25:30,796 INFO [stdout] (Dispatcher-Thread-99) ORA-01000: maximum open cursors exceeded
Steps to Reproduce
A defect or configuration issue in the operating environment
The default value for Oracle is 300. This is not sufficient for the Pega Platform.
Make the following change to the operating environment:
Increase the value of the Oracle database parameter open_cursors as explained in the Oracle Help Center and the Oracle Online Documenation.
- Go to the Oracle Help Center for Middleware, the Oracle Fusion Middleware Online Documentation Library, 11g Release 2 (126.96.36.199).
- In the Oracle Fusion Middleware Administrator's Guide for Oracle Entitlements Server (OES), go to Chapter 16, Tuning Performance and Monitoring Components, Section 16.3.1, Tuning the OES Policy Store, Subsection 188.8.131.52 Oracle Database System Parameters Tuning.
- After Table 16-1, Oracle Database System Parameters Tuning (where the open_cursors parameter is defined), see the SQL script for setting the system parameters, which includes the following statement:
ALTER SYSTEM SET open_cursors = 500 SCOPE = spfile;
The SQL script shown must be run as a DB user with the SYSDBA role.
- See also the Oracle Database Online Documentation for 12c Release 1 (12.1) Database Administration, Section 1.178 OPEN_CURSORS.
With that said, 300 open cursors can inevitably fall victim to the ORA-01000: maximum open cursors exceeded error in the logs and cause other Pega Platform issues. To put this into perspective, an internal system at Pega has its Development, Test-QA, and Staging environments with this Oracle database parameter set to 2000, and its Production environment set to 3000. This open cursors setting is also proportionate to the number of users, the size of the application, service calls, report queries, and all of the other features of the application. The default value of 300 is exceptionally low.