rebuild index is failing in UD for all the tables

  • KM03172970
  • 06-Jun-2018
  • 11-Jun-2018

Summary

rebuild index mbean fails when executed from jmx-console. It fails with below timeout error. com.mercury.topaz.cmdb.shared.base.CmdbException: [ErrorCode [-2147483648] undefined error code] java.sql.SQLException: [mercury][Oracle JDBC Driver][Oracle]ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired ORA-06512: at "CMDB.SPU_REBUILD_INDEXES", line 17 ORA-06512: at line 1

Error

rebuildIndex mbean fails with below error whenever it is executed from the jmx-console

 

  ERROR  [QuartzScheduler_Worker-2] (DalAbstractCommand.java:64) - Failed to execute command [CmdbDalRebuildIndexesCommand] time [79 ms] customer ID [1, id name: Default Client] operation stack [882594216]
com.mercury.topaz.cmdb.shared.base.CmdbException: [ErrorCode [-2147483648] undefined error code]
java.sql.SQLException: [mercury][Oracle JDBC Driver][Oracle]ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
ORA-06512: at "CMDB.SPU_REBUILD_INDEXES", line 17
ORA-06512: at line 1

Cause

It is observed that rebuild of index fails whenever there is a SQL client opened with the schema.

If we close any opened sql client session with the schema and then run the rebuild index , it works without fail.

it was observed that there was no actual timeout issue but opened session in the SQL client was causing the rebuild index to fail.

Fix

Close all the SQL client sessions opened with UD schema and then run the rebuild index.