Oracle RAC load Balancing test (combined with server-side vs. client)

Oracle RAC Load Balancing enables client-initiated connections to be efficiently allocated to instances with smaller listener loads. There are two ways to achieve client load balancing, one is by configuring the client's load_balance, one by configuring the server-side Remote_listener parameters. Both approaches have advantages and disadvantages, and they are not mutually exclusive, so they can be combined in two ways to achieve more efficient load balancing. This article describes the use of both combinations (Oralce 10g RAC).

For a separate test of client and server load Balancing, refer to:
Oracle RAC Client Connection Load balancer (load Balance)
Oracle RAC server-side Connection load balancing (load Balance)

The tests in this article will be combined with the script and sample used in the previous article, which is a summary of the first two Tests.

First, configuration requirements

[SQL]View PlainCopyprint?

2. 1, server-side listener normal service, if the use of non-default 1521 port, please refer to the ORACLE RAC non-default port monitoring configuration (Listener.ora Tnsnames.ora)
4. [Email protected]:~> more $ORACLE _home/network/admin/listener.ora
6. # LISTENER.ORA.BO2DBP Network Configuration File:/U01/ORACLE/DB/NETWORK/ADMIN/LISTENER.ORA.BO2DBP
8. # Generated by Oracle configuration tools.
12. LISTENER_BO2DBP =
14. (Description_list =
16. (DESCRIPTION =
18. (address = (PROTOCOL = TCP) (HOST = bo2dbp-vip.2gotrade.com) (PORT = 1521) (IP = First )
20. (address = (PROTOCOL = TCP) (HOST = 192.168.7.51) (PORT = 1521) (IP = First )
22. )
24. )
28. SID_LIST_LISTENER_BO2DBP =
30. (Sid_list =
32. (Sid_desc =
34. (Sid_name = Plsextproc)
36. (Oracle_home =/u01/oracle/db)
38. (program = Extproc)
40. )
42. )
46. [Email protected]:~> lsnrctl Status
48. Service "GOBO4" has 2 instance (s).
50. Instance "gobo4a", status ready, have 2 handler (s) for the This service ...
52. Instance "gobo4b", status ready, have 1 handler (s) for the This service ...
56. 2, the server side of the Remote_listener parameter settings
58. The connection identifier that requires the Remote_listener parameter has a corresponding entry in the server-side Tnsnames.ora
62. Sql> Show Parameter Listener
66. NAME TYPE VALUE
68. ------------------------------------ ----------- ------------------------------  
70. Local_listener string
72. Remote_listener string Remote_lsnr_gobo4
76. [Email protected]:~> more $ORACLE _home/network/admin/tnsnames.ora
78. # Tnsnames.ora Network Configuration File:/u01/oracle/db/network/admin/tnsnames.ora
80. # Generated by Oracle configuration tools.
82. Remote_lsnr_gobo4 =
84. (Address_list =
86. (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.7.61) (PORT = 1521))
88. (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.7.62) (PORT = 1521))
90. )
94. 3. Enable Load_balance in client Tnsnames.ora
96. [Email protected]:~> tail-11 $ORACLE _home/network/admin/tnsnames.ora
100. GOBO4 =
102. (DESCRIPTION =
104. (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.7.61) (PORT = 1521))
106. (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.7.62) (PORT = 1521))
108. (load_balance = yes)
110. (Connect_data =
112. (SERVER = dedicated)
114. (service_name = GOBO4)
116. )
118. )

Second, test load Balance

[Python]View PlainCopyprint?

2. #还是使用之前的脚步来进行测试
4. #Author: Robinson
6. #Blog: http://blog.csdn.net/robinson_0612
8. [Email protected]:~> more load_balance.sh
10. #!/bin/bash
12. For I in {1 ... +}
14. Do
16. Echo $i
18. Sqlplus-s System/[email protected] <<eof
20. Select instance_name from v\ $instance;
22. Eof
24. Sleep 1
26. Done
28. Exit 0
32. [Email protected]:~>./load_balance.sh >load_bal.log
36. #查看日志
38. [Email protected]:~> grep gobo4a Load_bal.log |wc-l
40. 750
42. [Email protected]:~> grep gobo4b Load_bal.log |wc-l
44. 250
48. #查看监听器的日志
50. [Email protected]:/u01/oracle/db/network/log> grep Establish Listener_bo2dbp.log |wc-l
52. 894
56. [Email protected]:/u01/oracle/db/network/log> grep Instance_name=gobo4 Listener_bo2dbp.log |wc-l
58. 415
62. #从上面的查询中可以得知, the node BO2DBP received a total of 894 connection requests, and 415 connection requests were forwarded by Bo2dbs.
64. #因此, actually the number of actual connection requests initiated from the client to BO2DBP is 894-415=479
68. #下面来看在节点bo2dbs上的监听日志
70. [Email protected]:/u01/oracle/db/network/log> grep Establish Listener_bo2dbs.log |wc-l
72. 665
76. [Email protected]:/u01/oracle/db/network/log> grep Instance_name=gobo4 Listener_bo2dbs.log |wc-l
78. 144
80. #从上面的查询中可知, the node Bo2dbs received a total of 665 connection requests, while 144 connection requests were forwarded by BO2DBP.
82. #因此, actually the number of actual connection requests initiated from the client to Bo2dbs is 655-144=511
86. #从上面的结果可知,
88. #基于客户端的连接请求数为节点bo2dbp为479, node Bo2dbs is 511
90. #监听器路由到本地实例数目为, node bo2dbp,479-144=335, node bo2dbs,511-415=96
92. #远程监听器路由道本地实例的数据为, node BO2DPB is 415, node Bo2dbs is 144
94. #监听器路由的概念是指基于服务器端的负载均衡
96. #即服务器端的监听器根据自身以及远程监听器的负载情况来确定将当前的连接请求转发到本地或远程, this is the route.
98. Ext.: http://blog.csdn.net/leshami/article/details/8072367

Oracle RAC load Balancing test (combined with server-side vs. client)