File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes Oracle/OAS and the fly likes fail over in jdc URL Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "fail over in jdc URL" Watch "fail over in jdc URL" New topic
Author

fail over in jdc URL

jaya kemmannu
Ranch Hand

Joined: Sep 23, 2011
Posts: 81

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>
jdbcracle: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


Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3433
    
  47

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

Joined: Sep 23, 2011
Posts: 81

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

Joined: Sep 23, 2011
Posts: 81

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 Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3433
    
  47

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 Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3433
    
  47

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

Joined: Sep 23, 2011
Posts: 81
Hi,

My connection pool setting is as follow




Regards,
Jaya
jaya kemmannu
Ranch Hand

Joined: Sep 23, 2011
Posts: 81

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 Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3433
    
  47

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

Joined: Sep 23, 2011
Posts: 81

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>
jdbcracle: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 Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3433
    
  47

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

Joined: Sep 23, 2011
Posts: 81

MNPGDBLB i just used to check whether fail over happen or not. We do not use that our app
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3433
    
  47

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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: fail over in jdc URL
 
Similar Threads
This should work in Groovy, but it doesn't--why?
usage of "this"
Getting MethodMissingException on trying to run groovy class