Load Balancing for Rac

last week i had work on  Load Balancing issue , below is my finding on Rac load Balancing  .

Client-Side Load Balancing 

Client-side load balancing is defined in your client connection definition (tnsnames.ora file, for example) by setting the parameter LOAD_BALANCE=ON. When you set this parameter to ON, Oracle Database randomly selects an address in the address list, and connects to that node's listener. This balances client connections across the available SCAN listeners in the cluster. 

The SCAN listener redirects the connection request to the local listener of the instance that is least loaded and provides the requested service. When the listener receives the connection request, the listener connects the user to an instance that the listener knows provides the requested service. To see what services a listener supports, run the lsnrctl services command. 

When clients connect using SCAN, Oracle Net automatically load balances client connection requests across the three IP addresses you defined for the SCAN, unless you are using EZConnect. 

Server-Side Load Balancing 

When you create an Oracle RAC database with DBCA, it automatically: 

Configures and enables server-side load balancing 

Sets the remote listener parameter to the SCAN listener (Note: If you do not use DBCA, you should set the REMOTE_LISTENER database parameter to scan_name:scan_port.) 

Creates a sample client-side load balancing connection definition in the tnsnames.ora file on the server 

FAN, Fast Connection Failover, and the load balancing advisory depend on an accurate connection load balancing configuration that includes setting the connection load balancing goal for the service. You can use a goal of either LONG or SHORT for connection load balancing. These goals have the following characteristics: 

LONG: Use the LONG connection load balancing method for applications that have long-lived connections. This is typical for connection pools and SQL*Forms sessions. LONG is the default connection load balancing goal. The following is an example of modifying a service, batchconn, with the srvctl utility to define the connection load balancing goal for long-lived sessions: 

srvctl modify service -d db_unique_name -s batchconn -j LONG 
SHORT: Use the SHORT connection load balancing method for applications that have short-lived connections. When using connection pools that are integrated with FAN, set the CLB_GOAL to SHORT. The following example modifies the service known as oltpapp, using SRVTCL to set the connection load balancing goal to SHORT: 

srvctl modify service -d db_unique_name -s oltpapp -j SHORT 

------------------ 

Also you have to check that both nodes are with correct settings about max number of process ( node 1 process + node 2 process = total process as configuration for cluster database, the idea is to have valid configuration of max processes in order in case of node failure to handle workload by surviving node and in the calculation you have to add some % on top in order to avoid max process issues. 

create a cluster managed service using below steps 


1. What is the service for connection? 
- srvctl status service -d <DBNAME> 

2. Add here your connection string 
3. Add here hostname information, VIP`s, SCAN 
4. Check how many ip`s you have related to SCAN hostname ip 
nslookup SCAN_NAME 
if you have more then one IP related to SCAN name -> ping 5 times hostname and check if IP is changing, if you have such results then all is fine.( add information here) 

Ping check should be done as well from Application host in order to ensure that you are using DNS not host file. 

This is how you can add services in case of missing or wrongly added 

Add OLTP service to be available on both two nodes 
srvctl add service -d <DBNAME> -s <SERVICE_NAME> -r <INSTANCE1>,<INSTANCE2> j SHORT -B SERVICE_TIME 

Check service 
srvctl status service -d <DBNAME> 
Service OLTP is not running. 

Start service 
srvctl start service -d <DBNAME> -s <SERVICE_NAME> 

ora.<DBNAME>.<SERVICE_NAME>.svc 
1 ONLINE ONLINE labnode3 Started 

2 ONLINE ONLINE labnode4 Started 

 Jdbc connection string should be like this 

jdbc:oracle:thin:@(DESCRIPTION =(TRANSPORT_CONNECT_TIMEOUT=3)(CONNECT_TIMEOUT=60)(RETRY_COUNT=3)(FAILOVER=ON)(ADDRESS_LIST =(ADDRESS=(PROTOCOL=tcp)(HOST=joshi-SCANVIP.example.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=joshi)))Remote_listeners=joshi-SCANVIP.example.com:1521 


jdbc:oracle:thin:@(DESCRIPTION =(TRANSPORT_CONNECT_TIMEOUT=3)(CONNECT_TIMEOUT=60)(RETRY_COUNT=3)(FAILOVER=ON)(ADDRESS_LIST =(ADDRESS=(PROTOCOL=tcp)(HOST=joshi-SCAN.example.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=joshi)))