ORA-12514: TNS:listener could not resolve SERVICE_NAME given in connect

POSSIBLE CAUSE

Your TNS Entry is reachable to the Database listener. But SERVICE_NAME specified in the TNS Entry is not reachable or not Contactable

Begin by looking at your tnsnames.ora. you will a setting like (SERVICE_NAME=<name>)

Note: service name will be registered in the listener as DB_NAME.DOMAIN_NAME.

Example of tns entry with service name

bhu =

(DESCRIPTION =

  (ADDRESS=(PROTOCOL=TCP)(HOST=BHUVANRACA-SCAN)(PORT=1526))

(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=bhuvanrac))

)

1)   Check whether specified service_name is correct in the database

SQL> Show parameter service_name

ADD SERVICE IN RAC (Oracle 11gR2)

$ srvctl add service -d bhu_a -s bhuvanrac -r bhu_1 -a bhu_2 -l PRIMARY -q FALSE -e NONE -m NONE -w 0 -z 0

ADD SERVICE IN SINGLE INSTANCE DATABASE

SQL> exec dbms_service.CREATE_SERVICE(SERVICE_NAME=>'bhuvan', NETWORK_NAME=>'bhuvan');

SQL> exec dbms_service.START_SERVICE('bhuvan');

SQL> show parameter service

Note: you can have multiple service_name for a single database. if you have already a service, this will come second.

2)      Check the specified service is listening in the LISTERNER

$ lsnrctl service LSNR_DGA_ZS2

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 02-NOV-2011 21:13:42

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LSNR_DGA_BHU)))

Services Summary...

Service "bhuvanrac " has 1 instance(s).

  Instance "bhu_1", status READY, has 1 handler(s) for this service...

    Handler(s):

      "DEDICATED" established:47 refused:0 state:ready

         LOCAL SERVER

The command completed successfully

3)      Check the status of the LISTENER

$ lsnrctl status LSNR_DGA_BHU

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 02-NOV-2011 21:17:37

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LSNR_DGA_BHU)))

STATUS of the LISTENER

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

Alias                     LSNR_DGA_BHU

Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production

Start Date                12-OCT-2011 17:18:01

Uptime                    21 days 4 hr. 59 min. 35 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

(REMOVED SOME LINES)

Service "oraclerac " has 1 instance(s).

  Instance "BHU_1", status READY, has 1 handler(s) for this service...

The command completed successfully

4) If you are running on RAC environment and having a second IP Configured. Check whether you have properly configured(Entry, server details & port number) with the LOCAL_LISTENER, REMOTE_LISTENER & LISTENER_NETWORKS.

5) Check your LISTENER log for more details.

SOLUTION

1)      if the service is not register with the listener, you can register it

Log in to oracle as sys user

SQL> alter system register;

2)      if the service is not start

RAC Environment

$ crsctl start res <service_name>    à 11gR2

Or

$ srvctl start service –d <DB_NAME> -s <SERVICE_NAME>

In Single Instance Environment

SQL> exec dbms_service.START_SERVICE('bhuvan');

if it is related to ORA-12514 + SID

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

solution: you need to dynamically register the SID in the listener.ora file
Ex:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
        (ORACLE_HOME = /u01/oracle/product/11.2.0.4)
        (SID_NAME = BHUVAN)
    )
  )

some environment will have the global_dbname as well; if you have, you can use the below one

(SID_DESC =
(GLOBAL_DBNAME = orcl.oracle.com)
(ORACLE_HOME = C:\oracle\product\11.2.0\dbhome_1)
(SID_NAME = orcl)
)
)

Hope this documents helps you in resolving your problem.