RAC Load balancing

  • From: David Sharples <davidsharples@xxxxxxxxx>
  • To: Oracle-L <oracle-l@xxxxxxxxxxxxx>, racdba@xxxxxxxxxxxxx
  • Date: Fri, 9 Dec 2005 11:53:38 +0000

9.2.0.5, 2 Node RAC, Redhat 2.1 AS

Got an annoying situation where our database isn't being load balanced
correctly.  We have a database test, the database also has an added
service_name of prod (added via alter system ser service_names = 'test,prod'
scope = both).

The clients connect via the service_name of prod in the tnsnames.ora file.
The app creates launches about 300 connection when it starts up and every
single one of them goes to the same node (usually node 2).

This is the tnsnames.ora entry

prod =
  (DESCRIPTION =
  (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = server01)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = server02)(PORT = 1521))
    (LOAD_BALANCE = yes)
  )
 (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = prod)
  )
 )


lsnrctl status on each node shows this

Service "prod" has 2 instance(s).
  Instance "test1", status READY, has 2 handler(s) for this service...
  Instance "test2", status READY, has 2 handler(s) for this service...
Service "test" has 2 instance(s).
  Instance "test1", status READY, has 2 handler(s) for this service...
Instance "test2", status READY, has 2 handler(s) for this service...


I *think* before we added the service name and reconfigured the apps to look
at the new service name it load balanced correctly (cant be sure and cant go
back to that eother)

Anyone any idea why the load balancing isnt perfect (if I use sqlplus I will
switch between the instances, still a bias towards one node then).  The
database is idle when these connections are made so it isnt a case of one
node being busier than the other

Other related posts:

  • » RAC Load balancing