Oracle11g RAC – LOCAL VIP JDBC URL – Load Balancing

Basically, there are 3 types of load balancing:

1. Client-Side Load Balancing
* Establishing a connection to various node listeners randomly
* Use LOAD_BALANCE=ON attribute in Connect URL

2. Server-Side Load Balancing
* Listener load balances incoming connections across all nodes actively providing a service
* Use parameters LOCAL_LISTENER, REMOTE_LISTENER to ensure instances register services with all listeners

3. Runtime Connection Load Balancing
* Real-time balancing does by JDBC and based on RAC Metric Event
* Use UCP, or use ICC configured with FCF enabled (ICC/FCF has been deprecated in recent versions but is still functional)

Testcase_1 ORACLE11g – CHECK SERVER SIDE – LOCAL VIP – LOAD BALANCING

Create SAMPLE SCRIPT
========================================================================================
-bash-4.1$ cat check_load_balancing.sh
#!/bin/bash
. /home/oracle/.bash_profile
for ((i=1; i <= 50 ; i++))
do
sqlplus -S scott/scott@ecdb < SELECT INST_ID,USERNAME,COUNT(1) FROM GV$SESSION
     GROUP BY INST_ID,USERNAME;

 INST_ID USERNAME                         COUNT(1)
---------- ------------------------------ ----------
         2 SCOTT                              29
         2 SYS                                     3
         2                                        44
         1 SCOTT                              21
         1 SYS                                     5
         1                                        42

Oracle RAC Network configuration : tnsnames.ora
===========================================================================================
ecdb =
 (DESCRIPTION =
#   (ADDRESS = (PROTOCOL = TCP)(HOST = node-scan)(PORT = 1521))
   (ADDRESS = (PROTOCOL = TCP)(HOST = node1-vip)(PORT = 1521))
   (ADDRESS = (PROTOCOL = TCP)(HOST = node2-vip)(PORT = 1521))
   (LOAD_BALANCE=ON)
    (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = ecdb)
   )
 )

TestCase_2 ORACLE11g CHECK CLIENT SIDE LOCAL_VIP – LOAD BALANCING

wrong URL - Session cannot distributes between instance 1 and 2

jdbc:oracle:thin:@(DESCRIPTION = (ENABLE=BROKEN)
(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = prd-node-db1-vip)
(Port = 1521)) (ADDRESS = (PROTOCOL = TCP)(Host = prd-node-db2-vip)(Port = 1521)))
(FAILOVER = ON) (LOAD_BALANCE=on) (CONNECT_DATA=(SERVICE_NAME = racdb)
(FAILOVER_MODE = (TYPE=SELECT)(METHOD=BASIC))))

it looks like a connect string for using TAF, and the JDBC Thin driver doesn't support TAF. Instead,
it supports Fast Connection Failover (FCF). The Thin driver will ignore the FAILOVER_MODE clause.

A sample JDBC Thin FCF connect string would look like this, without SCAN:

jdbc:oracle:thin:@(DESCRIPTION= 
(LOAD_BALANCE=yes) 
(ADDRESS=(PROTOCOL=TCP)(HOST=apparkins-austin-us.us.oracle.com)(PORT=1521)) 
(ADDRESS=(PROTOCOL=TCP)(HOST=bpparkins2-austin-us.us.oracle.com)(PORT=1521)) 
(CONNECT_DATA=(service_name=lb_service)))

when we test with this URL we got actual load balancing on database side

🙂

http://ggsig.blogspot.in/2012/04/client-side-load-balancing-in-oracle.html

Testing Load balancing in Oracle RAC

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.