UIM upgrade failed with error ORA-00942: table or view does not exist in Oracle 19c
search cancel

UIM upgrade failed with error ORA-00942: table or view does not exist in Oracle 19c

book

Article ID: 8309

calendar_today

Updated On:

Products

DX Unified Infrastructure Management (Nimsoft / UIM) CA Unified Infrastructure Management On-Premise (Nimsoft / UIM) CA Unified Infrastructure Management SaaS (Nimsoft / UIM) Unified Infrastructure Management for Mainframe

Issue/Introduction

If you are running the UIM upgrade and the UIM abort the installation, check the uimserver_ia_install.log and check if you can see this information on the log.

2017-09-23 03:30:59,607 ERROR console.NimCustomCodeConsoleBase:executeConsoleAction:134 [main]  - Exception caught during processing. Aborting installation.
(503) , java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist
ORA-06512: at line 58
: ORA-00942: table or view does not exist
ORA-06512: at line 58

    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:268)
    at com.nimsoft.install.uimserver.action.impl.UIMServerDBSchemaController.configureSlmCreate(UIMServerDBSchemaController.java:204)
    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:168)
    at com.nimsoft.install.uimserver.action.impl.UIMServerDBSchemaController.doConfigure(UIMServerDBSchemaController.java:146)
    at com.nimsoft.install.uimserver.action.impl.UIMServerDBSchemaController.run(UIMServerDBSchemaController.java:130)
    at com.nimsoft.install.uimserver.console.ia.UIMServerDBSchemaIACustomCodeConsole.nimExecuteConsoleAction(UIMServerDBSchemaIACustomCodeConsole.java:67)
    at com.nimsoft.install.iacore.console.NimCustomCodeConsoleBase.executeConsoleAction(NimCustomCodeConsoleBase.java:114)
    at com.zerog.ia.installer.ConsoleBasedAAMgr.ah(Unknown Source)
    at com.zerog.ia.installer.ConsoleBasedAAMgr.ac(Unknown Source)
    at com.zerog.ia.installer.ConsoleBasedAAMgr.runPostInstall(Unknown Source)
    at com.zerog.ia.installer.LifeCycleManager.consoleInstallMain(Unknown Source)
    at com.zerog.ia.installer.LifeCycleManager.executeApplication(Unknown Source)
    at com.zerog.ia.installer.Main.main(Unknown Source)
    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.zerog.lax.LAX.launch(Unknown Source)
    at com.zerog.lax.LAX.main(Unknown Source)

2017-09-23 03:30:59,607 INFO  util.IAAbortInstallerUtils:abortInstall:23 [main]  - Calling abortInstall

You will see this message just after this part of the oracle_slm_create.sql script or mysql_slm_create.sql or sqlserver_slm_create.sql, this script is based on your Database.

      BEGIN
        OPEN c;
        FETCH c INTO x;
        IF (c%NOTFOUND)
        THEN
          lVCreateView := 'CREATE VIEW ' || lVName || '(table_id, sampletime, samplevalue) AS ';
          lVCreateView := lVCreateView || 'SELECT table_id, sampletime,samplevalue FROM ' || lRName || ' ';
          lVCreateView := lVCreateView || ' UNION ALL ';
          lVCreateView := lVCreateView || 'SELECT table_id, sampletime,sampleavg AS samplevalue FROM ' || lHName;
          EXECUTE IMMEDIATE lVCreateView;
        END IF;
        CLOSE c;
      END;
    END IF;
  END LOOP;
  CLOSE ciQOS;
END;

Environment

  • Any UIM upgrade above 7.x

Cause

  • The script is trying to do a UNION for the RN_QOS_DATA and HN_QOS_DATA table queries and if there is a RN that does not have a HN or there is a HN with no RN, this will fail
  • Missing RN or HN table in the database.

Resolution

Determine if the tables that need to be created will require the samplemax column or not:

select 
name,
description,
r_table,
unit,
hasmax,
isbool,
type
from s_qos_definition where r_table in ('RN_QOS_DATA_0020','RN_QOS_DATA_0021')

In this case one of the missing tables has the hasmax value and the other does not. I will need to create new tables, one with, and one without the samplemax column.

The rest of the article will be based on a table that has the samplemax column, but the process of finding a like table that does not have the samplemax column can be created using the same principles listed here:

 

Access the database using a Oracle client like SQLDeveloper and look the list of the tables in the database.   

Look at the RN_QOS_DATA_  and HN_QOS_DATA_ tables.

When you find the missing table

Open the SQLDeveloper to check the structure for the table.

Here my sample I don't have the HN_QOS_DATA_0044

Open the table RH_QOS_DATA_0044 and compare with another table to see if the structure is the same

The table below 0040 has 7 entries including the samplemax column.

Normally we just have two tables formats one with 7 rows in collumn_name and one with 6 rows.

 

 After you find a RN table that has the same format as your missing HN table you will have to go to the HN table to export the file to create the missing table.

After you find the references open the HN table and do right click, go to Quick DDL and save to file.

After you save, open the file at notepad++ or any other that you prefer, you need to copy the name the table that you have and replace for the name that your table is missing.

After you changed the table names as above you can ask you DBA to run this creation script.

When missing table is created you will be able to upgrade the environment.

Additional Information

The failure can also happen if there is a gap in the tables. 

This query will identify missing tables. 

set serveroutput on
DECLARE CURSOR ciQOS IS SELECT qos_def_id FROM S_QOS_DEFINITION;
  qosid        NUMBER;
  lRName       VARCHAR2(255);
  lHName       VARCHAR2(255);
  lQosDefId    VARCHAR2(255);
  table_exists NUMBER;
  htable_exists NUMBER;
BEGIN
  OPEN ciQOS;
  LOOP
    FETCH ciQOS INTO qosid;
    EXIT WHEN ciQOS%NOTFOUND;
    IF (ciQOS%FOUND)
    THEN

      lQosDefId := qosid;
      IF (length(lQosDefId) < 4)
      THEN
        lQosDefId := LPAD(lQosDefId, 4, '0');
      END IF;

      lRName := 'RN_QOS_DATA_' || lQosDefId;
      lHName := 'HN_QOS_DATA_' || lQosDefId;
      dbms_output.put_line('QoS Def ID : ' ||lQosDefId);
     SELECT
    count(*)
  INTO table_exists
  FROM USER_TABLES
  WHERE TABLE_NAME = lRName;
  IF (table_exists= 0)
  THEN
   dbms_output.put_line(lRName || ' table does not exist');
  END IF;
    SELECT
    count(*)
  INTO htable_exists
  FROM USER_TABLES
  WHERE TABLE_NAME = lHName;
  IF (htable_exists= 0)
  THEN
   dbms_output.put_line(lHName || ' table does not exist');
  END IF;
      END IF;
  END LOOP;

  CLOSE ciQOS;
 
END;

Powered by Wolken Software