Failure connecting to dbtype [oracle10]

  • KM02897225
  • 03-Aug-2017
  • 11-Jan-2019

Summary

This document will provide the details as why the SM application fails to connect with oracle DB with below errors: 8976( 8976) 07/19/2017 21:35:03 RTE E Failure connecting to dbtype [oracle10], using Oracle TNS '', user '' 9080( 9080) 07/19/2017 21:35:07 RTE E Error: SQL code=12162 message=ORA-12162: TNS:net service name is incorrectly specified 9080( 9080) 07/19/2017 21:35:07 RTE E SQL code=12162 message=ORA-12162: TNS:net service name is incorrectly specified

Error

Service Manager RTE  fails to connect to Oracle Database with below errors, even when there is no parameter for [oracle10] dbtype in the sm.ini file.


RTE E Failure connecting to dbtype [oracle10], using Oracle TNS '', user ''

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

Cause

The names [oracle] and [oracle10] are just names and do not reflect anything about the actual version of Oracle. 

These names correspond with the SQL table names in the Service Manager dbdict.  The application is looking for [oracle10] because there must be a dbdict that has a type of oracle10 ( on the SQL tables tab in the dbdict ).

The sqllibrary parameter in the sm.ini controls the client version of Oracle, so if using the Oracle 12 client nothing is referencing the Oracle 10 client.

To find the dbdicts with the oracle10 type

1. Login to Service Manager
2. 
Goto Database Manager

Table: dbdict
Form:dbdict.sql

3. At the blank dbdict format click on SQL tables tab
4. In the Type column enter: oracle10
5. Click Search.

So [oracle10] with a sqllibrary that points to the oracle 12 client is using Oracle 12, this type is just a name that corresponds to the entry in the sm.ini file. 

Fix

While using Oracle 12 client, the sqldictionary needs to remain oracle. However, [oracle10] entries - if there are any dbdict that has a type of oracle10 - needs to be upated.referenced within the sm.ini

The [Oracle10] parameters must remain in combination with oracle dbtype sm.ini in order to connect:

1. Edit the sm.ini
2. Update the [oracle] section to look like the following below example

sqldictionary:oracle
[oracle10]
sqldb:HPSMDBBSS
*sqllogin:FGFG43F09672C6DF94A45A81DBF79F088161GFGF
sqllibrary:sqoracle.oci12.so

[oracle]
sqldb:HPSMDBBSS
*sqllogin:FGFG43F09672C6DF94A45A81DBF79F088161GFGF
sqllibrary:sqoracle.oci12.so

3. Save sm.ini
4. Stop and restart Service Manager