Identity Manager Startup fails with ORA-02158: invalid CREATE INDEX option ORA-06512

Products

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

Issue/Introduction

Startup of Identity Manager fails with DB errors. DB not initialized properly, specifically TaskPersistence:

2018-04-03 14:43:45,514 INFO [ims.Main] (MSC service thread 1-1) ################################################ 2018-04-03 14:43:45,515 INFO [ims.Main] (MSC service thread 1-1) # CA Identity Manager 14.1.0.0.347 2018-04-03 14:43:45,515 INFO [ims.Main] (MSC service thread 1-1) ################################################ 2018-04-03 14:43:45,515 INFO [ims.Main] (MSC service thread 1-1) ---- CA IAM FW Startup Sequence Initiated. ---- 2018-04-03 14:43:45,520 INFO [ims.Main] (MSC service thread 1-1) * Startup Step 1 : Attempting to start ServiceLocator. 2018-04-03 14:43:45,783 ERROR [ims.tmt.CreateDatabaseSchema] (MSC service thread 1-1) Error in creating Task Persistence database schema.: java.sql.SQLSyntaxErrorException: ORA-02158: invalid CREATE INDEX option ORA-06512: at line 6 at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:91) [ojdbc6.jar:Oracle JDBC Driver version - "11.1.0.7.0-Production"] at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133) [ojdbc6.jar:Oracle JDBC Driver version - "11.1.0.7.0-Production"] at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:206) [ojdbc6.jar:Oracle JDBC Driver version - "11.1.0.7.0-Production"] at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455) [ojdbc6.jar:Oracle JDBC Driver version - "11.1.0.7.0-Production"] at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413) [ojdbc6.jar:Oracle JDBC Driver version - "11.1.0.7.0-Production"] at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1034) [ojdbc6.jar:Oracle JDBC Driver version - "11.1.0.7.0-Production"] at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:183) [ojdbc6.jar:Oracle JDBC Driver version - "11.1.0.7.0-Production"] at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:942) [ojdbc6.jar:Oracle JDBC Driver version - "11.1.0.7.0-Production"]

Environment

Identity Suite 14.x with Oracle object store.
(Including identity Manager, Governance and Portal)
 

Cause

The startup fails due to insufficient DB permissions.

 

Resolution

You should use the least-privileged security model, and not require more access than needed.

 For the DBA, we do NOT require  “dba role” (aka “root" access” on a complete database server)

The process for IM/IG/IP/Jasper/SSO or most applications, for Oracle db, may be simplified to the five (5) lines below:

drop user <idm_db_user> cascade;
create user <idm_db_user> identified by <Password01>;
grant connect, resource to <idm_db_user>;
grant create table, create view, create session, create trigger, create sequence to <idm_db_user>;
grant create tablespace, drop tablespace, manage tablespace, unlimited tablespace to <idm_db_user>;

Also, regarding Oracle NEW SERVICE ID & ACCESS:
- Run twice to validate; must be able to drop and add back the new service ID.
- Create a different service ID for each solution

The IM/IP (Identity Manager/Identity Portal) solutions will auto-recreate their database tables upon restart of the J2EE server.
This activity may be monitored with the Oracle SQL Developer GUI or via 3rd party Database Tools.