Applies to Pega Platform versions 8.1.x through 8.6.x
Symptoms
Connecting to an external Oracle database fails after you apply certain Pega 8.x patch releases or after you update to Pega Platform 8.x.
Errors
When Oracle is the external database, the database connection error looks like this example:
Caused by: com.pega.pegarules.pub.database.DatabaseException: Database-General ORA-00942: table or view does not exist
From: (unknown)
at com.pega.pegarules.data.internal.access.PerformaneHelper.initialize(PerformaneHelper.java:81)
at com.pega.pegarules.data.internal.access.PerformaneHelper.initiazeAndGet(PerformaneHelper.java:55)
In some patch releases the error might look like the following example of a database connection error:
Caused by: com.pega.pegarules.pub.database.DatabaseException: ERROR: relation "v$parameter" does not exist
Position: 19: code: <none> SQLState: <none> Message: <none>
at com.pega.pegarules.data.internal.access.PerformaneHelper.initialize(PerformaneHelper.java:81) ~[prprivate-data.jar:?]
at com.pega.pegarules.data.internal.access.PerformaneHelper.initiazeAndGet(PerformaneHelper.java:55) ~[prprivate-data.jar:?]
The error example above is from a PegaRULES PostgreSQL database. If the PegaRULES database is Microsoft SQL Server or IBM Db2, the error might be slightly different, but the stack trace will be the same.
Explanation
Pega has added Oracle metadata query optimizations to help boost the platform runtime performance.
Pega Platform versions 8.1.9, 8.2.8, 8.3.5, 8.4.3, 8.4.4, 8.5.2, 8.5.3 and 8.6.x have the Oracle metadata query optimizations enabled by default. As a prerequisite, Pega requires that the SELECT and ALTER SESSION database privileges be granted to the platform Oracle database user as specified in Solution.
The Symptoms and Errors described above result from lack of privileges for the database user that Pega is connecting to the Oracle database.
Solution
Apply the latest Pega Platform 8 Patch Release
How to enable optimization
How to disable optimization
How to request a hotfix
Apply the latest Pega Platform 8 Patch Release
Choose the solution for the Pega 8 release that you are using:
- Apply the latest and final Pega Platform version 8.1 Patch Release, which is Pega 8.1.9, and apply HFix-69504. Then either enable optimization or disable it.
- Apply the latest and final Pega Platform version 8.2 Patch Release, which is Pega 8.2.8, and apply HFix-70496. Then either enable optimization or disable it.
- Apply the latest Pega Platform version 8.3 Patch Release, which is Pega 8.3.5, and apply HFix-70497. Then either enable optimization or disable it.
- Apply the latest Pega Platform version 8.4 Patch Release, which is Pega 8.4.4 or later release if available. Then either enable optimization or disable it.
- Apply the latest Pega Platform version 8.5 Patch Release, which is Pega 8.5.3 or later release if available. Then either enable optimization or disable it.
- Apply the latest Pega Platform version 8.6 Patch Release, which is Pega 8.6.5 or later release if available. Then either enable optimization or disable it.
How to enable optimization
- To allow Oracle database optimization, set the following Dynamic System Setting:
Ruleset: Pega-Engine
Name: prconfig/database/oracle/allowOptimization/default
Value: true - Specify the following privileges:
grant select on sys.v_$parameter to <externalUser>
grant alter session to <externalUser>
<externalUser>is the database username that is configured to connect to the external Oracle database. - For Pega Platform version 8.6.x, specify the following privilege:
grant SELECT ON SYS.V_$PARAMETER to <DatabaseUser>;
The SELECT permission on the v$parameter table gives the Pega Platform information about Oracle database’s optimizer_mode, which is temporarily altered for the current Pega session for running the metadata query.
If the application is adequately performant and if the Oracle DBA chooses not to grant the database privileges, you can disable optimization by setting the DSS value for allowOptimization to false.
How to disable optimization
If the application is adequately performant and if the Oracle DBA chooses not to grant the database privileges, the optimization can be disabled by setting the DSS value for allowOptimization to false.
Ruleset: Pega-Engine
Name: prconfig/database/oracle/allowOptimization/default
Value: false
How to request a hotfix
In My Support Portal, create a service request For Something I Need > Existing hotfix (HF-).
See My Support Portal Frequently Asked Questions.
Related Content
Pega software maintenance and Extended Support
Troubleshooting failed system startup after update or patch (Oracle database)
