Tuesday, April 17, 2012

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.bhaviktest.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.bhaviktest.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.bhaviktest.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.bhaviktest.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)
)
)
)

5 comments:

  1. Wow !!! I've spent four days looking for this issue and you just gave me the solution. Thank you very much Bhavik

    ReplyDelete
  2. I have two questions. How can I make the scan know of SIDs.?

    I have an application that is failing with same error. I cannot modify the connect string. Is there any way to connect to one node (and SID)
    and continue with installation?

    regards
    PRP

    ReplyDelete
  3. Thanks Paul to contact us.

    We can surely help you out with the resolution of connectivity between application and database. If you are interested in hiring us please let us know we can surely ask our Sales team to contact you.

    Thanks,
    Ingress Team

    ReplyDelete
  4. Took me time to read all the comments, but I really enjoyed the article. It proved to be Very helpful to me and I am sure to all the commenters here! Its always nice when you can not only be informed, but also entertained! Im sure you had fun writing this article.
    Valuable information and excellent design you got here! I would like to thank you for sharing your thoughts and time into the stuff you post!!
    http://www.sqlservermasters.com/

    ReplyDelete