Error message when start identity Manager - Error in creating Auditing database schema.: java.sql.SQLException: ORA-01430: the column already exists

Products

• CA Identity Manager
• CA Identity Governance
• CA Identity Portal

Issue/Introduction

When starting the Identity Manager server an error appears in the logs which prevents the server from loading:

ERROR [ims.tmt.CreateDatabaseSchema] (ServerService Thread Pool -- 235) Error in creating Auditing database schema.: java.sql.SQLException: ORA-01430: the column already exists in the table Caused by: Error : 1430, Position : 32, Sql = alter table imsAuditEvent12 add(subject_user varchar2(100)), OriginalSql = alter table imsAuditEvent12 add(subject_user varchar2(100)),

Environment

Identity Manager 14.x

Cause

The audit database check up and creation at the startup of the server are failing for any number of reasons. The example in subject here is when a column cannot be updated.

Resolution

Perform the following steps on the Oracle database server:

(Note: If you need to retain the audit data, then please export/save the data before completing the steps below.)

1. Please delete all existing Audit tables. You can run the CA\Identity Manager\IAM Suite\Identity Manager\tools\db\auditing\Oracle\ims_oracle_audit_delete.sql file to accomplish this.
2. If the following tables exist, then drop them: IDMSTALEEVENTOBJECTRECORDS6 and IDMSTALEEVENTRECORDS6
3. Then restart the server, so that the audit schema is laid down automatically.

 

Note: If you are using Virtual Appliance the ims_oracle_audit_delete.sql file is located in the Virtual Appliance Tools, below an example of the path after installation.

X:\...\Virtual Appliance Tools\CA-IM_Tools\db\auditing\Oracle