Upgrade/patch fails with 'ORA-00001: unique constraint (SYS.I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST) violated' error in RSA Identity Governance & Lifecycle

[#1]

Article Number

000038553

Applies To

RSA Product Set: RSA Identity Governance & Lifecycle
RSA Version/Condition: 7.0.x, 7.1.x, 7.2.x
 

Issue

After migrating an RSA Identity Governance & Lifecycle database during an upgrade or patch installation, the installation fails with the following errors in the log files:

patch.log ($AVEKSA_HOME/wildfly/standalone/log/patch.log):

java.sql.SQLIntegrityConstraintViolationException: 
ORA-00001: unique constraint (SYS.I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST) violated
ORA-06512: at line 16

aveksaServer.log ($AVEKSA_HOME/wildfly/standalone/log/aveksaServer.log):

****************************************

Patching has failed

ORA-00001: unique constraint (SYS.I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST) violated
ORA-06512: at line 16

****************************************

03/10/2020 12:25:39.012 WARN  (ServerService Thread Pool -- 115) [com.aveksa.server.runtime.AveksaSystem] 
Skipping startup operations because checkDatabase is not clear for startup
03/10/2020 12:25:39.012 WARN  (ServerService Thread Pool -- 115) [com.aveksa.server.runtime.AveksaSystem] 
Is Operation In Progress: false Is Operation Pending: false 
Is Restart Needed: false Has Errors: true Is OK To Startup: false
03/10/2020 12:25:39.013 INFO  (ServerService Thread Pool -- 115) [com.aveksa.server.runtime.AveksaSystem] 
******************** Aveksa System Initialization End ********************
03/10/2020 12:25:39.013 FATAL (ServerService Thread Pool -- 115) [com.aveksa.server.runtime.AveksaSystem] 

****************************************

Initialization has failed!

ORA-00001: unique constraint (SYS.I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST) violated
ORA-06512: at line 16


****************************************

Cause

This problem occurs when dropping a table column from an RSA Identity Governance & Lifecycle database table and corrupt data exists in the retained database statistics history data. Oracle database statistics are retained by default for 31 days. See related RSA Knowledge Base Article 000029937 -- How to change the default Oracle Statistics History Retention period for RSA Identity Governance & Lifecycle on the Oracle statistics history retention period. 
 

Resolution

To resolve this error, the database statistics need to be purged until the corrupted data has been purged. The fully documented solution can be found at Oracle Doc ID 2127444.1.

Here is the resolution from the Oracle web site. Please note these commands must be executed as sysdba or the SYS user:

1. Purge the old statistics from the history to clear the anomalies. By default, Oracle maintains the history of optimizer statistics up to 31 days.
2. Start with purging the statistics history from the recent to the oldest as follows.
3. Since the bad data could exist in any date, it is recommended to perform below actions one by one and check whether the drop column is succeeded:

SQL> exec dbms_stats.purge_stats(sysdate - 20); 
=> purge the old statistics history more than 20 days retention.
SQL> exec dbms_stats.purge_stats(sysdate - 15); 
=> purge the old statistics history more than 15 days retention.
SQL> exec dbms_stats.purge_stats(sysdate - 7); 
=> purge the old statistics history more than 7 days retention.
SQL> exec dbms_stats.purge_stats(sysdate - 1); 
=> purge the old statistics history more than 1 day retention.
SQL> exec dbms_stats.purge_stats(DBMS_STATS.PURGE_ALL); 
=> purge the complete history of optimizer statistics

Once you can drop a table column, the corrupted data has been purged and you can stop further purging of the historical statistics data.