java.sql.SQLException: Listener refused the connection error

Running the test_db.sh script sometimes results in the java.sql.SQLException: Listener refused the connection with the following error: ORA-12505, TNS:listener does not currently know of SID given in connect descriptor error.

Symptoms

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

at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:743)
at oracle.jdbc.driver.PhysicalConnection.connect(PhysicalConnection.java:662)
at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:560)
at java.sql.DriverManager.getConnection(DriverManager.java:426)
at java.sql.DriverManager.getConnection(DriverManager.java:474)
at com.ibm.ccd.common.db.TestDB.main(TestDB.java:82)
Caused by: oracle.net.ns.NetException: Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

at oracle.net.ns.NSProtocolStream.negotiateConnection(NSProtocolStream.java:275)
at oracle.net.ns.NSProtocol.connect(NSProtocol.java:264)
at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1452)
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:496)
... 6 more
The test_db.sh script tries to create two types of connections:
  • Client database connection
  • JDBC connection
Connection type Special character that is used in the syntax after port number
Colon (:) Forward slash (/)
Client database String is considered as local service name, if the string is present in the tnsnames.oar file, the connection is successful.
jdbc:oracle:thin:@IP address:port number:mdmdb
mdmdb - String is considered as local service name
Note: In case the string is missing, the connection fails with an ORA-12154: Unknown service name error. To resolve, create a tns-entry having same value as the string.
String is considered as Oracle Database service that is present on Oracle Server and registered with listener and the connection is successful.
jdbc:oracle:thin:@IP address:port number/mdmdb_India
mdmdb_India - String is considered as Oracle Database service name
JDBC String is considered as SID, and the connection is successful.
jdbc:oracle:thin:@IP address:port number:mdmdb
mdmdb - String is considered as SID

Causes

In the db.xml file if the generated db_url has a colon : after the port number, change to forward slash / for the Oracle Database 12c Release 2 or Oracle RAC.

Resolving the problem

Proceed as follows to resolve the connection issue:
  • Browse to the db.xml file at the $TOP/etc/default folder and change the colon : to forward slash / as highlighted:
    <?xml version="1.0" encoding="UTF-8"?>
<db_config>
<db_userName>PIMDB</db_userName>
<db_password_encrypted/>
<db_password_plain>PIMDB</db_password_plain>
<db_url>jdbc:oracle:thin:@IP address:port number/PIMDB</db_url>
<db_class_name>oracle.jdbc.driver.OracleDriver</db_class_name>
<db_type>oracle</db_type>
</db_config>