Contributed by Celeste Dufresne, Adam Talbot, and Srinivas Chilukuri

Prerequisite
Symptoms
   Releases subject to symptoms
Errors
Explanations
Environments
Solutions
Related content

Prerequisite

This Support Document supplements Troubleshooting ORA-02443, ORA-02429, ORA-06512, Oracle optimization issues after updating.

Symptoms

Rule schema update fails when you update your Pega deployment that uses an Oracle database system.

When you run the rules schema update using the ./upgrade.sh or ./upgrade.bat utility on your server, you get the Oracle errors: ORA-01451, ORA-02429, ORA-02443, and ORA-00955.

Two tables and their indexes throw errors with DDL generation on Oracle:

PC_ASSIGN_WORKBASKET and indexes GETNEXTWORKFROMBASKET and GETNEXTWORKINWORKGROUP

PR_DATA_DECISION_DF_MET and index PR_DATA_DECISION_DF_MET_IDX2

Various scenarios report a persistent issue with generating the correct DDL for an Oracle update. The Oracle database optimizes the unique index for the primary key to remove the one that was automatically created by the database if there is another index on the table that contains all the columns in the primary key.

In a variant of the same general issue, the reflection of the existing data schema that is used for the cloning to the temporary data (TEMPDATA) schema incorrectly identifies the index being used by the primary key as the primary key and clones the table, making the PR_DATA_DECISION_DF_MET_IDX2 the primary key constraint during the migrate step.  Similarly, the issue is seen where cloning makes the GETNEXTWORKFROMBASKET the primary key in the TEMPDATA schema.

In another variant of the same general issue, there are cases when Oracle will optimize the index being used by the primary key to use another index on the table that contains all the columns in the primary key but, Oracle database does not remove the one that was automatically created by the database.

Releases subject to symptoms

The index PR_DATA_DECISION_DF_MET_IDX2 was introduced in Pega Platform™ version 8.7.x.

The indexes GETNEXTWORKFROMBASKET and GETNEXTWORKINWORKGROUP have existed since Pega Platform 6.x.

The ORA-01451, ORA-02429, ORA-02443, and ORA-00955 errors have been observed during more recent updates to Pega Platform version 8.x when you upgrade to a version of Oracle that enables smart optimization of indexes in certain situations.

Errors

ORA-01451: column to be modified to NULL cannot be modified to NULL

ORA-02429: cannot drop index used for enforcement of unique/primary key

ORA-02443: cannot drop constraint

ORA-00955: name is already used by an existing object

Explanations

Pegasystems defect – Incorrect introspection

Bad introspection interferes with root cause analysis. During problem investigation, engineers make assumptions about one problem that lead to other problems.

As you move from Oracle version 12.x to later versions of Oracle, you see more instances where simple schema export and import migrations are missing objects on the target database. This is the result of Oracle smart optimization.

The missing object and import errors occur when Oracle invokes its smart optimization feature. Smart optimization skips the creation of a unique index from the source database in favor of substituting a functionally equivalent non-unique index with the same column list. It is possible for the substituted index to have the same columns but in a different order. The result is that a unique index is missing on the target side, but, functionally, your schema will work as expected with applications. The net effect of Oracle smart optimization is that two indexes have been collapsed into one. While your applications will still work, risk arises for upgrade (update) scenarios and for system performance.

For more information on the effects of Oracle smart optimization, see impdp too smart for its own good - ORA-2000 and missing indexes on target... | ba6.us and other Oracle documents cited in Related Content.

ORA-01451

The update of the rules schema step when using the TEMPDATA schema fails with error ORA-01451: column to be modified to NULL cannot be modified to NULL. 

While the error occurs in the rules schema update step, the problem occurs during the migration of data schema to temporary data schema, where the cloning is done incorrectly.  The error is seen during an out-of-place update for zero-downtime (ZDT) or minimal downtime, whenever the TEMPDATA schema is used.

ORA-02429

This error occurs when Pega tries to “correct” the DDL and drop the problematic indexes. The real reason for this error is the same root cause determined for incorrect introspection for the generated DDL. Not realizing that the primary key is using the index, Pega drops indexes in the wrong order.

ORA-02443

The DDL generated for a Pega update displays the error ALTER TABLE PC_ASSIGN_WORKBASKET DROP CONSTRAINT GETNEXTWORKINWORKGROUP when trying to complete the update. In trying to update the table to the correct structure, it issues incorrect DDL. The real reason for this error is the same root cause determined for incorrect introspection for the generated DDL. It has incorrectly identified the index being used by the constraint as being the primary key constraint on the table.

ORA-00955

The DDL generated for the Pega platform update fails with the following error message while trying to create the table index pr_data_decision_df_met_idx2:

ORA-00955: name is already used by an existing object

When the table index is being updated to the correct structure, an incorrect DDL is generated. The real reason for this error is the same root cause determined for incorrect introspection for the generated DDL. In this case while the pr_data_decision_df_met_pk index is not being used by the primary key constraint, the index still exists on the table causing the incorrect ddl to be generated.

Environments

The problem was reported for a variety of update scenarios in various environments, ranging from Pega Platform version 7.x to version 8.8.x when a Pega deployment is running on an Oracle database system.

There are several ways that Oracle looks at the indexes and invokes smart optimization.  The most common one observed is when the database schemas are exported from one database and imported into another on Oracle 19.

See Symptoms, Release subject to symptoms.

See the Solutions for the various update scenarios.

Solutions

If you are using Pega Helm Charts, see Solution for Pega Helm Charts update.

If you plan to upgrade your Oracle database system that works with your Pega deployment, choose the Preventative Solution that best meets your needs:

Preventative Solution: Out-of-place update, ZDT or minimum downtime, TEMPDATA schema used

Preventative Solution: In-Place or Out-of-place with minimum downtime, TEMPDATA schema not used

Preventative Solution: Out-of-place update, TEMPDATA schema not used, data only step might see issue

Preventative Solution: In-place update

If you did not anticipate the Oracle errors and started to update your Pega deployment, see Reactive Solution: Update started.

And, finally, see Resolved Issues for fixed Pega defects.

Preventative Solution: Out-of-place update, ZDT or minimum downtime, TEMPDATA schema used 

For this case the issue may be seen during the rules schema upgrade step but issue happens in the migrate script.  To work around this issue for an out-of-place update with minimal downtime or zero downtime (ZDT) when TEMPDATA schema is used, complete the following steps:

1. Run the migrate script, migrate.sh or migrate.bat, to migrate to new rules schema and temporary data schema.

2. From the temporary data schema, drop the primary key and indexes on the two tables that experience the problem: PC_ASSIGN_WORKBASKET and PR_DATA_DECISION_DF_MET

3.  Continue with the update.

Preventative Solution: Out-of-place or In-Place with minimum downtime, TEMPDATA schema not used

For an out-of-place or in-place update when a temporary data schema is not used, drop the indexes and constraints on the real data schema prior to updating the data schema.

Preventative Solution: Out-of-place update, TEMPDATA schema not used, data only step might see the issue

Steps 1 through 3 are the normal update process. Step 4 is the workaround.

1. Run the migrate script, migrate.sh or migrate.bat.
2. Update the Rules schema.
3. Shut down the environment.
4. Workaround: From the PegaDATA schema, drop the primary key and indexes on the two tables that experience the problem:
   PC_ASSIGN_WORKBASKET
   PR_DATA_DECISION_DF_MET

5. Continue with update process and complete the post update tasks and deployment as you normally do.

Preventative Solution: In-place update

1. Shut down the environment.
2. Before you start the in-place update, from the PegaDATA schema, drop the primary key and indexes on the two tables that experience the problem:
   PC_ASSIGN_WORKBASKET
   PR_DATA_DECISION_DF_MET
3. Continue with the update process and complete the post update tasks and deployment as you normally do.

Reactive Solution: Update started

If you started to update your Pega deployment and encounter an Oracle error, perform the following steps:

1. Drop the constraints and indexes on the problematic tables. Do this in TEMPDATA or real data, depending on where you see the error.
2. Restart the update from the beginning by removing the resume.properties file from the scripts directory or setting automatic.resume to false in the setupDatabase.properties file.

Solution for Pega Helm Charts update

If you are updating your Pega deployment using Pega Helm Charts, request support from the Global Client Support team:

Go to My Support Portal and report an Issue for either Deployment or Installation or Development or Testing.

See Creating a support ticket and the related videos.

Resolved Issues for fixed Pega defects

This Pega Platform issue will be addressed in one or more future patch releases.

The defect corrections will remove the problem indexes from the Pega Platform and address any scenarios where problem indexes might persist in Pega deployments.

Watch the Pega Support Center Pega Platform Resolved Issues and search for the following ISSUEs for the Pega Platform release in which they were fixed:

ISSUE-544662 in Pega Platform 8.6 for error ORA-02443

ISSUE-663550 in Pega Platform 8.7

ISSUE-794801, ISSUE-794803, ISSUE-794811, ISSUE-707714, and ISSUE-775413 (BUG set) in Pega Infinity ’23 for error ORA-01451

ISSUE-789670, ISSUE-794807, ISSUE-794808 in Pega Infinity ’23 for error ORA-02443

ISSUE-798778 in Pega Infinity ’23 for error ORA-02429

Related content

Oracle Documentation

Oracle / PLSQL: ORA-01451 Error Message

ORA-02443: Cannot drop constraint – nonexistent constraint – Oracle PL/SQL Tutorial (plsql.co)

Drop Tablespace Failed with ORA-02429: cannot drop index used for enforcement of unique/primary key (oracle.com)

impdp too smart for its own good - ORA-2000 and missing indexes on target... | ba6.us

Why Does The Primary/Unique Key Constraint Point To A Different Index After Import? (oracle.com)

Primary Key Indexes Not Created During Import (oracle.com)

Pega Documentation

Performing your update

Pega Helm Charts

Upgrading Pega Platform in your deployment with zero-downtime

Pega Support Document

Troubleshooting ORA-02443, ORA-02429, ORA-06512, Oracle optimization issues after updating