Connect the Oracle RAC client to Server Load balancer

Load Balance is one of the most important features of Oracle RAC. It distributes the Load evenly to each node in the cluster to improve the overall throughput of the system. Generally, there are two methods to achieve load balancing. One is Load Balancing Based on Client connection, and the other is based on the server Listener (Listener) the collected information is used to allocate new connection requests to instances with fewer connections. This article mainly discusses Server Load balancer based on client connections and provides a demonstration.

I. Client Server Load balancer

The server Load balancer on the client is implemented by adding load_balance = yes to tnsnames. ora. Let's take a look at the explanation of oracle (Note: 226880.1 ).

The client load balancing feature enables clients to randomize connection requests among the listeners. Oracle Net progresses through the list

Protocol addresses in a random sequence, balancing the load on the varous listeners. Without client load balancing, Oracle Net progresses through

List of protocol addresses sequentially until one succeeds. This normally is referred to connect-time load balance.

As described above, if load_balance = yes is not enabled, Oracle Net selects a connection based on the address list in order until the connection is successful.

If the connection to the first host fails, in the case of multiple addresses, select the second address to connect, and so on until the connection is successful.

When load_balance = yes is enabled, Oracle Net selects a random address from multiple addresses for connection until the connection is successful.

Note: This connection method is randomly selected based on the address list, and does not take into account the number of real connections on each instance, that is, the actual connection load of each node is not considered.

Ii. server and client configurations

1. Server Listener Configuration

Oracle @ bo2dbp: ~> More $ ORACLE_HOME/network/admin/listener. ora -- # listener on node bo2dbp

# Listener. ora. bo2dbp Network Configuration File:/u01/oracle/db/network/admin/listener. ora. bo2dbp

# Generated by Oracle configuration tools.

LISTENER_NEW_BO2DBP =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP) (HOST = bo2dbp-vip.2gotrade.com) (PORT = 1314) (IP = FIRST ))

(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.7.51) (PORT = 1314) (IP = FIRST ))

)

)

........................... -- # The rest part is omitted. Note that the configuration here uses a non-default listener Port Number of 1314.

Oracle @ bo2dbs:/u01/oracle/db/network/admin> more listener. ora -- # listener on node bo2dbs

# Listener. ora. bo2dbs Network Configuration File:/u01/oracle/db/network/admin/listener. ora. bo2dbs

# Generated by Oracle configuration tools.

LISTENER_NEW_BO2DBS =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP) (HOST = bo2dbs-vip.2gotrade.com) (PORT = 1314) (IP = FIRST ))

(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.7.52) (PORT = 1314) (IP = FIRST ))

)

)

...............

2. parameter configuration

--> Parameter configuration on balance ora10g1

SQL> show parameter instance_na

NAME TYPE VALUE

-----------------------------------------------------------------------------

Instance_name string ora10g1

SQL> show parameter listener

NAME TYPE VALUE

-----------------------------------------------------------------------------

Local_listener string local_lsnr_ora10g1

Remote_listener string remote_lsnr_ora10g

--> Parameter configuration on balance ora10g2

SQL> show parameter instance_name

NAME TYPE VALUE

-----------------------------------------------------------------------------

Instance_name string ora10g2

SQL> show parameter listener

NAME TYPE VALUE

-----------------------------------------------------------------------------

Local_listener string local_lsnr_ora10g2

Remote_listener string remote_lsnr_ora10g

--> Monitoring information on ance ora10g2

SQL> ho ps-ef | grep lsnr

Oracle 17372 1 0? 00:00:00/u01/oracle/db/bin/tnslsnr LISTENER_NEW_BO2DBS-inherit

Oracle 17502 24301 0 00:00:00 pts/0/bin/bash-c ps-ef | grep lsnr

Oracle 17504 17502 0 00:00:00 pts/0 grep lsnr

SQL> ho lsnrctl status LISTENER_NEW_BO2DBS

Listening Endpoints Summary...

(DESCRIPTION = (ADDRESS = (PROTOCOL = tcp) (HOST = 192.168.7.62) (PORT = 1314 )))

(DESCRIPTION = (ADDRESS = (PROTOCOL = tcp) (HOST = 192.168.7.52) (PORT = 1314 )))

Services Summary...

Service "ora10g" has 2 instance (s ).

Instance "ora10g1", status READY, has 1 handler (s) for this service...

Instance "ora10g2", status READY, has 2 handler (s) for this service...

..........

--> Because only the client-based load balance is tested, the remote_listener parameter is removed.

SQL> alter system reset remote_listener scope = both sid = '*';

Alter system reset remote_listener scope = both sid = '*'

*

ERROR at line 1:

ORA-32009: cannot reset the memory value for instance * from instance ora10g2

SQL> alter system reset remote_listener scope = spfile sid = '*';

System altered.

SQL> ho srvctl stop database-d ora10g --> close database ora10g

SQL> ho srvctl start database-d ora10g --> start database ora10g to make the modified remote_listener take effect.

SQL> ho lsnrctl status LISTENER_NEW_BO2DBS --> at this time, we can see that only ora10g2 registers with the listener.

Listening Endpoints Summary...

(DESCRIPTION = (ADDRESS = (PROTOCOL = tcp) (HOST = 192.168.7.62) (PORT = 1314 )))

(DESCRIPTION = (ADDRESS = (PROTOCOL = tcp) (HOST = 192.168.7.52) (PORT = 1314 )))

Services Summary...

Service "PLSExtProc" has 1 instance (s ).

Instance "PLSExtProc", status UNKNOWN, has 1 handler (s) for this service...

Service "ora10g" has 1 instance (s ).

Instance "ora10g2", status READY, has 1 handler (s) for this service...

..........

Oracle @ bo2dbp: ~> Lsnrctl status LISTENER_NEW_BO2DBP # Similarly, only ora10g1 registers the listener on the node bo2dbp.

Listening Endpoints Summary...

(DESCRIPTION = (ADDRESS = (PROTOCOL = tcp) (HOST = 192.168.7.61) (PORT = 1314 )))

(DESCRIPTION = (ADDRESS = (PROTOCOL = tcp) (HOST = 192.168.7.51) (PORT = 1314 )))

Services Summary...

Service "ora10g" has 1 instance (s ).

Instance "ora10g1", status READY, has 1 handler (s) for this service...

.........

3. Client Configuration

SZDB :~ # Ifconfig eth1 | grep "inet addr" | cut-d ""-f12 | cut-d:-f2 # ip address of the client host

192.168.7.2

SZDB :~ # Su-oracle

Oracle @ SZDB: ~> Cat/etc/hosts -- # ip address information of two virtual nodes on RAC added to the client host

192.168.7.61 bo2dbp-vip.2gotrade.com bo2dbp-vip

192.168.7.62 bo2dbs-vip.2gotrade.com bo2dbs-vip

Oracle @ SZDB: ~> Tail-12 $ ORACLE_HOME/network/admin/tnsnames. ora

ORA10G =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP) (HOST = bo2dbp-vip.2gotrade.com) (PORT = 1314 ))

(ADDRESS = (PROTOCOL = TCP) (HOST = bo2dbs-vip.2gotrade.com) (PORT = 1314 ))

(LOAD_BALANCE = yes)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = ora10g)

)

)

Iii. Test load Balancing)

1. Enable the load balance test

Oracle @ SZDB: ~> More load_balance.sh

#! /Bin/bash

For I in {1 .. 100}

Do

Echo $ I

Sqlplus-S system/oracle @ ORA10G <EOF

Select instance_name from v \ $ instance;

EOF

Sleep 1

Done

Exit 0

# Author: Robinson Cheng

Oracle @ SZDB: ~> ./Load_balance.sh> load_bal.log

Oracle @ SZDB: ~> Head-20 load_bal.log

1

INSTANCE_NAME

----------------

Ora10g2

2

INSTANCE_NAME

----------------

Ora10g1

3

INSTANCE_NAME

----------------

Ora10g2

4

Oracle @ SZDB: ~> Grep ora10g1 load_bal.log | wc-l

47

Oracle @ SZDB: ~> Grep ora10g2 load_bal.log | wc-l

53

From the preceding log, we can see that enabling the Server Load balancer of the client basically ensures the balance of connections initiated from the client.

2. The load balance test is not enabled.

Remove the (LOAD_BALANCE = yes) option from tnsnames. ora on the client, and continue to use the above script for testing.

Oracle @ SZDB: ~> Grep ora10g1 no_load_bal.log | wc-l

100

Oracle @ SZDB: ~> Grep ora10g2 no_load_bal.log | wc-l

0

From the above log, we can see that after LOAD_BALANCE = yes is removed, all user connection requests are set to ora10g1, because the connection request is selected from tnsnames. ora

The position of the column in the first row of the ADDRESS item.

Next we will close the instance ora10g1 and test the connection.

Oracle @ bo2dbp: ~> Srvctl stop instance-d ora10g-I ora10g1

Oracle @ SZDB: ~> ./Load_balance.sh> no_load_bal_new.log

Oracle @ SZDB: ~> Grep ora10g1 no_load_bal_new.log | wc-l

0

Oracle @ SZDB: ~> Grep ora10g2 no_load_bal_new.log | wc-l

100

Because the instance ora10g1 has been disabled, all connection requests are allocated to ora10g2.

Iv. Summary

1. The Server Load balancer configuration on the client is simple. You only need to add LOAD_BALANCE = yes | on

2. The connection allocation principle is to randomly select the ADDRESS list under the connection identifier in tnsnames. ora to connect to the server.

3. If the listener or instance of a node in the selected list is unavailable, the listener or instance is randomly selected from the remaining ADDRESS list until the list is successful.

Oracle video tutorial follow: http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html