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

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
Cause: The listener received a request to establish a connection to a database or other service. The connect descriptor received by the listener specified a service name for a service (usually a database service) that either has not yet dynamically registered with the listener or has not been statically configured for the listener. This may be a temporary condition such as after the listener has started, but before the database instance has registered with the listener.


Action:

  • Wait a moment and try to connect a second time.
  • Check which services are currently known by the listener by executing: lsnrctl services
  • Check that the SERVICE_NAME parameter in the connect descriptor of the net service name used specifies a service known by the listener.
  • If an easy connect naming connect identifier was used, check that the service name specified is a service known by the listener.
  • Check for an event in the listener.log file.

Reference: Oracle Documentation

C:\Users\>sqlplus scott/tiger@orcl
SQL*Plus: Release 11.2.0.3.0 Production on Sat Apr 12 14:15:01 2014
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

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


Resolving ORA-12514:
“ORA-12514: TNS:listener does not currently know of service requested in connect descriptor” is self explanatory. ORA-12514 is most likely related to tnsnames.ora where connect descriptor is specified incorrectly. So here our first step is to check tnsnames.ora for “orcl” connect descriptor to find what service name was actually requested.

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = sachin.mydomain.co.in)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl.mydomain.co.in)
    )
  )

Now we know that we are requesting “orcl.mydomain.co.in” service on “sachin.mydomain.co.in” machine at 1521 port. Now we need to check what are the services listener at “sachin.mydomain.co.in” knows

-- on "sachin.mydomain.co.in"

C:\>lsnrctl status

LSNRCTL for 32-bit Windows: Version 11.2.0.3.0 - Production on 12-APR-2014 14:23:16

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=sachin.mydomain.co.in)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 11.2.0.3.0 - Production
Start Date                07-APR-2014 20:05:09
Uptime                    4 days 18 hr. 18 min. 8 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   E:\oracle\app\sachin\product\11.2.0\dbhome_1\NETWORK\ADMIN\listener.ora
Listener Log File         E:\oracle\app\sachin\diag\tnslsnr\ngarg\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sachin.mydomain.co.in)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 2 handler(s) for this service...
Service "myorcl.mydomain.co.in" has 1 instance(s).
  Instance "myorcl", status READY, has 1 handler(s) for this service...
The command completed successfully

As we can see here listener at “sachin.mydomain.co.in” knows about “myorcl.mydomain.co.in” service and we are looking for “orcl.mydomain.co.in”, which is the reason we are facing ORA-12514. So we just need to correct our client’s tnsnames.ora as

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = sachin.mydomain.co.in)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = myorcl.mydomain.co.in)
    )
  )

Now lets try to connect again.

C:\Users>sqlplus scott/tiger@orcl
SQL*Plus: Release 11.2.0.3.0 Production on Sat Apr 12 14:28:45 2014
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Release 11.2.0.3.0 - Production

SQL>

Success !!! 


 Also some times ORA-12514 is related to the listener configuration, there are times when database is not registered with listener. lsnrctl status command can let you know if listener is serving the database service or not, otherwise you may need to configure the listerer again using Static or Dynamic Registration.