關於11G 客戶端連線資料庫 SCAN 和 Transparent Application Failover（TAF）

張玉龍發表於2016-10-19
客戶端資料庫APPAI


一、客戶端通過SCAN IP連線資料庫

  客戶端配置：

214 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.16.214)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )
  客戶端連線 

C:\Users\zylong>sqlplus sys/oracle@214 as sysdba

SQL> select host_name,instance_name from v$instance;

HOST_NAME                 INSTANCE_NAME
------------------------- ----------------
0906rac2                  orcl2

  例項orcl2發生故障

SQL> shutdown abort
ORACLE instance shut down.
  此時客戶端的連線斷開，需要重新連線資料庫來實現故障轉移 

SQL> select host_name,instance_name from v$instance;
ORA-03113: 通訊通道的檔案結尾
程式 ID: 12874
會話 ID: 50 序列號: 49

SQL> select host_name,instance_name from v$instance;
ERROR:
ORA-03114: 未連線到 ORACLE

SQL> exit

C:\Users\zylong>sqlplus sys/oracle@214 as sysdba

SQL> select host_name,instance_name from v$instance;

HOST_NAME                 INSTANCE_NAME
------------------------- ----------------
0906rac1                  orcl1


二、客戶端通過VIP連線資料庫

  客戶端配置：

212-213 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.16.212)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.16.213)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )
注：
客戶端連線會一直連上面的IP（212），後面不管開多少個連線都會連到第一個IP（212）上
當客戶端連線一個例項後，如果這個例項掛掉，客戶端不能自動切換到另一個節點，需要客戶端斷掉後重新連線才能連線到另一個節點
   客戶端連線 

C:\Users\zylong>sqlplus sys/oracle@212-213 as sysdba

SQL> select host_name,instance_name from v$instance;

HOST_NAME                 INSTANCE_NAME
------------------------- ----------------
0906rac1                  orcl1
  例項orcl1發生故障
SQL> shutdown abort
ORACLE instance shut down.
  此時客戶端的連線斷開，需要重新連線資料庫來實現故障轉移 

SQL> select host_name,instance_name from v$instance;
ORA-03113: 通訊通道的檔案結尾
程式 ID: 14841
會話 ID: 35 序列號: 11

SQL> select host_name,instance_name from v$instance;
ERROR:
ORA-03114: 未連線到 ORACLE

SQL> exit

C:\Users\zylong>sqlplus sys/oracle@212-213 as sysdba

SQL> select host_name,instance_name from v$instance;

HOST_NAME                 INSTANCE_NAME
------------------------- ----------------
0906rac2                  orcl2

三、客戶端通過Client Side TAF連線資料庫,使用BASIC方式
  客戶端配置： 

212_213 =
  (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.16.213)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.16.212)(PORT = 1521))
      (LOAD_BALANCE = on)
      (CONNECT_DATA =
         (SERVICE_NAME = orcl)
         (SERVER = DEDICATED)
         (FAILOVER_MODE =
            (TYPE = session)
            (METHOD =basic)
            (RETRIES=180)
            (DELAY=5)
          )
       )
    )
  客戶端連線 

C:\Users\zylong>sqlplus sys/oracle@212_213 as sysdba

SQL> select host_name,instance_name from v$instance;

HOST_NAME                 INSTANCE_NAME
------------------------- ----------------
0906rac2                  orcl2
  例項orcl2發生故障 

SQL> shutdown abort
ORACLE instance shut down.
  此時客戶端的連線不會斷開，但是會出現報錯，接著轉移到其他例項上。

  其實這個地方的切換連線會慢一點，但是不需要客戶端重新連線。

SQL> select host_name,instance_name from v$instance;
ORA-25408: 無法安全重放呼叫

SQL> select host_name,instance_name from v$instance;

HOST_NAME                 INSTANCE_NAME
------------------------- ----------------
0906rac1                  orcl1


四、客戶端通過Client Side TAF連線資料庫,使用preconnect方式
  客戶端配置： 

212_213 =
  (DESCRIPTION =
      (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.16.213)(PORT = 1521))
       )
      (CONNECT_DATA =
         (SERVICE_NAME = orcl)
         (SERVER = DEDICATED)
         (FAILOVER_MODE =
            (TYPE = session)
            (METHOD = preconnect)
            (BACKUP = 212213)
          )
       )
    )

212213 =
  (DESCRIPTION =
      (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.16.212)(PORT = 1521))
       )
      (CONNECT_DATA =
         (SERVICE_NAME = orcl)
         (SERVER = DEDICATED)
         (FAILOVER_MODE =
            (TYPE = session)
            (METHOD = preconnect)
            (BACKUP = 212_213)
          )
       )
    )
  客戶端連線 

C:\Users\zylong>sqlplus sys/oracle@212_213 as sysdba

SQL> select host_name,instance_name from v$instance;

HOST_NAME                 INSTANCE_NAME
------------------------- ----------------
0906rac2                  orcl2
  例項orcl2發生故障 

SQL> shutdown abort
ORACLE instance shut down.

  此時客戶端的連線不會斷開，但是會出現報錯，接著轉移到其他例項上。

  其實這個地方的切換連線會非常快，並且不需要客戶端重新連線，但是需要消耗資料庫連線數

SQL> select host_name,instance_name from v$instance;
ORA-25408: 無法安全重放呼叫

SQL> select host_name,instance_name from v$instance;

HOST_NAME                 INSTANCE_NAME
------------------------- ----------------
0906rac1                  orcl1

 關於Client Side TAF配置Preconnect方式可以參考：

 How to Configure Client Side Transparent Application Failover with Preconnect Option (文件 ID 802434.1)

 

五、客戶端通過Server Side TAF連線資料庫,只能使用BASIC方式
  在server端配置TAF 

新增服務
[oracle@0906rac2 ~]$ srvctl add service -d orcl -s server_taf -r "orcl1,orcl2" -P BASIC
啟動服務
[grid@0906rac1 ~]$ srvctl start service -d orcl -s server_taf
檢視服務狀態
[grid@0906rac1 ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS       
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.ARCH.dg
               ONLINE  ONLINE       0906rac1                                     
               ONLINE  ONLINE       0906rac2                                     
ora.CRS.dg
               ONLINE  ONLINE       0906rac1                                     
               ONLINE  ONLINE       0906rac2                                     
ora.DATA.dg
               ONLINE  ONLINE       0906rac1                                     
               ONLINE  ONLINE       0906rac2                                     
ora.LISTENER.lsnr
               ONLINE  ONLINE       0906rac1                                     
               ONLINE  ONLINE       0906rac2                                     
ora.asm
               ONLINE  ONLINE       0906rac1                 Started             
               ONLINE  ONLINE       0906rac2                 Started             
ora.gsd
               OFFLINE OFFLINE      0906rac1                                     
               OFFLINE OFFLINE      0906rac2                                     
ora.net1.network
               ONLINE  ONLINE       0906rac1                                     
               ONLINE  ONLINE       0906rac2                                     
ora.ons
               ONLINE  ONLINE       0906rac1                                     
               ONLINE  ONLINE       0906rac2                                     
ora.registry.acfs
               ONLINE  ONLINE       0906rac1                                     
               ONLINE  ONLINE       0906rac2                                     
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.0906rac1.vip
      1        ONLINE  ONLINE       0906rac1                                     
ora.0906rac2.vip
      1        ONLINE  ONLINE       0906rac2                                     
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       0906rac1                                     
ora.cvu
      1        ONLINE  ONLINE       0906rac1                                     
ora.oc4j
      1        ONLINE  ONLINE       0906rac1                                     
ora.orcl.db
      1        ONLINE  ONLINE       0906rac1                 Open                
      2        ONLINE  ONLINE       0906rac2                 Open                
ora.orcl.server_taf.svc
      1        ONLINE  ONLINE       0906rac1                                     
      2        ONLINE  ONLINE       0906rac2                                     
ora.scan1.vip
      1        ONLINE  ONLINE       0906rac1

SQL> show parameter service

NAME                  TYPE        VALUE
--------------------- ----------- -------------
service_names         string      server_taf

SQL> select name,service_id from dba_services where name = 'server_taf'; 

NAME                           SERVICE_ID
------------------------------ ----------
server_taf                              3

col name format a15  
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' 
select name, failover_method, failover_type, failover_retries,goal, clb_goal,aq_ha_notifications from dba_services where service_id = 3;

NAME            METHOD      TYPE        RETRIES GOAL       CLB_GOAL AQNOT
--------------- ----------- ---------- -------- ---------- -------- -----
server_taf      NONE        NONE              0 NONE       LONG     NO

配置引數
begin
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); 
end;
/

刪除方法
begin
DBMS_SERVICE.DELETE_SERVICE('server_taf');
end;
/

  11G R2需要修改service配置 

[oracle@0906rac2 ~]$ srvctl modify service -d ORCL -s server_taf -m BASIC -e SELECT -q TRUE -j LONG
[oracle@0906rac2 ~]$ srvctl config service -d orcl
Service name: server_taf
Service is enabled
Server pool: orcl_server_taf
Cardinality: 2
Disconnect: false
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: true
Failover type: SELECT
Failover method: BASIC
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: orcl1,orcl2
Available instances: 

col name format a15  
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' 
select name, failover_method, failover_type, failover_retries,goal, clb_goal,aq_ha_notifications from dba_services where service_id = 3;

NAME            METHOD      TYPE        RETRIES GOAL       CLB_GOAL AQNOT
--------------- ----------- ---------- -------- ---------- -------- -----
server_taf      BASIC       SELECT            0 NONE       LONG     YES

如果不生效，重新配置引數

SQL> begin
  2  dbms_service.modify_service (
  3  service_name => 'server_taf',
  4  aq_ha_notifications => true,
  5  failover_method => dbms_service.failover_method_basic,
  6  failover_type => dbms_service.failover_type_select,
  7  failover_retries => 180,
  8  failover_delay => 5,
  9  clb_goal => dbms_service.clb_goal_long); 
 10  end;
 11  /

PL/SQL procedure successfully completed.

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 from dba_services where service_id = 3;

NAME            METHOD      TYPE        RETRIES GOAL       CLB_GOAL AQNOT
--------------- ----------- ---------- -------- ---------- -------- -----
server_taf      BASIC       SELECT          180 NONE       LONG     YES

  檢視service監聽狀態 

[oracle@0906rac2 ~]$ lsnrctl services 

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 18-OCT-2016 16:15:47

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM2", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "orcl" has 1 instance(s).
  Instance "orcl2", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "orclXDB" has 1 instance(s).
  Instance "orcl2", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: 0906rac2, pid: 15756>
         (ADDRESS=(PROTOCOL=tcp)(HOST=0906rac2)(PORT=19341))
Service "server_taf" has 1 instance(s).
  Instance "orcl2", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
The command completed successfully
  客戶端配置，使用VIP： 

SERVER_TAF =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.16.212)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.16.213)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = server_taf)
    )
  )
  客戶端連線 

C:\Users\zylong>sqlplus system/oracle@server_taf

SQL> select host_name,instance_name from v$instance;

HOST_NAME                 INSTANCE_NAME
------------------------- ----------------
0906rac1                  orcl1

  例項orcl1發生故障

SQL> shutdown abort
ORACLE instance shut down.
  此時客戶端的連線不會斷開，也不會出現報錯，使用BASIC方式轉移到其他例項上。
SQL> select host_name,instance_name from v$instance;

HOST_NAME                 INSTANCE_NAME
------------------------- ----------------
0906rac2                  orcl2


  客戶端配置，使用SCAN IP： 

SCAN_TAF =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.16.214)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = server_taf)
    )
  )
  客戶端連線
C:\Users\zylong>sqlplus system/oracle@SCAN_TAF

SQL> select host_name,instance_name from v$instance;

HOST_NAME                 INSTANCE_NAME
------------------------- ----------------
0906rac1                  orcl1
  例項orcl1發生故障 

SQL> shutdown abort
ORACLE instance shut down.
  此時客戶端的連線不會斷開，也不會出現報錯，使用BASIC方式轉移到其他例項上。
SQL> select host_name,instance_name from v$instance;

HOST_NAME                 INSTANCE_NAME
------------------------- ----------------
0906rac2                  orcl2

  關於Server Side TAF配置可以參考：

  How To Configure Server Side Transparent Application Failover (文件 ID 460982.1)

  但是Server Side TAF不能使用sqlplus sys/oracle@server_taf as sysdba 實現透明切換，如果要實現，需要特殊配置：

  SYSDBA Sessions Do Not Failover with SRVCTL TAF Configured (文件 ID 1342992.1)

C:\Users\zylong>sqlplus sys/oracle@server_taf as sysdba

SQL> select host_name,instance_name from v$instance;

HOST_NAME                 INSTANCE_NAME
------------------------- ----------------
0906rac1                  orcl1

SQL> shutdown abort
ORACLE instance shut down.

SQL> select host_name,instance_name from v$instance;
select host_name,instance_name from v$instance
*
第 1 行出現錯誤:
ORA-03113: 通訊通道的檔案結尾
程式 ID: 30737
會話 ID: 32 序列號: 15

  另外需要注意，Oracle JDBC thin驅動程式不支援TAF。 TAF僅受JDBC OCI驅動程式支援。

  Is Transparent Application Failover (TAF) Supported on the JDBC Thin Driver? (文件 ID 297490.1)

  WebLogic Server（WLS）使用Oracle Thin驅動程式，而不是OCI驅動程式，不支援TAF。

  Does WebLogic Server (WLS) Support Oracle RAC with TAF? (文件 ID 950174.1)

  使用JDBC OCI的配置方式，參考:

  How to Setup SQL Developer to support Transparent Application Failover (TAF) (文件 ID 1389747.1)



























相關文章