Client connections slow in failover situation [message #648497] |
Wed, 24 February 2016 03:37 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/4137c5d902cb4b267ac35297c1a5501e?s=64&d=mm&r=g) |
ToomasAas
Messages: 4 Registered: October 2015
|
Junior Member |
|
|
We have Data Guard set up with Oracle 12c. Failover itself works fine, but there is a problem when connecting from Java application to database in failover situation (when primary server is down).
This is perhaps a problem in a way the underlying networking is set up (which is not under our control). When primary server is down, TCP connection attempts from application server to the primary database do not fail immediately with "no route to host" or similar error. Instead, connection attempts stall for 60 seconds and then fail with "connection timed out".
I added TRANSPORT_CONNECT_TIMEOUT=2 and CONNECT_TIMEOUT=3 in connection string of our Java application. This helps somewhat, but still it seems that every request from application to database is subject to this 3-second timeout so there is a noticeable performance impact.
Is there anything else I can do on application or Oracle side before I point the finger at network team?
For reference, here is the entire connection string:
ctx.appDataSource.url=jdbc:oracle:thin:@(DESCRIPTION=(LOAD_BALANCE=no)(FAILOVER=yes)(ADDRESS=(PROTOCOL=TCP)(HOST=Db01)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=DG))(ADDRESS=(PROTOCOL=TCP)(HOST=Db02)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=DG))(FAILOVER_MODE=(type=select)(method=preconnect)(retries=180)(delay=10))(TRANSPORT_CONNECT_TIMEOUT=2)(CONNECT_TIMEOUT=3))
P.S. I also tried TYPE=SESSION and METHOD=BASIC, but this didn't seem to make any difference.
|
|
|
Re: Client connections slow in failover situation [message #648498 is a reply to message #648497] |
Wed, 24 February 2016 03:51 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8931 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Remember that failover is never instantaneous. The standby database has to complete and finish recovery, and then open: this does take some time. Your attempt to use method=preconnect won't work in a data guard environment, only in RAC. type=select is also highly dubious, because failover implies loss of data.
Overall, I think you are confusing failover in Data Guard with failover in a RAC. Data Guard does not deliver zero down time.
|
|
|
Re: Client connections slow in failover situation [message #648503 is a reply to message #648498] |
Wed, 24 February 2016 05:42 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/4137c5d902cb4b267ac35297c1a5501e?s=64&d=mm&r=g) |
ToomasAas
Messages: 4 Registered: October 2015
|
Junior Member |
|
|
These are all valid points. However, the situation that I described in my initial post is not happening at the time of failover. The primary server (Db01) has already been shut down hours ago and former standby server Db02 has successfully become primary. When I re-configure the application to connect only to Db02, there are no delays, but obviously I cannot do this every time there is a failover.
[Updated on: Wed, 24 February 2016 05:44] Report message to a moderator
|
|
|
Re: Client connections slow in failover situation [message #648515 is a reply to message #648503] |
Wed, 24 February 2016 08:12 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8931 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Looking at your connect string in more detail, I'm actually surprised it works at all. This would be better:
@(DESCRIPTION=(address_list=(LOAD_BALANCE=no)(FAILOVER=yes)(ADDRESS=(PROTOCOL=TCP)(HOST=Db01)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=Db02)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=DG)))
Do you have a service DG statically registered with both listeners? It should be dynamically registered only. What does
lsnrctl status
tell you on each machine?
|
|
|
Re: Client connections slow in failover situation [message #648522 is a reply to message #648515] |
Wed, 24 February 2016 10:44 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.gravatar.com/avatar/4137c5d902cb4b267ac35297c1a5501e?s=64&d=mm&r=g) |
ToomasAas
Messages: 4 Registered: October 2015
|
Junior Member |
|
|
I really appreciate your insight into this.
I replaced the connection string with the one you suggested but this didn't change the behaviour. When server Db01 is shut down, connections from application server to Db01 time out and there is a noticeable delay in application when it does database requests. When Oracle database on server Db01 is down but server itself is up, connections from application server to Db01 immediately fail with "connection refused" and there is no delay in application.
The service DG is registered dynamically. Following is 'lsnrctl status' output in normal situation (when Db01 is primary):
Db01
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 24-FEB-2016 11:33:17
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Db01)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date 24-FEB-2016 11:02:48
Uptime 0 days 0 hr. 30 min. 29 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /opt/oracle/home/network/admin/listener.ora
Listener Log File /opt/oracle/diag/tnslsnr/Db01/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Db01)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=Db01)(PORT=5500))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "db01" has 1 instance(s).
Instance "db01", status READY, has 1 handler(s) for this service...
Service "db01XDB" has 1 instance(s).
Instance "db01", status READY, has 1 handler(s) for this service...
Service "db01_DGB" has 1 instance(s).
Instance "db01", status READY, has 1 handler(s) for this service...
Service "dg" has 1 instance(s).
Instance "db01", status READY, has 1 handler(s) for this service...
The command completed successfully
Db02
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 24-FEB-2016 11:32:26
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Db02)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date 24-FEB-2016 11:05:59
Uptime 0 days 0 hr. 26 min. 26 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /opt/oracle/home/network/admin/listener.ora
Listener Log File /opt/oracle/diag/tnslsnr/Db02/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Db02)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "db02" has 1 instance(s).
Instance "db01", status READY, has 1 handler(s) for this service...
Service "db02_DGB" has 1 instance(s).
Instance "db01", status READY, has 1 handler(s) for this service...
The command completed successfully
|
|
|