Summary
uCMDB 9.05 upgrader fails with below error message at the step of – "Deletes Obsolete records from LOA table".
Error
The upgrader from 9.05 to 10.0 fails at step of – "Deletes Obsolete records from LOA table" with timeout error.
This error is seen with oracle with version of 11.2.0.3.0
2013-09-01 00:54:42,832 [LOACleanupUpgrader.v10] (LoaCleanupUpgrader.java:20) INFO - LoaCleanupUpgrader is about to execute SQL statement: DELETE FROM LOA WHERE NOT EXISTS (SELECT 1 FROM CDM_ROOT_1 WHERE CDM_ROOT_1.CMDB_ID = LOA.CMDB_ID) AND CUSTOMER_ID=1
2013-09-01 01:54:42,805 [LOACleanupUpgrader.v10] (LoaCleanupUpgrader.java:39) ERROR - Cleanup of LOA table failed with exception:
java.sql.SQLTimeoutException: [mercury][Oracle JDBC Driver]Execution timeout expired.
at com.mercury.jdbc.oraclebase.ddca.b(Unknown Source)
at com.mercury.jdbc.oraclebase.ddca.a(Unknown Source)
at com.mercury.jdbc.oraclebase.ddb9.b(Unknown Source)
at com.mercury.jdbc.oraclebase.ddb9.a(Unknown Source)
at com.mercury.jdbc.oraclebase.ddem.a(Unknown Source)
at com.mercury.jdbc.oraclebase.ddem.x(Unknown Source)
at com.mercury.jdbc.oraclebase.ddem.executeUpdate(Unknown Source)
at org.apache.commons.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:228)
at com.hp.ucmdb.reconciliation.maintenance.dal.LoaCleanupUpgrader.upgrade(LoaCleanupUpgrader.java:30)
at com.hp.ucmdb.upgrade.UpgradeExecutor.doUpgrade(UpgradeExecutor.java:389)
at com.hp.ucmdb.upgrade.UpgradeExecutor.runUpgraderPerCustomer(UpgradeExecutor.java:306)
at com.hp.ucmdb.upgrade.UpgradeExecutor.prepareUpgrade(UpgradeExecutor.java:248)
at com.hp.ucmdb.upgrade.UpgradeExecutor.upgrade(UpgradeExecutor.java:208)
at com.hp.upgrade.backend.api.UpgradeServerAPI.upgrade(UpgradeServerAPI.java:171)
at com.hp.upgrade.ui.pages.UpgradePage$2.run(UpgradePage.java:558)
2013-09-01 07:18:10,909 [AWT-EventQueue-0] (UpgradeServerAPI.java:226) ERROR - upgrade error for [LOACleanupUpgrader.v10]
com.hp.ucmdb.upgrade.UpgraderFailedException: [ErrorCode [-2147483648] undefined error code]
com.mercury.topaz.cmdb.shared.base.CmdbException: [ErrorCode [-2147483648] undefined error code]
java.sql.SQLTimeoutException: [mercury][Oracle JDBC Driver]Execution timeout expired.
at com.hp.ucmdb.upgrade.UpgradeExecutor.doUpgrade(UpgradeExecutor.java:409)
at com.hp.ucmdb.upgrade.UpgradeExecutor.runUpgraderPerCustomer(UpgradeExecutor.java:306)
at com.hp.ucmdb.upgrade.UpgradeExecutor.prepareUpgrade(UpgradeExecutor.java:248)
at com.hp.ucmdb.upgrade.UpgradeExecutor.upgrade(UpgradeExecutor.java:208)
at com.hp.upgrade.backend.api.UpgradeServerAPI.upgrade(UpgradeServerAPI.java:171)
at com.hp.upgrade.ui.pages.UpgradePage$2.run(UpgradePage.java:558)
Caused by: com.mercury.topaz.cmdb.shared.base.CmdbException: [ErrorCode [-2147483648] undefined error code]
java.sql.SQLTimeoutException: [mercury][Oracle JDBC Driver]Execution timeout expired.
at com.hp.ucmdb.reconciliation.maintenance.dal.LoaCleanupUpgrader.upgrade(LoaCleanupUpgrader.java:40)
at com.hp.ucmdb.upgrade.UpgradeExecutor.doUpgrade(UpgradeExecutor.java:389)
... 5 more
Caused by: java.sql.SQLTimeoutException: [mercury][Oracle JDBC Driver]Execution timeout expired.
at com.mercury.jdbc.oraclebase.ddca.b(Unknown Source)
at com.mercury.jdbc.oraclebase.ddca.a(Unknown Source)
at com.mercury.jdbc.oraclebase.ddb9.b(Unknown Source)
at com.mercury.jdbc.oraclebase.ddb9.a(Unknown Source)
at com.mercury.jdbc.oraclebase.ddem.a(Unknown Source)
at com.mercury.jdbc.oraclebase.ddem.x(Unknown Source)
at com.mercury.jdbc.oraclebase.ddem.executeUpdate(Unknown Source)
at org.apache.commons.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:228)
at com.hp.ucmdb.reconciliation.maintenance.dal.LoaCleanupUpgrader.upgrade(LoaCleanupUpgrader.java:30)
... 6 more
Cause
"Deletes Obsolete records from LOA table" upgrader steps executes few sql statements with alter statements like mentioned below.
alter table UCMDBCMDB.LOA rename to UCMDBCMDB.LOA_OLD;
alter index UCMDBCMDB.PK_LOA rename to PK_LOA_OLD;
With specific oracle version 11.2.0.3.0 , the above mentiond alter statement fails with below error.
alter table UCMDBCMDB.LOA rename to UCMDBCMDB.LOA_OLD
ORA-14047: ALTER TABLE
INDEX RENAME may not be combined with other operations
Because of this upgrader fails.
Fix
workaround for this to do the changes to the statements as below.
Change the original sql statement like below
alter table UCMDBCMDB.LOA rename to UCMDBCMDB.LOA_OLD;
with
alter table UCMDBCMDB.LOA rename to LOA_OLD;