459 views
in RAC by
Why we need multiple Listners configured ?
How do manage Client side load balancing ?
How do we enable/Configure Load balancing ?
What are the types of work load ?
Using which type can we benefit ?

1 Answer

by
selected by
 
Best answer
Automatic Work Load Managment

sales1-scan.example.com IN A 133.22.67.194
IN A 133.22.67.193
IN A 133.22.67.192

NAME TYPE VALUE
-------------------------- ----------- ------------------------------
local_listener string (DESCRIPTION=(ADDRESS_LIST=(AD
DRESS=(PROTOCOL=TCP)(HOST=133.
22.67.111)(PORT=1521))))
remote_listener string sales1-scan.example.com:1521

Automatic workload management enables you to manage workload distributions to provide optimal performance for users and applications

We can configure multiple listener on multiple nodes to handle client connection requests for the same database service.

 

Types of workLoad Distribution

Connection balancing
 Client side connect time load balancing ( LOAD_BALANCE=ON) - Using SCAN IP address list
 Client side connect time failover (FAILOVER=ON) - If listener connect fails in first attempt.
 Server side connect time load balancing

Runtime connection load balancing
  Work requests automatically balanced across the pool of connections.
  Native feature of JDBC Implicit connection cache and ODP.Net connection Pool.

1) Client side connect time load balancing - enables clients to randomize connection requests among
   a list of available listeners.

   This feature enabled by LOAD_BALANCE=ON

Randomly select an address list in the address list
|
|
V
Connects to Node Listener - This balances client connections across the available SCAN listeners in the cluster.

Scan Listener redirects the connection request to the local listener of the instance that is least loaded and
provides the requested service.

When Using SCAN, Oracle Net automatically load balances the client connection requests across three IP addresses your
defined for the SCAN.

LISTENERS_db_unique_name =
  (ADDRESS_LIST =
    (ADDRESS=(PROTOCOL=TCP)(HOST=scan_VIP1)(PORT = scan_port_number))
    (ADDRESS=(PROTOCOL=TCP)(HOST=scan_VIP2)(PORT = scan_port_number))
    (ADDRESS=(PROTOCOL=TCP)(HOST=scan_VIP3)(PORT = scan_port_number))
    (ADDRESS = (PROTOCOL = TCP)(HOST = node_VIP_name1-vip)(PORT = listener_port_number))
    (ADDRESS = (PROTOCOL = TCP)(HOST = node_VIP_name2-vip)(PORT = listener_port_number))
  )

SQL> ALTER SYSTEM SET remote_listener = '
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=scan_VIP1)(PORT = scan_port_number))
(ADDRESS=(PROTOCOL=TCP)(HOST=scan_VIP2)(PORT = scan_port_number))
(ADDRESS=(PROTOCOL=TCP)(HOST=scan_VIP3)(PORT = scan_port_number))
(ADDRESS=(PROTOCOL=TCP)(HOST=node_VIP_name1-vip)(PORT = listener_port_number))
(ADDRESS=(PROTOCOL=TCP)(HOST=node_VIP_name2-vip)(PORT = listener_port_number)))'
SCOPE=BOTH SID=*

SQL> SYSTEM SET REMOTE_LISTENER = 'LISTENERS_db_unique_name' SCOPE=BOTH SID=*

Client Side Connect Time Failover
________________________________

Enables clients to connect another listener if the intial connection to the first listener fails.

failover=on
loadbalance=on

Server Side Load Balancing
__________________________
Enables and configure using DBCA while creating Database with setting REMOTE_LISTENER parameter to SCAN listener.

The listener directs a connection request to the best instance currently providing the service by using information from
the load balancing advisory.

Runtime Connection Load Balancing and Connection Pools
______________________________________________________

It is afeature of Oracle connection pools that can distribute client work requests across the instances in an oracle RAC
database based on the load.
...