Pega 8.5.1 to 8.8.1
getting the below error in update logs and update failing:
getting the below error in update logs and update failing:
[java] 2023-02-07 14:08:14,833 ( SQLLoaderInternal) INFO - File: oracledate032.sql [java] 2023-02-07 14:08:14,833 ( SQLLoaderInternal) INFO - ALTER TABLE PRPCDATA.PR_DATA [java] 2023-02-07 14:08:15,493 ( SQLLoaderInternal) INFO - Failed to execute SQL in file oracledate032.sql [java] [java] java.sql.SQLException: ORA-02437: cannot validate (PRPCDATA.PR_DATA_PK) - primary key violated [java] [java] at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:509) [java] at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:461) [java] at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1104) [java] at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:553) [java] at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:269) [java] at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:655) [java] at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:229) [java] at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:41) [java] at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:928) [java] at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1205) [java] at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1823) [java] at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1778) [java] at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:303) [java] at com.pega.pegarules.install.SQLLoaderInternal.readAndExecuteSQLStatements(SQLLoaderInternal.java:160) [java] at com.pega.pegarules.install.SQLLoaderInternal.load(SQLLoaderInternal.java:119) [java] at com.pega.pegarules.install.SQLLoader.loadInner(SQLLoader.java:99) [java] at com.pega.pegarules.install.SQLLoader.load(SQLLoader.java:78) [java] at com.pega.pegarules.install.SQLLoader.main(SQLLoader.java:66) [java] Caused by: Error : 2437, Position : 44, Sql = ALTER TABLE PRPCDATA.pr_data [java] ADD CONSTRAINT "PR_DATA_PK" PRIMARY KEY [java] ( [java] "PZINSKEY" [java] ), OriginalSql = ALTER TABLE PRPCDATA.pr_data [java] ADD CONSTRAINT "PR_DATA_PK" PRIMARY KEY [java] ( [java] "PZINSKEY" [java] ), Error Msg = ORA-02437: cannot validate (PRPCDATA.PR_DATA_PK) - primary key violated [java] [java] at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:513) [java] ... 17 more [java] 2023-02-07 14:08:15,493 ( SQLLoaderInternal) WARNING - Statement: ALTER TABLE PRPCDATA.pr_data [java] ADD CONSTRAINT "PR_DATA_PK" PRIMARY KEY [java] ( [java] "PZINSKEY" [java] ) [java] 2023-02-07 14:08:15,493 ( SQLLoaderInternal) WARNING - [java] ******************************************************************************** [java] [java] Encountered a SQL Exception, clean schema before running again. [java] [java] ******************************************************************************** [java] Exception in thread "main" com.pega.pegarules.install.SQLLoaderException: Error executing SQL statements [java] at com.pega.pegarules.install.SQLLoader.load(SQLLoader.java:81) [java] at com.pega.pegarules.install.SQLLoader.main(SQLLoader.java:66) [java] Caused by: java.sql.SQLException: ORA-02437: cannot validate (PRPCDATA.PR_DATA_PK) - primary key violated [java] [java] at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:509) [java] at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:461) [java] at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1104) [java] at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:553) [java] at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:269) [java] at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:655) [java] at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:229) [java] at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:41) [java] at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:928) [java] at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1205) [java] at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1823) [java] at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1778) [java] at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:303) [java] at com.pega.pegarules.install.SQLLoaderInternal.readAndExecuteSQLStatements(SQLLoaderInternal.java:160) [java] at com.pega.pegarules.install.SQLLoaderInternal.load(SQLLoaderInternal.java:119) [java] at com.pega.pegarules.install.SQLLoader.loadInner(SQLLoader.java:99) [java] at com.pega.pegarules.install.SQLLoader.load(SQLLoader.java:78) [java] ... 1 more [java] Caused by: Error : 2437, Position : 44, Sql = ALTER TABLE PRPCDATA.pr_data [java] ADD CONSTRAINT "PR_DATA_PK" PRIMARY KEY [java] ( [java] "PZINSKEY" [java] ), OriginalSql = ALTER TABLE PRPCDATA.pr_data [java] ADD CONSTRAINT "PR_DATA_PK" PRIMARY KEY [java] ( [java] "PZINSKEY" [java] ), Error Msg = ORA-02437: cannot validate (PRPCDATA.PR_DATA_PK) - primary key violated [java] [java] at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:513) [java] ... 17 more
In-place upgrade on existing schema.
Accepted Solution
Updated: 2 Jun 2023 8:16 EDT
Pegasystems Inc.
GB
@Atanu Sen this sounds like on a DataOnly update there are Duplicate pzinskey values present in table pr_data .
when running:
select pzinskey from pr_data order by pzinskey;
You should fix the source system by removing the duplicate entries, reapply the PK consttraint and redo your upgrade. Other scenarios: if a PK index on a shipped table may have been dropped in the source system, and that this allowed for the creation of duplicate entries in the pr4_rule_sysgen table. When the upgrade is attempted, the upgrade rightly applies the shipped index which fails due to the table inconsistent data.
Perform the below changes:-
1. Run the below query to get the duplicate pzinskey.
select pzinskey from pr_data group GROUP BY pzinskey HAVING COUNT(*) > 1;
2.Then remove the duplicate entries from the table and resume the upgrade process.
Updated: 18 Jun 2024 13:58 EDT
Cognizant
CA
Pega patch update issues:
Thanks. This helped to resolve my issue. I used the below queries to fix this issue with pr_data & PR_LOG tables.
Find duplicates:
select pzinskey from data.PR_LOG GROUP BY pzinskey HAVING COUNT(*) > 1;
Delete Duplicates:
delete from data.PR_LOG a WHERE ROWID > (SELECT MIN(ROWID) FROM data.PR_LOG b WHERE b.pzInsKey=a.pzInsKey); ()