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

4 comments:

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

    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

How can I remove a URL on my website from the Google Index?

To remove a specific URL (for example, the page http://www.example.com/page4.html) of your own website from Google’s index, there are two ...