Upgrade to UIM 8.51 or higher fails in Oracle environment

Document ID : KB000008603
Last Modified Date : 14/02/2018
Show Technical Document Details
Issue:

When upgrading to UIM 8.51 or higher the installer stops at 10% with oracle_nis_server_create.sql on the screen.

When examining the uimserver_ia_install.log file the following error is observed:

2017-10-17 12:07:18,902 ERROR impl.UIMServerDBSchemaController:run:133 [Thread-28]  - NimException caught

(503) , java.sql.SQLException: ORA-54032: column to be renamed is used in a virtual column expression

ORA-06512: at line 93

: ORA-54032: column to be renamed is used in a virtual column expression

ORA-06512: at line 93

 

at com.nimsoft.nimdbprovider.base.util.NimDBScriptRunner.runDBScript(NimDBScriptRunner.java:77)

at com.nimsoft.nimdbprovider.base.provider.NimDBProviderBase.runDBScript(NimDBProviderBase.java:252)

at com.nimsoft.nimdbprovider.base.provider.NimDBProviderBase.runDBScriptFile(NimDBProviderBase.java:185)

at com.nimsoft.install.uimserver.action.impl.UIMServerDBSchemaController.runScriptCommon(UIMServerDBSchemaController.java:276)

at com.nimsoft.install.uimserver.action.impl.UIMServerDBSchemaController.configureNisServerCreate(UIMServerDBSchemaController.java:240)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

at java.lang.reflect.Method.invoke(Unknown Source)

at com.nimsoft.install.nimcommon.method.NimMethodCallback.invokeMethod(NimMethodCallback.java:532)

at com.nimsoft.install.nimcommon.method.NimMethodCallback.invokeMethod(NimMethodCallback.java:516)

at com.nimsoft.install.uimserver.action.impl.UIMServerDBSchemaController.doConfigure_dbScript(UIMServerDBSchemaController.java:169)

at com.nimsoft.install.uimserver.action.impl.UIMServerDBSchemaController.doConfigure(UIMServerDBSchemaController.java:147)

at com.nimsoft.install.uimserver.action.impl.UIMServerDBSchemaController.run(UIMServerDBSchemaController.java:131)

at java.lang.Thread.run(Unknown Source)

Caused by: java.sql.SQLException: ORA-54032: column to be renamed is used in a virtual column expression

ORA-06512: at line 93

 

Environment:
UIM 8.51Oracle Database
Cause:

Oracle databases supports the use of virtual columns. If a virtual column has been created that references a regular database column, the Oracle database engine will prevent UIM from renaming the regular column and cause the database upgrade to fail.

Note that virtual columns are never created by UIM. UIM accesses your database via a vendor-agnostic database layer and therefore does not make use of RDBMS-specific features like virtual columns.

It is also possible that Virtual Columns may have been created by some other system or process. If you are not sure of the origin of the virtual columns in your database, contact your database administrator to investigate further and to have these removed before proceeding with your upgrade.

Resolution:

To find the extended stats on the object CM_GROUP

SELECT COLUMN_NAME, DATA_DEFAULT, HIDDEN_COLUMN

FROM USER_TAB_COLS

WHERE TABLE_NAME = 'CM_GROUP';

This will output the columns with the extended statistics in the SYS_OP_COMBINED_HASH() output.

 

 

Removing columns created by Extended Statistics requires using an in-build stored procedure, DBMS_STATS.DROP_EXTENDED_STATS(). Usage of this stored procedure is covered further in ORA-54033 and the Hidden Virtual Column Mystery, and looks similar to the following.  Be sure to replace with values from your own environment and USER_TAB_COLS output:

EXEC DBMS_STATS.DROP_EXTENDED_STATS(ownname=>'<YOUR_DB_USERNAME>', tabname=>'CM_GROUP', extension=>'("IS_UNIFIED","TARGET")')

 

At this point, the installation should proceed beyond the point it was failing at previously

 

Additional Information:

ORA-54033 and the Hidden Virtual Column Mystery