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

Document created by RSA Customer Support Employee on Mar 10, 2020
Version 1Show Document
  • View in full screen mode

Article Content

Article Number000038553
Applies ToRSA Product Set: RSA Identity Governance & Lifecycle
RSA Version/Condition: 7.x
 
IssueAfter 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



****************************************
CauseThis 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. 
 
ResolutionTo 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.
 

Attachments

    Outcomes