Upgrade to UIM 8.51 or higher fails in Oracle environment
Article ID: 8603
Updated On:
Products
Issue/Introduction
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
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
Feedback
