esevents.log flood of ORA-12514 "TNS listener does not currently know of service requested in connect descriptor"

Products

• CA Service Desk Manager
• CA Service Management - Service Desk Manager

Issue/Introduction

Administrators noticed esevents.log flood of ORA-12514 messages:

ERROR DBConnectionPool 228 Unable to obtain Database Connection 
java.sql.SQLException: Listener refused the connection with the following error:
ORA-12514, TNS:listener does not currently know of service requested in connect descriptor

Cause

It happens when Clarity Service Desk is running on Oracle and the Address Name in tnsnames.ora (Service Desk side) is different compared with the Service Name (database side). ES_EBL (ES Events) daemon pulls NX_DB_NODE from NX.env and it is valid for searching this variable in tnsnames.ora, but it also searches for the Service Name in the database side. If they are different, you start having the message ORA-12514.

esevents.log (when working as expected):

INFO  DBConnectionPool 198 Oracle Host Name: <database.hostname>
INFO  DBConnectionPool 199 Oracle Port: 1521
INFO  DBConnectionPool 200 Oracle Service Name: orcl
INFO  DBConnectionPool 201 Oracle User ID: mdbadmin
INFO  DBConnectionPool 120 DB configuration loaded.

Oracle Service Name is based on NX_DB_NODE.

Environment

Clarity Service Management 17.2
Clarity Service Management 17.1

Resolution

Make sure the Address Name in tnsnames.ora and the Service Name in the database side are the same. It is pretty easy to update the tnsnames.ora if necessary in Service Desk server side.

The Address Name should match with NX_DB_NODE in NX.env and with the Service Name configured in the database side.

Additional Information

tnsnames.ora (Database server side):

[[email protected] admin]$ pwd
/home/oracle/app/oracle/product/12.2.0/dbhome_1/network/admin
[[email protected] admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /home/oracle/app/oracle/product/12.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = database.ip)(PORT = 1521))

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = database.ip)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

tnsnames.ora (Service Desk server side):

ORCL=
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = database.hostname)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ORCL)
    )
  )