Oracle RAC 用戶端串連負載平衡

   實現負載平衡(Load Balance)是Oracle RAC最重要的特性之一，主要是把負載平均分配到叢集中的各個節點，以提高系統的整體吞吐能力。通常情況下有兩種方式來實現負載平衡，一個是基於用戶端串連的負載平衡，一個是基於伺服器端監聽器(Listener)收集到的資訊來將新的串連請求分配到串連數較少執行個體上的實現方式。本文主要討論的是基於用戶端串連的負載平衡，並給出示範。

一、用戶端的負載平衡

        用戶端的負載平衡主要是通過為tnsnames.ora增加load_balance=yes條目來實現，下面看看oracle(Note:226880.1)的解釋

       The client load balancing feature enables clients to randomize connection requests among the listeners. Oracle Net progresses through the list of

       protocol addresses in a random sequence, balancing the load on the various listeners. Without client load balancing, Oracle Net progresses through the

       list of protocol addresses sequentially until one succeeds.  This normally is referred to connect-time load balance.

       從上面的描述中可以得知，如果未開啟load_balance=yes時，Oracle Net會根據地址清單按順序來選擇一個進行串連，直到串連成功為止。  

       如果第一個host主機串連失敗，在有多個地址的情形下，接下來選擇第二個地址串連，依此類推，直到串連成功為止。

       當開啟了load_balance=yes時，則Oracle Net會從多個地址中隨機地選擇一個地址進行串連，直到串連成功為止。

       注意，此串連方式僅根據地址清單隨機播放，並不考慮到各個執行個體上當前真正串連數量的多少，也即是沒有考慮各個節點真實的串連負載情況。

二、伺服器與用戶端的配置情況

1、伺服器端監聽器配置  

oracle@bo2dbp:~> more $ORACLE_HOME/network/admin/listener.ora  --#節點bo2dbp上的listener  

# listener.ora.bo2dbp Network Configuration File: /u01/oracle/db/network/admin/listener.ora.bo2dbp  

# Generated by Oracle configuration tools.  

LISTENER_NEW_BO2DBP =  

 (DESCRIPTION_LIST =  

   (DESCRIPTION =  

     (ADDRESS = (PROTOCOL = TCP)(HOST = bo2dbp-vip.2gotrade.com)(PORT = 1314)(IP = FIRST))  

     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.7.51)(PORT = 1314)(IP = FIRST))  

   )  

 )  

...........................--#其餘部分省略，注意此處的配置是使用了1314的非預設監聽器連接埠號碼      

oracle@bo2dbs:/u01/oracle/db/network/admin> more listener.ora  --#節點bo2dbs上的listener  

# listener.ora.bo2dbs Network Configuration File: /u01/oracle/db/network/admin/listener.ora.bo2dbs  

# Generated by Oracle configuration tools.  

LISTENER_NEW_BO2DBS =  

 (DESCRIPTION_LIST =  

   (DESCRIPTION =  

     (ADDRESS = (PROTOCOL = TCP)(HOST = bo2dbs-vip.2gotrade.com)(PORT = 1314)(IP = FIRST))  

     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.7.52)(PORT = 1314)(IP = FIRST))  

   )  

 )  

...............  

2、參數配置  

-->instrance ora10g1上的參數配置  

SQL> show parameter instance_na  

NAME                                 TYPE        VALUE  

------------------------------------ ----------- ------------------------------  

instance_name                        string      ora10g1  

SQL> show parameter listener  

NAME                                 TYPE        VALUE  

------------------------------------ ----------- ------------------------------  

local_listener                       string      local_lsnr_ora10g1  

remote_listener                      string      remote_lsnr_ora10g  

-->instrance ora10g2上的參數配置  

SQL> show parameter instance_name  

NAME                                 TYPE        VALUE  

------------------------------------ ----------- ------------------------------  

instance_name                        string      ora10g2  

SQL> show parameter listener  

NAME                                 TYPE        VALUE  

------------------------------------ ----------- ------------------------------  

local_listener                       string      local_lsnr_ora10g2  

remote_listener                      string      remote_lsnr_ora10g  

-->instrance ora10g2上的監聽資訊  

SQL> ho ps -ef | grep lsnr  

oracle   17372     1  0 11:00 ?        00:00:00 /u01/oracle/db/bin/tnslsnr LISTENER_NEW_BO2DBS -inherit  

oracle   17502 24301  0 12:10 pts/0    00:00:00 /bin/bash -c ps -ef | grep lsnr  

oracle   17504 17502  0 12:10 pts/0    00:00:00 grep lsnr  

SQL> ho lsnrctl status LISTENER_NEW_BO2DBS  

Listening Endpoints Summary...  

 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.7.62)(PORT=1314)))  

 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.7.52)(PORT=1314)))  

Services Summary...  

Service "ora10g" has 2 instance(s).  

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

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

..........  

-->由於只是測試基於用戶端的load balance，因此移出remote_listener參數  

SQL> alter system reset remote_listener scope=both sid='*';  

alter system reset remote_listener scope=both sid='*'  

*  

ERROR at line 1:  

ORA-32009: cannot reset the memory value for instance * from instance ora10g2  

SQL> alter system reset remote_listener scope=spfile sid='*';  

System altered.  

SQL> ho srvctl stop database -d ora10g  -->關閉資料庫ora10g  

SQL> ho srvctl start database -d ora10g  -->啟動資料庫ora10g使得剛剛修改的remote_listener生效  

SQL> ho lsnrctl status LISTENER_NEW_BO2DBS  -->此時可以看到只有ora10g2註冊到監聽器  

Listening Endpoints Summary...  

 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.7.62)(PORT=1314)))  

 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.7.52)(PORT=1314)))  

Services Summary...  

Service "PLSExtProc" has 1 instance(s).  

 Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...  

Service "ora10g" has 1 instance(s).  

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

..........    

oracle@bo2dbp:~> lsnrctl status LISTENER_NEW_BO2DBP  #同樣在節點bo2dbp也只有ora10g1註冊到監聽器  

Listening Endpoints Summary...  

 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.7.61)(PORT=1314)))  

 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.7.51)(PORT=1314)))  

Services Summary...  

Service "ora10g" has 1 instance(s).  

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

.........  

3、用戶端配置  

SZDB:~ # ifconfig eth1 | grep "inet addr"|cut -d " " -f12|cut -d : -f2  #用戶端主機的ip  

192.168.7.2  

SZDB:~ # su - oracle  

oracle@SZDB:~> cat /etc/hosts     --#用戶端主機添加了RAC上的兩個虛擬節點的ip資訊  

192.168.7.61   bo2dbp-vip.2gotrade.com    bo2dbp-vip  

192.168.7.62   bo2dbs-vip.2gotrade.com    bo2dbs-vip  

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

ORA10G =  

 (DESCRIPTION =  

   (ADDRESS = (PROTOCOL = TCP)(HOST = bo2dbp-vip.2gotrade.com)(PORT = 1314))  

   (ADDRESS = (PROTOCOL = TCP)(HOST = bo2dbs-vip.2gotrade.com)(PORT = 1314))  

   (LOAD_BALANCE = yes)  

   (CONNECT_DATA =  

     (SERVER = DEDICATED)  

     (SERVICE_NAME = ora10g)  

   )  

 )

三、測試負載平衡(load balance)

1、啟用load balance的測試  

oracle@SZDB:~> more load_balance.sh  

#!/bin/bash  

for i in {1..100}  

do  

echo $i  

sqlplus -S system/oracle@ORA10G <<EOF  

select instance_name from v\$instance;  

EOF  

sleep 1  

done  

exit 0    

# Author: Robinson Cheng  

oracle@SZDB:~> ./load_balance.sh >load_bal.log  

oracle@SZDB:~> head -20 load_bal.log  

1  

INSTANCE_NAME  

----------------  

ora10g2  

2  

INSTANCE_NAME  

----------------  

ora10g1  

3  

INSTANCE_NAME  

----------------  

ora10g2  

4  

oracle@SZDB:~> grep ora10g1 load_bal.log |wc -l  

47  

oracle@SZDB:~> grep ora10g2 load_bal.log |wc -l  

53  

從上面的log日誌中可以看出啟用用戶端的負載平衡基本上使得從用戶端發起串連的能夠保持均衡。  

2、未啟用load balance的測試    

從用戶端的tnsnames.ora中移出(LOAD_BALANCE = yes)選項，然後繼續使用上面的指令碼來測試  

oracle@SZDB:~> grep ora10g1 no_load_bal.log |wc -l  

100  

oracle@SZDB:~> grep ora10g2 no_load_bal.log |wc -l  

0  

從上面的日誌中可以看出當移出LOAD_BALANCE = yes項後，所有的使用者串連請求都被定為到ora10g1，這是因為串連請求從tnsnames.ora中選擇  

列在ADDRESS項中排在第一行的位置。  

下面我們關閉執行個體ora10g1，再來測試連接情形  

oracle@bo2dbp:~> srvctl stop instance -d ora10g -i ora10g1  

oracle@SZDB:~> ./load_balance.sh >no_load_bal_new.log  

oracle@SZDB:~> grep ora10g1 no_load_bal_new.log |wc -l  

0  

oracle@SZDB:~> grep ora10g2 no_load_bal_new.log |wc -l  

100    

由於執行個體ora10g1已經關閉，因此所有的串連請求都被分配到ora10g2。

四、總結

1、用戶端的負載平衡配置較為簡單，僅僅是在用戶端的tnsnames.ora添加 LOAD_BALANCE = yes |on

2、其串連分配原則是根據tnsnames.ora中串連標識符下的ADDRESS列表隨機播放來進行與伺服器之間的串連

3、如果挑選清單中的某個節點listener或instance不可用，則再從剩餘的ADDRESS列表隨機播放，直到成功為止

oracle視頻教程請關注:http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html