Knowledge Base

Make sure the database is started, and that its service_name is registered with the listener

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

First of all, make sure the database is started. If it is not, you’ll get this message (In 10g release 2).

C:>sc query OracleServiceXE

SERVICE_NAME: OracleServiceXE
        TYPE               : 10  WIN32_OWN_PROCESS
        STATE              : 1  STOPPED
                                (NOT_STOPPABLE,NOT_PAUSABLE,IGNORES_SHUTDOWN)
        WIN32_EXIT_CODE    : 1077       (0x435)
        SERVICE_EXIT_CODE  : 0  (0x0)
        CHECKPOINT         : 0x0
        WAIT_HINT          : 0x0

C:>sqlplus myuser@xe

SQL*Plus: Release 10.2.0.1.0 

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

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

If the database is running, probably the database hasn’t registered yet with the listener.
This occurs when the database or listener just starts up.

Normally this problem should be solved by waiting a minute or so.

If you are using dynamic registration (using the local_listener database parameter), you can try to register the database service with the listener using the following command (from Oracle 9i onwards):

show parameter local listener
-- if the above is empty
alter system set local_listener='(address=(protocol=tcp)(host=localhost)(port=1521))';
alter system register;

If this does not work or you’re using static registration, make sure the SERVICE_NAME entry used in the connection string (TNSNAMES.ORA, NAMES, OID, …) matches a valid service know by the listener.

eg.

C:>tnsping ora920

TNS Ping Utility for 32-bit Windows: Version 9.2.0.7.0 - Production 

Copyright (c) 1997 Oracle Corporation.  All rights reserved.

Used parameter files:
c:\oracle\ora920\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = DEV01)(PORT =
 2491))) (CONNECT_DATA = (SERVICE_NAME = UNKNOWN) (SERVER = DEDICATED)))
OK (20 msec)

As one can see, this is the connection information stored in a tnsnames.ora file:

ORA920.EU.DBMOTIVE.COM =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = DEV01)(PORT = 2491))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = UNKNOWN)
      (SERVER = DEDICATED)
    )
  )

However, the SERVICE_NAME UNKNOWN is not known by the listener at the database server side.
In order to test the known services by a listener, we can issue following command at the database server side:

C:>lsnrctl services

LSNRCTL for 32-bit Windows: Version 10.1.0.2.0 - Production 

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=DEV01)(PORT=1521)))
Services Summary...
Service "ORA10G.eu.dbmotive.com" has 1 instance(s).
  Instance "ORA10G", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
Service "ORA920.eu.dbmotive.com" has 2 instance(s).
  Instance "ORA920", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
  Instance "ORA920", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:2 refused:0 state:ready
         LOCAL SERVER
The command completed successfully

Know services are ORA10G and ORA920.
Changing the SERVICE_NAME in our tnsnames.ora to a known service by the listener (ORA920.EU.DBMOTIVE.COM) solved the problem.

  • Hossein Mokhtari

    Hi;
    I am using below string in VB.Net 2012 64 bit.
    my application(64 bit) into client win7 (64 bit)
    and oracle database 10g 32 bit on the win 2003(32 bit) , other server

    Dim strCon As String = “DATA SOURCE=Host-Name:Port/service-name;PERSIST SECURITY INFO=True;USER ID=user-name;Password=password;”
    Dim conn As New OracleConnection(strCon)
    conn.Open()

    Regard;
    Hossein Mokhtari
    hossein_mokhtari@yahoo.com

  • SHAKEEB

    JAZAKALLAH . THANKS ALOT :)

  • Abhishek Parsana

    great …thanks a ton!!!

  • vinay chilakamarri

    This is awesome!! Thank you for this!!!!

  • Mathew

    Thank you very much really helped !!!!!!!!

  • mamun

    thanks a lot bro……………..

  • venkat

    very useful information ………. I got solution to my problem …. thank you so much…

Oracle Gold Partner DbMotive is an Oracle Gold Partner