Load balancing in Oracle RAC 11.1 is not working.

Questions relating to Oracle Real Application Clusters (RAC) and Clusterware.

Moderator: Tim...

Load balancing in Oracle RAC 11.1 is not working.

Postby ursusca » Mon Apr 09, 2012 6:48 pm

Hi!

I have a 2 node 11.1.0.6 cluster running on Oracle Enterprise Linux 5 and I have a problem with the Load balancing. All users get connected against node-2. I can connect to the Node-1 using the Node-1 TNS entry but I can't connect to the Node-1 using the RAC TNS entry. All sessions go to the Node-2. Only background processes get created successfuly on the Node-1 and no user process is created unless the user process gets connected to the Node-1 directly.
My listener.ora files are as follows:
Node-1
Code: Select all
LISTENER_DB1 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = db1-vip)(PORT = 1521)(IP = FIRST))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.40)(PORT = 1521)(IP = FIRST))
    )
  )


Node-2
Code: Select all
LISTENER_DB2 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = db2-vip)(PORT = 1521)(IP = FIRST))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.42)(PORT = 1521)(IP = FIRST))
    )
  )


The two listeners are up on both nodes:
Code: Select all
$ ps -ef | grep tns
oracle   30281     1  0 12:50 ?        00:00:02 /u01/app/oracle/product/11.1.0/db_1/bin/tnslsnr LISTENER_DB1 -inherit


Code: Select all
$ lsnrctl status

LSNRCTL for Linux: Version 11.1.0.6.0 - Production on 09-APR-2012 13:56:50

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_DB1
Version                   TNSLSNR for Linux: Version 11.1.0.6.0 - Production
Start Date                09-APR-2012 12:50:56
Uptime                    0 days 1 hr. 5 min. 53 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.1.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/db1/listener_db1/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.48)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.40)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_XPT" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "dbrac.domain.com" has 2 instance(s).
  Instance "dbrac1", status READY, has 2 handler(s) for this service...
  Instance "dbrac2", status READY, has 1 handler(s) for this service...
Service "dbracXDB.domain.com" has 2 instance(s).
  Instance "dbrac1", status READY, has 1 handler(s) for this service...
  Instance "dbrac2", status READY, has 1 handler(s) for this service...
Service "dbrac_XPT.domain.com" has 2 instance(s).
  Instance "dbrac1", status READY, has 2 handler(s) for this service...
  Instance "dbrac2", status READY, has 1 handler(s) for this service...
The command completed successfully


Node-2
Code: Select all
$ ps -ef | grep tns
oracle   11033     1  0 Mar21 ?        01:08:09 /u01/app/oracle/product/11.1.0/db_1/bin/tnslsnr LISTENER_DB2 -inherit


Code: Select all
$ lsnrctl status

LSNRCTL for Linux: Version 11.1.0.6.0 - Production on 09-APR-2012 14:00:45

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_DB2
Version                   TNSLSNR for Linux: Version 11.1.0.6.0 - Production
Start Date                21-MAR-2012 13:01:42
Uptime                    19 days 0 hr. 59 min. 3 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.1.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/db2/listener_db2/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.49)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.42)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM2", status READY, has 1 handler(s) for this service...
Service "+ASM_XPT" has 1 instance(s).
  Instance "+ASM2", status READY, has 1 handler(s) for this service...
Service "dbrac.domain.com" has 2 instance(s).
  Instance "dbrac1", status READY, has 1 handler(s) for this service...
  Instance "dbrac2", status READY, has 2 handler(s) for this service...
Service "dbracXDB.domain.com" has 2 instance(s).
  Instance "dbrac1", status READY, has 1 handler(s) for this service...
  Instance "dbrac2", status READY, has 1 handler(s) for this service...
Service "dbrac_XPT.domain.com" has 2 instance(s).
  Instance "dbrac1", status READY, has 1 handler(s) for this service...
  Instance "dbrac2", status READY, has 2 handler(s) for this service...
The command completed successfully


My tnsnames.ora files are as follows:

Code: Select all
DBRAC =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = db2-vip)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = db1-vip)(PORT = 1521))
    (LOAD_BALANCE = ON)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dbrac.domain.com)
    )
  )

DBRAC1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = db1-vip)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dbrac.domain.com)
      (INSTANCE_NAME = dbrac1)
    )
  )

DBRAC2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = db2-vip)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dbrac.domain.com)
      (INSTANCE_NAME = dbrac2)
    )
  )

LISTENERS_DB =
  (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = db1-vip)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = db2-vip)(PORT = 1521))
  )



init parameters local_listener and remote_listener:
Code: Select all
SQL> sho parameter listener

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string
remote_listener                      string      LISTENERS_DB


The Netstat statistics shows that all user sessions in the TIME_WAIT state on the Node-1.

I didn't change any Linux configuration files or oracle ini-parameters and can't understand why this happens.
If anyone has an explanation, I'll be very grateful.

Thanks in advance and regards,
User avatar
ursusca
Senior Member
 
Posts: 131
Joined: Wed Oct 08, 2008 8:42 pm

Re: Load balancing in Oracle RAC 11.1 is not working.

Postby Tim... » Mon Apr 09, 2012 8:51 pm

Hi.

Load balancing is done entirely on number of session (foreground and background). When you test it you need to:

1) Check the number of sessions on each instance (query gv$session).
2) Start a number of new sessions (like 10) and see how they get spread.

If you are just reconnecting the same session, there a good chance it will continually go to the same server. Also, if node 1 has a few of extra sessions running, then everything will look like it's going to node 2, when in fact it is just balancing the load.

A brief scan of your network setup suggests it is OK. It look the same as mine in this example.

http://www.oracle-base.com/articles/11g ... tns_config

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17935
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle RAC and Clusterware

Who is online

Users browsing this forum: No registered users and 3 guests

cron