Oracle教程：配置 RAC 负载均衡与故障转移

Oracle教程：配置 RAC 负载均衡与故障转移

Oracle负载均衡主要是指新会话连接到RAC数据库时,如何判定这个新的连接要连到哪个节点进行工作?通常情况下，负载均衡分为客户端负载均衡与服务器端负载均衡。客户端负载均衡通常是在客户端的tnsnames.ora中多添加一个链接地址以及LOAD_BALANCE与failover参数。而服务器端的负载均衡则相对复杂，下面具体描述服务器端负载均衡。

一、负载均衡
    注意这里的负载均衡指的是连接的负载均衡，即客户可以随机从不同的实例中连接到数据库
    1.配置tnsnames.ora使得该文件中包含如下全部内容：

1. # LISTENERS_DEVDB DEVDB是数据库名，可以使用netmgr,netca编辑或直接使用Vim创建  
2. LISTENERS_DEVDB =                                                             
3.   (ADDRESS_LIST =                                                             
4.     (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.robinson.com)(PORT = 1521))     
5.     (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.robinson.com)(PORT = 1521))     
6.   )                                                                           

    2.配置参数文件remote_listener

1. SQL> alter system set remote_listener='LISTENERS_DEVDB' scope=both sid='*';   

    3.需要配置连接描述信息的两个IP地址、端口号、以及load_balance子项为yes (主要是load_balance子项)

1. DEVDB =                                                                         
2.   (DESCRIPTION =                                                                
3.     (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.robinson.com)(PORT = 1521))       
4.     (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.robinson.com)(PORT = 1521))       
5.     (LOAD_BALANCE = yes)                                                          
6.     (CONNECT_DATA =                                                               
7.       (SERVER = DEDICATED)                                                        
8.       (SERVICE_NAME = devdb.robinson.com)                                         
9.     )                                                                             
10.   )                                                                             

    4.查看侦听器的状态，从下面可以看到devdb.robinson.com服务中有两个实例为其提供服务 

1. [oracle@rac2 ~]$ lsnrctl status                                                  
2.     Service "devdb.robinson.com" has 2 instance(s).                                
3.       Instance "devdb1", status READY, has 1 handler(s) for this service...        
4.       Instance "devdb2", status READY, has 2 handler(s) for this service...        

    5.测试负载均衡
    使用shell脚本来进行测试负载均衡 

1. --编辑TestLoadBalance.sh                                                                             
2.     #!/bin/bash                                                                                       
3.     #Usage: TestLoadBalance devdb 1000                                                                
4.     count=0                                                                                           
5.     while [ $count -lt $2 ]   # Set up a loop control                                                 
6.     do                        # Begin the loop                                                        
7.         count='expr $count + 1' # Increment the counter                                                 
8.         sqlplus -s usr1/usr1pwd@$1 @TestLoadBalance.sql  # Connect instance and execute sql statement   
9.         sleep 1                                                                                         
10.     done                                                                                              
11.                                                                                                     
12. --TestLoadBalance.sql 脚本                                                                           
13.     col instance_name format a30                                                                      
14.     select instance_name from v$instance;                                                             
15.                                                                                                     
16. --实施测试                                                                                           
17.     ./TestLoadBalance.sh devdb 1000                                                                   
18.                                                                                                       
19. --查看结果                                                                                           
20.     SQL> select inst_id,count(1) from gv$instance;                                                    
21.                                                                                                       
22.     INST_ID       COUNT(1)                                                                            
23.     ----------    ----------                                                                           
24.     devdb1        446                                                                                 
25.     devdb2        554