LB配置方法(三种)
客户端LB
在客户端tnsnames.ora文件中加入LOAD_BALANCE=YES.
服务端LB
1.动态更新REMOTE_LISTENER参数,alter system set REMOTE_LISTENER='listener_alias'。
2.将REMOTE_LISTENER设置为空以取消(PMON)远程监听,alter system set REMOTE_LISTENER='' 。
3.在tnsnames.ora中配置listener_alias。
例如:
由以下
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基本上是将请求平分到各个实例中。
因为在客户端tnsnames.ora的LBCLIENT中配置了LOAD_BALANCE = off,禁用了客户端LB,所以1000个连接全部发给了实例1(实例1(rac1-vip)的配置在第一条).
实例2的请求全部由远程监听转发而来,连接请求数为647,而实例1全部由本地监听请求,请求数为1000-647=353。
服务端LB 353/647,并不像客户端LB那样将请求平分到各个实例中。
客户端LB
在客户端tnsnames.ora文件中加入LOAD_BALANCE=YES.
服务端LB
配置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也许是另一种不错的分散负载的方案。