oracle 10g RAC LoadBalance(三)

最新推荐文章于 2021-04-13 04:13:55 发布

Huangcn

最新推荐文章于 2021-04-13 04:13:55 发布

阅读量262

点赞数

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

本文链接：https://blog.csdn.net/hrich/article/details/8832416

版权

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

20 篇文章 0 订阅

订阅专栏

Oracle 10g

7 篇文章 0 订阅

订阅专栏

Oracle RAC

6 篇文章 0 订阅

订阅专栏

oracle 10g RAC LoadBalance(一)

oracle 10g RAC LoadBalance(二)

二、基于服务器端的负载均衡

1、确定两个节点的实例都是正常运行的

[oracle@orlrac1 rs]$ 
[oracle@orlrac1 rs]$ ./crs_stat.sh 
 Resource name                                Target     State             
--------------                                ------     -----             
ora.orcldb.db                                 ONLINE     ONLINE on orlrac1 
ora.orcldb.orcldb1.inst                       ONLINE     ONLINE on orlrac1 
ora.orcldb.orcldb2.inst                       ONLINE     ONLINE on orlrac2 
ora.orcldb.orcltaf.cs                         OFFLINE    OFFLINE           -- 配置的failover服务，可以不用启动
ora.orcldb.orcltaf.orcldb1.srv                OFFLINE    OFFLINE           -- 配置的failover服务，可以不用启动
ora.orcldb.orcltafsrv.cs                      OFFLINE    OFFLINE           -- 配置的failover服务，可以不用启动
ora.orcldb.orcltafsrv.orcldb1.srv             OFFLINE    OFFLINE           -- 配置的failover服务，可以不用启动
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]$

2、关闭客户端tnsnames.ora中的loadbalance

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)
    )
  )
  
--查看服务器监听情况
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、配置local_listener和remote_listener

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

1号实例：
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      LISTENERS_ORCLDB
SQL> 
SQL> 

2号实例：
SQL> 
SQL> show parameter listener

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

--服务器端的tnsnames.ora

LISTENERS_ORCLDB =
  (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = orlrac1-vip)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = orlrac2-vip)(PORT = 1521))
  )

4、清空监听日志信息，便于观察连接转发的情况

[oracle@orlrac1 log]$ 
[oracle@orlrac1 log]$ pwd
/u01/oracle/product/10.2.0/db_1/network/log
[oracle@orlrac1 log]$ 
[oracle@orlrac1 log]$ >listener_orlrac1.log 
[oracle@orlrac1 log]$ 
[oracle@orlrac2 log]$ 
[oracle@orlrac2 log]$ pwd
/u01/oracle/product/10.2.0/db_1/network/log
[oracle@orlrac2 log]$ 
[oracle@orlrac2 log]$ >listener_orlrac2.log 
[oracle@orlrac2 log]$

5、执行串行连接脚本

[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
5
[oracle@hcn10g script]$ 
[oracle@hcn10g script]$ grep orcldb2 lb.log|wc -l
5
[oracle@hcn10g script]$ 
[oracle@hcn10g script]$ 
---数据库的连接被平均分配到两个节点


[oracle@orlrac1 log]$ 
[oracle@orlrac1 log]$ cat listener_orlrac1.log 
18-APR-2013 16:39:11 * service_update * orcldb2 * 0  --PMON的service-update过程
18-APR-2013 16:40:11 * (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=54176)) * establish * orcldb * 0
18-APR-2013 16:40:11 * (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=54177)) * establish * orcldb * 0
18-APR-2013 16:40:11 * (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=54179)) * establish * orcldb * 0
18-APR-2013 16:40:11 * (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=54180)) * establish * orcldb * 0
18-APR-2013 16:40:11 * (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=54182)) * establish * orcldb * 0
18-APR-2013 16:40:11 * (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=54183)) * establish * orcldb * 0
18-APR-2013 16:40:11 * (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=54185)) * establish * orcldb * 0
18-APR-2013 16:40:11 * (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=54186)) * establish * orcldb * 0
18-APR-2013 16:40:11 * (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=54188)) * establish * orcldb * 0
18-APR-2013 16:40:11 * (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=54189)) * establish * orcldb * 0
18-APR-2013 16:40:12 * service_update * orcldb1 * 0
18-APR-2013 16:40:12 * service_update * orcldb1 * 0
[oracle@orlrac1 log]$ 


[oracle@orlrac2 log]$ 
[oracle@orlrac2 log]$ cat listener_orlrac2.log 
18-APR-2013 16:39:11 * service_update * orcldb2 * 0
18-APR-2013 16:39:11 * service_update * orcldb2 * 0   ---PMON的service-update过程
18-APR-2013 16:40:11 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcldb)(CID=(PROGRAM=sqlplus)(HOST=hcn10g.oracle.com)(USER=oracle))(INSTANCE_NAME=orcldb2)) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.41)(PORT=63718)) * establish * orcldb * 0
18-APR-2013 16:40:11 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcldb)(CID=(PROGRAM=sqlplus)(HOST=hcn10g.oracle.com)(USER=oracle))(INSTANCE_NAME=orcldb2)) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.41)(PORT=63721)) * establish * orcldb * 0
18-APR-2013 16:40:11 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcldb)(CID=(PROGRAM=sqlplus)(HOST=hcn10g.oracle.com)(USER=oracle))(INSTANCE_NAME=orcldb2)) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.41)(PORT=63724)) * establish * orcldb * 0
18-APR-2013 16:40:11 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcldb)(CID=(PROGRAM=sqlplus)(HOST=hcn10g.oracle.com)(USER=oracle))(INSTANCE_NAME=orcldb2)) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.41)(PORT=63727)) * establish * orcldb * 0
18-APR-2013 16:40:11 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcldb)(CID=(PROGRAM=sqlplus)(HOST=hcn10g.oracle.com)(USER=oracle))(INSTANCE_NAME=orcldb2)) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.41)(PORT=63730)) * establish * orcldb * 0
18-APR-2013 16:40:12 * service_update * orcldb1 * 0
[oracle@orlrac2 log]$ 

--在18-APR-2013 16:40:11时刻，节点2的连接信息中有5条INSTANCE_NAME=orcldb2的连接信息，
--而节点1上也有10条类似的信息，因此节点2上面的这5条连接日志是由节点1转发过来而建立的连接请求。

--对于直接连接，监听器日志中将出现establish，且不含有INSTANCE_NAME=GOBO4B 字样  
--而对于转发的连接，则转发节点与接收的节点同时存在连接信息，转发节点上存在连接信息的与普通的连接请求一样，  
--而接收的节点上存在INSTANCE_NAME=<instance_name> 信息  


--从监听器的日志检查可以，测试中的连接全部请求到节点1，是由于tnsnames.ora中ADDRESS的第一个IP地址就是节点1的IP  
--因此所有的连接都是请求到节点1，而没有客户端发出到节点2的连接请求  
--在节点1有10个连接请求，而真正建立连接的只有5个，有5个转发到了节点2

6、执行并行连接脚本
执行前先清空监听日志

[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]$ cat batch_login.sh 
for i in {1..10}
do
echo 'No.' $i
nohup ksh login.sh &
done
[oracle@hcn10g script]$ 
[oracle@hcn10g script]$ 

[oracle@hcn10g script]$ ksh batch_login.sh 
[oracle@orlrac1 log]$ 
[oracle@orlrac2 log]$ 

[oracle@hcn10g script]$ 
[oracle@hcn10g script]$ grep orcldb1 nohup.out|wc -l
52
[oracle@hcn10g script]$ 
[oracle@hcn10g script]$ grep orcldb2 nohup.out|wc -l
48
[oracle@hcn10g script]$ 

---1号节点分配了52个连接、2号节点分配了48个

[oracle@orlrac1 log]$ grep INSTANCE_NAME listener_orlrac1.log|wc -l
0
[oracle@orlrac1 log]$ 
[oracle@orlrac1 log]$ 
[oracle@orlrac1 log]$ grep establish listener_orlrac1.log |wc -l
100
[oracle@orlrac1 log]$ 
[oracle@orlrac2 log]$ 
[oracle@orlrac2 log]$ grep INSTANCE_NAME listener_orlrac2.log|wc -l
48
[oracle@orlrac2 log]$ 
[oracle@orlrac2 log]$ 
[oracle@orlrac2 log]$ grep establish listener_orlrac2.log |wc -l
48
[oracle@orlrac2 log]$

---1号节点有100个连接请求，而真正建立连接的只有52个，有48转发到了节点2

Huangcn

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
oracle 10g RAC LoadBalance(三)

oracle 10g RAC LoadBalance(一)oracle 10g RAC LoadBalance(二)二、基于服务器端的负载均衡1、确定两个节点的实例都是正常运行的[oracle@orlrac1 rs]$ [oracle@orlrac1 rs]$ ./crs_stat.sh Resource name
复制链接

扫一扫