Client Side Connect Time Load Balance and Connect Time Fail Over

Client Side Connect Time Load Balance :-

By definition it says that when more than one listener services a single database , then a client while at the time of connect can randomly choose between the listeners for its connect request. This selection is random, its not depend on the load on any of the instances. This random selection enables all listeners to share the load of servicing incoming connect requests.

TNS_PARAMETER to enable this is LOAD_BALANCE.

(LOAD_BALANCE=YES) instructs SQLNet to progress through the list of listeners randomly, balancing load on various listeners. When set to NO then SQLNet tries the address sequentially until connection with one listener succeeds

Parameter should be correctly coded in the net service name connect descriptor. If in ADDRESS_LIST, then (LOAD_BALANCE=YES) should be written within ADDRESS_LIST. If ADDRESS_LIST is not used then it has to be in DESCRIPTION portion.

Sample of TNS net service alias for Client Side Connect Time Load Balance :-

LOADBALANCE=
 (DESCRIPTION =
  (LOAD_BALANCE = yes)
  (ADDRESS = (PROTOCOL = TCP)(HOST = host1-vip)(PORT = 1522))
  (ADDRESS = (PROTOCOL = TCP)(HOST = host2-vip)(PORT = 1522))
  (CONNECT_DATA =
   (SERVICE_NAME = DATABASE)
  )
 )

Client Side Connect Time Failover :- 

Client side Connnect Time Failover enables clients to connect to another listener when the first connection to first listener fails. How many listeners will be tried out in this depends on the number of listener addresses.

Without Connect time failover once a client connection fails with a listener, it retries with that listener only till it connects with that. 

TNS Parameter for Connect time failover is FAILOVER. Default value of this is on.

(failover=on) is default for ADDRESS_LISTs and DESCRIPTION_LISTs , so we do not need to explicitly specify this parameter to put it on.

Sample for TNS net service alias for Client Side Connect time Failover :-

FAILOVER=  
(DESCRIPTION =  
 (ADDRESS_LIST =  
 (ADDRESS = (PROTOCOL = TCP)(HOST = host1-vip)(PORT = 1522))
  (ADDRESS = (PROTOCOL = TCP)(HOST = host2-vip)(PORT = 1522))  
 )  
 (CONNECT_DATA =  
   (SERVICE_NAME = DATABASE)  
 )  
)

Sample for TNS net service alias having both Client Side Connect time Load balancing and Failover :-

LOAD_WITH_FAILOVER= 
(DESCRIPTION =  
 (ADDRESS_LIST =  
  (LOAD_BALANCE = yes)  
  (ADDRESS = (PROTOCOL = TCP)(HOST = host1-vip)(PORT = 1522))
  (ADDRESS = (PROTOCOL = TCP)(HOST = host2-vip)(PORT = 1522)) 
 )  
 (CONNECT_DATA =  
  (SERVICE_NAME = DATABASE)  
 )  
) 

The above configuration is for Client Side Load Balancing. PFB link to configure Server Side Load Balancing.

I hope this article helped you

Regards,
Amit Rath