Unable to connect to oracle DB.

Summary

Unable to connect to oracle DB even though tnsping, SQL*Plus and sm -sqlverifyconnection are working.

Unable to connect to oracle DB even though tnsping, SQL*Plus and sm -sqlverifyconnection are working.

This can happen after you have imported a DB dump from another environment for example.

Line in sm.log showing connection is setup correctly:

RTE I Command line: /opt/HP/ServiceManager9.30/Server/RUN/smserver -sqlverifyconnection

Also in the sm.log you can see a line like:

RTE I Connection to dbtype 'oracledbtype' Oracle server 'TNSALIAS' as user 'SMUSER' successful

If this is followed later by an oracle TNS error you most likely are having a sm.ini configuration issue.

The exact oracle error in question differs per OS used, below 2 examples based on Linux and Windows.

Linux

RTE E Error: SQL code=12162 message=ORA-12162: TNS:net service name is incorrectly specified

RTE E SQL code=12162 message=ORA-12162: TNS:net service name is incorrectly specified

RTE E API=OCIServerAttach-Local [in sqociConnect]

Windows

RTE E Error: SQL code=12560 message=ORA-12560: TNS:protocol adapter error

RTE E SQL code=12560 message=ORA-12560: TNS:protocol adapter error

RTE E API=OCIServerAttach-Local [in sqociConnect]

This is caused by the fact that in the sm.ini the parameter sqldictionary value cannot be found in the SM database table SQLDBINFOM1.

Which in the example above is oracledbtype.

This means that the lines containing:

sqldictionary:oracledbtype

[oracledbtype]

need to be altered.

The values you can use you can get from the SM database in the following way:

Connect via SQL*Plus to the oracle database using the SM oracle user (example below uses same dummy names as used above in messages)

sqlplus SMUSER/SMUSER@TNSALIAS

Then you issue the following SQL statement:

SQL> select SQL_DB_TYPE from SQLDBINFOM1;

SQL_DB_TYPE

--------------------------------------------------

db2universal

oraclecustomer

sqlserver

SQL>

NOTE The output above is for test purposes altered from OOB and the only oracle based one is the one called oraclecustomer.

So in this example you should rewrite those entries in the sm.ini to state:

sqldictionary:oraclecustomer

[oraclecustomer]

After saving the sm.ini you can start SM again and connect successfully.