关于11G 客户端连接数据库 SCAN 和 Transparent Application Failover（TAF）

最新推荐文章于 2024-09-02 07:45:24 发布

原创最新推荐文章于 2024-09-02 07:45:24 发布 · 2k 阅读

1 ·

CC 4.0 BY-SA版权

oracle 专栏收录该内容

79 篇文章

订阅专栏

一、客户端通过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)