Oracle RAC server connection Load Balance)

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.

 

For more information about Server Load balancer on the client, see
Load Balance)
Configure RAC load balancing and Failover
 
For more information about the basics and concepts of Oracle network configuration, see:
Configure the Oracle client to connect to the database
Configure dynamic service registration for non-default ports
Configure sqlnet. ora to restrict IP Access to Oracle
Configure and manage Oracle listener logs
Set the Oracle listener password (listener)
Oracle RAC Listener Configuration
Non-Default port listening configuration in Oracle RAC (listener. ora tnsnames. ora)

 

I. Server Load balancer Configuration

1. Add the corresponding network service name (configured for each node) for tnsnames. ora. 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> Sh Ow parameter listener name type value parameter ------------- ---------------------------- 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 @ 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 Server Load balancer option (CONNECT_DATA = (Server = dedicated) because only the Server Load balancer is tested) (SERVICE_NAME = gobo4) # Author: Robinson Cheng # blog: http://blog.csdn.net/robinson_0612 4. Check the listener oracl E @ 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 th Is 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. # -- clear the listener log to facilitate subsequent statistics on the 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 to Oracle @ szdb from the client: ~> 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.log2. Analyze the listening log Oracle @ bo2dbp: /u01/Oracle/DB/Network/log> More listener_bo2dbp.log 12-oct-2012 12:00:10 * service_update * gobo4b * 0 # update information of the gobo4b instance on the node bo2dbs to the listener on the bo2dbp 12-oct-2012 12:00:35 * service_update * gobo4b * 0 12-oct-2012 12:01:04 * (CONNECT_DATA = (Server = dedicated) (SERVICE_NAME = gobo4) (cid = (program = sqlplus @ szdb) (host = szdb) (user = Oracle) * (address = (protoc OL = TCP) (host = 192.168.7.2) (Port = 50322) * establish * gobo4 * 0 12-oct-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-oct-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-oct-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 snippets show all the connection information initiated by the client to the bo2dbp node # The following shows the listening log on bo2dbs: Oracle @ bo2dbs: /u01/Oracle/DB/Network/log> Mo Re listener_bo2dbs.log 12-oct-2012 12:00:10 * service_update * gobo4b * 0 12-oct-2012 12:00:10 * service_update * gobo4b * 0 12-oct-2012 12:00:14 * (CONNECT_DATA = (cid = (program =) (host = bo2dbs) (user = Oracle) (command = status) (arguments = 64) (Service = listener_bo2dbs) (version = 169870080 )) * Status * 0 12-oct-2012 12:00:35 * service_update * gobo4b * 0 12-oct-2012 12:00:35 * service_update * gobo4b * 0 12-o CT-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-oct-2012 12:01:07 * (CONNECT_DATA = (Server = dedicated) (SERVICE_NAME = gobo4) (cid = (program = sqlplus @ szdb) (host = szdb) (user = Oracle) (instance_name = gobo4b) * (address = (protoco L = TCP) (host = 192.168.7.2) (Port = 61868) * establish * gobo4 * 0 12-oct-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 # The connection information contains the connection information of instance_name = gobo4b at 12:01:04 on 12-oct-2012, the node bo2dbp also has a similar information, so this connection # the log is created after the node bo2dbp is forwarded. Connection Request. # The Connection Request established after the node bo2dbp is forwarded at 12-12:01:07. # To summarize, # for direct connections, the listener log will show establish without the words instance_name = gobo4b # For forwarded connections, the forwarding node and the receiving node have connection information at the same time, and the forwarding node has connection information the same as the common connection request, # The received node has instance_name = <instance_name> Information 3. Check the Server Load balancer result 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, 245 of clients connected to gobo4b # view the listener log below to obtain the connection information # In the following query, a total of 1000 users were accepted to connect to Oracle @ bo2dbp on the node bo2dbp: /u01/Oracle/DB/Network/log> grep establish listener_bo2dbp.log | WC-l 1000 # check whether there is a connection forwarded from the node bo2dbs. The result is 0, it indicates that no connection request is forwarded from bo2dbs to Oracle @ bo2dbp: /u01/Oracle/DB/Network/log> grep instance_name = gobo4 listener_bo2dbp.log | WC-l 0 # Check the listening log of node bo2dbs, we can see that a total of 245 connection requests were received from Oracle @ bo2dbs: /u01/Oracle/DB/Network/log> grep establish listener_bo2dbs.log | WC-L 245 # The filtering condition below also indicates that the connection on node bo2dbs is forwarded from bo2dbp, instead of sending a request from a client to bo2dbs to connect to Oracle @ bo2dbs: /u01/Oracle/DB/Network/log> grep instance_name = gobo4 listener_bo2dbs.log | WC-L 245 # Check logs from the listener. All connections in the test are requested to the node bo2dbp, it is due to tnsnames. the first IP address of address in ora is the bo2dbp IP address # therefore, all connections are requested to bo2dbp, no client sends a connection request to bo2dbs # Second, although there are 1000 connection requests in bo2dbp, only 755 are actually connected, 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.

 

Iv. More references

For more information about user-managed backup and recovery, see

Oracle cold backup

Oracle Hot Backup

Concept of Oracle backup recovery

Oracle instance recovery

Oracle recovery based on user management

System tablespace management and Backup Recovery

Sysaux tablespace management and recovery

Oracle backup control file recovery (unsing backup controlfile)

 

For information on RMAN backup recovery and management, see

RMAN overview and architecture

RMAN configuration, Monitoring and Management

Detailed description of RMAN backup

RMAN restoration and recovery

Create and use RMAN catalog

Create RMAN storage script based on catalog

Catalog-based RMAN backup and recovery

RMAN backup path confusion

 

For the Oracle architecture, see

Oracle tablespace and data files

Oracle Password File

Oracle parameter file

Oracle online redo log file)

Oracle Control File)

Oracle archiving logs

Oracle rollback and undo)

Oracle database instance startup and Shutdown Process

Automated Management of Oracle 10g SGA

Oracle instances and Oracle databases (Oracle Architecture)