Home » , , , , , , » JDBC Connection Issue with Oracle Database 11gR2 RAC SCAN listener

JDBC Connection Issue with Oracle Database 11gR2 RAC SCAN listener

Written By Srikrishna Murthy Annam on Friday, February 18, 2011 | 9:17 AM

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.

On Source RAC side:
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
On Applications Side: 
<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))))" />
$ nslookup scan-cluster
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
Share this article :

Related Articles By Category



+ comments + 73 comments

free professional kids swimming lessons...

[...]JDBC Connection Issue with Oracle Database 11gR2 RAC SCAN listener « Oracle Database 11g Blog[...]...

sven
October 16, 2011 at 5:52 PM

Sri -

I've a similar situation but we're in 10g. Can you make the video pubic? I'm converting a 2 node rac/10g/Linux/Ocfs2.

RAC is up and running and would like to test the application JDBC now.

thanks,
sve

October 26, 2011 at 1:47 AM

Can you please make this video available for me or public??

learnwithme11g
November 3, 2011 at 1:11 PM

Hi Rajiv,
You are given access to this video. You will have this access for next three days. Thanks for your interest to this article.
Thanks
SRI

November 4, 2011 at 3:21 PM

Hi SRI,

Currently I am working on a deployment scenario for one of our client. Our environment has a production setup with Database servers in RAC with SCAN IP. There is a standby database for this 2 node RAC Primary setup. Applications are running on weblogic servers. I need to make sure failover happens from RAC/SCAN ip to Standby Database server in case of failure of RAC/SCAN setup.

Is this possible? Oracle support is not giving direct answers.

Regards
James

learnwithme11g
November 15, 2011 at 2:04 PM

Hi James,
You can use the following connecting string for your case. Incase of primary scan ip failure, it will send connections to standby scan ip.
"jdbc:oracle:thin:@(DESCRIPTION = (ADDRESS_LIST = (ADDRESS =(PROTOCOL = TCP) (HOST = ) (PORT = )) (ADDRESS =(PROTOCOL = TCP) (HOST =) (PORT = )))(CONNECT_DATA = (SERVICE_NAME = )))"

Thanks
SRI

Balaji
February 4, 2012 at 6:38 AM

Hi Sri,

Can you provide me the access to the video pls

Regards,
Balaji.

learnwithme11g
February 7, 2012 at 8:37 AM

Hi balaji, you are given access to this video. --SRI

surya
February 10, 2012 at 2:42 AM

Boss.. Stuff was really Good.. i am new to 11g.. infact completely new with sql.. can u give acess to video..!!

Gopinath
February 10, 2012 at 4:43 PM

Could you please make this video available for me please. Thanks

learnwithme11g
February 14, 2012 at 6:17 AM

Hi Gopi, You are given access to this video.
Thanks
SRI

learnwithme11g
February 14, 2012 at 6:18 AM

Hi Surya, You are given access to this video.
Thanks
SRI

Gopinath
February 15, 2012 at 3:17 PM

Hi SRI,
I am still not able to access the video. Could you please give access to "JDBC Connection Issue with Oracle Database 11gR2 RAC SCAN listener". Thanks,

Gopinath

abbas.ocp@gmail.com
February 16, 2012 at 3:34 AM

hi

can you give me access to this video.

thanks


abbas.ocp@gmail.com

learnwithme11g
February 16, 2012 at 10:16 AM

Hi Abbas, you are given access to this video , pls check.

learnwithme11g
February 16, 2012 at 10:21 AM

Hi Gopi, The system is not accepting any mail id other than gmail. Do you have gmail account?

HARISH RAHMAN
February 21, 2012 at 12:40 AM

Hi,

Please give access to the video.

thank you
Harish

February 21, 2012 at 7:25 AM

learnwithme11g,

what is the reason of making video private on public blog ? ;)

learnwithme11g
February 21, 2012 at 7:45 AM

Hi Harish, you are given access to this video.

learnwithme11g
February 21, 2012 at 7:51 AM

All public videos are prepared on my test servers. All private videos are prepared by me but not on my test lab. Hope you can understand the security and confidentiality of information.
Thanks
SRI

Akhilesh
February 29, 2012 at 7:49 PM

Hi Sri,
Can you please give accesss to my id : akhileshs0203@gmai.com

learnwithme11g
March 1, 2012 at 7:26 AM

Hi Akhilesh, you are given access to the video.

H Wren
March 4, 2012 at 1:05 PM

Can I please get acces to video

Amir
March 6, 2012 at 6:24 PM

Hi Sri,

Thank you very much for sharing your knowledge and experience.

Can you please give me access to the video?

learnwithme11g
March 7, 2012 at 9:50 AM

Wren, You are given access to this video.

learnwithme11g
March 7, 2012 at 9:51 AM

Amir,You are given access to this video.

SK
March 14, 2012 at 7:41 PM

Hi Sri,

The configuration in weblogic 8 and database is 11gr2

="jdbc:oracle:thin:@(DESCRIPTION=(LOAD_BALANCE=ON)(ADDRESS=(PROTOCOL=TCP)(HOST=rac-vip1) (port=1576) (ADDRESS=(PROTOCOL=TCP)(HOST=rac-vip2) (port=1576)) (Connection....

Is always connecting to only one node and when that node fails the application fails...do you foresee any issue with the connect string

learnwithme11g
March 17, 2012 at 2:21 PM

Hi SK,
Please provide me following details :

set lines 120
set pages 200
column name format a20 tru
column value format a40 wra
select inst_id, name, value
from gv$parameter
where name in ('service_names','local_listener','remote_listener',
'db_name','db_domain','instance_name')
order by 1,2,3;

ps -ef | grep -i tns ( on each rac node)

srvctl status scan_listener -i ( For all scan listeners)

and also
your tnsnames and listener config files details.

Thanks
SRI

akakkk
March 21, 2012 at 2:33 AM

Hi,

Please note you need define 3 scan ip when you are accesing 10g client else it will not.

Gopinath
April 18, 2012 at 4:04 PM

I could not view the video even after providing my gmail_id. I really appreciate your help. Also could you provide the details on the above weblogic connection details using SCAN.

Gopinath
April 18, 2012 at 4:12 PM

Hi Sri,
I am still not able to access the video. Could you please give access to “JDBC Connection Issue with Oracle Database 11gR2 RAC SCAN listener”. Thanks,

Gopinath

Santosh
April 30, 2012 at 12:57 PM

please give me access to this video.

April 30, 2012 at 5:13 PM

Can I please have rights to see the video?

learnwithme11g
May 1, 2012 at 5:33 AM

Hi Santosh, you are given access to this video.

learnwithme11g
May 1, 2012 at 5:34 AM

Hi Sandeep, Youtube is accepting only gmails ids for access. Please let me know if you have gmail id.

learnwithme11g
May 1, 2012 at 5:38 AM

Hi Gopi, Please check now. If you are still unable to access it, i will provide you direct video link.

ibrahim
May 9, 2012 at 6:11 PM

can I have access to this video?
thanks

May 19, 2012 at 10:45 PM

Hi Sri,
Would you please give me access to this video

May 19, 2012 at 10:47 PM

Hi Sri,
Would you please give me access to this video ?

Thanks,
Abip

May 28, 2012 at 2:38 AM

Hi Sri,
can you give me access to this video. Thank you for sharing the knowledge regarding scan IP. I am facing the same kind of issue between web methods and oracle 11gR2 database.
I have created a SR with oracle but not getting any response from them.
I am getting the error intermittently :
ORA-12516, TNS:listener could not find available handler with matching protocol stack

Ananth
August 9, 2012 at 12:00 PM

Hello Sri,

Could you please gimme access to the video ananth1681@gmail.com

learnwithme11g
August 15, 2012 at 4:08 AM

hi Ananth, You are given access to this video.

Ananth
August 15, 2012 at 9:05 AM

Thanks Sri. Appreciated. If you are the moderator could you please get my email id deleted from the request message please.

Ravi
August 30, 2012 at 6:38 PM

Hi,

Please provide access on to this i am struck with an issue saying
<Received exception while creating connection for pool "PRIMARY_JTS": Listener refused the connection with the following error:
ORA-12518, TNS:listener could not hand off client connection

Raj
November 10, 2012 at 11:09 PM

Hi Sri,

Can you please share the access to this video

Himanshu
November 28, 2012 at 3:32 AM

hi, Can i get access to this video?

Srikrishna Murthy Annam
December 5, 2012 at 5:23 AM

Hi Himanshu,
You are given access to this video.

Thanks
SRI

Srikrishna Murthy Annam
December 5, 2012 at 5:24 AM

Hi Raj,
Please try to access the video. If you are not able to access it, please provide me a gmail id.

Thanks
SRI

Chris
February 6, 2013 at 1:02 AM

Hi could you provide me access to the video pls?

Ankur Mundra
March 20, 2013 at 3:27 PM

Hi I am running into the same issue and I got it resolved for the java application which was using a thin oracle driver but do have any idea for odbc driver.

Ankur Mundra
March 20, 2013 at 3:29 PM

Please update me.

Srikrishna Murthy Annam
March 23, 2013 at 3:25 PM

Hi Ankur, I haven't tried with ODBC, but have used ODBC drivers for different task. Please let me know the issue you are facing.

Thanks
SRI

sunder
April 24, 2013 at 11:12 PM

Sri we have an issue, we can connect from sqlplus but not from java. What happens is this.
If we connect via sqlplus , it is redirecting to host vip setup in the local listener and it works.
But in jboss, it is not redirecting to host vip but try to connect throush scan ip and local listener and it is failing

Srikrishna Murthy Annam
April 26, 2013 at 5:39 AM

Hi Sunder, Can you please provide me any error messages or log files or the connecting string you are using or the vip and scan details.

Chandra Mohan
May 10, 2013 at 10:34 AM

can you provide access to vedio

Srikrishna Murthy Annam
May 11, 2013 at 8:03 AM

Hi Chandra, You are given access to this video.

yatin
June 25, 2013 at 11:42 PM

hi

can you please give me access to the above video

Prasad
July 10, 2013 at 3:11 AM

Can you please give me access to this video?

Dave
July 24, 2013 at 9:17 AM

Hi .. Could I please have access to the video

Helcio
July 25, 2013 at 5:50 PM

hi

can you give me access to this video.

thanks

hdsalles@uol.com.br

chandra kumar
August 27, 2013 at 1:39 PM

could you plz give access to me.

mvsarma
November 15, 2013 at 4:51 AM

Hi dude , could you please provide access to me. Thanks in advance.

Swamy
November 16, 2013 at 6:06 PM

Does the jdbc connection to Oracle work even with TIBCO as well. Could you please let me access the video link as it is not letting me access. Thanks

Swamy
November 16, 2013 at 6:16 PM

Are there any connection issues with jdbc connection to Oracle connecting from TIBCO. Could you please let me access the video. Thanks

Srikrishna Murthy Annam
November 18, 2013 at 3:27 AM

HI Swamy, I don't know much on TIBCO configuration, but as far as i know , it is an integration tools and every integration tool must have some sort of functionality to talk to any kind of database. Please refer TIBCO documentation for further details pls.

Srikrishna Murthy Annam
November 18, 2013 at 3:28 AM

I have never come across with a TIBCO integration with Oracle Database.

Nitin Surana
November 18, 2013 at 1:08 PM

It works well with TIBCO as well. We are using Oracle Driver provided by tibco.

Srikrishna Murthy Annam
November 19, 2013 at 8:08 AM

Thanks Nitin.

sarma
November 19, 2013 at 9:21 AM

Hi Sri,

Can you please provide video access , am facing same issue when we are installation datastage with RAC DB.

Thanks in advance.

regards
Sarma

Srikrishna Murthy Annam
November 19, 2013 at 1:09 PM

Hi Sarma, I could not share the private videos. I will try to help you , if you need any help with the topic discussed here.

Bogdan
December 5, 2013 at 7:35 AM

Thank you for the article, really useful! You have a small problem in the Java code, System.currentTimeMillis already gives you milliseconds so no need to divide by 1000 to get the duration in milliseconds.

Srikrishna Murthy Annam
December 5, 2013 at 8:22 AM

Thank you.

April 14, 2017 at 11:18 PM

I really appreciate information shared above. It’s of great help. If someone want to learn Online (Virtual) instructor lead live training in Oracle 11g RAC Admin, kindly contact us http://www.maxmunus.com/contact
MaxMunus Offer World Class Virtual Instructor led training on Oracle 11g RAC Admin. We have industry expert trainer. We provide Training Material and Software Support. MaxMunus has successfully conducted 100000+ trainings in India, USA, UK, Australlia, Switzerland, Qatar, Saudi Arabia, Bangladesh, Bahrain and UAE etc.
For Demo Contact us:
Name : Arunkumar U
Email : arun@maxmunus.com
Skype id: training_maxmunus
Contact No.-+91-9738507310
Company Website –http://www.maxmunus.com



Post a Comment

Thank you for visiting our site and leaving your valuable comment.

 
Support :
Copyright © 2013. askMLabs - All Rights Reserved
Proudly powered by Blogger