ORA-12514: TNS:listener does not currently know of service requested in connect

ORA-12514: TNS:listener does not currently know of service requested in connect

check the service name and sid name.

if there is sid _name running fine , then add a entry about the service_name as global _name


example

listener.ora


(SID_DESC =
      (GLOBAL_DBNAME = DOM.h1.al.mi.com)
      (ORACLE_HOME = /oraclebase/app/oracle/product/18.0.0/dbhome_1)
      (SID_NAME = DOM)
    )
(SID_DESC =
      (GLOBAL_DBNAME = DOM)
      (ORACLE_HOME = /oraclebase/app/oracle/product/18.0.0/dbhome_1)
      (SID_NAME = DOM)



Let me explain :

listener status :

Service "magesh.hq.dal.omi.com" has 1 instance(s). ====================================> Service_name = magesh.hq.dal.omi.com
Instance "magesh", status UNKNOWN, has 1 handler(s) for this service... ====================> SID/ Instance = magesh

a) When you connected using SID : magesh , connection succeed as the instance name in the listener matched :

Note :
Instance "magesh", status UNKNOWN, has 1 handler(s) for this service... ====================> SID/ Instance = magesh
----------------------------------------------------------

b) When you connected using SERVICE_NAME = magesh , connection succeed as the service name in the listener status is not matching : :

Note :
Service "magesh.hq.dal.omi.com" has 1 instance(s). ====================================> Service_name = magesh.hq.dal.omi.com

Solution:

Change the SERVICE_NAME=magesh.hq.dal.omi.com