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\nimish.garg>sqlplus soctt/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 = ngarg.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 "ngarg.mydomain.co.in" machine at 1521 port. Now we need to check what are the services listener at "ngarg.mydomain.co.in" knows

-- on "ngarg.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=ngarg.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\nimish.garg\product\11.2.0\dbhome_1\NETWORK\ADMIN\listener.ora
Listener Log File         E:\oracle\app\nimish.garg\diag\tnslsnr\ngarg\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ngarg.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 "ngarg.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 = ngarg.mydomain.co.in)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = myorcl.mydomain.co.in)
    )
  )

Now lets try to connect again.

C:\Users\nimish.garg>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.


Related Posts:
- SQLNET: How does Oracle Client connect with Oracle Server
- ORA-12154: TNS:could not resolve the connect identifier specified
- ORA-12545: Connect failed because target host or object does not exist
- ORA-12560: TNS:protocol adapter error
- ORA-03135: connection lost contact

1 comment:

  1. TNS-12514 can have many causes. Here are some I have seen and their fixes.

    (1) Check that the target database has been started, or that it has been running for a few minutes to give the listener time to add the services. Sometimes it’s just as simple as that.

    (2) Type “lsnrctl status” on the database machine. If it shows no service that matches the SERVICE_NAME in the tnsnames.ora file for your database then the SERVICE_NAME in the tnsnames.ora might be misspelled. If that’s the case just correct it in the tnsnames.ora file.

    (3) The database was up but refusing new connections. This can happen when the PROCESSES setting has been reached. Type “lsnrctl status” and if it shows that the database service has been “blocked” this might be the cause. Check the alert.log for hints. To increase the processes do this:
    alter system set processes=NNN SCOPE=spfile;
    You need to restart the database for the change to take effect.

    (4) The database had been renamed. Typed “lsnrctl status” to see the new service names. Changed the service names in the tnsnames.ora file to match.

    (5) The spfile was created in the wrong directory so it was not being used by the database. Created the spfile in the right directory using the command like this:
    create spfile=’...SPFILEsid.ORA’ from pfile=’...pfile.ora’;

    (6) The database had hanged because db_recovery_file_dest_size had been exceeded on a Windows database server. Shut down the database and restarted it with a pfile that contained the increased db_recovery_file_dest_size setting. Ran RMAN backup to clean the archived log files then restarted the database using its usual spfile and updated it with the new quota nnG:
    alter system set db_recovery_file_dest_size=nnG SCOPE=both;

    ReplyDelete