【RAC】RAC中的負載均衡和故障切換--TAF配置

---

---

涉及到的內容包括：

---

---

 

Oracle RAC 客戶端連線負載均衡(Load Balance)

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

        與負載均衡配置之前的監聽配置請參考
              ORACLE RAC 監聽配置 (listener.ora tnsnames.ora) 
              ORACLE RAC 下非預設埠監聽配置(listener.ora tnsnames.ora)

 

一、客戶端的負載均衡
         客戶端的負載均衡主要是通過為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會從多個地址中隨機地選擇一個地址進行連線，直到連線成功為止。
        注意，此連線方式僅根據地址列表隨機選擇，並不考慮到各個例項上當前真正連線數量的多少，也即是沒有考慮各個節點真實的連線負載情況。

 

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

[sql] view plain copy

 print?

1. 1、伺服器端監聽器配置  
2. oracle@bo2dbp:~> more $ORACLE_HOME/network/admin/listener.ora  --#節點bo2dbp上的listener  
3. # listener.ora.bo2dbp Network Configuration File: /u01/oracle/db/network/admin/listener.ora.bo2dbp  
4. # Generated by Oracle configuration tools.  
5.   
6. LISTENER_NEW_BO2DBP =  
7.   (DESCRIPTION_LIST =  
8.     (DESCRIPTION =  
9.       (ADDRESS = (PROTOCOL = TCP)(HOST = bo2dbp-vip.2gotrade.com)(PORT = 1314)(IP = FIRST))  
10.       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.7.51)(PORT = 1314)(IP = FIRST))  
11.     )  
12.   )  
13. ...........................--#其餘部分省略，注意此處的配置是使用了1314的非預設監聽器埠號     
14.    
15. oracle@bo2dbs:/u01/oracle/db/network/admin> more listener.ora  --#節點bo2dbs上的listener  
16. # listener.ora.bo2dbs Network Configuration File: /u01/oracle/db/network/admin/listener.ora.bo2dbs  
17. # Generated by Oracle configuration tools.  
18.   
19. LISTENER_NEW_BO2DBS =  
20.   (DESCRIPTION_LIST =  
21.     (DESCRIPTION =  
22.       (ADDRESS = (PROTOCOL = TCP)(HOST = bo2dbs-vip.2gotrade.com)(PORT = 1314)(IP = FIRST))  
23.       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.7.52)(PORT = 1314)(IP = FIRST))  
24.     )  
25.   )  
26. ...............  
27.   
28. 2、引數配置  
29. -->instrance ora10g1上的引數配置  
30. SQL> show parameter instance_na  
31.   
32. NAME                                 TYPE        VALUE  
33. ------------------------------------ ----------- ------------------------------  
34. instance_name                        string      ora10g1  
35.   
36. SQL> show parameter listener  
37.   
38. NAME                                 TYPE        VALUE  
39. ------------------------------------ ----------- ------------------------------  
40. local_listener                       string      local_lsnr_ora10g1  
41. remote_listener                      string      remote_lsnr_ora10g  
42.   
43. -->instrance ora10g2上的引數配置  
44. SQL> show parameter instance_name  
45.   
46. NAME                                 TYPE        VALUE  
47. ------------------------------------ ----------- ------------------------------  
48. instance_name                        string      ora10g2  
49.   
50. SQL> show parameter listener  
51.   
52. NAME                                 TYPE        VALUE  
53. ------------------------------------ ----------- ------------------------------  
54. local_listener                       string      local_lsnr_ora10g2  
55. remote_listener                      string      remote_lsnr_ora10g  
56.   
57. -->instrance ora10g2上的監聽資訊  
58. SQL> ho ps -ef | grep lsnr  
59. oracle   17372     1  0 11:00 ?        00:00:00 /u01/oracle/db/bin/tnslsnr LISTENER_NEW_BO2DBS -inherit  
60. oracle   17502 24301  0 12:10 pts/0    00:00:00 /bin/bash -c ps -ef | grep lsnr  
61. oracle   17504 17502  0 12:10 pts/0    00:00:00 grep lsnr  
62.   
63. SQL> ho lsnrctl status LISTENER_NEW_BO2DBS  
64. Listening Endpoints Summary...  
65.   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.7.62)(PORT=1314)))  
66.   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.7.52)(PORT=1314)))  
67. Services Summary...  
68. Service "ora10g" has 2 instance(s).  
69.   Instance "ora10g1", status READY, has 1 handler(s) for this service...  
70.   Instance "ora10g2", status READY, has 2 handler(s) for this service...  
71. ..........  
72.     
73. -->由於只是測試基於客戶端的load balance，因此移出remote_listener引數  
74. SQL> alter system reset remote_listener scope=both sid='*';  
75. alter system reset remote_listener scope=both sid='*'  
76. *  
77. ERROR at line 1:  
78. ORA-32009: cannot reset the memory value for instance * from instance ora10g2  
79.   
80. SQL> alter system reset remote_listener scope=spfile sid='*';  
81.   
82. System altered.  
83.   
84. SQL> ho srvctl stop database -d ora10g  -->關閉資料庫ora10g  
85.   
86. SQL> ho srvctl start database -d ora10g  -->啟動資料庫ora10g使得剛剛修改的remote_listener生效  
87.   
88. SQL> ho lsnrctl status LISTENER_NEW_BO2DBS  -->此時可以看到只有ora10g2註冊到監聽器  
89.   
90. Listening Endpoints Summary...  
91.   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.7.62)(PORT=1314)))  
92.   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.7.52)(PORT=1314)))  
93. Services Summary...  
94. Service "PLSExtProc" has 1 instance(s).  
95.   Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...  
96. Service "ora10g" has 1 instance(s).  
97.   Instance "ora10g2", status READY, has 1 handler(s) for this service...  
98. ..........    
99.   
100. oracle@bo2dbp:~> lsnrctl status LISTENER_NEW_BO2DBP  #同樣在節點bo2dbp也只有ora10g1註冊到監聽器  
101.   
102. Listening Endpoints Summary...  
103.   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.7.61)(PORT=1314)))  
104.   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.7.51)(PORT=1314)))  
105. Services Summary...  
106. Service "ora10g" has 1 instance(s).  
107.   Instance "ora10g1", status READY, has 1 handler(s) for this service...  
108. .........  
109.   
110. 3、客戶端配置  
111. SZDB:~ # ifconfig eth1 | grep "inet addr"|cut -d " " -f12|cut -d : -f2  #客戶端主機的ip  
112. 192.168.7.2  
113.   
114. SZDB:~ # su - oracle  
115. oracle@SZDB:~> cat /etc/hosts     --#客戶端主機新增了RAC上的兩個虛擬節點的ip資訊  
116. 192.168.7.61   bo2dbp-vip.2gotrade.com    bo2dbp-vip  
117. 192.168.7.62   bo2dbs-vip.2gotrade.com    bo2dbs-vip  
118.   
119. oracle@SZDB:~> tail -12 $ORACLE_HOME/network/admin/tnsnames.ora  
120.   
121. ORA10G =  
122.   (DESCRIPTION =  
123.     (ADDRESS = (PROTOCOL = TCP)(HOST = bo2dbp-vip.2gotrade.com)(PORT = 1314))  
124.     (ADDRESS = (PROTOCOL = TCP)(HOST = bo2dbs-vip.2gotrade.com)(PORT = 1314))  
125.     (LOAD_BALANCE = yes)  
126.     (CONNECT_DATA =  
127.       (SERVER = DEDICATED)  
128.       (SERVICE_NAME = ora10g)  
129.     )  
130.   )  

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

[sql] view plain copy

 print?

1. 1、啟用load balance的測試  
2.    
3. oracle@SZDB:~> more load_balance.sh   
4. #!/bin/bash  
5. for i in {1..100}  
6. do  
7. echo $i  
8. sqlplus -S system/oracle@ORA10G <<EOF  
9. select instance_name from v\$instance;  
10. EOF  
11. sleep 1  
12. done  
13. exit 0    
14.   
15. # Author: Robinson Cheng  
16. # Blog :  http://blog.csdn.net/robinson_0612    
17. oracle@SZDB:~> ./load_balance.sh >load_bal.log  
18. oracle@SZDB:~> head -20 load_bal.log  
19. 1  
20.   
21. INSTANCE_NAME  
22. ----------------  
23. ora10g2  
24.   
25. 2  
26.   
27. INSTANCE_NAME  
28. ----------------  
29. ora10g1  
30.   
31. 3  
32.   
33. INSTANCE_NAME  
34. ----------------  
35. ora10g2  
36.   
37. 4  
38.   
39. oracle@SZDB:~> grep ora10g1 load_bal.log |wc -l  
40. 47  
41. oracle@SZDB:~> grep ora10g2 load_bal.log |wc -l  
42. 53  
43.   
44. 從上面的log日誌中可以看出啟用客戶端的負載均衡基本上使得從客戶端發起連線的能夠保持均衡。  
45.     
46. 2、未啟用load balance的測試    
47. 從客戶端的tnsnames.ora中移出(LOAD_BALANCE = yes)選項，然後繼續使用上面的指令碼來測試  
48. oracle@SZDB:~> grep ora10g1 no_load_bal.log |wc -l  
49. 100  
50. oracle@SZDB:~> grep ora10g2 no_load_bal.log |wc -l  
51. 0  
52.   
53. 從上面的日誌中可以看出當移出LOAD_BALANCE = yes項後，所有的使用者連線請求都被定為到ora10g1，這是因為連線請求從tnsnames.ora中選擇  
54. 列在ADDRESS項中排在第一行的位置。  
55. 下面我們關閉例項ora10g1，再來測試連線情形  
56.   
57. oracle@bo2dbp:~> srvctl stop instance -d ora10g -i ora10g1  
58.   
59. oracle@SZDB:~> ./load_balance.sh >no_load_bal_new.log  
60. oracle@SZDB:~> grep ora10g1 no_load_bal_new.log |wc -l  
61. 0  
62. oracle@SZDB:~> grep ora10g2 no_load_bal_new.log |wc -l  
63. 100    
64.   
65. 由於例項ora10g1已經關閉，因此所有的連線請求都被分配到ora10g2。  

 四、總結  
1、客戶端的負載均衡配置較為簡單，僅僅是在客戶端的tnsnames.ora新增 LOAD_BALANCE = yes |on 
2、其連線分配原則是根據tnsnames.ora中連線識別符號下的ADDRESS列表隨機選擇來進行與伺服器之間的連線
3、如果選擇列表中的某個節點listener或instance不可用，則再從剩餘的ADDRESS列表隨機選擇，直到成功為止

Oracle RAC 伺服器端連線負載均衡(Load Balance)

  Oracle RAC伺服器端的負載均衡是根據RAC中各節點的連線負荷數情況，將新的連線請求分配到負荷最小的節點上去。當資料庫處於執行時，RAC中各節點的PMON程式每3秒會將各自節點的連線負荷數更新到service_register。而對於節點中任意監聽器故障或監聽器意外失敗時，PMON程式會每1秒鐘檢查當前節點上的監聽是否重啟，以獲得最新的負載資訊來及時調整負載均衡。本文主要演示suse 10 + oracle 10g rac下的伺服器端的負載均衡。

有關客戶端的負載均衡可參考
      Oracle RAC 客戶端連線負載均衡(Load Balance)
      配置RAC負載均衡與故障轉移
 
有關Oracle 網路配置相關基礎以及概念性的問題請參考：
        配置ORACLE 客戶端連線到資料庫 
        配置非預設埠的動態服務註冊 
        配置sqlnet.ora限制IP訪問Oracle 
       Oracle 監聽器日誌配置與管理 
       設定 Oracle 監聽器密碼(LISTENER)
       Oracle RAC 監聽配置
       ORACLE RAC 下非預設埠監聽配置(listener.ora tnsnames.ora)

一、伺服器端負載均衡配置

  二、測試伺服器端的負載均衡

三、總結
      1、伺服器端的負載均衡需要配置remote_listener引數，而該引數的值依賴於tnsnames.ora的連線字串
      2、對於基於伺服器端的連線負載均衡，監聽器會根據當前節點、例項上的連線負載情況進行轉發到空閒的例項
      3、轉發的依據僅僅是當前節點監聽的連線數量的多少，而非當前例項的過度負載
      4、從上面的測試可以得出，各個節點的連線並不算均衡，是相對的均衡，因此應結合客戶端連線負載協同工作
      5、對於當前例項的過度負載的情形，應結合配置service方法來實現負載均衡  

配置 RAC 負載均衡與故障轉移

[sql] view plain copy

 print?

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.   )                                                                           

[sql] view plain copy

 print?

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

[sql] view plain copy

 print?

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.   )                                                                             

[sql] view plain copy

 print?

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...        

[sql] view plain copy

 print?

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 group by inst_d;                                                    
21.                                                                                                       
22.     INST_ID       COUNT(1)                                                                            
23.     ----------    ----------                                                                          
24.     devdb1        446                                                                                 
25.     devdb2        554                                                                                 

[sql] view plain copy

 print?

1. SALES =                                                                      
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 = sales.robinson.com)                                      
9.       (FAILOVER =      --failover_mode是實現故障轉移的關鍵選項                 
10.         (TYPE = SELECT)                                                          
11.         (METHOD = BASIC)   --TAF 策略：此處當前為 Basic                          
12.         (RETRIES = 180)                                                          
13.         (DELAY = 5)                                                              
14.       )                                                                        
15.     )                                                                          
16.   )                                                                          

[sql] view plain copy

 print?

1. SQL> select instance_name from v$instance;                                          
2.                                                                                     
3. INSTANCE_NAME                                                                       
4. ----------------                                                                    
5. devdb1                                                                              
6.                                                                                     
7. SQL> show parameter service_names                                                   
8.                                                                                     
9. NAME                                 TYPE        VALUE                              
10. ------------------------------------ ----------- ------------------------------     
11. service_names                        string      devdb.robinson.com, sales          
12.                                                                                     
13. SQL> select instance_name from v$instance;                                          
14.                                                                                     
15. INSTANCE_NAME                                                                       
16. ----------------                                                                    
17. devdb2                                                                              
18.                                                                                     
19. SQL> show parameter service                                                         
20.                                                                                     
21. NAME                                 TYPE        VALUE                              
22. ------------------------------------ ----------- ------------------------------     
23. service_names                        string      devdb.robinson.com                 

[sql] view plain copy

 print?

1. SQL> ho srvctl status service -d devdb -s sales                              
2. Service sales is running on instance(s) devdb1                               
3.                                                                              
4. SQL> ho lsnrctl status                                                       
5.                                                                              
6. Service "sales.robinson.com" has 1 instance(s).   --sales正常提供服務        
7.   Instance "devdb1", status READY, has 2 handler(s) for this service...      

[sql] view plain copy

 print?

1. C:\>sqlplus usr1/usr1@sales                                                                         
2. SQL> col host_name format a20                                                                       
3. SQL> select instance_number ins_no,instance_name,host_name,status from v$instance;                  
4.                                                                                                     
5.     INS_NO INSTANCE_NAME    HOST_NAME            STATUS                                               
6. ---------- ---------------- -------------------- ------------                                       
7.          1 devdb1           rac1.robinson.com    OPEN                                                   
8. SQL> select failover_type,failover_method,failed_over from v$session                                
9.   2  where username='USR1';                                                                         
10.                                                                                                     
11. FAILOVER_TYPE FAILOVER_M FAI                                                                        
12. ------------- ---------- ---                                                                        
13. SELECT        BASIC      NO                                                                         
14.                                                                                                     
15. --從其它會話使用sys帳戶登陸到crm 並關閉該例項                                                         
16. SQL> show user;                                                                                     
17. USER is "SYS"                                                                                       
18. SQL> select instance_name from v$instance;                                                          
19.                                                                                                     
20. INSTANCE_NAME                                                                                       
21. ----------------                                                                                    
22. devdb1                                                                                              
23.                                                                                                     
24. SQL> shutdown abort                                                                                 
25. ORACLE instance shut down.                                                                          
26.                                                                                                     
27. --從先前登陸到sales的會話中驗證會話故障切換功能                                                     
28. SQL> select instance_number ins_no,instance_name,host_name,status from v$instance;                  
29.                                                                                                     
30.     INS_NO INSTANCE_NAME    HOST_NAME            STATUS                                               
31. ---------- ---------------- -------------------- ------------                                       
32.                  2 devdb2           rac2.robinson.com    OPEN                                               
33.                                                                                                     
34. SQL> select failover_type,failover_method,failed_over from v$session                                
35.   2  where username='USR1';  --第3列顯示的為yes，也表明經過故障切換後提供的服務                     
36.                                                                                                     
37. FAILOVER_TYPE FAILOVER_M FAI                                                                        
38. ------------- ---------- ---                                                                        
39. SELECT        BASIC      YES                                                                            
40.                                                                                                     
41. --由下面的查詢中可以看到服務名sales被新增到可用例項devdb2，節點rac2 的service_names引數中           
42. SQL> select instance_name from v$instance;                                                          
43.                                                                                                     
44. INSTANCE_NAME                                                                                       
45. ----------------                                                                                    
46. devdb2                                                                                              
47.                                                                                                     
48. SQL> show parameter service                                                                         
49.                                                                                                     
50. NAME                                 TYPE        VALUE                                              
51. ------------------------------------ ----------- ------------------------------                     
52. service_names                        string      devdb.robinson.com, sales                            

[sql] view plain copy

 print?

1. SQL> startup  --啟動devdb1                                                           
2. SQL> show parameter service_names                                                    
3.                                                                                      
4. NAME                                 TYPE        VALUE                               
5. ------------------------------------ ----------- ------------------------------      
6. service_names                        string      devdb.robinson.com                  
7.                                                                                      
8. SQL> ho srvctl relocate service -d devdb -s sales -i devdb2 -t devdb1                
9.                                                                                      
10. SQL> show parameter service_names                                                    
11.                                                                                      
12. NAME                                 TYPE        VALUE                               
13. ------------------------------------ ----------- ------------------------------      
14. service_names                        string      devdb.robinson.com, sales           

[sql] view plain copy

 print?

1. C:\>sqlplus usr1/usr1@sales                                                            
2.                                                                                        
3. SQL> show user;                                                                        
4. USER is "USR1"                                                                         
5. SQL> create table tb_temp (id int,ename varchar2(20)) tablespace tbs1;                 
6.                                                                                          
7. Table created.                                                                         
8.                                                                                        
9. SQL> insert into tb_temp                                                               
10.   2  select 1,'Robinson' from dual                                                     
11.   3  union all                                                                         
12.   4  select 2,'Jackson'  from dual;                                                    
13.                                                                                        
14. 2 rows created.                                                                        
15.                                                                                        
16. SQL> commit;                                                                           
17.                                                                                        
18. Commit complete.                                                                       
19.                                                                                        
20. SQL> select * from tb_temp;                                                            
21.                                                                                        
22.         ID ENAME                                                                           
23. ---------- --------------------                                                        
24.          1 Robinson                                                                        
25.          2 Jackson                                                                         
26.                                                                                        
27. SQL> delete from tb_temp;                                                              
28.                                                                                        
29. 2 rows deleted.                                                                        
30.                                                                                        
31. SQL> select * from tb_temp;                                                            
32.                                                                                        
33. no rows selected                                                                       

[sql] view plain copy

 print?

1. SQL> select * from tb_temp;                                                  
2. select * from tb_temp                                                        
3. *                                                                            
4. ERROR at line 1:                                                             
5. ORA-25402: transaction must roll back                                        
6.                                                                              
7. SQL> rollback;                                                               
8.                                                                              
9. Rollback complete.                                                           
10. SQL> select * from tb_temp;                                                  
11.                                                                              
12.         ID ENAME                                                                 
13. ---------- --------------------                                              
14.          1 Robinson                                                              
15.          2 Jackson                                                               
16.                                                                              
17. SQL> select failover_type,failover_method,failed_over from v$session         
18.   2  where username='USR1';                                                  
19.                                                                              
20. FAILOVER_TYPE FAILOVER_M FAI                                                 
21. ------------- ---------- ---                                                 
22. SELECT        BASIC      YES                                                 

TAF配置

11g RAC環境下客戶端配置TAF

[oracle@rac122 tnslsnr]$ cat /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1               localhost.localdomain localhost
::1             localhost6.localdomain6 localhost6

#Public
10.1.252.121            rac121
10.1.252.122            rac122
#Private
192.168.123.1           rac121-priv
192.168.123.2           rac122-priv

#Virtual IP
10.1.252.75             rac121-vip
10.1.252.76             rac122-vip 10.1.252.68             rac-scan [oracle@rac122 tnslsnr]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Jun 26 15:33:29 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> show parameter service_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      racdb SQL>  

racdb =  
  (DESCRIPTION =  
    (ADDRESS_LIST =  
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.252.68)(PORT = 1521))  
    )  
    (CONNECT_DATA =  
      (SERVICE_NAME = racdb)  
      (FAILOVER_MODE =  
       (TYPE = SELECT)  
       (METHOD = BASIC)  
       (RETRIES = 180)  
       (DELAY = 5))  
    )  
  ) 

C:\Documents and Settings\```>sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on 星期二 6月 26 15:00:56 2012
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> conn sys/system@racdb as sysdba
已連線。
SQL> show parameter instance_name

NAME                      TYPE                    VALUE
------------------------- ----------------------  ---------------
instance_name             string racdb2 SQL> select sid from v$mystat where rownum=1;
       SID
----------
       163
SQL> select machine,failover_type,failover_method,failed_over from v$session where sid=163;
MACHINE           FAILOVER_TYPE       FAILOVER_METHOD       FAILED
----------------  ----------          --------------------  ------
AILK\TANWEI       SELECT              BASIC                 NO

SQL> create table taf_client_test(id int,vname varchar2(10));
表已建立。
SQL> insert into taf_client_test values(1,'taf');
已建立 1 行。
SQL> update taf_client_test set id=5;
已更新 1 行。
SQL> select * from taf_client_test;
        ID VNAME
---------- --------------------
         5 taf 

[oracle@rac122 tnslsnr]$ srvctl status instance -d racdb -i racdb2
Instance racdb2 is running on node rac122
[oracle@rac122 tnslsnr]$ srvctl stop instance -d racdb -i racdb2 -o abort
[oracle@rac122 tnslsnr]$ srvctl status instance -d racdb -i racdb2 Instance racdb2 is not running on node rac122 [oracle@rac122 tnslsnr]$ srvctl status instance -d racdb -i racdb1 Instance racdb1 is running on node rac121 --還有一個節點在工作

SQL> select * from taf_client_test;
select * from taf_client_test
*
第 1 行出現錯誤:
ORA-25402: 事務處理必須重新執行


SQL> rollback;


回退已完成。


SQL> select instance_name from v$instance;


INSTANCE_NAME
--------------------------------
racdb1


SQL> show parameter instance_name




NAME                TYPE            VALUE
----------------    -------------   -----------
instance_name       string          racdb1 --發現自動切換到節點1， SQL> select * from taf_client_test;
未選定行
SQL> select * from v$mystat where rownum=1;


       SID STATISTIC#      VALUE
---------- ---------- ----------
       166          0          0


SQL> select failover_type,failover_method,failed_over from v$session where sid=166;

FAILOVER_TYPE              FAILOVER_METHOD      FAILED
-------------------------- -------------------- ------
SELECT                     BASIC                YES 

如何在RAC伺服器端配置TAF

服務端TAF配置會覆蓋客戶端TNS連線串中配置的TAF。如果客戶端沒有配置TAF，在最小配置模式，failover型別必須設定為啟用TAF。如果failover type是在服務端設定，那麼faliover method預設為BASIC。DELAY和RETRIES引數是可選的，可分別指定。

注意：service name是唯一的，並且不能與預設建立服務名一致。預設情況下，Oracle RAC資料庫會建立一個特殊的資料庫服務。這個預設的服務在RAC環境中對所有例項總是可用，除非例項在限制模式。並且，DBA不能修改這個預設的服務及其屬性。

新增service的語法：

srvctl add service -d orcl -s taf_orcl.oracle.com -r "orcl1,orcl2" -P BASIC

注：引數-d：資料庫名；-s：服務名；-r：首選例項名；-P：TAF策略

srvctl start service -d orcl -s taf_orcl.oracle.com

srvctl config service -d orcl

select name,service_id from dba_services where name = 'taf_orcl.oracle.com';

col failover_method format a11 heading 'METHOD' 
col failover_type format a10 heading 'TYPE' 
col failover_retries format 9999999 heading 'RETRIES' 
col goal format a10 
col clb_goal format a8 
col AQ_HA_NOTIFICATIONS format a5 heading 'AQNOT' 

SQL>select name, failover_method, failover_type, failover_retries,goal, clb_goal,aq_ha_notifications  
from dba_services where service_id = X

注意：預設情況下，method，type，retries是沒有值的，這些值對於服務端的TAF是必須的。這是由於srvctl命令新增服務時，沒有修改DBMS_SERVICE引數，在11.2之後，該bug解決。

--11.2之前：

SQL> execute dbms_service.modify_service (service_name => 'taf_orcl.oracle.com' - 
, aq_ha_notifications => true - 
, failover_method => dbms_service.failover_method_basic - 
, failover_type => dbms_service.failover_type_select - 
, failover_retries => 180 - 
, failover_delay => 5 - 
, clb_goal => dbms_service.clb_goal_long); 

PL/SQL procedure successfully completed.

注：由於TAF的method為BASIC，BASIC是當前支援的唯一值，不再支援PRECONNECT。

--11.2開始：

srvctl add service -d db_unique_name -s service_name -t edition_name{-r preferred_list [-a available_list]} | {-g server_pool[-c {UNIFORM | SINGLETON}] [-k net_number]}
[-P {BASIC | NONE}] [-l {[PRIMARY] | [PHYSICAL_STANDBY] | [LOGICAL_STANDBY] |
[SNAPSHOT_STANDBY]}] [-y {AUTOMATIC | MANUAL}] [-q {TRUE | FALSE}]
[-x {TRUE | FALSE}] [-j {SHORT | LONG}] [-B {NONE | SERVICE_TIME | THROUGHPUT}]
 [-e {NONE |SESSION | SELECT}] [-m {NONE | BASIC}] [-z failover_retries]
 [-w failover_delay]

SQL>select name, failover_method, failover_type, failover_retries,goal, clb_goal,aq_ha_notifications 
from dba_services where service_id = X

lsnrctl services

當然，還可以通過DBCA或者Oracle OEM來配置RAC Server。本文不再詳述。

配置Server Side TAF

[oracle@jyrac1 ~]$ srvctl add service -d jyzhao -s server_taf -r "jyzhao1,jyzhao2" -P BASIC
[oracle@jyrac1 ~]$ 

[grid@jyrac1 ~]$ srvctl add service -d jyzhao -s server_taf -r "jyzhao1,jyzhao2" -P BASIC
PRCD-1288 : User is not authorized to create service server_taf for database jyzhao
PRKH-1014 : Current user "grid" is not the oracle owner user "oracle" of oracle home "/opt/app/oracle/product/11.2.0/dbhome_1"

[oracle@jyrac1 ~]$ srvctl start service -d jyzhao -s server_taf

[oracle@jyrac1 ~]$ srvctl config service -d jyzhao
Service name: server_taf
Service is enabled
Server pool: jyzhao_server_taf Cardinality: 2 Disconnect: false Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false AQ HA notifications: false Failover type: NONE
Failover method: NONE
TAF failover retries: 0 TAF failover delay: 0 Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: NONE
TAF policy specification: BASIC Edition: Preferred instances: jyzhao1,jyzhao2
Available instances: 

SQL> select name,service_id from dba_services where name = 'server_taf'; NAME SERVICE_ID
---------------------------------------------------------------- ----------
server_taf 7

SQL> col name format a15  
SQL> col failover_method format a11 heading 'METHOD' SQL> col failover_type format a10 heading 'TYPE' SQL> col failover_retries format 9999999 heading 'RETRIES' SQL> col goal format a10 
SQL> col clb_goal format a8 
SQL> col AQ_HA_NOTIFICATIONS format a5 heading 'AQNOT' SQL> select name, failover_method, failover_type, failover_retries,goal, clb_goal,aq_ha_notifications 2 from dba_services where service_id = 7 3 ; NAME METHOD TYPE RETRIES GOAL       CLB_GOAL AQNOT
--------------- ----------- ---------- -------- ---------- -------- -----
server_taf NONE NONE 0 NONE LONG     NO

SQL> 

SQL> execute dbms_service.modify_service (service_name => 'server_taf' - 
> , aq_ha_notifications => true - 
> , failover_method => dbms_service.failover_method_basic - 
> , failover_type => dbms_service.failover_type_select - 
> , failover_retries => 180 - 
> , failover_delay => 5 - 
> , clb_goal => dbms_service.clb_goal_long); 

PL/SQL procedure successfully completed.

SQL> select name, failover_method, failover_type, failover_retries,goal, clb_goal,aq_ha_notifications 2 from dba_services where service_id = 7; NAME METHOD TYPE RETRIES GOAL       CLB_GOAL AQNOT
--------------- ----------- ---------- -------- ---------- -------- -----
server_taf      BASIC SELECT 180 NONE LONG     YES

--node1: Service "server_taf" has 1 instance(s).
  Instance "jyzhao1", status READY, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 state:ready LOCAL SERVER The command completed successfully --node2: Service "server_taf" has 1 instance(s). Instance "jyzhao2", status READY, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 state:ready LOCAL SERVER The command completed successfully

SERVERTAF =
  (DESCRIPTION =
    (LOAD_BALANCE = yes)
    (ADDRESS = (PROTOCOL = TCP)(HOST = jyrac1)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = jyrac2)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = server_taf)
    )
  )

[grid@jyrac1 ~]$ sqlplus system/oracle@192.168.56.160/server_taf

SQL*Plus: Release 11.2.0.4.0 Production on Fri Mar 10 02:59:53 2017 Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> select host_name,instance_name from v$instance;

HOST_NAME ---------------------------------------------------------------- INSTANCE_NAME ---------------- jyrac1
jyzhao1 --這裡強制關掉jyzhao1例項。 SQL> /

HOST_NAME ---------------------------------------------------------------- INSTANCE_NAME ---------------- jyrac2
jyzhao2

SQL> select host_name,instance_name from v$instance;

HOST_NAME ---------------------------------------------------------------- INSTANCE_NAME ---------------- jyrac1
jyzhao1 --這裡強制關掉jyzhao1例項。 SQL> / select host_name,instance_name from v$instance
*
ERROR at line 1:
ORA-12153: TNS:not connected
Process ID: 20116 Session ID: 24 Serial number: 7

TAF =
  (DESCRIPTION =
    (LOAD_BALANCE = yes)
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.150)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.152)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = server_taf)
    )
  )

[oracle@jyrac2 admin]$ sqlplus system/oracle@taf

SQL*Plus: Release 11.2.0.4.0 Production on Fri Mar 10 05:11:30 2017 Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> select host_name,instance_name from v$instance;

HOST_NAME ---------------------------------------------------------------- INSTANCE_NAME ---------------- jyrac2
jyzhao2 --這裡強制關掉jyzhao2例項。 SQL> /

HOST_NAME ---------------------------------------------------------------- INSTANCE_NAME ---------------- jyrac1
jyzhao1

[grid@jyrac1 ~]$ sqlplus system/oracle@192.168.56.151/server_taf

SQL*Plus: Release 11.2.0.4.0 Production on Fri Mar 10 04:32:20 2017 Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> select host_name,instance_name from v$instance;

HOST_NAME
----------------------------------------------------------------
INSTANCE_NAME
----------------
jyrac1
jyzhao1


SQL> / select host_name,instance_name from v$instance
* ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 32459 Session ID: 159 Serial number: 3

TAFVIP =
  (DESCRIPTION =
    (LOAD_BALANCE = yes)
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.151)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.153)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = server_taf)
    )
  )

[oracle@jyrac2 admin]$ sqlplus system/oracle@tafvip

SQL*Plus: Release 11.2.0.4.0 Production on Fri Mar 10 05:15:32 2017 Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> select host_name,instance_name from v$instance;

HOST_NAME ---------------------------------------------------------------- INSTANCE_NAME ---------------- jyrac2
jyzhao2 --這裡強制關掉jyzhao2例項。 SQL> /

HOST_NAME ---------------------------------------------------------------- INSTANCE_NAME ---------------- jyrac1
jyzhao1

官方文件：http://docs.oracle.com/cd/E11882_01/network.112/e41945/advcfg.htm#NETAG455

sales.us.example.com=
 (DESCRIPTION= (LOAD_BALANCE=on) (FAILOVER=on)  (ADDRESS=
       (PROTOCOL=tcp)  
       (HOST=sales1-server)  
       (PORT=1521)) 
  (ADDRESS=
       (PROTOCOL=tcp)  
       (HOST=sales2-server)  
       (PORT=1521)) 
  (CONNECT_DATA=
     (SERVICE_NAME=sales.us.example.com) (FAILOVER_MODE= (TYPE=select)  (METHOD=basic))))

sales.us.example.com=
 (DESCRIPTION=
  (ADDRESS=
       (PROTOCOL=tcp)  
       (HOST=sales1-server)  
       (PORT=1521)) 
  (CONNECT_DATA=
     (SERVICE_NAME=sales.us.example.com) (FAILOVER_MODE= (TYPE=select)  (METHOD=basic) (RETRIES=20) (DELAY=15))))

sales1.us.example.com=
 (DESCRIPTION=
  (ADDRESS=
       (PROTOCOL=tcp)  
       (HOST=sales1-server)  
       (PORT=1521)) 
  (CONNECT_DATA=
     (SERVICE_NAME=sales.us.example.com) 
     (INSTANCE_NAME=sales1) (FAILOVER_MODE= (BACKUP=sales2.us.example.com) (TYPE=select)  (METHOD=preconnect))))
sales2.us.example.com=
 (DESCRIPTION=
  (ADDRESS=
       (PROTOCOL=tcp)  
       (HOST=sales2-server)  
       (PORT=1521)) 
  (CONNECT_DATA=
     (SERVICE_NAME=sales.us.example.com) 
     (INSTANCE_NAME=sales2) (FAILOVER_MODE= (BACKUP=sales1.us.example.com) (TYPE=select)  (METHOD=preconnect))))

SELECT MACHINE, FAILOVER_TYPE, FAILOVER_METHOD, FAILED_OVER, COUNT(*)
FROM V$SESSION
GROUP BY MACHINE, FAILOVER_TYPE, FAILOVER_METHOD, FAILED_OVER;

MACHINE              FAILOVER_TYPE FAILOVER_M FAI   COUNT(*)
-------------------- ------------- ---------- --- ----------
sales1               NONE          NONE       NO          11
sales2               SELECT        PRECONNECT NO           1

MACHINE              FAILOVER_TYPE FAILOVER_M FAI   COUNT(*)
-------------------- ------------- ---------- --- ----------
sales2               NONE          NONE       NO          10
sales2               SELECT        PRECONNECT YES          1

Oracle RAC failover 測試(Server TAF方式)

  Oracle RAC中，除了基於客戶端的TAF方式之外，還有基於伺服器端的TAF方式，可以把服務端的TAF方式看作是客戶端TAF方式的一個升級版吧。伺服器端的TAF，當然是需要在伺服器端進行配置了，這個是通過Service來完成的。本文主要描述Oracle 10g rac 下通過service方式配置伺服器端的TAF。

  下面是一些關於這方面的基礎參考連結：
  有關負監聽配置，載均衡(load balance)請參考
    ORACLE RAC 監聽配置 (listener.ora tnsnames.ora) 
    ORACLE RAC 下非預設埠監聽配置(listener.ora tnsnames.ora) 
    Oracle RAC 客戶端連線負載均衡(Load Balance)
    Oracle RAC 伺服器端連線負載均衡(Load Balance)
    Oracle RAC 負載均衡測試(結合伺服器端與客戶端) 
    
  有關Oracle RAC failover 連線時故障轉移請參考
    Oracle RAC failover 測試(連線時故障轉移)
    Oracle RAC failover 測試(TAF方式)
    
  有關Services的建立請參考
    Services in Oracle Database 10g 
    再說 Oracle RAC services

---

---

Oracle RAC 負載均衡測試(結合伺服器端與客戶端)

      Oracle RAC 負載均衡使得從客戶端發起的連線能夠有效地分配到監聽器負載較小的例項上。有兩種方式實現客戶端負載均衡，一是通過配置客戶端的load_balance，一是通過配置伺服器端的remote_listener引數。兩種方式各有優劣，而且兩者並不相互排斥，因此可以結合兩種方式來更加有效的實現負載均衡。本文將描述兩者結合的使用情況(oralce 10g rac)。

        有關客戶端與服務端負載均衡的單獨測試請參考：
              Oracle RAC 客戶端連線負載均衡(Load Balance) 
              Oracle RAC 伺服器端連線負載均衡(Load Balance)

        本文的測試將結合前篇文章使用的指令碼與樣例，是前兩篇測試的一個總結。

一、配置需求

二、測試Load Balance

Oracle RAC failover 測試(連線時故障轉移)

  Oracle RAC 叢集最突出的表現就是高可用性，這些內容主要包括load balance以及failover，通過這些技術使得單點故障不影響客戶端端應用程式對資料庫的正常訪問，以及通過建立service實現節點間負載均衡。本文主要描述Oracle 10g rac環境下的Oracle failover測試。
  
  下面是一些關於這方面的基礎參考或相關連結：
  有關負監聽配置，載均衡(load balance)以及Oracle service請參考
    ORACLE RAC 監聽配置 (listener.ora tnsnames.ora) 
    ORACLE RAC 下非預設埠監聽配置(listener.ora tnsnames.ora) 
    Oracle RAC 客戶端連線負載均衡(Load Balance)
    Oracle RAC 伺服器端連線負載均衡(Load Balance)
    Oracle RAC 負載均衡測試(結合伺服器端與客戶端)
    Oracle RAC failover 測試(TAF方式) 
    Oracle RAC failover 測試(Server TAF方式)

---

---

Oracle RAC failover 測試(TAF方式)

 Oracle RAC 客戶端故障轉移(failover)，當採用TAF方式時，對於已經建立連線的客戶端，在連線的例項或節點出現故障時，客戶端無需再次發出連線請求，仍然可以繼續之前的資料庫操作，此稱之為透明故障轉移。本文描述基於Oracle 10g rac，客戶端TAF方式的故障轉移並給出示例。

  下面是一些關於這方面的基礎參考連結：
  有關負監聽配置，載均衡(load balance)以及Oracle service請參考
    ORACLE RAC 監聽配置 (listener.ora tnsnames.ora) 
    ORACLE RAC 下非預設埠監聽配置(listener.ora tnsnames.ora) 
    Oracle RAC 客戶端連線負載均衡(Load Balance)
    Oracle RAC 伺服器端連線負載均衡(Load Balance)
    Oracle RAC 負載均衡測試(結合伺服器端與客戶端) 
    
  有關Oracle RAC failover 連線時故障轉移請參考
    Oracle RAC failover 測試(連線時故障轉移)
    Oracle RAC failover 測試(Server TAF方式)

About Me

............................................................................................................................... ● 本文整理自網路 ● 本文在itpub（http://blog.itpub.net/26736162）、部落格園（http://www.cnblogs.com/lhrbest）和個人微信公眾號（xiaomaimiaolhr）上有同步更新 ● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/ ● 本文部落格園地址：http://www.cnblogs.com/lhrbest ● 本文pdf版及小麥苗雲盤地址：http://blog.itpub.net/26736162/viewspace-1624453/ ● 資料庫筆試面試題庫及解答：http://blog.itpub.net/26736162/viewspace-2134706/ ● QQ群：230161599     微信群：私聊 ● 聯絡我請加QQ好友(646634621)，註明新增緣由 ● 於 2017-07-01 09:00 ~ 2017-07-31 22:00 在魔都完成 ● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解 ● 版權所有，歡迎分享本文，轉載請保留出處 ............................................................................................................................... 拿起手機使用微信客戶端掃描下邊的左邊圖片來關注小麥苗的微信公眾號：xiaomaimiaolhr，掃描右邊的二維碼加入小麥苗的QQ群，學習最實用的資料庫技術。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-2142102/，如需轉載，請註明出處，否則將追究法律責任。