2 Replies Latest reply: Nov 29, 2012 1:32 AM by 943610 RSS

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

    943610
      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-Oracle
          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
            Is that the case for your first CONNECT command?
            Yes