Connect Oracle RAC server to Server Load balancer

The server Load balancer of the Oracle RAC server distributes new connection requests to the node with the smallest load based on the number of connection loads of each node in the RAC. When the database is running, the PMON process of each node in RAC updates the connection load of each node to service_register every three seconds. When any listener failure or unexpected failure occurs on the node, the PMON process checks whether the listener on the current node is restarted every second to obtain the latest load information and adjust the load balancing in time. This article mainly demonstrates server load balancing Under suse 10 + oracle 10g rac.

I. Server Load balancer Configuration

1. Add the corresponding network service name for tnsnames. ora (configured for each node)

Oracle @ bo2dbp: ~> More $ ORACLE_HOME/network/admin/tnsnames. ora

# Tnsnames. ora Network Configuration File:/u01/oracle/db/network/admin/tnsnames. ora

# Generated by Oracle configuration tools.

Remote_lsnr_gobo4 =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.7.61) (PORT = 1521 ))

(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.7.62) (PORT = 1521 ))

)

Local_lsnr_gobo4a =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.7.61) (PORT = 1521 ))

)

Local_lsnr_gobo4b =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.7.62) (PORT = 1521 ))

)

2. Set the remote_listener parameter.

Alter system set remote_listener = '<net_service_name> 'scope = both sid = '*';

SQL> show parameter instance_name

NAME TYPE VALUE

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

Instance_name string GOBO4A

SQL> show parameter listener

NAME TYPE VALUE

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

Local_listener string

Remote_listener string

SQL> alter system set remote_listener = 'remote _ lsnr_gobo4 'scope = both sid = '*';

System altered.

3. Configure the client tnsnames. ora

--> The client is suse 10.

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: ~> Tail-10 $ ORACLE_HOME/network/admin/tnsnames. ora

GOBO4 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.7.61) (PORT = 1521 ))

(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.7.62) (PORT = 1521 ))

(LOAD_BALANCE = off) # -- disable the client load balancing option because only the Server Load balancer is tested.

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = GOBO4)

)

)

# Author: Robinson Cheng

4. Check the listener

Oracle @ bo2dbp: ~> Lsnrctl status

Listening Endpoints Summary...

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

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

Services Summary...

Service "+ ASM" has 1 instance (s ).

Instance "+ ASM1", status BLOCKED, has 1 handler (s) for this service...

Service "GOBO4" has 2 instance (s ).

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

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

.......

Oracle @ bo2dbs: ~> Lsnrctl status

Listening Endpoints Summary...

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

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

Services Summary...

Service "+ ASM" has 1 instance (s ).

Instance "+ ASM2", status BLOCKED, has 1 handler (s) for this service...

Service "GOBO4" has 2 instance (s ).

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

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

..........

# -- If the listener or database needs to be restarted, restart the listener or database.

# -- The following is a clear listener log to facilitate subsequent statistics on connection information.

Oracle @ bo2dbp:/u01/oracle/db/network/log> cat/dev/null> listener_bo2dbp.log

Oracle @ bo2dbs:/u01/oracle/db/network/log> cat/dev/null> listener_bo2dbs.log

Ii. Test Server Load balancer

1. Establish a connection from the client

Oracle @ SZDB: ~> More load_balance.sh

#! /Bin/bash

For I in {1 .. 1000}

Do

Echo $ I

Sqlplus-S system/oracle @ GOBO4 <EOF

Select instance_name from v \ $ instance;

EOF

Sleep 1

Done

Exit 0

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

2. Analyze listening logs

Oracle @ bo2dbp:/u01/oracle/db/network/log> more listener_bo2dbp.log

12---2012 12:00:10 * service_update * GOBO4B * 0 # update information of the listener updated to bo2dbp on the instance GOBO4B on the node bo2dbs

12---2012 12:00:35 * service_update * GOBO4B * 0

12---2012 12:01:04 * (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = GOBO4) (CID = (PROGRAM = sqlplus @ SZDB) (HOST = SZDB)

(USER = oracle) * (ADDRESS = (PROTOCOL = tcp) (HOST = 192.168.7.2) (PORT = 50322) * establish * GOBO4 * 0

12---2012 12:01:05 * (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = GOBO4) (CID = (PROGRAM = sqlplus @ SZDB) (HOST = SZDB)

(USER = oracle) * (ADDRESS = (PROTOCOL = tcp) (HOST = 192.168.7.2) (PORT = 50325) * establish * GOBO4 * 0

12---2012 12:01:07 * (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = GOBO4) (CID = (PROGRAM = sqlplus @ SZDB) (HOST = SZDB)

(USER = oracle) * (ADDRESS = (PROTOCOL = tcp) (HOST = 192.168.7.2) (PORT = 50328) * establish * GOBO4 * 0

12---2012 12:01:08 * (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = GOBO4) (CID = (PROGRAM = sqlplus @ SZDB) (HOST = SZDB)

(USER = oracle) * (ADDRESS = (PROTOCOL = tcp) (HOST = 192.168.7.2) (PORT = 50330) * establish * GOBO4 * 0

# The above log segment shows all the connection information initiated by the client to the bo2dbp node.

# View the monitoring logs on bo2dbs

Oracle @ bo2dbs:/u01/oracle/db/network/log> more listener_bo2dbs.log

12---2012 12:00:10 * service_update * GOBO4B * 0

12---2012 12:00:10 * service_update * GOBO4B * 0

12---2012 12:00:14 * (CONNECT_DATA = (CID = (PROGRAM =) (HOST = bo2dbs) (USER = oracle) (COMMAND = status) (ARGUMENTS = 64)

(SERVICE = LISTENER_BO2DBS) (VERSION = 169870080) * status * 0

12---2012 12:00:35 * service_update * GOBO4B * 0

12---2012 12:00:35 * service_update * GOBO4B * 0

12---2012 12:01:04 * (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = GOBO4) (CID = (PROGRAM = sqlplus @ SZDB) (HOST = SZDB)

(USER = oracle) (INSTANCE_NAME = GOBO4B) * (ADDRESS = (PROTOCOL = tcp) (HOST = 192.168.7.2) (PORT = 61862) * establish * GOBO4 * 0

12---2012 12:01:07 * (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = GOBO4) (CID = (PROGRAM = sqlplus @ SZDB) (HOST = SZDB)

(USER = oracle) (INSTANCE_NAME = GOBO4B) * (ADDRESS = (PROTOCOL = tcp) (HOST = 192.168.7.2) (PORT = 61868) * establish * GOBO4 * 0

12---2012 12:01:09 * (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = GOBO4) (CID = (PROGRAM = sqlplus @ SZDB) (HOST = SZDB)

(USER = oracle) (INSTANCE_NAME = GOBO4B) * (ADDRESS = (PROTOCOL = tcp) (HOST = 192.168.7.2) (PORT = 61872) * establish * GOBO4 * 0

# At 12:01:04 on 12---2012, the connection information contains the connection information of INSTANCE_NAME = GOBO4B, And the node bo2dbp also has a similar information, so this connection

# A log is a connection request established by the node bo2dbp.

# The Connection Request established after the node bo2dbp is forwarded at 12-12:01:07.

# Summary,

# For direct connections, establish will appear in the listener log without the words INSTANCE_NAME = GOBO4B

# For forwarded connections, the forwarding node and the received node have connection information at the same time, and the forwarding node has the same connection information as the normal connection request,

# The received node has INSTANCE_NAME = <instance_name> Information

3. Check the Server Load balancer results

Oracle @ SZDB: ~> Grep GOBO4A srv_load_bal.log | wc-l

755

Oracle @ SZDB: ~> Grep GOBO4B srv_load_bal.log | wc-l

245

# From the preceding log file, we can see that a total of 755 clients are connected to gobo4a, and 245 clients are connected to gobo4b.

# View the listener log to obtain the connection information

# In the following query, a total of 1000 user connections are accepted on the node bo2dbp.

Oracle @ bo2dbp:/u01/oracle/db/network/log> grep establish listener_bo2dbp.log | wc-l

1000

# Check whether a connection is forwarded from the node bo2dbs in the following query. The result is 0, indicating that no connection request is forwarded from bo2dbs.

Oracle @ bo2dbp:/u01/oracle/db/network/log> grep INSTANCE_NAME = GOBO4 listener_bo2dbp.log | wc-l

0

# View the monitoring log of node bo2dbs. We can see that a total of 245 connection requests are received.

Oracle @ bo2dbs:/u01/oracle/db/network/log> grep establish listener_bo2dbs.log | wc-l

245

# The following filtering condition also indicates that the connection on the node bo2dbs is a connection forwarded from bo2dbp, rather than a request connection from the client directly to bo2dbs.

Oracle @ bo2dbs:/u01/oracle/db/network/log> grep INSTANCE_NAME = GOBO4 listener_bo2dbs.log | wc-l

245

# Check the listener logs. The first ip address of the ADDRESS in tnsnames. ora is the ip address of bo2dbp.

# Therefore, all connections are requests to bo2dbp, and no client sends a connection request to bo2dbs.

# Second, although there are 1000 connection requests in bo2dbp, only 755 connections are established, and 245 are forwarded to the node bo2dbs.

Iii. Summary

1. The remote_listener parameter needs to be configured for Server Load balancer. The value of this parameter depends on the connection string of tnsnames. ora.

2. for server-side connection load balancing, listeners will forward the load to idle instances based on the current node and instance connection load.

3. Forwarding is based only on the number of connections monitored by the current node, rather than the excessive load of the current instance.

4. From the test above, we can see that the connections of each node are not balanced and are relatively balanced. Therefore, we should work collaboratively with the client connection load.

5. When the current instance is overloaded, you should configure the service method to achieve load balancing.

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