SA-41181

Summary

After installing HFix-29955, which fixes the error ORA-01000: Maximum number of open cursors exceeded, the error persists.

Error Messages

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

Unknown

Root Cause

A defect or configuration issue in the operating environment

The default value for Oracle is 300. This is not sufficient for the Pega Platform.

Resolution

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.

1. Go to the Oracle Help Center for Middleware, the Oracle Fusion Middleware Online Documentation Library, 11g Release 2 (11.1.2.2).
2. 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 16.3.1.1 Oracle Database System Parameters Tuning.
3. 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.
4. See also the Oracle Database Online Documentation for 12c Release 1 (12.1) Database Administration, Section 1.178 OPEN_CURSORS.

The number of open cursors is in direct relationship to the number of open connections being made to the database. Setting this database value ensures that you do not exceed it in any given process, such as falling into a infinite loop or a process not properly closing connections when it should be closing them.

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.