Home » Server Options » Data Guard » Client connections slow in failover situation
Client connections slow in failover situation [message #648497] Wed, 24 February 2016 03:37 Go to next message
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 messageGo to next message
John Watson
Messages: 8922
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 messageGo to next message
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 messageGo to next message
John Watson
Messages: 8922
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
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

Previous Topic: Archive log shipping not happening between Primary and Standby
Next Topic: no output from v$archived_log in standby
Goto Forum:
  


Current Time: Thu Mar 28 06:52:33 CDT 2024