Oracle RAC服务器端的负载均衡是根据RAC中各节点的连接负荷数情况，将新的连接请求分配到负荷最小的节点上去。当数据库处于运行时，RAC中各节点的PMON进程每3秒会将各自节点的连接负荷数更新到service_register。而对于节点中任意监听器故障或监听器意外失败时，PMON进程会每1秒钟检查当前节点上的监听是否重启，以获得最新的负载信息来及时调整负载均衡。本文主要演示suse 10 + oracle 10g rac下的服务器端的负载均衡。

一、服务器端负载均衡配置

1. 1、为tnsnames.ora 添加相应的网络服务名(每个节点配置)

2. oracle@bo2dbp:~> more $ORACLE_HOME/network/admin/tnsnames.ora

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

4. # Generated by Oracle configuration tools.

5.

6. remote_lsnr_gobo4 =

7. (ADDRESS_LIST =

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

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

10. )

11.

12. local_lsnr_gobo4a =

13. (ADDRESS_LIST =

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

15. )

16.

17. local_lsnr_gobo4b =

18. (ADDRESS_LIST =

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

20. )

21.

22. 2、设置remote_listener参数

23. alter system set remote_listener='<net_service_name>' scope=both sid='*';

24.

25. SQL> show parameter instance_name

26.

27. NAME TYPE VALUE

28. ------------------------------------ ----------- ------------------------------

29. instance_name string GOBO4A

30. SQL> show parameter listener

31.

32. NAME TYPE VALUE

33. ------------------------------------ ----------- ------------------------------

34. local_listener string

35. remote_listener string

36.

37. SQL> alter system set remote_listener='remote_lsnr_gobo4' scope=both sid='*';

38.

39. System altered.

40.

41. 3、配置客户端tnsnames.ora

42. -->客户端为suse 10

43. SZDB:~ # ifconfig eth1 | grep "inet addr"|cut -d " " -f12|cut -d : -f2 #--客户端主机的ip

44. 192.168.7.2

45. SZDB:~ # su - oracle

46. oracle@SZDB:~> tail -10 $ORACLE_HOME/network/admin/tnsnames.ora

47. GOBO4 =

48. (DESCRIPTION =

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

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

51. (LOAD_BALANCE = off) #--由于仅仅测试寄予服务器端的负载均衡，因此关闭客户端负载均衡选项

52. (CONNECT_DATA =

53. (SERVER = DEDICATED)

54. (SERVICE_NAME = GOBO4)

55. )

56. )

57. #Author : Robinson Cheng

58. #Blog : http://blog.csdn.net/robinson_0612

59.

60. 4、检查监听情况

61. oracle@bo2dbp:~> lsnrctl status

62. Listening Endpoints Summary...

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

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

65. Services Summary...

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

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

68. Service "GOBO4" has 2 instance(s).

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

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

71. .......

72.

73. oracle@bo2dbs:~> lsnrctl status

74. Listening Endpoints Summary...

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

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

77. Services Summary...

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

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

80. Service "GOBO4" has 2 instance(s).

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

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

83. ..........

84.

85. #--如果监听或数据库需要重启异常请考虑重新启动监听器或数据库

86. #--下面清空监听日志以便于后续统计连接信息

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

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

二、测试服务器端的负载均衡

1. 1、从客户端建立连接

2. oracle@SZDB:~> more load_balance.sh

3. #!/bin/bash

4. for i in {1..1000}

5. do

6. echo $i

7. sqlplus -S system/oracle@GOBO4 <<EOF

8. select instance_name from v\$instance;

9. EOF

10. sleep 1

11. done

12. exit 0

13. oracle@SZDB:~> ./load_balance.sh >srv_load_bal.log

14.

15. 2、分析监听日志

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

17. 12-OCT-2012 12:00:10 * service_update * GOBO4B * 0 #节点bo2dbs上的实例GOBO4B的更新到bo2dbp上监听器的更新信息

18. 12-OCT-2012 12:00:35 * service_update * GOBO4B * 0

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

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

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

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

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

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

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

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

27.

28. #上面的日志片断中可以看出全部是客户端发起的到bo2dbp节点上的建立连接的信息

29.

30. #下面来查看bo2dbs上的监听日志

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

32. 12-OCT-2012 12:00:10 * service_update * GOBO4B * 0

33. 12-OCT-2012 12:00:10 * service_update * GOBO4B * 0

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

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

36. 12-OCT-2012 12:00:35 * service_update * GOBO4B * 0

37. 12-OCT-2012 12:00:35 * service_update * GOBO4B * 0

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

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

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

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

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

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

44.

45. #在12-OCT-2012 12:01:04时刻，连接信息中有INSTANCE_NAME=GOBO4B的连接信息，而节点bo2dbp上也有一条类似的信息，因此该条连接

46. #日志是由节点bo2dbp转发过来而建立的连接请求。

47. #同样在12-OCT-2012 12:01:07时刻，节点bo2dbp转发过来而建立的连接请求。

48.

49. #小结一下，

50. #对于直接连接，监听器日志中将出现establish，且不含有INSTANCE_NAME=GOBO4B 字样

51. #而对于转发的连接，则转发节点与接收的节点同时存在连接信息，转发节点上存在连接信息的与普通的连接请求一样，

52. #而接收的节点上存在INSTANCE_NAME=<instance_name> 信息

53.

54. 3、检查负载均衡结果

55. oracle@SZDB:~> grep GOBO4A srv_load_bal.log |wc -l

56. 755

57. oracle@SZDB:~> grep GOBO4B srv_load_bal.log |wc -l

58. 245

59. #从上面的日志文件中可知总共有755个客户端连接到了gobo4a，有245各客户端连接到了gobo4b

60.

61. #下面查看监听器日志来获得连接信息

62. #下面的查询中在节点bo2dbp上总共有接受了1000个用户连接

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

64. 1000

65.

66. #下面的查询查看是否有从节点bo2dbs转发过来的连接，结果为0，说明没有任何连接请求从bo2dbs转发过来

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

68. 0

69.

70. #接下来查看节点bo2dbs的监听日志，可以看出总共接受了245个连接请求

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

72. 245

73.

74. #下面的过滤情况也表明在节点bo2dbs上的连接是从bo2dbp上转发的连接，而非客户端直接到bo2dbs的请求连接

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

76. 245

77.

78. #从监听器的日志检查可以，测试中的连接全部请求到节点bo2dbp，是由于tnsnames.ora中ADDRESS的第一个IP地址就是bo2dbp的IP

79. #因此所有的连接都是请求到bo2dbp，而没有客户端发出到bo2dbs的连接请求

80. #其次是尽管在bo2dbp有1000个连接请求，而真正建立连接的只有755个，有245转发到了节点bo2dbs

三、总结
      1、服务器端的负载均衡需要配置remote_listener参数，而该参数的值依赖于tnsnames.ora的连接字符串
      2、对于基于服务器端的连接负载均衡，监听器会根据当前节点、实例上的连接负载情况进行转发到空闲的实例
      3、转发的依据仅仅是当前节点监听的连接数量的多少，而非当前实例的过度负载
      4、从上面的测试可以得出，各个节点的连接并不算均衡，是相对的均衡，因此应结合客户端连接负载协同工作
     5、对于当前实例的过度负载的情形，应结合配置service方法来实现负载均衡

ITPub博客

Oracle RAC 服务器端连接负载均衡(Load Balance)

最新文章