Oracle Technologies Blog

By ASKM

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

77 Responses to “JDBC Connection Issue with Oracle Database 11gR2 RAC SCAN listener”

  1. free professional kids swimming lessons…

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

  2. sven said

    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

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

  4. learnwithme11g said

    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

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

  6. learnwithme11g said

    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

  7. Balaji said

    Hi Sri,

    Can you provide me the access to the video pls

    Regards,
    Balaji.

  8. learnwithme11g said

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

  9. surya said

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

  10. Gopinath said

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

  11. learnwithme11g said

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

  12. learnwithme11g said

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

  13. Gopinath said

    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

  14. abbas.ocp@gmail.com said

    hi

    can you give me access to this video.

    thanks

    abbas.ocp@gmail.com

  15. learnwithme11g said

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

  16. learnwithme11g said

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

  17. HARISH RAHMAN said

    Hi,

    Please give access to the video.

    thank you
    Harish

  18. odenysenko said

    learnwithme11g,

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

  19. learnwithme11g said

    Hi Harish, you are given access to this video.

  20. learnwithme11g said

    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

  21. Akhilesh said

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

  22. learnwithme11g said

    Hi Akhilesh, you are given access to the video.

  23. H Wren said

    Can I please get acces to video

  24. Amir said

    Hi Sri,

    Thank you very much for sharing your knowledge and experience.

    Can you please give me access to the video?

  25. learnwithme11g said

    Wren, You are given access to this video.

  26. learnwithme11g said

    Amir,You are given access to this video.

  27. SK said

    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

  28. learnwithme11g said

    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

  29. akakkk said

    Hi,

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

  30. Gopinath said

    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.

  31. Gopinath said

    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

  32. Santosh said

    please give me access to this video.

  33. Can I please have rights to see the video?

  34. learnwithme11g said

    Hi Santosh, you are given access to this video.

  35. learnwithme11g said

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

  36. learnwithme11g said

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

  37. ibrahim said

    can I have access to this video?
    thanks

  38. abip0279 said

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

  39. abip0279 said

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

    Thanks,
    Abip

  40. Asish Paul said

    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

  41. Ananth said

    Hello Sri,

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

  42. learnwithme11g said

    hi Ananth, You are given access to this video.

  43. Ananth said

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

  44. Ravi said

    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

  45. Raj said

    Hi Sri,

    Can you please share the access to this video

  46. Himanshu said

    hi, Can i get access to this video?

  47. Srikrishna Murthy Annam said

    Hi Himanshu,
    You are given access to this video.

    Thanks
    SRI

  48. Srikrishna Murthy Annam said

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

    Thanks
    SRI

  49. Chris said

    Hi could you provide me access to the video pls?

  50. Ankur Mundra said

    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.

  51. Ankur Mundra said

    Please update me.

  52. Srikrishna Murthy Annam said

    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

  53. sunder said

    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

  54. Srikrishna Murthy Annam said

    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.

  55. Chandra Mohan said

    can you provide access to vedio

  56. Srikrishna Murthy Annam said

    Hi Chandra, You are given access to this video.

  57. yatin said

    hi

    can you please give me access to the above video

  58. Prasad said

    Can you please give me access to this video?

  59. Dave said

    Hi .. Could I please have access to the video

  60. Helcio said

    hi

    can you give me access to this video.

    thanks

    hdsalles@uol.com.br

  61. chandra kumar said

    could you plz give access to me.

  62. mvsarma said

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

  63. Swamy said

    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

  64. Swamy said

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

  65. Srikrishna Murthy Annam said

    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.

  66. Srikrishna Murthy Annam said

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

  67. Nitin Surana said

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

  68. Srikrishna Murthy Annam said

    Thanks Nitin.

  69. sarma said

    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

  70. Srikrishna Murthy Annam said

    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.

  71. Bogdan said

    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.

  72. Srikrishna Murthy Annam said

    Thank you.

  73. Pan said

    Hello,

    I also have similar case with one of client wherein they are able to connect with database (11.2.0.4.0) using VIP address but it doesn’t work with SCAN. Both app and database are in same domain. The DB connection was successful which was made from application back end. We get error when they make connection with application (jar file).

    Please assist on the point that i need to verify to make them use SCAN.

    Second, what could be issue for not working with SCAN wherein work fine with VIP.

  74. Srikrishna Murthy Annam said

    Hi Pan,
    Please make sure your local_listener and remote_listener parameters are set properly. Your remote_listener value should be set to have scan name.

  75. Srikrishna Murthy Annam said

    Hi Pan, if you have MOS credentials, please look into this note id : 975457.1. This may help you.

  76. Sridhar Majety said

    Could you give access to the video please.

    Sridhar

  77. Anil said

    Hello,

    Can you please give me the access to video?

    Regards,
    Anil

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: