12 Replies Latest reply: Mar 3, 2011 11:36 AM by 835297 RSS

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

    835297
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          Pavan Kumar
                          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
                            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
                              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