This forum is currently locked. You can't register or post questions at this time. (read more)

Load balancing in Oracle RAC 11.1 is not working.

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

Moderator: Tim...

User avatar
ursusca
Senior Member
Posts: 131
Joined: Wed Oct 08, 2008 8:42 pm
Contact:

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,

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

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


Return to “Oracle RAC and Clusterware”

Who is online

Users browsing this forum: No registered users and 0 guests

cron