• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

fail over in jdc URL

 
Ranch Hand
Posts: 99
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Hi,

My application uses below jdbc url for database connectivity. Today one of the db node was down for one hour and FAIL OVER did not happen. Please can anyone tell what is the reason behind this ?

<connection-url>
jdbc:oracle:thin:@(DESCRIPTION=(LOAD_BALANCE=ON)(FAILOVER=ON)(ADDRESS=(PROTOCOL=TCP)(HOST=host1)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=host2)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ser_name)))
</connection-url>

Regards,
Jaya


 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
For Transparent Application Failover to happen, you need the OCI (type 2) driver: see the documentation. You have a thin driver. I haven't ever used TAF, perhaps there are other requirements to be met - see the docs.

If you're not striving for TAF, you might use Connect Time Failover (your connect string does look like that - see the docs). When you use a connection pool, the connections to the failed node will get eventually evicted from the pool and new connections should go to the second node. So, after a few failed requests, the application should continue to work. Is this what you're trying to achieve?
 
jaya kemmannu
Ranch Hand
Posts: 99
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Hi Martin,

Your point " once the one of the node go down,eventually new connection should go to second node and after few failed transaction application should continue to work normally" is what i am looking for. But in yesterdays scenario it did not happen at all.


Regards,
Jaya
 
jaya kemmannu
Ranch Hand
Posts: 99
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Even with thin drivers TAF happened whenever there is one of db node was down. Only difference is my URL has LOAD_BALANCE = OFF instead of ON.
 
Martin Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I don't have much experience troubleshooting connection strings. One of our clients uses a similar setup and as far as I know, it "just works".

Is your connection pool set up to verify connections before they're handed out?

Can you reconstruct from the log what was happening in the application, ie. did the connection pool try to obtain new connection and failed (and if it did, with which error)?
 
Martin Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

jaya kemmannu wrote:
Even with thin drivers TAF happened whenever there is one of db node was down. Only difference is my URL has LOAD_BALANCE = OFF instead of ON.


I would be surprised: TAF means that the connection is seamlessly redirected to the other instance. The active transaction fails, but your application can continue to use the connection, it doesn't need to get a new connection. And, if I'm not mistaken, TAF is activated by the FAILOVER_MODE parameter, not FAILOVER.

But I may be wrong.
 
jaya kemmannu
Ranch Hand
Posts: 99
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi,

My connection pool setting is as follow




Regards,
Jaya
 
jaya kemmannu
Ranch Hand
Posts: 99
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Every Time i try to connect d i used to get below Exceptions:

2012-12-17 15:49:57,975 WARN [org.jboss.resource.connectionmanager.JBossManagedConnectionPool] Throwable while attempting to get a new connection: null
org.jboss.resource.JBossResourceException: Could not create connection; - nested throwable: (java.sql.SQLException: Io exception: The Network Adapter could not establish the connection)
at org.jboss.resource.adapter.jdbc.local.LocalManagedConnectionFactory.createManagedConnection(LocalManagedConnectionFactory.java:179)
at org.jboss.resource.connectionmanager.InternalManagedConnectionPool.createConnectionEventListener(InternalManagedConnectionPool.java:577)
at org.jboss.resource.connectionmanager.InternalManagedConnectionPool.getConnection(InternalManagedConnectionPool.java:262)
at org.jboss.resource.connectionmanager.JBossManagedConnectionPool$BasePool.getConnection(JBossManagedConnectionPool.java:500)
at org.jboss.resource.connectionmanager.BaseConnectionManager2.getManagedConnection(BaseConnectionManager2.java:341)
at org.jboss.resource.connectionmanager.TxConnectionManager.getManagedConnection(TxConnectionManager.java:315)
at org.jboss.resource.connectionmanager.BaseConnectionManager2.allocateConnection(BaseConnectionManager2.java:396)
at org.jboss.resource.connectionmanager.BaseConnectionManager2$ConnectionManagerProxy.allocateConnection(BaseConnectionManager2.java:842)
at org.jboss.resource.adapter.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:88)

2012-12-17 15:04:04,218 WARN [org.jboss.resource.connectionmanager.JBossManagedConnectionPool] Throwable while attempting to get a new connection: null
org.jboss.resource.JBossResourceException: Could not create connection; - nested throwable: (java.sql.SQLException: Io exception: Connection reset)
at org.jboss.resource.adapter.jdbc.local.LocalManagedConnectionFactory.createManagedConnection(LocalManagedConnectionFactory.java:179)
at org.jboss.resource.connectionmanager.InternalManagedConnectionPool.createConnectionEventListener(InternalManagedConnectionPool.java:577)
at org.jboss.resource.connectionmanager.InternalManagedConnectionPool.getConnection(InternalManagedConnectionPool.java:262)
at org.jboss.resource.connectionmanager.JBossManagedConnectionPool$BasePool.getConnection(JBossManagedConnectionPool.java:500)
at org.jboss.resource.connectionmanager.BaseConnectionManager2.getManagedConnection(BaseConnectionManager2.java:341)
at org.jboss.resource.connectionmanager.TxConnectionManager.getManagedConnection(TxConnectionManager.java:315)
at org.jboss.resource.connectionmanager.BaseConnectionManager2.allocateConnection(BaseConnectionManager2.java:396)
at org.jboss.resource.connectionmanager.BaseConnectionManager2$ConnectionManagerProxy.allocateConnection(BaseConnectionManager2.java:842)
at org.jboss.resource.adapter.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:88)

Regards,
Jaya
 
Martin Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Just a wild though: have you verified that the application can connect to both instances, using eg. TNSPING?

If something (say, a firewall) prevented you from connecting to both instances, you wouldn't necessarily notice that until the other node (the one that handled all DB operations) failed. Of course that would mean your application wasn't load balanced at all, but if there are other applications on the DB, it might not be immediately apparent.
 
jaya kemmannu
Ranch Hand
Posts: 99
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

I have tried to do TNSPING when one of the node was down.

MNPGDBLB =
(DESCRIPTION =
(LOAD_BALANCE = yes)
(ADDRESS = (PROTOCOL = TCP)(HOST = ip-1)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = ip-2)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = sname)
(FAILOVER_MODE =
(TYPE = SELECT)
(METHOD = BASIC)
(RETRIES = 10)
(DELAY = 5)
)
)
)


$ tnsping MNPGDBLB

TNS Ping Utility for Linux: Version 10.2.0.4.0 - Production on 17-DEC-2012 16:11:02

Copyright (c) 1997, 2007, Oracle. All rights reserved.

Used parameter files:
/home/oracle/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (LOAD_BALANCE = yes) (ADDRESS = (PROTOCOL = TCP)(HOST = ip-1)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = ip-2)(PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = sname) (FAILOVER_MODE = (TYPE = SELECT) (METHOD = BASIC) (RETRIES = 10) (DELAY = 5))))
OK (0 msec)

One more strange thing what i observed is, one of the app server uses below jdbc url with LB=OFF :


<connection-url>
jdbc:oracle:thin:@(DESCRIPTION=(LOAD_BALANCE=off)(FAILOVER=ON)(ADDRESS=(PROTOCOL=TCP)(HOST=ip02-vip)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=ip01-vip)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=sname)))
</connection-url>

As per the above URL my app will connect only to virtual ip "ip02-vip" if it up and running. yesterday ip02-vip was up and running but my application log says it is not able to connect to db for some transaction (Not all the times, In between some transaction was succesful too). . This means definitely some load balancing is happening at Oracle side too. Do you think this might have caused my app not to go in FAIL OVER MODE.

Regards,
Jaya

 
Martin Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
We're already far beyond JDBC, so I've moved the thread to our Oracle forum. Hopefully some Oracle folks will show up with more help.

Please note that the connect string from your last post (the MNPGDBLB) differs from the one in your original question. Granted, that should not matter, but if you want to pursue the problem, you should test using the same exact connect string. Also note that the MNPGDBLB connect string corresponds to what our client is using (FAILOVER_MODE parameters are more or less the same), and I'm pretty sure the Connection Time Failover (that's the mode you're striving for) is functioning there.

Couldn't there be a network glitch at the same moment which would prevent the app from connecting? Is there anything in the logs of the second instance that would explain the situation (perhaps it was not that healthy after all, or it could not handle all the connection from the first instance)?

No idea about the load balancing at Oracle side. There is a way in which a connection pool can cooperate with RAC to load balance, but I only know something like that exists. Didn't ever try to do that, some service must be running in the DB to provide the connection pool info needed for the balancing.

I'm afraid I cannot help any further. At this situation I'd probably try to simulate the condition and monitor the application, making sure to always use identical connect string. You can use the full connect string with TNSPING or SqlPlus, as far as I know.
 
jaya kemmannu
Ranch Hand
Posts: 99
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

MNPGDBLB i just used to check whether fail over happen or not. We do not use that our app
 
Martin Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
So if the MNPGDBLB apparently works, why not use that in your app?

One more thing - make sure you're using the latest version of the JDBC driver. I'd go with 11g driver even if you're still on 10g - it is backward compatible.
 
reply
    Bookmark Topic Watch Topic
  • New Topic