oracle 10g RAC LoadBalance(二)

最新推荐文章于 2021-04-13 03:16:57 发布

Huangcn

最新推荐文章于 2021-04-13 03:16:57 发布

阅读量324

点赞数

CC 4.0 BY-SA版权

分类专栏： Oracle RAC Oracle管理 Oracle 10g

本文链接：https://blog.youkuaiyun.com/hrich/article/details/8832288

Oracle管理同时被 3 个专栏收录

20 篇文章

订阅专栏

Oracle 10g

7 篇文章

订阅专栏

Oracle RAC

6 篇文章

订阅专栏

本文介绍了Oracle 10g RAC环境下如何通过客户端配置实现负载均衡，包括设置remote_listener参数、调整tnsnames.ora文件，并通过实际测试验证负载均衡效果。

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

oracle 10g RAC LoadBalance(一)

一、基于客户端的负载均衡

1、设置remote_listener为空

ALTER SYSTEM SET remote_listener='' sid='orcldb1';
ALTER SYSTEM SET remote_listener='' sid='orcldb2';

--确认remote_listener和local_listener配置了正确的值
--由于是测试基于客户端的负载均衡，所以不设置remote_listener

SQL> 
SQL> show parameter listener

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string      (ADDRESS = (PROTOCOL = TCP)(HO
                                                 ST = 192.168.100.112)(PORT = 1
                                                 521))
remote_listener                      string
SQL> 
SQL>

2、在客户端tnsnames.ora中增加以下内容

ORCLDBLB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.112)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.114)(PORT = 1521))
    (LOAD_BALANCE = yes)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcldb)
    )
  )
  
--查看服务器监听情况
lsnrctl status LISTENER_ORLRAC1

[oracle@orlrac1 rs]$ 
[oracle@orlrac1 rs]$ lsnrctl status LISTENER_ORLRAC1

LSNRCTL for Linux: Version 10.2.0.5.0 - Production on 18-APR-2013 16:24:31

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orlrac1-vip)(PORT=1521)(IP=FIRST)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_ORLRAC1
Version                   TNSLSNR for Linux: Version 10.2.0.5.0 - Production
Start Date                18-APR-2013 15:04:29
Uptime                    0 days 1 hr. 20 min. 2 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/oracle/product/10.2.0/db_1/network/log/listener_orlrac1.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.112)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.111)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM1", status BLOCKED, has 1 handler(s) for this service...
Service "+ASM_XPT" has 1 instance(s).
  Instance "+ASM1", status BLOCKED, has 1 handler(s) for this service...
Service "orcldb" has 2 instance(s).
  Instance "orcldb1", status READY, has 2 handler(s) for this service...
  Instance "orcldb2", status READY, has 1 handler(s) for this service...
Service "orcldbXDB" has 2 instance(s).
  Instance "orcldb1", status READY, has 1 handler(s) for this service...
  Instance "orcldb2", status READY, has 1 handler(s) for this service...
Service "orcldb_XPT" has 2 instance(s).
  Instance "orcldb1", status READY, has 2 handler(s) for this service...
  Instance "orcldb2", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@orlrac1 rs]$ 
[oracle@orlrac1 rs]$ 

其中的Service "orcldb"包含了两个实例
Service "orcldb" has 2 instance(s).
  Instance "orcldb1", status READY, has 2 handler(s) for this service...
  Instance "orcldb2", status READY, has 1 handler(s) for this service...

3、运行下面的脚本
—运行前清空节点1和节点的监听日志
—测试10次串行连接

[oracle@hcn10g script]$ 
[oracle@hcn10g script]$ cat loginlb.sh
for i in {1..10}
do
echo 'No.' $i
sqlplus -s scott/oracle@orcldblb<<EOF
select instance_name from v\$instance;
exit
EOF
done
exit
[oracle@hcn10g script]$ 

###########################################################
[oracle@hcn10g script]$ 
[oracle@hcn10g script]$ 
[oracle@hcn10g script]$ ksh loginlb.sh > lb.log
[oracle@hcn10g script]$ 
[oracle@hcn10g script]$ grep orcldb1 lb.log|wc -l
5
[oracle@hcn10g script]$ 
[oracle@hcn10g script]$ grep orcldb2 lb.log|wc -l
5
[oracle@hcn10g script]$ 
[oracle@hcn10g script]$ 

----串行连接10次，每个节点平均分配了5次

[oracle@orlrac1 log]$ 
[oracle@orlrac1 log]$ cat listener_orlrac1.log 
18-APR-2013 17:17:18 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcldb)(CID=(PROGRAM=sqlplus)(HOST=hcn10g.oracle.com)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.41)(PORT=45035)) * establish * orcldb * 0
18-APR-2013 17:17:19 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcldb)(CID=(PROGRAM=sqlplus)(HOST=hcn10g.oracle.com)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.41)(PORT=45036)) * establish * orcldb * 0
18-APR-2013 17:17:19 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcldb)(CID=(PROGRAM=sqlplus)(HOST=hcn10g.oracle.com)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.41)(PORT=45038)) * establish * orcldb * 0
18-APR-2013 17:17:19 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcldb)(CID=(PROGRAM=sqlplus)(HOST=hcn10g.oracle.com)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.41)(PORT=45041)) * establish * orcldb * 0
18-APR-2013 17:17:19 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcldb)(CID=(PROGRAM=sqlplus)(HOST=hcn10g.oracle.com)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.41)(PORT=45043)) * establish * orcldb * 0
18-APR-2013 17:17:20 * service_update * orcldb1 * 0
[oracle@orlrac1 log]$ 
[oracle@orlrac1 log]$ 

[oracle@orlrac2 log]$ 
[oracle@orlrac2 log]$ cat listener_orlrac2.log 
18-APR-2013 17:17:18 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcldb)(CID=(PROGRAM=sqlplus)(HOST=hcn10g.oracle.com)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.41)(PORT=30510)) * establish * orcldb * 0
18-APR-2013 17:17:18 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcldb)(CID=(PROGRAM=sqlplus)(HOST=hcn10g.oracle.com)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.41)(PORT=30513)) * establish * orcldb * 0
18-APR-2013 17:17:19 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcldb)(CID=(PROGRAM=sqlplus)(HOST=hcn10g.oracle.com)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.41)(PORT=30515)) * establish * orcldb * 0
18-APR-2013 17:17:19 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcldb)(CID=(PROGRAM=sqlplus)(HOST=hcn10g.oracle.com)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.41)(PORT=30516)) * establish * orcldb * 0
18-APR-2013 17:17:19 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcldb)(CID=(PROGRAM=sqlplus)(HOST=hcn10g.oracle.com)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.41)(PORT=30520)) * establish * orcldb * 0
18-APR-2013 17:19:11 * service_update * orcldb2 * 0
[oracle@orlrac2 log]$ 

---1号节点和2号节点各有5个连接请求，不存在转发的情况

---下面测试100次并发连接
[oracle@hcn10g script]$ 
[oracle@hcn10g script]$ cat login.sh
sqlplus -s scott/oracle@orcldblb<<EOF
select instance_name from v\$instance;
exit
EOF
exit
[oracle@hcn10g script]$ 
[oracle@hcn10g script]$ 
[oracle@hcn10g script]$ 
[oracle@hcn10g script]$ 
[oracle@hcn10g script]$ cat batch_login.sh 
for i in {1..100}
do
echo 'No.' $i
nohup ksh login.sh &
done
[oracle@hcn10g script]$ 
[oracle@hcn10g script]$ 

[oracle@hcn10g script]$ 
[oracle@hcn10g script]$ ksh batch_login.sh
[oracle@hcn10g script]$ 
[oracle@hcn10g script]$ grep orcldb1 nohup.out|wc -l
55
[oracle@hcn10g script]$ 
[oracle@hcn10g script]$ grep orcldb2 nohup.out|wc -l
45
[oracle@hcn10g script]$

在并发100个会话的情况下，有55个会话分配到1号实例，有45个分配到2号实例上。

---关闭客户端LOAD_BALANCE
ORCLDBLB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.112)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.114)(PORT = 1521))
    (LOAD_BALANCE = off)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcldb)
    )
  )

---测试10次串行连接
[oracle@hcn10g script]$ 
[oracle@hcn10g script]$ cat loginlb.sh
for i in {1..10}
do
echo 'No.' $i
sqlplus -s scott/oracle@orcldblb<<EOF
select instance_name from v\$instance;
exit
EOF
done
exit
[oracle@hcn10g script]$ 
[oracle@hcn10g script]$ 
[oracle@hcn10g script]$ ksh loginlb.sh > lb.log
[oracle@hcn10g script]$ 
[oracle@hcn10g script]$ grep orcldb1 lb.log|wc -l
10
[oracle@hcn10g script]$ 
[oracle@hcn10g script]$ grep orcldb2 lb.log|wc -l
0
[oracle@hcn10g script]$ 

--关闭客户端LOAD_BALANCE后，所有的连接都连接到了地址列表中的第一个地址

--测试关闭1号节点后，连接的情况
[oracle@orlrac1 rs]$ 
[oracle@orlrac1 rs]$ srvctl stop instance -d orcldb -i orcldb1
[oracle@orlrac1 rs]$ 
[oracle@orlrac1 rs]$ ./crs_stat.sh 
 Resource name                                Target     State             
--------------                                ------     -----             
ora.orcldb.db                                 ONLINE     ONLINE on orlrac1 
ora.orcldb.orcldb1.inst                       OFFLINE    OFFLINE           
ora.orcldb.orcldb2.inst                       ONLINE     ONLINE on orlrac2 
ora.orcldb.orcltaf.cs                         OFFLINE    OFFLINE           
ora.orcldb.orcltaf.orcldb1.srv                OFFLINE    OFFLINE           
ora.orcldb.orcltafsrv.cs                      OFFLINE    OFFLINE           
ora.orcldb.orcltafsrv.orcldb1.srv             OFFLINE    OFFLINE           
ora.orlrac1.ASM1.asm                          ONLINE     ONLINE on orlrac1 
ora.orlrac1.LISTENER_ORLRAC1.lsnr             ONLINE     ONLINE on orlrac1 
ora.orlrac1.gsd                               ONLINE     ONLINE on orlrac1 
ora.orlrac1.ons                               ONLINE     ONLINE on orlrac1 
ora.orlrac1.vip                               ONLINE     ONLINE on orlrac1 
ora.orlrac2.ASM2.asm                          ONLINE     ONLINE on orlrac2 
ora.orlrac2.LISTENER_ORLRAC2.lsnr             ONLINE     ONLINE on orlrac2 
ora.orlrac2.gsd                               ONLINE     ONLINE on orlrac2 
ora.orlrac2.ons                               ONLINE     ONLINE on orlrac2 
ora.orlrac2.vip                               ONLINE     ONLINE on orlrac2 
[oracle@orlrac1 rs]$ 
[oracle@orlrac1 rs]$ 

[oracle@hcn10g script]$ 
[oracle@hcn10g script]$ ksh loginlb.sh > lb.log
[oracle@hcn10g script]$ 
[oracle@hcn10g script]$ 
[oracle@hcn10g script]$ grep orcldb1 lb.log|wc -l
0
[oracle@hcn10g script]$ 
[oracle@hcn10g script]$ grep orcldb2 lb.log|wc -l
10
[oracle@hcn10g script]$ 
[oracle@hcn10g script]$ 

--关闭1号实例以及关闭Load Balance的情况下，所有的连接都自动连接到2号节点上