In our last post, we talked about how to check the status of Oracle RAC listener. Today, let’s talk about how to fix the isue when Oracle RAC load balancing is not working and it can be related to Oracle RAC listener configuration.
Why Is Oracle RAC Load Balancing Not Working?
Load balancing is one of the most important features of Oracle RAC, designed to evenly distribute the load across the nodes in the cluster to improve the system’s overall throughput. There are typically two methods to achieve load balancing: one is client-side load balancing, and the other is server-side load balancing, which involves distributing new connection requests to instances with fewer connections based on information gathered by the listener.
The load balancing mechanism in Oracle RAC is divided into two layers: the OCSSD layer and the database layer. The OCSSD layer handles the lower-level cluster management, including monitoring and collecting status information from each node to maintain the overall health of the cluster. In the database layer, when a connection request arrives, the PMON (Process Monitor) process uses information collected by OCSSD to assess the load on each node. If a node is heavily loaded, PMON will use the remote_listener parameter to check available listener nodes in the cluster and redirect the connection request to a less busy node. This mechanism ensures load balancing, prevents any single node from being overloaded, and ensures efficient allocation of cluster resources.
If Oracle RAC load balancing is not working, it may be related to the configuration of remote_listener. The following configuration can be used to address this issue:
Recommended Configuration to Fix Oracle RAC Load Balancing Not Working
Server-side Configuration Example:
remote_listener string LISTENERS_RACDB
LISTENERS_RACDB =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip)(PORT = 1521))
)
It has been verified that when remote_listener is dynamically removed, nodes will randomly select a connection node from the list of instances when establishing a connection. If load_balance=NO is set, connections will only be made to the first RAC node listed in the tnsname. However, failover will still be effective in the event of a failure, maintaining high availability.
Changes in Listener Configuration (lsnrctl status):
[oracle@rac1 bin]$ lsnrctl stat LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 17-AUG-2018 13:51:00 Copyright (c) 1991, 2005, Oracle. All rights reserved. Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) STATUS of the LISTENER ------------------------ Alias LISTENER_RAC1 Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production Start Date 17-AUG-2018 13:18:52 Uptime 0 days 0 hr. 32 min. 8 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /opt/ora10g/product/10.2.0/data/network/admin/listener.ora Listener Log File /opt/ora10g/product/10.2.0/data/network/log/listener_rac1.log Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.110.70)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.110.60)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC))) Services Summary... Service "+ASM" has 1 instance(s). Instance "+ASM1", status BLOCKED, has 1 handler(s) for this service... Service "+ASM_XPT" has 1 instance(s). Instance "+ASM1", status BLOCKED, has 1 handler(s) for this service... Service "racdb" has 1 instance(s). Instance "racdb1", status READY, has 1 handler(s) for this service... Service "racdb_XPT" has 1 instance(s). Instance "racdb1", status READY, has 1 handler(s) for this service... The command completed successfully
Listener Status Before and After Removing remote_listener:
Services Summary... Service "+ASM" has 1 instance(s). Instance "+ASM1", status BLOCKED, has 1 handler(s) for this service... Service "+ASM_XPT" has 1 instance(s). Instance "+ASM1", status BLOCKED, has 1 handler(s) for this service... Service "hr" has 1 instance(s). Instance "racdb2", status READY, has 1 handler(s) for this service... Service "racdb" has 2 instance(s). Instance "racdb1", status READY, has 2 handler(s) for this service... Instance "racdb2", status READY, has 1 handler(s) for this service... Service "racdb_XPT" has 2 instance(s). Instance "racdb1", status READY, has 2 handler(s) for this service... Instance "racdb2", status READY, has 1 handler(s) for this service...
