Oracle 11g Release 2 Real Application Cluster ERROR ORA-12505 with Java JDBC

Recently I was facing an issue with JAVA when trying to connect my Oracle 11g Release 2 RAC Database that was using SCAN Listener.

Below is the test program that I have used to check the connection between JAVA and Oracle Database.

import java.sql.*;

class ScanTest {
public static void main(String args[]) throws SQLException {
DriverManager.registerDriver(
new oracle.jdbc.driver.OracleDriver()
);
String serverName = "rac-scan.ingresstest.com";
int port = 1525;
String user = "scott";
String password = "tiger";
String SID = "orcl";
String URL = "jdbc:oracle:thin:@" + serverName + ":" + port + ":" + SID;
Connection conn = DriverManager.getConnection(URL, user, password);
String SQL = "SELECT tname, tabtype FROM tab";
Statement stat = conn.createStatement();
ResultSet rs = stat.executeQuery(SQL);
while (rs.next()) {
System.out.println(
rs.getString(1)
);
}
stat.close();
conn.close();
}
}

 It always ended up with an error given below.
 
Exception in thread "main" 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
The Connection descriptor used by the client was:
rac-scan.ingresstest.com:1525:ORCL

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:261)
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:387)
at oracle.jdbc.driver.PhysicalConnection.(PhysicalConnection.java:439)
at oracle.jdbc.driver.T4CConnection.(T4CConnection.java:165)
at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:35)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:801)
at java.sql.DriverManager.getConnection(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at ScanTest.main(ScanTest.java:15)

Solution to the above problem is because when doing an Installation of Oracle Database 11g Release 2: RAC you might have used Normal Database Configuration which is not recommended by Oracle Corporation.

Oracle recommends to use Custom when you are creating a database with DBCA utility on RAC platform Ver 11g Release 2.

:: you are accessing the server with a database SID, whereas :/ will access a service.

The SCAN Listener only know of services, and not of database SIDs.


So either you can modify your connection URL to

String serverName = "rac-scan.ingresstest.com";

int port = 1525;
String user = "scott";
String password = "tiger";
String Service_Name = "orcl";
String URL = "jdbc:oracle:thin:@" + serverName + ":" + port + "/" + Service_Name;
Connection conn = DriverManager.getConnection(URL, user, password);

or you can use it with below method if you can add the custom URL for JDBC and using SCAN Listener.

jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac-scan.ingresstest.com)(PORT=1525))(CONNECT_DATA=(SERVICE_NAME=orcl)))

if you are not using SCAN Listener then create your JDBC URL in below method.

jdbc:oracle:thin:@(DESCRIPTION=(LOAD_BALANCE=on)

(ADDRESS=(PROTOCOL=TCP)(HOST=linux-rac01) (PORT=1525))
(ADDRESS=(PROTOCOL=TCP)(HOST=linux-rac02) (PORT=1525))
(CONNECT_DATA=(SERVICE_NAME=orcl)
(FAILOVER_MODE =
(TYPE = SELECT)
(METHOD = BASIC)
(RETRIES = 180)
(DELAY = 5)
)
)
)