Oracle Net Services provides the ability to add the CONNECT_TIMEOUT, RETRY_COUNT, and TRANSPORT_CONNECT_TIMEOUT parameters to the tnsnames.ora connection string.
For example, when using SCAN addresses for the remote listeners at the database:
jdbc:oracle:thin:@(DESCRIPTION =(TRANSPORT_CONNECT_TIMEOUT=3)(CONNECT_TIMEOUT=60)(RETRY_COUNT=3)(FAILOVER=ON)(ADDRESS_LIST =(ADDRESS=(PROTOCOL=tcp)(HOST=CLOUD-SCANVIP.example.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=GOLD)))Remote_listeners=CLOUD-SCANVIP.example.com:1521
For example, when using remote listeners pointing to VIPs at the database:
jdbc:oracle:thin:@(DESCRIPTION = (TRANSPORT_CONNECT_TIMEOUT=3) (CONNECT_TIMEOUT=60)(RETRY_COUNT=3)(FAILOVER=ON) (ADDRESS_LIST= (ADDRESS=(PROTOCOL=tcp)(HOST=CLOUD-VIP1)(PORT=1521) ) (ADDRESS=(PROTOCOL=tcp)(HOST=CLOUD-VIP2)(PORT=1521) ) (ADDRESS=(PROTOCOL=tcp)(HOST=CLOUD-VIP3)(PORT=1521) )) (CONNECT_DATA=(SERVICE_NAME=GOLD)))
The value of these parameters is expressed in seconds. In the preceding examples, Oracle Net waits for 60 seconds for each full connection to receive a response, after which it assumes that a failure occurred and retries the next address in the ADDRESS_LIST. Oracle Net retries the address list 3 times before it returns a failure message to the client. The TRANSPORT_CONNECT_TIMEOUT parameter establishes the time to wait to establish a TCP connection to the database server.
For SCAN, Oracle Net Services tries all three addresses (returned by the SCAN address) before returning a failure to the client. EZConnect with SCAN includes this connection failover feature.
This behavior is called Oracle Net connection failover. If an error is returned from a chosen address in the list, then Oracle Net Services tries the next address in the list until it is either successful or it has exhausted all addresses in its list.