This discussion is archived
2 Replies Latest reply: Nov 28, 2012 11:32 PM by 943610 RSS

bug? CONNECT scott/tiger@tns: TNS:listener does not currently know of SID

943610 Newbie
Currently Being Moderated
in tnsnames.ora I have 2 entries:
node1 =
    (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = eiger.reksoft.ru)(PORT = 1522))
        (CONNECT_DATA =
            (SERVICE_NAME = PPB)
        )
    )

node2 =
    (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan.localdomain)(PORT = 1521))
        (CONNECT_DATA =
            (SERVICE_NAME = RAC.localdomain)
        )
    )
I can use both in the CONNECT command in sqlplus.
But in sqldeveloper only node1 works:
 CONNECT scott/tiger@node1
Connected
...

 CONNECT scott/tiger@node2
Error starting at line 2 in command:
CONNECT scott/tiger@node2
Error report:
Connection Failed
Commit
Not very informative, so I attached a java debugger and set breakpoints on SQLException constructors.
There's a hit in public SQLException(String reason, String SQLState, int vendorCode)
The argumenst are:
"Listener refused the connection with the following error:\nORA-12505, TNS:listener does not currently know of SID given in connect descriptor\n "
66000
12505
I think sqldeveloper is trying to connect using SID instead of SERVICE_NAME.

At the same sqldeveloper successfully connects to this tns name configured in the connection properties dialog.

The program version is 3.2.20.09, I downloaded it today.

Edited by: 940607 on 23.11.2012 4:26
  • 1. Re: bug? CONNECT scott/tiger@tns: TNS:listener does not currently know of SID
    Gary Graham Expert
    Currently Being Moderated
    Hi,

    I believe you have found a bug. Thanks for reporting it!

    Possible explanation A ...
    1. You have duplicate entries in one tnsname.ora and SQL Developer is picking the wrong one (one (bad) SID, one (good) Service_Name).
    2. You have multiple tnsnames.ora file (back-up version or whatever. SQL Developer used to read them all, and still may -- not sure).

    Possible explanation B (my favorite) ...
    1. Since you are referencing a TNS alias, the code first constructs the connection URL as starting with "jdbc:oracle:oci8:@"
    2. This connection URL is constructed correctly to work for either SID or Service_Name.
    3. If you don't actually have any Oracle client software (with OCI) installed, the connection attempt fails.

    ...I suppose there could be reasons other than missing client software for the connection failure, but regardless...

    4. Any failure is trapped, and another connection attempt is made using "jdbc:oracle:thin:@"
    5. This time, however, the URL construction is flawed. It always uses the format required for SID, even if Service_Name is intended.
    6. Some older JDBC driver versions (not sure which) only supported the SID format, but now both formats are supported.
    7. Of course, if the SID and Service_Name happen to be identical, it works anyway. Is that the case for your first CONNECT command?

    I logged Bug 15931657 - FORUM - CONNECT COMMAND URL, UPON OCI FAIL-OVER TO THIN, ALWAYS USES SID FORMAT

    Thanks,
    Gary
    SQL Developer Team
  • 2. Re: bug? CONNECT scott/tiger@tns: TNS:listener does not currently know of SID
    943610 Newbie
    Currently Being Moderated
    Is that the case for your first CONNECT command?
    Yes

Legend

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