ORACLE RAC均衡負載

    oracle集群數據庫有些時候并沒有實現自動負載均衡，基本所有業務都在某一個節點上，所以容易造成某一個節點宕機的情況，針對這種情況我們就需要手動為期配置負載均衡。針對負載均衡配置有在服務器端和客戶端兩種入手方法（但是我發現主要還是服務器端配置效果更明顯）

    一、服務器端配置

      1、首先要在每個節點服務器上tnsnames.ora文件進行如下，注意看紅色部分，而且我建議在HOST后面的值直接寫IP地址，如果寫hosts文件的主機名的話，有可能會出現TNS連接超時的情況。

RACDB =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.230.153)(PORT = 1521))/*一節點的IP*/

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.230.154)(PORT = 1521))/*二節點的IP*/

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.230.155)(PORT = 1521))/*如果是11G的話，需要把scan ip也寫上，不然你用scan ip連不上去*/

    (LOAD_BALANCE = yes)                                             /*這個參數必須添加，不然沒法實現均衡負載*/

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = RACDB)

    )

  )

LISTENERS_RACDB =

  (ADDRESS_LIST =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.230.153)(PORT = 1521))

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.230.154)(PORT = 1521))

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.230.155)(PORT = 1521))

  )

      2、任意一個節點執行如下操作。在初始化參數中設置參數remote_listeners

SQL> conn system/admin@racdb 

已連接。

SQL> show parameter remote_listener

NAME                                 TYPE        VALUE

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

remote_listener                      string                                         /*可以看到當前的值為空*/

SQL>

SQL> alter system set remote_listener='LISTENERS_RACDB' sid='*';                   /*這個值一定要和前面tnsnames.ora文件里的一樣*/

#（reset命令可以撤銷設置，恢復默認值）                                          

系統已更改。

 show parameter remote_listener

NAME                                 TYPE        VALUE

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

remote_listener                      string      LISTENERS_RACDB                     /*可以看到剛才修改的已經生效*/

    3、現在可以看下監聽信息，正確配置參數后，通過lsnrctl status命令看到在監聽啟動以后，可以看到監聽器上有2個instance。

[grid@node01 bin]# lsnrctl status

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 18-DEC-2014 05:40:08

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

STATUS of the LISTENER

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

Alias                     LISTENER_NODE01

Version                   TNSLSNR for Linux: Version 10.2.0.1.0 - Production

Start Date                18-DEC-2014 00:18:07

Uptime                    0 days 15 hr. 22 min. 1 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /orac/orahome/oracle/product/11.2.0/db_1/network/admin/listener.ora

Listener Log File         /orac/orahome/oracle/product/11.2.0/db_1/network/log/listener_node01.log

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.230.153)(PORT=1521)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.230.154)(PORT=1521)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.230.155)(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 "RACDB" has 2 instance(s).

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

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

Service "RACDBXDB" has 2 instance(s).

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

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

Service "RACDB_XPT" has 2 instance(s).

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

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

The command completed successfully

二、客戶端配置   

 在每個客戶端或者JDBC的程序都用LOAD_BALANCE = yes的參數即可

                 客戶端的tnsnames.ora如下修改

RAC_DB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.230.153)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.230.154)(PORT = 1521))
     (LOAD_BALANCE = yes)
      (FAILOVER = on)
    )
    (CONNECT_DATA =
      (SERVICE_NAME = RAC_DB)
     (FAILOVER_MODE = (TYPE = SELECT)
                       (METHOD = BASIC)
                       (RETRIES = 20)
                       (DELAY = 10)
      )
    )
  )

標紅的這幾個參數代表意義：

LOAD_BALANCE=YES 表示的負載均衡，而且這個應該是客戶端的負載均衡，就是隨機的進行連接分配。

FAILOVER =ON 表示的是客戶端連接時的故障轉移。也就是說在建立會話的時候如果發現一個節點出現故障就去檢測另外的節點。默認就是開啟的。但是現在ORACLE一般使用TAF，就是說即使在連接期間出現故障，也會轉移到其他節點繼續操作
failover_mode 有幾個參數TYPE MOTHOD RETRIES DELAY。
mothod 取值 basic preconnect(前者是在感知了錯誤才建立到其他節點的連接，后者是預先建立到其他節點的連接）
TYPE 取值 SELECT或 SESSION（前者在故障節點的查詢結果不會拋棄，在新節點繼續余下的操作，后者就是重新進行操作，拋棄以前的操作）
retries就是重試次數
DELAY 重試間隔時間，也可以使用SERVICE來做服務端的 TAF。