This discussion is archived
12 Replies Latest reply: Mar 3, 2011 9:36 AM by 835297 RSS

ORA-12505, TNS:listener does not currently know of SID given in connect des

835297 Newbie
Currently Being Moderated
I am using Oracle 10g. Facing the same kind of problem. Also I am not able to connect isqlplus as well

java.sql.SQLException: Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
The Connection descriptor used by the client was:
localhost:1521:orcl

Where as I am very much able to connect to DB through sqlplus but not through either TOAD or Java program.

I was very much able to connect earlier. I have made no changes to oracle.



output of "tnsping orcl" :

TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 30-JAN-2
011 00:02:53

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

Used parameter files:
F:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = localho
st) (PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (30 msec)

output of "LSNRCTL SERVICES"


LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 30-JAN-2011 00:04
:59

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
The command completed successfully








- Thanks

Edited by: 832294 on Mar 3, 2011 9:56 AM
  • 1. Re: ORA-12505, TNS:listener does not currently know of SID given in connect des
    sybrand_b Guru
    Currently Being Moderated
    Your problem is you don't use the documentation to look up error messages.

    If you would have done so you would have noticed

    - sid and service_name are two different things
    - the error signifies your sid doesn't occur in listener.ora

    Kindly adjust the listener.ora and restart the listener.

    Please do not include your phone number in your post. This is a discussion forum and not a support forum.
    Nobody is going to call you.

    -----------
    Sybrand Bakker
    Senior Oracle DBA
  • 2. Re: ORA-12505, TNS:listener does not currently know of SID given in connect des
    sb92075 Guru
    Currently Being Moderated
    12505, 00000, "TNS:listener does not currently know of SID given 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 SID for an instance (usually a database instance) 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 instances are currently known by the listener by executing:
    //    lsnrctl services <listener name>
    //  - Check that the SID parameter in the connect descriptor specifies
    //    an instance known by the listener.
    //  - Check for an event in the listener.log file.
    post tail end of listener.log file

    post results from following OS command

    lsnrctl status
    Where as I am very much able to connect to DB through sqlplus but not through either TOAD or Java program.
    from DB Server system or remote client?
  • 3. Re: ORA-12505, TNS:listener does not currently know of SID given in connect des
    dulalhasan Newbie
    Currently Being Moderated
    Maybe you check ORACLE_HOME. if u are connect to TOAD or sqlplus and others. also check your 3rd party connection like JDBC, ODBC configuration.

    Thnx
    Dulal
  • 4. Re: ORA-12505, TNS:listener does not currently know of SID given in connect des
    835297 Newbie
    Currently Being Moderated
    Dear Bakker

    I also have suspected same thing.

    But request you to consider the below points and tell me your answer then.

    - First of all, I have not made any changes to this listener.ora
    - How am I able to work using sqlplus with out any problems ?
    - what kind of entry I need to make in listener.ora
    - I am not sure how come the SID "PLSExtProc" has been entered into listener.ora, as I have only DB/SID i.e. orcl


    please advice




    vg_paturi
  • 5. Re: ORA-12505, TNS:listener does not currently know of SID given in connect des
    835297 Newbie
    Currently Being Moderated
    listener.log

    TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE
    30-JAN-2011 20:07:13 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=)(USER=venugopal))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=169869568)) * status * 0
    30-JAN-2011 20:09:30 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl)(CID=(PROGRAM=E:\Program Files\Quest Software\Toad for Oracle\toad.exe)(HOST=HOME-1A32E018FF)(USER=venugopal))) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1335)) * establish * orcl * 12514
    TNS-12514: TNS:listener does not currently know of service requested in connect descriptor

    ________________________________________________________________________
    result of "lsnrctl status"

    LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 30-JAN-2011 20:07
    :13

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

    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
    STATUS of the LISTENER
    ------------------------
    Alias LISTENER
    Version TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Produ
    ction
    Start Date 30-JAN-2011 20:07:04
    Uptime 0 days 0 hr. 0 min. 8 sec
    Trace Level off
    Security ON: Local OS Authentication
    SNMP OFF
    Listener Parameter File F:\oracle\product\10.2.0\db_1\network\admin\listener.o
    ra
    Listener Log File F:\oracle\product\10.2.0\db_1\network\log\listener.log

    Listening Endpoints Summary...
    (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
    Services Summary...
    Service "PLSExtProc" has 1 instance(s).
    Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
    The command completed successfully

    ___________________________________________________________________

    This case, both server and client are same. I am trying to connect from server itself using TOAD which has got failed.
    I am getting succeeded when connecting from server using SQLPLUS.


    vg_paturi
  • 6. Re: ORA-12505, TNS:listener does not currently know of SID given in connect des
    sb92075 Guru
    Currently Being Moderated
    Uptime 0 days 0 hr. 0 min. 8 sec
    shows listener was started a mere 8 seconds before STATUS was issued.
    the database registers itself with listener every 60 seconds.

    post results from

    lsnrctl status
    lsnrctl service
    sqlplus scott/tiger@orcl
    (HOST=127.0.0.1)
    can result is errors because it is a non-route-able IP#
  • 7. Re: ORA-12505, TNS:listener does not currently know of SID given in connect des
    sybrand_b Guru
    Currently Being Moderated
    I consider the following things

    - You are calling me by my last name only. In my locale this is considered rude.
    - You can not be bothered to post listener.ora
    - nor can you be bothered to read documentation and require to be spoon fed, at the same time being rude to the person who is trying to help you out.

    No further help is possible.

    --------
    Sybrand Bakker
    Senior Oracle DBA
  • 8. Re: ORA-12505, TNS:listener does not currently know of SID given in connect des
    835297 Newbie
    Currently Being Moderated
    Hi Sybrand

    Thanks for your help so far. My apologies, if that is considered as rude.
  • 9. Re: ORA-12505, TNS:listener does not currently know of SID given in connect des
    PavanKumar Guru
    Currently Being Moderated
    Hi,

    You can make a search in forum and you can solve the issue by spending some time (or) try to spend some time with Oracle documentation.
    I accept with Sybrand.

    Refer to doc: http://download.oracle.com/docs/cd/B28359_01/network.111/b28317/listener.htm
    link : ORA-12505, TNS:listener does not currently know of SID given in connect des

    I hope you come up with resolution and success by your self. We have provided the step for you, now it's time for you to climb the ladder by your self.

    - Pavan Kumar N
  • 10. Re: ORA-12505, TNS:listener does not currently know of SID given in connect des
    EdStevens Guru
    Currently Being Moderated
    832294 wrote:
    I am using Oracle 10g. Facing the same kind of problem. Also I am not able to connect isqlplus as well

    java.sql.SQLException: Listener refused the connection with the following error:
    ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
    The Connection descriptor used by the client was:
    localhost:1521:orcl
    localhost is non-routeable

    =================================

    A couple of important points.

    First, the listener is a server side only process. It's entire purpose in life is to receive requests for connections to databases and set up those connections. Once the connection is established, the listener is out of the picture. It creates the connection. It doesn't sustain the connection. One listener, with the default name of LISTENER, running from one oracle home, listening on a single port, will serve multiple database instances of multiple versions running from multiple homes. It is an unnecessary complexity to try to have multiple listeners or to name the listener as if it belongs to a particular database. That would be like the telephone company building a separate switchboard for each customer.

    Additional notes on the listener: One listener is capable of listening on multiple ports. But please notice that it is the listener using these ports, not the database instance. You can't bind a specific listener port to a specific db instance. Similarly, one listener is capable of listnening on multiple IP addresses (in the case of a server with multiple NICs) But just like the port, you can't bind a specific ip address to a specific db instance.

    Second, the tnsnames.ora file is a client side issue. It's purpose is for address resolution - the tns equivalent of the 'hosts' file further down the network stack. The only reason it exists on a host machine is because that machine can also run client processes.

    Assume you have the following in your tnsnames.ora:
    larry =
      (DESCRIPTION =
        (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1521))
        )
        (CONNECT_DATA =
          (SERVICE_NAME = curley)
        )
      )
    Now, when you issue a connect, say like this:
    $> sqlplus scott/tiger@larry
    tns will look in your tnsnames.ora for an entry called 'larry'. Finding it, tns sends a request through the normal network stack to (PORT = 1521) on (HOST = myhost) using (PROTOCOL = TCP), asking for a connection to (SERVICE_NAME = curley).

    Where is (HOST = myhost) on the network? When the request gets passed from tns to the next layer in the network stack, the name 'myhost' will get resolved to an IP address, either via a local 'hosts' file, via DNS, or possibly other less used mechanisms. You can also hard-code the ip address (HOST = 123.456.789.101) in the tnsnames.ora.

    Next, the standard networking process delivers the message to port 1521 on myhost. Hopefully, there is a listener on myhost configured to listen on port 1521, and that listener knows about SERVICE_NAME = curley. If so, the listener will spawn a server process to act as the intermediary between your client and the database instance. Communication to the server process will be on a randomly selected available port. At that point the listener is out of the process and continues to user port 1521 to await other connection requests.



    What can go wrong?

    First, there may not be an entry for 'larry' in your tnsnames. In that case you get "ORA-12154: TNS:could not resolve the connect identifier specified" No need to go looking for a problem on the host, with the listener, etc. If you can't place a telephone call because you don't know the number (can't find your telephone directory (tnsnames.ora) or can't find the party you are looking for listed in it (no entry for larry)) you don't look for problems at the telephone switchboard.

    Maybe the entry for larry was found, but myhost couldn't be resolved to an IP address (say there was no entry for myhost in the local hosts file). This will result in "ORA-12545: Connect failed because target host or object does not exist"

    Maybe there was an entry for myserver in the local hosts file, but it specified a bad IP address. This will result in "ORA-12545: Connect failed because target host or object does not exist"

    Maybe the IP was good, but there is no listener running: "ORA-12541: TNS:no listener"

    Maybe the IP was good, there is a listener at myhost, but it is listening on a different port. "ORA-12560: TNS:protocol adapter error"

    Maybe the IP was good, there is a listener at myhost, it is listening on the specified port, but doesn't know about SERVICE_NAME = curley. "ORA-12514: TNS:listener does not currently know of service requested in connect descriptor"

    Third: If the client is on the same machine as the db instance, it is possible to connect without referencing tnsnames and without going through the listener.

    Now, when you issue a connect, say like this:
    $> sqlplus scott/tiger
    tns will attempt to establish an IPC connection to the db instance. How does it know the name of the instance? It uses the current value of the enviornment variable ORACLE_SID. So...
    $> export ORACLE_SID=fred
    $> sqlplus scott/tiger
    It will attempt to connect to the instance known as "fred". If there is no such instance, it will, of course, fail. Also, if there is no value set for ORACLE_SID, the connect will fail.

    check executing instances to get the SID
    [oracle@vmlnx01 ~]$ ps -ef|grep pmon|grep -v grep
    oracle    4236     1  0 10:30 ?        00:00:00 ora_pmon_vlnxora1
    set ORACLE_SID appropriately, and connect
    [oracle@vmlnx01 ~]$ export ORACLE_SID='vlnxora1
    [oracle@vmlnx01 ~]$ sqlplus scott/tiger
    
    SQL*Plus: Release 10.2.0.4.0 - Production on Wed Sep 22 10:42:37 2010
    
    Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
    
    
    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    Now set ORACLE_SID to a bogus value, and try to connect
    SQL> exit
    [oracle@vmlnx01 ~]$ export ORACLE_SID=FUBAR
    [oracle@vmlnx01 ~]$ sqlplus scott/tiger
    
    SQL*Plus: Release 10.2.0.4.0 - Production on Wed Sep 22 10:42:57 2010
    
    Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
    
    ERROR:
    ORA-01034: ORACLE not available
    ORA-27101: shared memory realm does not exist
    Linux Error: 2: No such file or directory
    
    
    Enter user-name: 
    Now set ORACLE_SID to null, and try to connect
    [oracle@vmlnx01 ~]$ export ORACLE_SID=
    [oracle@vmlnx01 ~]$ sqlplus /scott/tiger
    
    SQL*Plus: Release 10.2.0.4.0 - Production on Wed Sep 22 10:43:24 2010
    
    Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
    
    ERROR:
    ORA-12162: TNS:net service name is incorrectly specified
    Ok, that is how we get from the client connection request to the listener. What about the listener's part of all this?

    The listener is very simple. It's job is to listen for connection requests and make the connection (server process) between the client and the database instance. Once that connection is made, the listener is out of the picture. If you were to kill the listener, all existing connections would continue. The listener is configured with the listener.ora file, but if that file doesn't exist, the listener is quite capable of starting up with all default values. One common mistake with the listner configuration is to specify "HOST=localhost" or "HOST=127.0.01". This is a NONROUTABLE ip address. LOCALHOST and ip address 127.0.0.1 always mean "this machine on which I am sitting". So, all computers are known as "localhost" or "127.0.0.1". If you specify this address, the listener will only be capable of receiving requests from the machine on which it is running. If you specified that address in your tnsnames file - on a remote client machine - the request would be routed to the machine on which the requesting client resides. Probably not what you want.

    =====================================
    Where as I am very much able to connect to DB through sqlplus but not through either TOAD or Java program.

    I was very much able to connect earlier. I have made no changes to oracle.



    output of "tnsping orcl" :

    TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 30-JAN-2
    011 00:02:53

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

    Used parameter files:
    F:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora

    Used TNSNAMES adapter to resolve the alias
    Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = localho
    st) (PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
    OK (30 msec)

    output of "LSNRCTL SERVICES"


    LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 30-JAN-2011 00:04
    :59

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

    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
    Services Summary...
    Service "PLSExtProc" has 1 instance(s).
    Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
    "DEDICATED" established:0 refused:0
    LOCAL SERVER
    The command completed successfully








    - Thanks
    vg_paturi
    +919642400557
  • 11. Re: ORA-12505, TNS:listener does not currently know of SID given in connect des
    835297 Newbie
    Currently Being Moderated
    Hi

    Now things are fine. The actual problem is due to recent ISP which has changed my system IP as dynamic i.e. DHCP. After making changes to network adapter, oracle is working as how it was.

    Thanks for your explanation and help in this regrad.

    vg_paturi

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points