1 2 3 Previous Next 33 Replies Latest reply: Oct 19, 2012 6:39 AM by 967142 RSS

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

    967142
      Hello,

      After installation of Oracle Database Express Edition 11g and Oracle SQL Developer 3.0, I tryed connect a database.

      Informations entered :
      Nom de connexion : xe_sys
      Nom utilisateur : sys
      Mdp : *********

      Type de connexion : de base, role sysdba
      Nom d'hote : POST1.compteUs.local
      Port : 1521
      SID : XE

      when I tested the connection I have this famous message that appears:
      Statut : échec -Echec du test : Listener refused the connection with the following error:
      ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

      this is listener.ora :
      SID_LIST_LISTENER =
      (SID_LIST =
      (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
      (PROGRAM = extproc)
      )
      (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
      (PROGRAM = extproc)
      )
      )

      LISTENER =
      (DESCRIPTION_LIST =
      (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = POST1.compteUs.local)(PORT = 1521))
      )
      )

      DEFAULT_SERVICE_LISTENER = (XE)

      this is sqlnet.ora :
      SQLNET.AUTHENTICATION_SERVICES = (NTS)

      this is tnsname.ora :
      XE =
      (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = POST1.compteUs.local)(PORT = 1521))
      (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
      )
      )

      EXTPROC_CONNECTION_DATA =
      (DESCRIPTION =
      (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      )
      (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
      )
      )

      ORACLR_CONNECTION_DATA =
      (DESCRIPTION =
      (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      )
      (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
      )
      )

      Can you help me please ?
      Thanks
        • 1. Re: ORA-12505, TNS:listener does not currently know of SID given in connect des
          orafad
          There is a dedicated forum for SQL Developer.

          1. Do not connect as 'sys' user, not from a dev tool anyway. Try a different user.

          2. try Service Name instead of SID. SID is a very old way of connecting, so Service Name should be the first choice.


          If that does not help, post result of

          lsnrctl stat
          • 2. Re: ORA-12505, TNS:listener does not currently know of SID given in connect des
            967142
            Thanks of help.

            here, commande 'lsnrctl stat' executed :


            LSNRCTL for 32-bit Windows: Version 11.2.0.2.0 - Production on 10-OCT. -2012 08:
            40:29

            Copyright (c) 1991, 2010, 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 11.2.0.2.0 - Produ
            ction
            Start Date 10-OCT. -2012 03:30:05
            Uptime 0 days 5 hr. 10 min. 24 sec
            Trace Level off
            Security ON: Local OS Authentication
            SNMP OFF
            Default Service XE
            Listener Parameter File C:\oraclexe\app\oracle\product\11.2.0\server\network\admin\listener.ora
            Listener Log File C:\oraclexe\app\oracle\diag\tnslsnr\POST1\listener\alert\log.xml
            Listening Endpoints Summary...
            (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))
            (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=POST1.compteUs.local)(PORT=1521)
            ))
            Services Summary...
            Service "CLRExtProc" has 1 instance(s).
            Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
            Service "PLSExtProc" has 1 instance(s).
            Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
            The command completed successfully

            So, I tryed :

            Informations entered :
            Nom de connexion : test
            Authentification OS

            Type connection : de base, role sysdba
            host name : POST1.compteUs.local
            Port : 1521
            Service name : XE

            And this is message erreur :
            Statut : échec -Echec du test : Listener refused the connection with the following error:
            ORA-12514, TNS:listener does not currently know of service requested in connect descriptor
            • 3. Re: ORA-12505, TNS:listener does not currently know of SID given in connect des
              orafad
              There's a problem that hinders service registration -- XE service should show up in the Services summary section above.

              Stop the listener. (OracleXETNSListener service)

              Rename the file mentioned as the Listener Parameter File in output posted above.

              Start the listener again.

              Wait 1 minute, then run lsnrctl stat command. Post output, hopefully showing the XE service as registered.
              • 4. Re: ORA-12505, TNS:listener does not currently know of SID given in connect des
                967142
                Sorry bu I did not understand everything.

                I stop OracleXETNSListener service Ok and I rename wich file et how ?

                Thanks
                • 5. Re: ORA-12505, TNS:listener does not currently know of SID given in connect des
                  967142
                  should i add a SID such as :
                  (SID_DESC =
                  (SID_NAME = newSID)
                  (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
                  (PROGRAM =*??*)
                  • 6. Re: ORA-12505, TNS:listener does not currently know of SID given in connect des
                    orafad
                    >
                    I stop OracleXETNSListener service Ok and I rename wich file et how ?
                    Listener Parameter File C:\oraclexe\app\oracle\product\11.2.0\server\network\admin\listener.ora

                    This file. Rename it to, say, listener.ora_xe
                    • 7. Re: ORA-12505, TNS:listener does not currently know of SID given in connect des
                      967142
                      I rename file l listener.ora ---> listener.ora_xe and start OracleXETNSListener service

                      After 5 minutes, I try connect me but there was no change.

                      This error is alway display :

                      Statut : échec -Echec du test : Listener refused the connection with the following error:
                      ORA-12514, TNS:listener does not currently know of service requested in connect descriptor


                      here, commande 'lsnrctl stat' executed :

                      LSNRCTL for 32-bit Windows: Version 11.2.0.2.0 - Production on 10-OCT. -2012 13:
                      37:04

                      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 32-bit Windows: Version 11.2.0.2.0 - Produ
                      ction
                      Start Date 10-OCT. -2012 13:31:10
                      Uptime 0 days 0 hr. 5 min. 54 sec
                      Trace Level off
                      Security ON: Local OS Authentication
                      SNMP OFF
                      Listener Log File C:\oraclexe\app\oracle\diag\tnslsnr\NSTWS2\listener\al
                      ert\log.xml
                      Listening Endpoints Summary...
                      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=POST1.compteUs.local)(PORT=1521)
                      ))
                      The listener supports no services
                      The command completed successfully

                      Otherwise, before of rename the file, i executed the command following : tnsping oraclexe
                      It's result :
                      TNS Ping Utility for 32-bit Windows: Version 11.2.0.2.0 - Production on 10-OCT.
                      -2012 09:36:16

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

                      Used parameter files:
                      C:\oraclexe\app\oracle\product\11.2.0\server\network\admin\sqlnet.ora

                      TNS-03505: Failed to resolve name
                      • 8. Re: ORA-12505, TNS:listener does not currently know of SID given in connect des
                        orafad
                        964139 wrote:
                        Listener Log File C:\oraclexe\app\oracle\diag\tnslsnr\NSTWS2\listener\alert\log.xml
                        (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=POST1.compteUs.local)(PORT=1521)
                        Previously NSTWS2 above was POST1.

                        You likely have a networking change or mis-configuration.

                        The listener supports no services
                        The command completed successfully
                        Underlying network configuration needs to be corrected for registration to be successful.


                        Following commands might help provide some clues:

                        netstat -nao | findstr /R /C:":1521 .* LIST"

                        type %systemroot%\system32\drivers\etc\hosts | findstr /vr ^#

                        hostname

                        ping -4 <output from hostname>

                        ping -4 nstws2

                        ping -4 post1
                        • 9. Re: ORA-12505, TNS:listener does not currently know of SID given in connect des
                          967142
                          Sorry, Previously NSTWS2 above was POST1 it's one error of copy - paste. It's normal.

                          I speak my hostname POST1 for have a general name. Very Sorry.

                          Here, the commands executes :
                          C:\oraclexe\app\oracle\product\11.2.0\server\bin>netstat -nao | findstr /R /C:":
                          1521.*LIST"
                          TCP 0.0.0.0:1521 0.0.0.0:0 LISTENING 7260
                          TCP [::]:1521 [::]:0 LISTENING 7260

                          C:\oraclexe\app\oracle\product\11.2.0\server\bin>hostname
                          NSTWS2

                          C:\oraclexe\app\oracle\product\11.2.0\server\bin>ping -4 NSTWS2

                          Envoi d'une requête 'ping' sur NSTWS2.nestor-tech.local [192.168.1.10] avec 32 o
                          ctets de données :
                          Réponse de 192.168.1.10 : octets=32 temps<1ms TTL=128
                          Réponse de 192.168.1.10 : octets=32 temps<1ms TTL=128
                          Réponse de 192.168.1.10 : octets=32 temps<1ms TTL=128
                          Réponse de 192.168.1.10 : octets=32 temps<1ms TTL=128

                          Statistiques Ping pour 192.168.1.10:
                          Paquets : envoyés = 4, reçus = 4, perdus = 0 (perte 0%),
                          Durée approximative des boucles en millisecondes :
                          Minimum = 0ms, Maximum = 0ms, Moyenne = 0ms
                          • 10. Re: ORA-12505, TNS:listener does not currently know of SID given in connect des
                            orafad
                            I'm not sure what you just said, but if hostnames etc. is correct, then another networking aspect is the firewall.

                            Make sure you followed the requirements about fw configuration:
                            http://docs.oracle.com/cd/E17781_01/install.112/e18803/toc.htm#BABEBCDB
                            • 11. Re: ORA-12505, TNS:listener does not currently know of SID given in connect des
                              967142
                              Hi,

                              Sorry, i speak english only a few.

                              I stoped me firewall and nothing has changed. Not against it, i change listener.ora adding the lines following :
                              (SID_DESC =
                              (GLOBAL_DBNAME = DEMO)
                              (SID_NAME = DEMO)
                              )

                              this is listener.ora complet now :

                              SID_LIST_LISTENER =
                              (SID_LIST =
                              (SID_DESC =
                              (SID_NAME = PLSExtProc)
                              (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
                              (PROGRAM = extproc)
                              )
                              (SID_DESC =
                              (SID_NAME = CLRExtProc)
                              (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
                              (PROGRAM = extproc)
                              )
                                   (SID_DESC =
                                        (GLOBAL_DBNAME = DEMO)
                                        (SID_NAME = DEMO)
                                   )
                              )

                              LISTENER =
                              (DESCRIPTION_LIST =
                              (DESCRIPTION =
                              (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
                              (ADDRESS = (PROTOCOL = TCP)(HOST = NSTWS2.compteUs.local)(PORT = 1521))
                              )
                              )

                              DEFAULT_SERVICE_LISTENER = (XE)

                              Now, when i tried me connect with SID or Service Name = DEMO, the error message this is following :

                              Statut : échec -Echec du test : Listener refused the connection with the following error:
                              ORA-12518, TNS:listener could not hand off client connection

                              And before when I also tried to connect to oracle via the shortcut "Get Started With Oracle Database 11g Express Edition" to access the app "apex", the browser just showed he could not find the page and now it I displays a blank page! I do not know if this is a sign but still!
                              • 12. Re: ORA-12505, TNS:listener does not currently know of SID given in connect des
                                orafad
                                I think we've missed the simplest of explanations!

                                try:
                                sqlplus / as sysdba
                                
                                SQL> startup
                                And please move listener.ora away again -- static registration belongs to pre 8i era. A default config i.e without explicit listener.ora works well in most cases.

                                Edited by: orafad on Oct 11, 2012 11:58 AM
                                • 13. Re: ORA-12505, TNS:listener does not currently know of SID given in connect des
                                  967142
                                  The problem is that i not execute this command :


                                  C:\oraclexe\app\oracle\product\11.2.0\server\bin>sqlplus / as sysdba

                                  SQL*Plus: Release 11.2.0.2.0 Production on Jeu. Oct. 11 12:23:27 2012

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

                                  ERROR:
                                  ORA-12638: Credential retrieval failed


                                  Enter user-name: sys
                                  Enter password:
                                  ERROR:
                                  ORA-12638: Credential retrieval failed


                                  Enter user-name:
                                  • 14. Re: ORA-12505, TNS:listener does not currently know of SID given in connect des
                                    orafad
                                    Login to local machine user and re-try (looks like you are using a domain login).
                                    1 2 3 Previous Next