This discussion is archived
12 Replies Latest reply: Jan 18, 2013 1:30 PM by jgarry RSS

12505 - TNS:listener does not currently know of SID given in the descriptor

704048 Newbie
Currently Being Moderated
I can't get into my database, can't connect using tns name.

I had a direct connection using host/port/sid, but now I can't even get in that way.

too much fooling around with the tnsnames.ora and listener.ora files.

I was trying to avoid a confict with another database on our network.

How can I get back in?

Thanks -

so here's the tnsnames.ora:

FDD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = vorashankin)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = fdd)
)
)

~

and here's the listener.ora:

FDD =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = vorashankin)(PORT = 1521))
)
)


ADR_BASE_FDD = C:\Oracle


~

where I check status of lsnrctl I get this:
LSNRCTL> status
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias FDD
Version TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production
Start Date 17-JAN-2013 16:05:51
Uptime 0 days 0 hr. 1 min. 46 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File C:\Oracle\product\11.2.0\dbhome_1\network\admin\listener.ora
Listener Log File c:\oracle\diag\tnslsnr\vorashankin\fdd\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vorashankin)(PORT=1521)))
Services Summary...
Service "fdd" has 1 instance(s).
Instance "fdd", status READY, has 1 handler(s) for this service...
Service "fddXDB" has 1 instance(s).
Instance "fdd", status READY, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL>

LSNRCTL> services
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
Services Summary...
Service "fdd" has 1 instance(s).
Instance "fdd", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "fddXDB" has 1 instance(s).
Instance "fdd", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: VORASHANKIN, pid: 3036>
(ADDRESS=(PROTOCOL=tcp)(HOST=vorashankin)(PORT=49728))
The command completed successfully
LSNRCTL> services
  • 1. Re: 12505 - TNS:listener does not currently know of SID given in the descriptor
    sb92075 Guru
    Currently Being Moderated
    [oracle@localhost ~]$ oerr tns 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.
    post excerpt from Listener Log File c:\oracle\diag\tnslsnr\vorashankin\fdd\alert\log.xml that shows the 12505 error
  • 2. Re: 12505 - TNS:listener does not currently know of SID given in the descriptor
    EdStevens Guru
    Currently Being Moderated
    robshankin wrote:
    I can't get into my database, can't connect using tns name.

    I had a direct connection using host/port/sid, but now I can't even get in that way.

    too much fooling around with the tnsnames.ora and listener.ora files.

    I was trying to avoid a confict with another database on our network.

    How can I get back in?

    Thanks -

    so here's the tnsnames.ora:

    FDD =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = vorashankin)(PORT = 1521))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = fdd)
    )
    )

    ~

    and here's the listener.ora:

    FDD =
    (DESCRIPTION_LIST =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = vorashankin)(PORT = 1521))
    )
    )


    ADR_BASE_FDD = C:\Oracle


    ~

    where I check status of lsnrctl I get this:
    LSNRCTL> status
    Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
    STATUS of the LISTENER
    ------------------------
    Alias FDD
    Version TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production
    Start Date 17-JAN-2013 16:05:51
    Uptime 0 days 0 hr. 1 min. 46 sec
    Trace Level off
    Security ON: Local OS Authentication
    SNMP OFF
    Listener Parameter File C:\Oracle\product\11.2.0\dbhome_1\network\admin\listener.ora
    Listener Log File c:\oracle\diag\tnslsnr\vorashankin\fdd\alert\log.xml
    Listening Endpoints Summary...
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vorashankin)(PORT=1521)))
    Services Summary...
    Service "fdd" has 1 instance(s).
    Instance "fdd", status READY, has 1 handler(s) for this service...
    Service "fddXDB" has 1 instance(s).
    Instance "fdd", status READY, has 1 handler(s) for this service...
    The command completed successfully
    LSNRCTL>

    LSNRCTL> services
    Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
    Services Summary...
    Service "fdd" has 1 instance(s).
    Instance "fdd", status READY, has 1 handler(s) for this service...
    Handler(s):
    "DEDICATED" established:0 refused:0 state:ready
    LOCAL SERVER
    Service "fddXDB" has 1 instance(s).
    Instance "fdd", status READY, has 1 handler(s) for this service...
    Handler(s):
    "D000" established:0 refused:0 current:0 max:1022 state:ready
    DISPATCHER <machine: VORASHANKIN, pid: 3036>
    (ADDRESS=(PROTOCOL=tcp)(HOST=vorashankin)(PORT=49728))
    The command completed successfully
    LSNRCTL> services
    Offhand, I don't see anything out of synch with your config files. But of course, we don't know what you actually did when you tried to connect. Please reproduce the error at a command line, then copy the entire session - full commands and full responses -- and paste back here.
  • 3. Re: 12505 - TNS:listener does not currently know of SID given in the descriptor
    TSharma-Oracle Guru
    Currently Being Moderated
    Can you please upload your server side sqlnet.ora file? That file could be the culprit.
  • 4. Re: 12505 - TNS:listener does not currently know of SID given in the descriptor
    TSharma-Oracle Guru
    Currently Being Moderated
    Add this under you listener file with all the right information and reload your listener. This should work

    SID_LIST_<your Listenername> =
    (SID_LIST=
    (SID_DESC=
    (ORACLE_HOME=C:\Oracle\Oracle10g)
         (SID_NAME=FDD)
    )
    )
  • 5. Re: 12505 - TNS:listener does not currently know of SID given in the descriptor
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    Please Use Search mechanism in this Forum , you can find similar threads
  • 6. Re: 12505 - TNS:listener does not currently know of SID given in the descriptor
    704048 Newbie
    Currently Being Moderated
    >
    post excerpt from Listener Log File c:\oracle\diag\tnslsnr\vorashankin\fdd\alert\log.xml that shows the 12505 error
    here you go.
    see anything good?
         <msg time='2013-01-17T16:00:59.577-05:00' org_id='oracle' comp_id='tnslsnr'
         type='UNKNOWN' level='16' host_id='VORASHANKIN'
         host_addr='fe80::49ef:9c30:b586:78f8%10'>
         <txt>Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vorashankin.ddns.feith.com)(PORT=1521)))
         </txt>
         </msg>
         <msg time='2013-01-17T16:01:01.233-05:00' org_id='oracle' comp_id='tnslsnr'
         type='UNKNOWN' level='16' host_id='VORASHANKIN'
         host_addr='fe80::49ef:9c30:b586:78f8%10'>
         <txt>Listener completed notification to CRS on start
         </txt>
         </msg>
         <msg time='2013-01-17T16:01:01.233-05:00' org_id='oracle' comp_id='tnslsnr'
         type='UNKNOWN' level='16' host_id='VORASHANKIN'
         host_addr='fe80::49ef:9c30:b586:78f8%10'>
         <txt>
         TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE
         </txt>
         </msg>
         <msg time='2013-01-17T16:01:01.405-05:00' org_id='oracle' comp_id='tnslsnr'
         type='UNKNOWN' level='16' host_id='VORASHANKIN'
         host_addr='fe80::49ef:9c30:b586:78f8%10'>
         <txt>17-JAN-2013 16:01:01 * (CONNECT_DATA=(SID=fdd)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=ROBSHANKIN))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.251.93.239)(PORT=18228)) * establish * fdd * 12505
         </txt>
         </msg>
         <msg time='2013-01-17T16:01:01.405-05:00' org_id='oracle' comp_id='tnslsnr'
         type='UNKNOWN' level='16' host_id='VORASHANKIN'
         host_addr='fe80::49ef:9c30:b586:78f8%10'>
         <txt>TNS-12505: TNS:listener does not currently know of SID given in connect descriptor</txt>
  • 7. Re: 12505 - TNS:listener does not currently know of SID given in the descriptor
    704048 Newbie
    Currently Being Moderated
    TSharma wrote:
    Can you please upload your server side sqlnet.ora file? That file could be the culprit.
    here's the content of the file:
    # sqlnet.ora Network Configuration File: C:\Oracle\product\11.2.0\dbhome_1\network\admin\sqlnet.ora
    # Generated by Oracle configuration tools.

    # This file is actually generated by netca. But if customers choose to
    # install "Software Only", this file wont exist and without the native
    # authentication, they will not be able to connect to the database on NT.

    SQLNET.AUTHENTICATION_SERVICES= (NTS)

    NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
    what are we looking for?
  • 8. Re: 12505 - TNS:listener does not currently know of SID given in the descriptor
    sb92075 Guru
    Currently Being Moderated
    robshankin wrote:
         host_addr='fe80::49ef:9c30:b586:78f8%10'>
         <txt>17-JAN-2013 16:01:01 * (CONNECT_DATA=(SID=fdd)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=ROBSHANKIN))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.251.93.239)(PORT=18228)) * establish * fdd * 12505
         </txt>
    connect string needs to change from "SID=fdd" to "SERVICE=fdd"
  • 9. Re: 12505 - TNS:listener does not currently know of SID given in the descriptor
    704048 Newbie
    Currently Being Moderated
    sb92075 wrote:
    robshankin wrote:
         host_addr='fe80::49ef:9c30:b586:78f8%10'>
         <txt>17-JAN-2013 16:01:01 * (CONNECT_DATA=(SID=fdd)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=ROBSHANKIN))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.251.93.239)(PORT=18228)) * establish * fdd * 12505
         </txt>
    connect string needs to change from "SID=fdd" to "SERVICE=fdd"
    I'm not sure how but I got back in.

    on a remote machine I set the tnsnames.ora with

    vOraShankin =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = vOraShankin)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SID = fdd)
    (SERVER = DEDICATED)
    )
    )

    and that put me in business, mostly.
    I'm able to to use client software to connect to the database from a remote machine, but not from the local machine.

    tnsping on local seems ok, but I'm getting a 12504 when I try to connect with software.
    C:\Users\Administrator>tnsping vorashankin

    TNS Ping Utility for 32-bit Windows: Version 11.1.0.7.0 - Production on 18-JAN-2013 12:15:19

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

    Used parameter files:
    C:\app\Administrator\product\11.1.0\client_1\network\admin\sqlnet.ora

    Used EZCONNECT adapter to resolve the alias
    Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.15.149)(PORT=1521)))
    OK (10 msec)
    I'm not sure why its using EZCONNECT instead of TNSNAMES. I even removed EZCONNECT from sqlnet.ora.

    any ideas?
  • 10. Re: 12505 - TNS:listener does not currently know of SID given in the descriptor
    jgarry Guru
    Currently Being Moderated
    Connecting local doesn't need the listener. You need to cut and paste to show us what you typed in and what came back, as well as show us the environment variables with TNS or ORA in them.
  • 11. Re: 12505 - TNS:listener does not currently know of SID given in the descriptor
    704048 Newbie
    Currently Being Moderated
    jgarry wrote:
    Connecting local doesn't need the listener. You need to cut and paste to show us what you typed in and what came back, as well as show us the environment variables with TNS or ORA in them.
    SQL*Plus: Release 11.1.0.7.0 - Production on Fri Jan 18 14:06:36 2013

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

    Enter user-name: fdd
    Enter password:
    ERROR:
    ORA-12560: TNS:protocol adapter error
    as for environment variables - this is what is in the Path:
    C:\app\Administrator\product\11.1.0\client_1\bin;C:\Oracle\product\11.2.0\dbhome_1\bin;%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem;%SYSTEMROOT%\System32\WindowsPowerShell\v1.0\;C:\Windows\pixtran
  • 12. Re: 12505 - TNS:listener does not currently know of SID given in the descriptor
    jgarry Guru
    Currently Being Moderated
    Look for that error at http://edstevensdba.wordpress.com/2011/02/09/sqlnet_overview/

    Since you are on windows, there are several places that are examined for configuration files, including the local environment (what you see when you do a set command in a command window), the system environment (under my computer manage or some such thing), and the registry. Since you have several installs, you may have multiple settings in the registry, which could be very confusing. Some environment variables tell sqlplus to go through sqlnet even though you don't have an @ in your connect string. There are probably some other considerations I'm not even thinking of.

    So when you do the tnsping, it tells you what tnsnames.ora it is using - that confusing ezconnect is another clue. It seems to be using the tnsnames.ora in the first oracle home it finds in your path.

    Ed is a lot better at explaining how to figure all this out, so check the website above and follow it's examples. I suspect one way to get the local connection to work is to make a batch file with everything defined simply for that session (oracle_home, oracle_sid, path, tns_admin) and then try to connect. There is perhaps a better way for normal usage. I can't remember if the current versions have an oracle home selector that figures all that out for you, since I do little on windows.

Legend

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