How to fix "TNS:listener does not currently know of SID given in connect descriptor" with DBVisualizer on Linux?

Question

I am trying to connect with Oracle database from a server having Oracle client installed (RHEL Linux Environment). I installed DB Visualizer on client machine. But getting listner error on hitting connect button.

I have tried TNSPING and connecting through SQL Plus and both are fine. What's the possible cause of the error in DBViz? How can I fix it?

DB Connectivity Detail:

TNS Ping

[root@bvqa-emmcas01 bin]# ./tnsping CMSQA
    Used TNSNAMES adapter to resolve the alias
    Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.160.189.78)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME=CMSQA)))
    OK (10 msec)

SQL Plus Connectivity

[root@bvqa-emmcas01 bin]# ./sqlplus temcsys/<password>@CMSQA
    SQL*Plus: Release 11.2.0.3.0 Production on Tue Mar 24 00:08:35 2015
    Release 11.2.0.3.0 Production on Tue Mar 24 00:08:35 2015
    Copyright (c) 1982, 2011, Oracle.  All rights reserved.
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

tnsnames.ora

CMSQA=
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.160.189.78)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME=CMSQA)
    )
  )

Error in DB Visualzier

Long Message:
Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

Answer 1

TNS Ping does not care about service or SID, all it checks is the availability of the listener, so this does not mean your connection string is correct. Connecting through SQL*Plus successfully however confirms it.

Your tnsnames.ora uses SERVICE_NAME in CONNECT_DATA, but your JDBC URL uses the SID syntax. SID and service name are different concepts, and their value can be different. In your case, you can connect with CMSQA provided as service name, but not as SID.

In this syntax, CMSQA means SID:

jdbc:oracle:thin:@10.160.189.78:1521:CMSQA

To use it as a service name, your connection string should be:

jdbc:oracle:thin:@//10.160.189.78:1521/CMSQA