Oracle Technologies Blog

By ASKM

Posts Tagged ‘oracle’

JDBC Connection Issue with Oracle Database 11gR2 RAC SCAN listener

Posted by Srikrishna Murthy Annam on February 18, 2011

Recently we resolved a  JDBC connection issue with RAC database.  I will detail the issue in 8 steps

My Env setup :
We have 11gR2 RAC database running using ASM storage and all the servers are in network domain domain1. Client is using one JAVA application and it is running on non-RAC 11g database and the servers are in a different network domain domain2.
Our task :
Our task is to migrate the database that the JAVA applications is using to the RAC setup on ASM storage and establish the JAVA applications to connect to the newly migrated database. The big challenge we faced here is due to the reason that the setups are in different domains. We are using the 11gR2 SCAN concept,which complicated the issue even more.
What we did :
I am giving here the outlined view of the migration plan, but we should consider many other things in the actual migration process.

  1. export database from source
  2. Move the dump files to target database node.
  3. prepare the target database for import
  4. import the database to target database
  5. run utlrp to compile all the objects
  6. Verify the errors from import logfile
  7. “Note invalids,objects and schema status”
  8. Compare the objects status with the source
  9. verify the db links on target

All went fine. We tested the database connection from the server where java applications are running. We tested TOAD connection as well. All is well. We requested client to test the application after providing the JDBC connection string.

What is the issue :
Client raised an issue that the application is not able to connect to the database.

Troubleshooting:
As i said earlier, we tested TOAD connection. We tested sqlplus connection to the database. Both are working fine with the provided connecting string. Then we tried to dig into the SCAN listener concepts suspecting it might be an issue and configured everything as per the oracle documents. Finally we concluded that it is the issue only with JDBC connections.

Issue resolution :
Then prepared a java script to test the database java connections. Modified various settings on the database side and tested JDBC connection. Atlast we found the issue. The issue is with the connection string domain name specification.

How to test a Java Connection to oracle:
Pls see the video demo

Explanation:
As i said in my first sentence that the source and target environments are working in different domains, being source using domain2 and target using domain1. SCAN in domain1(RAC side) resolves to three IPs and the same is configured in the other network using NATing. Now the scan name resolves to three IPs in both source and target and they are connected with NATing. The host name in the connectiong string be the same as the init.ora parameter remote_listener and it should also match to the SCAN name. We should not include any domain names with SCAN name , remote_listername and with HOST setting in connecting string.

<strong>On Source RAC side:</strong>
The HOST=scan-cluster should exactly match the remote_listener parameter in the database.

SQL> show parameter remote_list

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_listener                      string      scan-cluster:1521

<strong>On Applications Side: </strong>
<database name="defaultOracle"
debug="false"
password="xxxxxxxxxxxxxxxx"
connectString="jdbc:oracle:thin:@(DESCRIPTION=(LOAD_BALANCE=on)(ADDRESS=(PROTOCOL=TCP)(HOST=scan-cluster) (PORT=1521))(CONNECT_DATA=(SERVICE_NAME=racpoc.domain1.com)(FAILOVER_MODE =(TYPE = SELECT)(METHOD = BASIC)(RETRIES = 180)(DELAY = 5))))" />

<strong>$ nslookup scan-cluster</strong>
Server:         128.191.2.13
Address:        128.191.2.13#53

Name:   scan-cluster.domain2.com
Address: 128.191.224.227
Name:   scan-cluster.domain2.com
Address: 128.191.224.29
Name:   scan-cluster.domain2.com
Address: 128.191.224.30

The Java Script to test Oracle Connection is :

import java.sql.*;
public class testconn {
public static void main(String[] s)throws Exception {
Class.forName("oracle.jdbc.OracleDriver");

String url="jdbc:oracle:thin:@(DESCRIPTION= (LOAD_BALANCE=on) (ADDRESS=(PROTOCOL=TCP)(HOST=scan-cluster)(PORT=1521)) (CONNECT_DATA=(SERVICE_NAME=racpoc.domain)))";

for (int i=0; i<20; i++) {
try {
long x= System.currentTimeMillis () ;
Connection conn = DriverManager.getConnection(url,"askm","askm");
long y= System.currentTimeMillis ();

System.out.println("Connection Succesful "+conn);
System.out.println("Connection time is "+(y-x)/1000+" ms");
Statement stmt =conn.createStatement();
ResultSet res= stmt.executeQuery(" select host_name from v$instance");

while(res.next()) {
System.out.println(res.getString(1));
}
stmt.close();
conn.close();
}
catch(Exception e) {
e.printStackTrace();}}}}

Hope it helps

SRI

Advertisements

Posted in 11gR2 RAC, Troubleshooting | Tagged: , , , , , , , | 77 Comments »