This discussion is archived
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 Newbie
Currently Being Moderated
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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    >
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    Login to local machine user and re-try (looks like you are using a domain login).
1 2 3 Previous Next

Legend

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