LB

最新推荐文章于 2023-01-14 22:33:51 发布

wapysun

最新推荐文章于 2023-01-14 22:33:51 发布

阅读量61

点赞数

文章标签：网络运维数据库

本文介绍 Oracle RAC 数据库中的客户端和服务端负载均衡配置方法，包括客户端 tnsnames.ora 文件配置 LOAD_BALANCE 参数和服务端 REMOTE_LISTENER 参数设置。通过对比不同配置下的连接分配情况，验证负载均衡效果。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

LB配置方法(三种)
客户端LB
在客户端tnsnames.ora文件中加入LOAD_BALANCE=YES.

服务端LB

配置REMOTE_LISTENER初始化参数。

专有模式下远程监听（REMOTE_LISTENER）配置：
1.动态更新REMOTE_LISTENER参数，alter system set REMOTE_LISTENER='listener_alias'。
2.将REMOTE_LISTENER设置为空以取消（PMON）远程监听，alter system set REMOTE_LISTENER='' 。
3.在tnsnames.ora中配置listener_alias。
例如：
SQL> show parameter remote_listener NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ remote_listener string LISTENERS_RACDB LISTENERS_RACDB = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip)(PORT = 1521)) )

但如果各个实例listener.ora中存在SID_LIST_LISTENER_RAC1条目，则应删除，以保证listener获得的信息是动态注册的，而不是从文件中读取的静态信息。

两者进行配置，即客户端与服务端LB(略)

测试

前提：
监听日志所在位置:
$ORACLE_HOME/network/log

仅包含有‘establish'字样的条目
同时包含有‘INSTANCE_NAME’与‘establish’字样的条目
区分关键：listener无论把用户请求路由到本地还是远程，本地listener日志条目都有'establish'字样，但是没有'INSTANCE_NAME'字样;而如果连接请求是由远程listener转发过来的，则日志条目中同时有'INSTANCE_NAME'与'establish'字样。

客户端LB

--取消远程监听 SQL> alter system set REMOTE_LISTENER='' scope=spfile sid='*'; System altered. --tnsnames.ora配置片段 LBCLIENT = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip)(PORT = 1521)) (LOAD_BALANCE = yes) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = RACDB) ) ) RACDB = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip)(PORT = 1521)) (LOAD_BALANCE = yes) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = RACDB) ) ) --重启数据库与监听 RACDB1@rac1 /u01/app/oracle/product/10.2.0/db_1/network/admin$ srvctl stop database -d racdb RACDB1@rac1 /u01/app/oracle/product/10.2.0/db_1/network/admin$ srvctl stop listener -n rac1 RACDB1@rac1 /u01/app/oracle/product/10.2.0/db_1/network/admin$ srvctl stop listener -n rac2 RACDB1@rac1 /u01/app/oracle/product/10.2.0/db_1/network/admin$ srvctl start listener -n rac1 RACDB1@rac1 /u01/app/oracle/product/10.2.0/db_1/network/admin$ srvctl start listener -n rac2 RACDB1@rac1 /u01/app/oracle/product/10.2.0/db_1/network/admin$ srvctl start database -d racdb --查看集群状态情况 RACDB1@rac1 /u01/app/oracle/product/10.2.0/db_1/network/admin$ crs_stat -t Name Type Target State Host ------------------------------------------------------------ ora....B1.inst application ONLINE ONLINE rac1 ora....B2.inst application ONLINE ONLINE rac2 ora....DB1.srv application OFFLINE OFFLINE ora....DB2.srv application OFFLINE OFFLINE ora.....TAF.cs application OFFLINE OFFLINE ora.RACDB.db application ONLINE ONLINE rac1 ora....SM1.asm application ONLINE ONLINE rac1 ora....C1.lsnr application ONLINE ONLINE rac1 ora.rac1.gsd application ONLINE ONLINE rac1 ora.rac1.ons application ONLINE ONLINE rac1 ora.rac1.vip application ONLINE ONLINE rac1 ora....SM2.asm application ONLINE ONLINE rac2 ora....C2.lsnr application ONLINE ONLINE rac2 ora.rac2.gsd application ONLINE ONLINE rac2 ora.rac2.ons application ONLINE ONLINE rac2 ora.rac2.vip application ONLINE ONLINE rac2 --添空两个节点的的监听日志文件 RACDB1@rac1 /u01/app/oracle/product/10.2.0/db_1/network/admin$ cd .. RACDB1@rac1 /u01/app/oracle/product/10.2.0/db_1/network$ cd log/ RACDB1@rac1 /u01/app/oracle/product/10.2.0/db_1/network/log$ cat /dev/null > listener_rac1.log RACDB1@rac1 /u01/app/oracle/product/10.2.0/db_1/network/log$ cat listener_rac1.log RACDB1@rac1 /u01/app/oracle/product/10.2.0/db_1/network/log$ ssh rac2 Last login: Sun Jan 8 12:51:57 2012 from rac1 RACDB2@rac2 /home/oracle$ cd $ORACLE_HOME/network/log RACDB2@rac2 /u01/app/oracle/product/10.2.0/db_1/network/log$ cat /dev/null > listener_rac2.log RACDB2@rac2 /u01/app/oracle/product/10.2.0/db_1/network/log$ cat listener_rac2.log RACDB2@rac2 /u01/app/oracle/product/10.2.0/db_1/network/log$ ssh rac1 Last login: Sun Jan 8 13:54:10 2012 from 192.168.246.1 RACDB1@rac1 /home/oracle$ cd shell/ --两个小脚本 RACDB1@rac1 /home/oracle/shell$ cat test.sh #!/bin/bash count=0 while [ $count -lt $2 ] do count=` expr $count + 1` sqlplus -s lau/lau@$1 @test.sql sleep 1 done RACDB1@rac1 /home/oracle/shell$ cat test.sql select instance_name from v$instance / exit; RACDB1@rac1 /home/oracle/shell$ ./test.sh LBCLIENT 1000 > lbclient.log -bash: ./test.sh: Permission denied RACDB1@rac1 /home/oracle/shell$ chmod 777 test.sh --建立1000个连接 RACDB1@rac1 /home/oracle/shell$ ./test.sh LBCLIENT 1000 > lbclient.log --查看连接到实例的情况 RACDB1@rac1 /home/oracle/shell$ grep RACDB1 lbclient.log | wc -l 481 RACDB1@rac1 /home/oracle/shell$ grep RACDB2 lbclient.log | wc -l 519 --查看两个节点中监听日志中的内容，查找包含有establish或INSTANCE_NAME 字样的行数 RACDB1@rac1 /home/oracle/shell$ cd /u01/app/oracle/product/10.2.0/db_1/network/log/ RACDB1@rac1 /u01/app/oracle/product/10.2.0/db_1/network/log$ grep establish listener_rac1.log | wc -l 481 RACDB1@rac1 /u01/app/oracle/product/10.2.0/db_1/network/log$ grep INSTANCE_NAME listener_rac1.log | wc -l 0 RACDB1@rac1 /u01/app/oracle/product/10.2.0/db_1/network/log$ ssh rac2 Last login: Sun Jan 8 13:57:33 2012 from rac1 RACDB2@rac2 /home/oracle$ cd /u01/app/oracle/product/10.2.0/db_1/network/log/ RACDB2@rac2 /u01/app/oracle/product/10.2.0/db_1/network/log$ grep establish listener_rac2.log | wc -l 519 RACDB2@rac2 /u01/app/oracle/product/10.2.0/db_1/network/log$ grep INSTANCE_NAME listener_rac2.log | wc -l 0
由以下
grep INSTANCE_NAME listener_rac1.log | wc -l
grep INSTANCE_NAME listener_rac2.log | wc -l
可知，没有远程监听将请求转发到实例1和实例2中。因为我们没有配置REMOTE_LISTENER初始化参数。

由发送到实例1的请求数481与发送到实例2的请求数519可知，客户端LB基本上是将请求平分到各个实例中。

服务端LB

--设置远程监听 SQL> alter system set remote_listener='LISTENERS_RACDB' scope=spfile sid='*'; System altered. --tnsnames.ora配置片段，LBCLIENT中取消客户端LB(LOAD_BALANCE = off) LBCLIENT = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip)(PORT = 1521)) (LOAD_BALANCE = off) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = RACDB) ) ) --各个节点的远程监听条目 LISTENERS_RACDB = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip)(PORT = 1521)) ) --重启数据库与监听 RACDB1@rac1 /home/oracle$ srvctl stop database -d racdb RACDB1@rac1 /home/oracle$ srvctl stop listener -n rac1 RACDB1@rac1 /home/oracle$ srvctl stop listener -n rac2 RACDB1@rac1 /home/oracle$ srvctl start listener -n rac1 RACDB1@rac1 /home/oracle$ srvctl start listener -n rac2 RACDB1@rac1 /home/oracle$ srvctl start database -d racdb --查看集群状态情况 RACDB1@rac1 /u01/app/oracle/product/10.2.0/db_1/network/log$ crs_stat -t Name Type Target State Host ------------------------------------------------------------ ora....B1.inst application ONLINE ONLINE rac1 ora....B2.inst application ONLINE ONLINE rac2 ora....DB1.srv application OFFLINE OFFLINE ora....DB2.srv application OFFLINE OFFLINE ora.....TAF.cs application OFFLINE OFFLINE ora.RACDB.db application ONLINE ONLINE rac1 ora....SM1.asm application ONLINE ONLINE rac1 ora....C1.lsnr application ONLINE ONLINE rac1 ora.rac1.gsd application ONLINE ONLINE rac1 ora.rac1.ons application ONLINE ONLINE rac1 ora.rac1.vip application ONLINE ONLINE rac1 ora....SM2.asm application ONLINE ONLINE rac2 ora....C2.lsnr application ONLINE ONLINE rac2 ora.rac2.gsd application ONLINE ONLINE rac2 ora.rac2.ons application ONLINE ONLINE rac2 ora.rac2.vip application ONLINE ONLINE rac2 --添空两个节点的的监听日志文件 RACDB1@rac1 /u01/app/oracle/product/10.2.0/db_1/network/log$ cat /dev/null > listener_rac1.log RACDB1@rac1 /u01/app/oracle/product/10.2.0/db_1/network/log$ ssh rac2 Last login: Sun Jan 8 15:03:38 2012 from rac1 RACDB2@rac2 /home/oracle$ cd $ORACLE_HOME/network/log RACDB2@rac2 /u01/app/oracle/product/10.2.0/db_1/network/log$ cat /dev/null > listener_rac2.log RACDB2@rac2 /u01/app/oracle/product/10.2.0/db_1/network/log$ ssh rac1 Last login: Sun Jan 8 15:04:48 2012 from rac2 RACDB1@rac1 /home/oracle$ cd shell/ RACDB1@rac1 /home/oracle/shell$ cat /dev/null > lbclient.log --运行脚本，建立1000个连接 RACDB1@rac1 /home/oracle/shell$ ./test.sh LBCLIENT 1000 > lbclient.log --查看连接到实例的情况 RACDB1@rac1 /home/oracle/shell$ grep RACDB1 lbclient.log | wc -l 353 RACDB1@rac1 /home/oracle/shell$ grep RACDB2 lbclient.log | wc -l 647 --查看两个节点中监听日志中的内容，查找包含有establish或INSTANCE_NAME 字样的行数 RACDB1@rac1 /home/oracle/shell$ cd /u01/app/oracle/product/10.2.0/db_1/network/log/ RACDB1@rac1 /u01/app/oracle/product/10.2.0/db_1/network/log$ grep establish listener_rac1.log | wc -l 1000 RACDB1@rac1 /u01/app/oracle/product/10.2.0/db_1/network/log$ grep INSTANCE_NAME listener_rac1.log | wc -l 0 RACDB1@rac1 /u01/app/oracle/product/10.2.0/db_1/network/log$ ssh rac2 RACDB2@rac2 /u01/app/oracle/product/10.2.0/db_1/network/log$ grep establish listener_rac2.log | wc -l 647 RACDB2@rac2 /u01/app/oracle/product/10.2.0/db_1/network/log$ grep INSTANCE_NAME listener_rac2.log | wc -l 647
因为在客户端tnsnames.ora的LBCLIENT中配置了LOAD_BALANCE = off,禁用了客户端LB,所以1000个连接全部发给了实例1(实例1（rac1-vip）的配置在第一条).
实例2的请求全部由远程监听转发而来，连接请求数为647，而实例1全部由本地监听请求，请求数为1000-647=353。
服务端LB 353/647,并不像客户端LB那样将请求平分到各个实例中。

客户端与服务端LB （略）
两者同时配置或许可以获得不错的LB效果，不过10g的Service也许是另一种不错的分散负载的方案。