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.
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.
The Java Script to test Oracle Connection is :
Hope it helps
SRI
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.
- export database from source
- Move the dump files to target database node.
- prepare the target database for import
- import the database to target database
- run utlrp to compile all the objects
- Verify the errors from import logfile
- "Note invalids,objects and schema status"
- Compare the objects status with the source
- verify the db links on target
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
Posts
+ comments + 73 comments
free professional kids swimming lessons...
[...]JDBC Connection Issue with Oracle Database 11gR2 RAC SCAN listener « Oracle Database 11g Blog[...]...
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
Can you please make this video available for me or public??
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
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
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
Hi Sri,
Can you provide me the access to the video pls
Regards,
Balaji.
Hi balaji, you are given access to this video. --SRI
Boss.. Stuff was really Good.. i am new to 11g.. infact completely new with sql.. can u give acess to video..!!
Could you please make this video available for me please. Thanks
Hi Gopi, You are given access to this video.
Thanks
SRI
Hi Surya, You are given access to this video.
Thanks
SRI
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
hi
can you give me access to this video.
thanks
abbas.ocp@gmail.com
Hi Abbas, you are given access to this video , pls check.
Hi Gopi, The system is not accepting any mail id other than gmail. Do you have gmail account?
Hi,
Please give access to the video.
thank you
Harish
learnwithme11g,
what is the reason of making video private on public blog ? ;)
Hi Harish, you are given access to this video.
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
Hi Sri,
Can you please give accesss to my id : akhileshs0203@gmai.com
Hi Akhilesh, you are given access to the video.
Can I please get acces to video
Hi Sri,
Thank you very much for sharing your knowledge and experience.
Can you please give me access to the video?
Wren, You are given access to this video.
Amir,You are given access to this video.
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
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
Hi,
Please note you need define 3 scan ip when you are accesing 10g client else it will not.
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.
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
please give me access to this video.
Can I please have rights to see the video?
Hi Santosh, you are given access to this video.
Hi Sandeep, Youtube is accepting only gmails ids for access. Please let me know if you have gmail id.
Hi Gopi, Please check now. If you are still unable to access it, i will provide you direct video link.
can I have access to this video?
thanks
Hi Sri,
Would you please give me access to this video
Hi Sri,
Would you please give me access to this video ?
Thanks,
Abip
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
Hello Sri,
Could you please gimme access to the video ananth1681@gmail.com
hi Ananth, You are given access to this video.
Thanks Sri. Appreciated. If you are the moderator could you please get my email id deleted from the request message please.
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
Hi Sri,
Can you please share the access to this video
hi, Can i get access to this video?
Hi Himanshu,
You are given access to this video.
Thanks
SRI
Hi Raj,
Please try to access the video. If you are not able to access it, please provide me a gmail id.
Thanks
SRI
Hi could you provide me access to the video pls?
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.
Please update me.
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
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
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.
can you provide access to vedio
Hi Chandra, You are given access to this video.
hi
can you please give me access to the above video
Can you please give me access to this video?
Hi .. Could I please have access to the video
hi
can you give me access to this video.
thanks
hdsalles@uol.com.br
could you plz give access to me.
Hi dude , could you please provide access to me. Thanks in advance.
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
Are there any connection issues with jdbc connection to Oracle connecting from TIBCO. Could you please let me access the video. Thanks
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.
I have never come across with a TIBCO integration with Oracle Database.
It works well with TIBCO as well. We are using Oracle Driver provided by tibco.
Thanks Nitin.
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
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.
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.
Thank you.
Don’t follow your role model. Be the Role model person for others. But it's so simple by getting Hadoop training in Chennai. Because it is an assurance course to bounce back from a double salary. For joining call 7502633633.
Post a Comment
Thank you for visiting our site and leaving your valuable comment.