13 Replies Latest reply on Nov 5, 2013 9:39 PM by sandeep.c

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

    sandeep.c

      Hello Everybody,.

       

      I would like to know,what am I missing here...

       

      I have the following Java code

      /**

           * Connect to Oracle Database.

           */

          private static void oracleConnect() {

       

              Connection conn = null;

       

              try {

                  //Class.forName("oracle.jdbc.driver.OracleDriver");

       

                  conn = DriverManager.getConnection(

                          "jdbc:oracle:thin:@localhost:1521:orcl", "scott",

                          "Scott123");

       

                  System.out.println("Connected to the Oracle database...");

              } /*catch (ClassNotFoundException e) {

                  e.printStackTrace();

              } */catch (SQLException e) {

                  e.printStackTrace();

              } finally {

                  try {

                      conn.close();

                      System.out.println("Connection closed...");

                  } catch (SQLException e) {

                      e.printStackTrace();

                  }

       

              }

       

          }

       

      Exception:

       

      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

       

          at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:412)

          at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:531)

          at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:221)

          at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)

          at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:503)

          at java.sql.DriverManager.getConnection(Unknown Source)

          at java.sql.DriverManager.getConnection(Unknown Source)

          at com.facebook.www.javagroup.GenericTaskInterpreter.oracleConnect(GenericTaskInterpreter.java:53)

          at com.facebook.www.javagroup.GenericTaskInterpreter.main(GenericTaskInterpreter.java:27)

      Caused by: oracle.net.ns.NetException: Listener refused the connection with the following error:

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

       

       

       

      All my oracle services are running fine and I am also able to login as user scott.My Oracle version is: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

       

      Any help would be appreciated.Thanks in advance.

        • 1. Re: ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
          sb92075

          post results from the following two OS commands:

          lsnrctl status

          lsnrctl service

           

           

          Since every connection request to the listener gets logged, listener.log file will contain a line with 12505 as status code.

           

           

          *This line contains valuable debugging details. So post this line & surrounding lines.*

           

           

          for additional debugging suggestions read the URL below:

          http://edstevensdba.wordpress.com/2011/03/19/ora-12514/

           

           

          [oracle@localhost ~]$ oerr ora 12505

          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.

          • 2. Re: ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
            sandeep.c

            Thanks sb92075 for the quick response.

             

            FYI Please...

             

            a) lsnrctl status

             

            Output:

             

            LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 05-NOV-2013 21:07:55

             

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

             

            Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))

            STATUS of the LISTENER

            ------------------------

            Alias                     LISTENER

            Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Produ

            ction

            Start Date                05-NOV-2013 18:41:18

            Uptime                    0 days 2 hr. 26 min. 37 sec

            Trace Level               off

            Security                  ON: Local OS Authentication

            SNMP                      OFF

            Listener Parameter File   D:\app\Sandeep\product\11.2.0\dbhome_1\network\admin\listener.ora

            Listener Log File         d:\app\sandeep\diag\tnslsnr\Sandeep-PC\listener\alert\log.xml

            Listening Endpoints Summary...

              (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 3 handler(s) for this service...

            The command completed successfully

             

            b)lsnrctl service

             

            Output:

             

            LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 05-NOV-2013 21:10:34

             

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

             

            Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))

            Services Summary...

            Service "CLRExtProc" has 1 instance(s).

              Instance "CLRExtProc", status UNKNOWN, has 3 handler(s) for this service...

                Handler(s):

                  "DEDICATED" established:0 refused:0

                     LOCAL SERVER

                  "ORACLE SERVER" established:0 refused:0 current:0 max:25 state:ready

                     CLRExtProc

                     (ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\NTN_E5C_373D2E46.ORA))

                  "ORACLE SERVER" established:0 refused:0 current:0 max:25 state:ready

                     CLRExtProc

                     (ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\NTN_E5C_373D2E46.ORA))

            The command completed successfully

            • 3. Re: ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
              EdStevens

              sandeep.c wrote:

               

              Thanks sb92075 for the quick response.

               

              FYI Please...

               

              a) lsnrctl status

               

              Output:

               

              LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 05-NOV-2013 21:07:55

               

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

               

              Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))

              STATUS of the LISTENER

              ------------------------

              Alias                     LISTENER

              Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Produ

              ction

              Start Date                05-NOV-2013 18:41:18

              Uptime                    0 days 2 hr. 26 min. 37 sec

              Trace Level               off

              Security                  ON: Local OS Authentication

              SNMP                      OFF

              Listener Parameter File   D:\app\Sandeep\product\11.2.0\dbhome_1\network\admin\listener.ora

              Listener Log File         d:\app\sandeep\diag\tnslsnr\Sandeep-PC\listener\alert\log.xml

              Listening Endpoints Summary...

                (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 3 handler(s) for this service...

              The command completed successfully

               

              b)lsnrctl service

               

              Output:

               

              LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 05-NOV-2013 21:10:34

               

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

               

              Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))

              Services Summary...

              Service "CLRExtProc" has 1 instance(s).

                Instance "CLRExtProc", status UNKNOWN, has 3 handler(s) for this service...

                  Handler(s):

                    "DEDICATED" established:0 refused:0

                       LOCAL SERVER

                    "ORACLE SERVER" established:0 refused:0 current:0 max:25 state:ready

                       CLRExtProc

                       (ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\NTN_E5C_373D2E46.ORA))

                    "ORACLE SERVER" established:0 refused:0 current:0 max:25 state:ready

                       CLRExtProc

                       (ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\NTN_E5C_373D2E46.ORA))

              The command completed successfully

              So now you have to ask yourself, "why doesn't my listener know about 'orcl'?"

               

              Two reasons come immediately to mind:

              1) this listener isn't on the server where your 'orcl' instance is running

              2) this listener IS on the same server, but the 'orcle' instance is NOT running

              3) this listener IS on the same server, the 'orcle' instance IS running, but an incorrectly set LOCAL_LISTENER parameter prevents it from registering with the listener.

               

              BTW, your listener is configured to listen on IP address 127.0.0.1.  This absolutely guarantees that it will never receive a connection request that originates from any other box.

              • 4. Re: ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
                sandeep.c

                I am running this code on my Laptop.

                 

                What really annoys me is that this code snippet worked before.I formatted my system and re-installed Oracle and now this exception.

                I am sorry if this sounds stupid.

                • 5. Re: ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
                  EdStevens

                  sandeep.c wrote:

                   

                  I am running this code on my Laptop.

                   

                  What really annoys me is that this code snippet worked before.I formatted my system and re-installed Oracle and now this exception.

                  I am sorry if this sounds stupid.

                  So is the database running on your laptop?  On the native OS - not a virtual machine?

                   

                  You've been given several clues ... I've not seen anything indicating you've actually followed up on them.

                  • 6. Re: ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
                    sb92075

                    is OS Service for the database been started & is now running?

                    is the database itself been started & is now online?

                    • 7. Re: ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
                      sandeep.c

                      The database is installed on the native OS.All its services are running and as mentioned before I am able to login as user scott and sysdba.

                       

                      I was going through your wordpress blog.and was looking at my tnsnames.ora file,the content given is as belows

                       

                      # tnsnames.ora Network Configuration File: D:\app\Sandeep\product\11.2.0\dbhome_1\network\admin\tnsnames.ora

                      # Generated by Oracle configuration tools.

                       

                      ORACLR_CONNECTION_DATA =

                        (DESCRIPTION =

                          (ADDRESS_LIST =

                            (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

                          )

                          (CONNECT_DATA =

                            (SID = CLRExtProc)

                            (PRESENTATION = RO)

                          )

                        )

                       

                      ORCL =

                        (DESCRIPTION =

                          (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

                          (CONNECT_DATA =

                            (SERVER = DEDICATED)

                            (SERVICE_NAME = orcl)

                          )

                        )


                      Still stuck

                      • 8. Re: ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
                        sb92075

                        the output from

                        lsnrctl status

                        would show any running database; which it does not for YOU!

                        see example from my system below

                         

                        [oracle@localhost ~]$ lsnrctl status

                         

                         

                        LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 05-NOV-2013 10:43:07

                         

                         

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

                         

                         

                        Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=0.0.0.0)(PORT=1521)))

                        STATUS of the LISTENER

                        ------------------------

                        Alias                     LISTENER

                        Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production

                        Start Date                12-OCT-2013 20:40:43

                        Uptime                    23 days 15 hr. 2 min. 24 sec

                        Trace Level               off

                        Security                  ON: Local OS Authentication

                        SNMP                      OFF

                        Listener Parameter File   /home/oracle/app/oracle/product/11.2.0/dbhome_2/network/admin/listener.ora

                        Listener Log File         /home/oracle/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml

                        Listening Endpoints Summary...

                          (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))

                          (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=80))(PROTOCOL_STACK=(PRESENTATION=HTTP)(SESSION=RAW)))

                          (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=21))(PROTOCOL_STACK=(PRESENTATION=FTP)(SESSION=RAW)))

                        Services Summary...

                        Service "orcl" has 1 instance(s).

                          Instance "orcl", status READY, has 2 handler(s) for this service...

                        Service "orclXDB" has 1 instance(s).

                          Instance "orcl", status READY, has 1 handler(s) for this service...

                        The command completed successfully

                        [oracle@localhost ~]$

                         

                        NOTICE that the Service "orcl" exists for my database

                        • 9. Re: ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
                          sandeep.c

                          I can see that all Oracle services has started by doing a services.msc.(particularly OracleServiceORCL)

                           

                          But when I run this from sqlplus as scott/Scott123

                          CONNECT scott/Scott123@orcl

                           

                          "TNS:listener does not currently know of SID given in connect descriptor"

                           

                          Is that what you meant ? Looks like I am missing something basic.What do you suggest ?

                          • 10. Re: ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
                            sb92075

                            sandeep.c wrote:

                             

                            I can see that all Oracle services has started by doing a services.msc.(particularly OracleServiceORCL)

                             

                            But when I run this from sqlplus as scott/Scott123

                            CONNECT scott/Scott123@orcl

                             

                            "TNS:listener does not currently know of SID given in connect descriptor"

                             

                            Is that what you meant ? Looks like I am missing something basic.What do you suggest ?

                             

                            earlier in this thread you posted the results from

                            lsnrctl status

                            that was run on  your system.

                            It does NOT show "Service "orcl" has 1 instance(s)."  since it does not know about any database named "orcl"

                            & this is consistent with the error message.

                             

                            open a Command Window & do as below

                             

                            lsnrctl stop

                            cd D:\app\Sandeep\product\11.2.0\dbhome_1\network\admin\

                            ren listener.ora listener.ora.sav

                            lsnrctl start

                            lsnrctl status

                            # wait 60+ seconds before proceeding

                            lsnrctl service

                             

                            COPY the results from above then  PASTE  all back here

                            1 person found this helpful
                            • 11. Re: ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
                              sandeep.c

                              a) I tried "lsnrctl stop" but it said

                               

                              LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 06-NOV-2013 02:45

                              :44

                               

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

                               

                              Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))

                              TNS-01190: The user is not authorized to execute the requested listener command

                               

                               

                              So, I tried stopping it from services.msc,i.e,stopping the service "OracleOraDb11g_home1TNSListener"

                               

                               

                              b) ren listener.ora listener.ora.sav => Gave me Access  denied error.

                              So,I renamed the file manually.

                               

                               

                              lsnrctl status gave me the following output.

                               

                              LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 06-NOV-2013 02:52

                              :45

                               

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

                               

                              Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

                              STATUS of the LISTENER

                              ------------------------

                              Alias                     LISTENER

                              Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Produ

                              ction

                              Start Date                06-NOV-2013 02:52:19

                              Uptime                    0 days 0 hr. 0 min. 26 sec

                              Trace Level               off

                              Security                  ON: Local OS Authentication

                              SNMP                      OFF

                              Listener Log File         d:\app\sandeep\diag\tnslsnr\Sandeep-PC\listener\alert\

                              log.xml

                              Listening Endpoints Summary...

                                (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Sandeep-PC)(PORT=1521)))

                              The listener supports no services

                              The command completed successfully

                               

                              -------------------------------------------------------------------------------

                               

                              lsnrctl service gave me the following output...

                               

                               

                              LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 06-NOV-2013 02:53

                              :43

                               

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

                               

                              Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

                              Services Summary...

                              Service "orcl" has 1 instance(s).

                                Instance "orcl", status READY, has 1 handler(s) for this service...

                                  Handler(s):

                                    "DEDICATED" established:6 refused:0 state:ready

                                       LOCAL SERVER

                              Service "orclXDB" has 1 instance(s).

                                Instance "orcl", status READY, has 1 handler(s) for this service...

                                  Handler(s):

                                    "D000" established:0 refused:0 current:0 max:1022 state:ready

                                       DISPATCHER <machine: SANDEEP-PC, pid: 4736>

                                       (ADDRESS=(PROTOCOL=tcp)(HOST=Sandeep-PC)(PORT=61533))

                              The command completed successfully

                               

                              -------------------------------------------------------------------------------

                               

                               

                              I can now see "Instance "orcl", status READY, has 1 handler(s) for this service..."

                               

                              I tried running my Java code and now I have the OP...

                               

                              Connected to the Oracle database...

                               

                              Now,all what I did was stopping the listener service,renaming the listener.ora file and starting the service again.

                              Please correct me if I am wrong at any point.

                              • 12. Re: ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
                                sb92075

                                previously listener.ora contained bad or incorrect data.

                                by eliminating the file which is NOT required to start or use the listener, the default values provided success.

                                1 person found this helpful
                                • 13. Re: ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
                                  sandeep.c

                                  Thanks a ton Ed.Marking the question as answered.

                                   

                                  Regards,
                                  Sandeep