Problem:

During running of the 2^nd Upgrade (DataOnly upgrade) which is basically the 4^th steps of the Upgrade Script execution process, below error is coming and the process get stopped.

:17,504 (SQLLoader) INFO - ALTER TABLE *****.PR4_LOG_RULE_USAGE

[java] 2016-01-04 08:01:17,859 (SQLLoader) WARNING - Failed to execute SQL in file oracledate02410.sql

[java]

[java] java.sql.SQLException: ORA-02437: cannot validate (******.PR4_LOG_RULE_USAGE_PK) - primary key violated

[java]

[java] at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447)

[java] at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)

[java] at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:951)

[java] at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:513)

[java] at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:227)

[java] at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)

[java] at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:195)

[java] at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:1036)

[java] at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1336)

[java] at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1916)

[java] at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1878)

[java] at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:318)

[java] at com.pega.pegarules.install.SQLLoader.main(SQLLoader.java:266)

[java] 2016-01-04 08:01:17,863 ( SQLLoader) WARNING - Statement: ALTER TABLE ******.pr4_log_rule_usage

[java] ADD CONSTRAINT pr4_log_rule_usage_PK PRIMARY KEY

[java] (

[java] pzInsKey

[java] )

[java] Exception in thread "main" com.pega.pegarules.install.SQLLoader$SQLLoaderException:

[java] ********************************************************************************

[java]

[java] Encountered a SQL Exception, clean schema before running again.

[java]

[java] ********************************************************************************

[java] at com.pega.pegarules.install.SQLLoader.fail(SQLLoader.java:359)

[java] at com.pega.pegarules.install.SQLLoader.main(SQLLoader.java:316)

Root Cause:

During the DataOnly upgrade, the Upgrade Script generate and apply many DDL SQL statements for data schema, among those there is one statement which create Primary key constrain in the pr4_log_rule_usage table in the data schema.

The statement is like below

ALTER TABLE <data schema>.pr4_log_rule_usage ADD CONSTRAINT pr4_log_rule_usage_PK PRIMARY KEY (pzInsKey);

we were getting “cannot validate (******.PR4_LOG_RULE_USAGE_PK) - primary key violated” error while running below statement and data only upgrade was failing, because in the Data schema which was copied from the existing 6.x environment, the table PR4_LOG_RULE_USAGE already contain some duplicate records which was not allowing to create primary key.

Solution:

We deleted duplicate records from the table PR4_LOG_RULE_USAGE. Once done we executed dataOnly upgrade again and it got executed successfully.

So it is recommended that in Lift and Shift approach, as pre upgrade steps truncate the PR4_LOG_RULE_USAGE table.

Truncating this table before the running of upgrade script will not be an issue, because this table does not holds any business transaction, this table holds statistics collected during system shutdown about cumulative rule execution statistics associated with theLog-RuleUsage class

Regards

Shankha

**Moderation Team has archived post**

This post has been archived for educational purposes. Contents and links will no longer be updated. If you have the same/similar question, please write a new post.