esevents.log flood of ORA-12514

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

Environment

CA Service Desk Manager 17.1 and up

Cause

It happens when 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: <tnsservicename>
INFO  DBConnectionPool 201 Oracle User ID: <oracle userid>
INFO  DBConnectionPool 120 DB configuration loaded.

Oracle Service Name is based on NX_DB_NODE.

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):

$ pwd
<oracle home folder>/network/admin
$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: <oracle home folder>/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

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

<tnsservicename> =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = <database.ip>)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = <tnsservicename>)
    )
  )

tnsnames.ora (Service Desk server side):

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

esevents.log is found in the %NXROOT%\log folder