Installing UIM 8.5.1 with Oracle database throws an error in the install log: PLS-00905: object UIM.SPN_BAS_CREATECONSTRAINT is invalid

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)

Issue/Introduction

The installation of UIM fails with an Oracle error in the installation log:

PLS-00905: object UIM.SPN_BAS_CREATECONSTRAINT is invalid 

ORA-06550: line 71, column 5: 

Environment

UIM 8.4,8.5.1

Oracle 11gR2, 12c

Cause

The installation of UIM created the stored procedure.  However, Oracle has it marked as INVALID.

 

Resolution

1- Delete the tablespace, data files, and contents in Oracle:

                     DELETE TABLESPACE <uim_tablespace> INCLUDING CONTENTS AND DATA FILES;

2- Drop the user.  The reason for this is to remove any dangling objects linked to the user.

                    DROP USER <uim_database_user> CASCADE;

3- Recreate the tablespace and user as appropriate for Oracle 11 or 12, reference: UIM installation guide

4- Please validate with the DBA that the UIM database user has these permissions.  These are a requirement.  

      grant unlimited tablespace to db_owner;

     grant administer database trigger to db_owner;

     grant create table to db_owner;

    grant create view to db_owner;

    grant alter any table to db_owner;

    grant select any table to db_owner;

    grant create sequence to db_owner;

    grant create procedure to db_owner;

    grant create session to db_owner;

   grant create trigger to db_owner;

   grant create type to db_owner;

   grant select on sys.v_$session to db_owner;

    grant execute on sys.dbms_lob to db_owner;

    grant execute on dbms_redefinition to db_owner;

    grant create any table to db_owner;

   grant drop any table to db_owner;

   grant lock any table to db_owner;

Additional Information

In Oracle, if an object (stored procedure, View, etc...) is marked as INVALID, it would normally get marked as VALID, the next time it executes.

The exceptions are:

1- Something is syntactically wrong with the object's DDL

2- One of the object's dependencies is also INVALID.

In this case, SPN_BAS_CREATECONSTRAINT referenced the stored procedure  SPN__LOGEVENT.

The procedure SPN__LOGEVENT referenced V$SESSION in it's DDL.  The UIM user was not able to query v$session, which highlighted that the user did not have the correct permissions.

Please work with your DBA to identify the correct steps were followed to setup the Oracle environment for UIM: UIM Installation Guide

Sample query to find an object's dependencies:

Select

TYPE || ' ' ||

OWNER || '.' || NAME || ' references ' ||

REFERENCED_TYPE || ' ' ||

REFERENCED_OWNER || '.' || REFERENCED_NAME

as DEPENDENCIES

From all_dependencies

Where name = UPPER(LTRIM(RTRIM( 'SPN__LOGEVENT' )))

AND (REFERENCED_OWNER <> 'SYS'

AND REFERENCED_OWNER <> 'SYSTEM'

AND REFERENCED_OWNER <> 'PUBLIC'

)

AND (OWNER <> 'SYS'

AND OWNER <> 'SYSTEM'

AND OWNER <> 'PUBLIC'

)

order by OWNER, name,

REFERENCED_TYPE ,

REFERENCED_OWNER ,

REFERENCED_name

/