RAC环境下配置TAF (Final)

TAF是Transparent Application Failover的英文缩写,顾名思义就是对应用透明的故障转移,举个例子,当应用连接某个oracle数据库的执行查询操作的时候,数据库服务器网络中断或者实例崩溃,在经过delay参数设置的值之后,将自动连接到其他可用的实例,继续进行查询。(前提是执行计划,输出结果集和输出的顺序不能发生变化)

TAF的特性:
1:TAF是ORACLE客户端提供的一项特性,使用TAF,对客户端的环境有一定的要求,比如JAVA的JDBC驱动、Oracle客户端的版本等(8i开始支持TAF);
2:大致上TAF可以分为2种,连接时的TAF和会话建立后TAF;
3:TAF本身与是否RAC环境无关,但一般都用在RAC环境,最小程度的减少最应用的影响,单实例环境下也可以使用TAF,这样使用PL/SQL developer连接数据库,即使数据库实例重启,也不需要重新连接;
4:RAC环境下,还可以把TAF配置在服务器端;
5:配置listener.ora文件的GLOBAL_DBNAME参数后将会禁用TAF

扩展阅读:
http://www.laoxiong.net/taf_part1.html

http://www.laoxiong.net/taf_part2.html

http://www.laoxiong.net/taf_part3.html

一:RAC环境下配置连接时的TAF,客户端为rhel6,当节点2关闭后,新建立的连接会连接到节点1


  1. [root@rhel6 ~]# su - oracle  
  2. [oracle@rhel6 ~]$ cd $ORACLE_HOME/network/admin  
  3. [oracle@rhel6 admin]$ tail  tnsnames.ora   
  4. RAC4 =  
  5.   (DESCRIPTION =  
  6.     (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.yang.com)(PORT = 1521))  
  7.     (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.yang.com)(PORT = 1521))  
  8.     (LOAD_BALANCE = yes)  
  9.     (FAILOVER = yes)  
  10.     (CONNECT_DATA =  
  11.       (SERVER = DEDICATED)  
  12.       (SERVICE_NAME = rac.yang.com)  
  13.     )  
  14. )  
  15.  
  16. [oracle@rhel6 admin]$ sqlplus sys/123456@rac4 as sysdba  
  17. SQL*Plus: Release 10.2.0.1.0 - Production on Sun Jan 29 13:26:19 2012  
  18. Copyright (c) 1982, 2005, Oracle.  All rights reserved.  
  19.  
  20. Connected to:  
  21. Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production  
  22. With the Partitioning, Real Application Clusters, OLAP, Data Mining  
  23. and Real Application Testing options  
  24.  
  25. SQL> col instance_name for a20  
  26. SQL> col host_name for a40  
  27. SQL> select instance_name,host_name from v$instance;  
  28.  
  29. INSTANCE_NAME        HOST_NAME  
  30. -------------------- ----------------------------------------  
  31. rac1                 rac1.yang.com  
  32.  
  33. SQL> conn sys/123456@rac4 as sysdba  
  34. Connected.  
  35. SQL> select instance_name,host_name from v$instance;  
  36.  
  37. INSTANCE_NAME        HOST_NAME  
  38. -------------------- ----------------------------------------  
  39. rac2                 rac2.yang.com  
  40.  
  41. [oracle@rac2 ~]$ srvctl stop instance -d rac -i rac2 -o immediate  
  42. [oracle@rac2 ~]$ srvctl status database -d rac  
  43. Instance rac1 is running on node rac1  
  44. Instance rac2 is not running on node rac2  
  45.  
  46. [oracle@rhel6 ~]$ sqlplus sys/123456@rac4 as sysdba  
  47. SQL*Plus: Release 10.2.0.1.0 - Production on Sun Jan 29 14:00:17 2012  
  48. Copyright (c) 1982, 2005, Oracle.  All rights reserved.  
  49.  
  50. Connected to:  
  51. Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production  
  52. With the Partitioning, Real Application Clusters, OLAP, Data Mining  
  53. and Real Application Testing options  
  54.  
  55. SQL> col instance_name for a20  
  56. SQL> col host_name for a40  
  57. SQL> select instance_name,host_name from v$instance;  
  58.  
  59. INSTANCE_NAME        HOST_NAME  
  60. -------------------- ----------------------------------------  
  61. rac1                 rac1.yang.com  
  62.  
  63. SQL> conn sys/123456@rac4 as sysdba  
  64. Connected.  
  65. SQL> select instance_name,host_name from v$instance;  
  66.  
  67. INSTANCE_NAME        HOST_NAME  
  68. -------------------- ----------------------------------------  
  69. rac1                 rac1.yang.com  
  70.  
  71. SQL> conn sys/123456@rac4 as sysdba  
  72. Connected.  
  73. SQL> select instance_name,host_name from v$instance;  
  74.  
  75. INSTANCE_NAME        HOST_NAME  
  76. -------------------- ----------------------------------------  
  77. rac1                 rac1.yang.com 

2: RAC环境下客户端的taf,10g的rac


  1. [oracle@rhel6 ~]$ cd $ORACLE_HOME/network/admin  
  2. [oracle@rhel6 admin]$ tail -17 tnsnames.ora   
  3. RAC =  
  4.   (DESCRIPTION =  
  5.     (ADDRESS_LIST =  
  6.       (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.yang.com)(PORT = 1521))  
  7.       (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.yang.com)(PORT = 1521))  
  8.     )  
  9.     (LOAD_BALANCE=YES)  
  10.     (FAILOVER=YES)  
  11.     (CONNECT_DATA =  
  12.       (SERVICE_NAME = rac.yang.com)  
  13.       (FAILOVER_MODE =  
  14.        (TYPE = SELECT)  
  15.        (METHOD = BASIC)  
  16.        (RETRIES = 180)  
  17.        (DELAY = 5))  
  18.     )  
  19.   )  
  20.  
  21. [oracle@rhel6 admin]$ sqlplus sys/123456@rac as sysdba  
  22. SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jan 30 19:14:50 2012  
  23. Copyright (c) 1982, 2005, Oracle.  All rights reserved.  
  24.  
  25. Connected to:  
  26. Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production  
  27. With the Partitioning, Real Application Clusters, OLAP, Data Mining  
  28. and Real Application Testing options  
  29.  
  30. SQL> SELECT SID FROM V$MYSTAT WHERE ROWNUM=1;  
  31.  
  32.        SID  
  33. ----------  
  34.        148  
  35.  
  36. SQL> SELECT MACHINE, FAILOVER_TYPE, FAILOVER_METHOD, FAILED_OVER FROM V$SESSION WHERE SID=148;  
  37.  
  38. MACHINE              TYPE       METHOD      FAI  
  39. -------------------- ---------- ----------- ---  
  40. rhel6.yang.com       SELECT     BASIC       NO  
  41.  
  42. SQL> select instance_name from v$instance;  
  43.  
  44. INSTANCE_NAME  
  45. ----------------  
  46. rac1  
  47.  
  48. SQL> select * from dba_source; //构造长查询  
  49.  
  50. [oracle@rac1 ~]$ sqlplus /nolog  
  51. SQL*Plus: Release 10.2.0.5.0 - Production on Mon Jan 30 19:20:18 2012  
  52. Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.  
  53.  
  54. SQL> conn /as sysdba  
  55. Connected.  
  56. SQL> select instance_name from v$instance;  
  57.  
  58. INSTANCE_NAME  
  59. ------------------------------------------------  
  60. rac1  
  61.  
  62. SQL> shutdown abort  
  63. ORACLE instance shut down.  
  64.  
  65. [oracle@rac1 ~]$ srvctl status database -d rac  
  66. Instance rac2 is running on node rac2  
  67. Instance rac1 is not running on node rac1  
  68.  
  69.  
  70. 前面的长查询将会继续,可以看到会话的sid已经改变,连接到了节点2上  
  71. 295528 rows selected.  
  72.  
  73. SQL> SELECT SID FROM V$MYSTAT WHERE ROWNUM=1;  
  74.  
  75.        SID  
  76. ----------  
  77.        130  
  78.  
  79. SQL> SELECT MACHINE, FAILOVER_TYPE, FAILOVER_METHOD, FAILED_OVER FROM V$SESSION WHERE SID=130;  
  80.  
  81. MACHINE              TYPE       METHOD      FAI  
  82. -------------------- ---------- ----------- ---  
  83. rhel6.yang.com       SELECT     BASIC       YES  
  84.  
  85. SQL> select instance_name from v$instance;  
  86.  
  87. INSTANCE_NAME  
  88. ----------------  
  89. rac2 

3: RAC环境下客户端的taf,11g的rac,11g的rac提供了scan地址的功能,大赞!


  1. [oracle@rhel6 admin]$ tail -14 tnsnames.ora   
  2. NODE =  
  3.   (DESCRIPTION =  
  4.     (ADDRESS_LIST =  
  5.       (ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan.yang.com)(PORT = 1521))  
  6.     )  
  7.     (CONNECT_DATA =  
  8.       (SERVICE_NAME = rac.yang.com)  
  9.       (FAILOVER_MODE =  
  10.        (TYPE = SELECT)  
  11.        (METHOD = BASIC)  
  12.        (RETRIES = 180)  
  13.        (DELAY = 5))  
  14.     )  
  15.   )  
  16.  
  17. [oracle@rhel6 admin]$ sqlplus sys/Ab123456@node as sysdba  
  18. SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jan 30 20:41:00 2012  
  19. Copyright (c) 1982, 2005, Oracle.  All rights reserved.  
  20. Connected to:  
  21. Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production  
  22. With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,  
  23. Data Mining and Real Application Testing options  
  24.  
  25. SQL> select instance_name from v$instance;  
  26.  
  27. INSTANCE_NAME  
  28. ----------------  
  29. node2  
  30.  
  31. SQL> select sid from v$mystat where rownum=1;  
  32.  
  33.        SID  
  34. ----------  
  35.        141  
  36.  
  37. SQL> SELECT MACHINE, FAILOVER_TYPE, FAILOVER_METHOD, FAILED_OVER FROM V$SESSION WHERE SID=141;  
  38.  
  39. MACHINE              TYPE       METHOD      FAI  
  40. -------------------- ---------- ----------- ---  
  41. rhel6.yang.com       SELECT     BASIC       NO  
  42.  
  43. SQL> create table taf_test(id number,name varchar(20));  
  44. Table created.  
  45.  
  46. SQL> insert into taf_test values (1,'zhangsan');  
  47. 1 row created.  
  48.  
  49. SQL> update taf_test set id=2 where id=1;  
  50. 1 row updated. 

这个时候不提交,使用shutdown abort命令关闭节点2,会话将自动连接到节点1上,但需要输入rollback命令回滚事务,否则不能执行正常的操作!


  1. SQL> select * from taf_test;  
  2.  
  3.         ID NAME  
  4. ---------- ---------------  
  5.          2 zhangsan  
  6.  
  7. [oracle@node2 ~]$ srvctl stop instance -d rac -i node2 -o abort  
  8. [oracle@node2 ~]$ srvctl status database -d rac  
  9. Instance node1 is running on node node1  
  10. Instance node2 is not running on node node2  
  11.  
  12. SQL> select * from dual;  
  13. select * from dual  
  14. *  
  15. ERROR at line 1:  
  16. ORA-25402: transaction must roll back  
  17.  
  18. SQL> rollback;  
  19. Rollback complete.  
  20.  
  21. SQL> select instance_name from v$instance;  
  22.  
  23. INSTANCE_NAME  
  24. ----------------  
  25. node1 

4:服务器端的taf,在我的测试中配置失败,详情http://www.itpub.net/thread-1568414-1-1.html

2012年05月19日更新,http://t.askmaclean.com/viewthread.php?tid=1052&pid=5532&page=1&extra=page%3D1#pid5532

[oracle@rac2 ~]$ cat $TNS_ADMIN/listener.ora
LISTENER_RAC2 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.yang.com)(PORT = 1521)(IP = FIRST))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.42)(PORT = 1521)(IP = FIRST))
    )
  )

SID_LIST_LISTENER_RAC2 =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
      (PROGRAM = extproc)
    )
  )

[oracle@rac2 ~]$ cat $TNS_ADMIN/tnsnames.ora
LISTENER_RAC2 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.yang.com)(PORT = 1521))
    )
  )

LISTENER_RAC1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.yang.com)(PORT = 1521))
    )
  )

LISTENERS_RAC =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.yang.com)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.yang.com)(PORT = 1521))
    )
  )

RAC2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.yang.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = rac.yang.com)
      (INSTANCE_NAME = rac2)
    )
  )

RAC1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.yang.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = rac.yang.com)
      (INSTANCE_NAME = rac1)
    )
  )

[oracle@rac2 ~]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.5.0 - Production on Fri May 18 20:55:39 2012
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

SQL> conn /as sysdba
Connected.
SQL> select name from dba_services;

NAME
--------------------------------------------------------------------------------
SYS$BACKGROUND
SYS$USERS
seeddataXDB
seeddata
racXDB
rac.yang.com
6 rows selected.

SQL> alter system set local_listener=LISTENER_RAC2 sid='rac2';
System altered.

SQL> alter system set local_listener=LISTENER_RAC1 sid='rac1';
System altered.

SQL> alter system set remote_listener=LISTENERS_RAC;
System altered.

[oracle@rac2 ~]$ srvctl  add service -d rac -s taf -r rac1,rac2 -P basic
[oracle@rac2 ~]$ srvctl config service -d rac -s taf
taf PREF: rac1 rac2 AVAIL:

[oracle@rac2 ~]$ srvctl  start service -d rac -s taf
[oracle@rac2 ~]$ srvctl status service -d rac -s taf
Service taf is running on instance(s) rac1, rac2

[oracle@rac2 ~]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.5.0 - Production on Fri May 18 21:11:35 2012
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

SQL> conn /as sysdba
Connected.
SQL> show parameter service;

NAME                                 TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
service_names                        string
taf, rac.yang.com

  1  begin
  2    dbms_service.modify_service(service_name        => 'taf',
  3                     goal                => DBMS_SERVICE.goal_throughput,
  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;
SQL> /
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,service_id from dba_services where name='taf';

NAME            SERVICE_ID
--------------- ----------
taf                 7
SQL> select name, failover_method, failover_type, failover_retries,goal, clb_goal,aq_ha_notifications  from dba_services where service_id = 7;

NAME            METHOD      TYPE        RETRIES GOAL       CLB_GOAL AQNOT
--------------- ----------- ---------- -------- ---------- -------- -----
taf        BASIC       SELECT          180 THROUGHPUT LONG     YES

[oracle@rhel6 admin]$ tail -12 tnsnames.ora
TAF =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.141)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.142)(PORT = 1521))
    )
    (LOAD_BALANCE=YES)
    (CONNECT_DATA =
      (SERVICE_NAME = taf.yang.com)
    )
  )
[oracle@rhel6 admin]$ sqlplus sys/123456@taf as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Fri May 18 23:19:08 2012
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> select sid from v$mystat where rownum=1;

       SID
----------
       129
SQL> col machine for a20
SQL> SELECT MACHINE, FAILOVER_TYPE, FAILOVER_METHOD, FAILED_OVER FROM V$SESSION WHERE SID=129;

MACHINE              TYPE       METHOD      FAI
-------------------- ---------- ----------- ---
rhel6.yang.com       NONE       NONE        NO

[oracle@rhel6 ~]$ sqlplus hr/hr@taf
SQL*Plus: Release 10.2.0.1.0 - Production on Sat May 19 10:04:12 2012
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> select sid from v$mystat where rownum=1;

       SID
----------
       147
SQL> col machine for a20
SQL> SELECT MACHINE, FAILOVER_TYPE, FAILOVER_METHOD, FAILED_OVER FROM V$SESSION WHERE SID=147;

MACHINE              TYPE       METHOD      FAI
-------------------- ---------- ----------- ---

rhel6.yang.com       SELECT     BASIC       NO

本文转自斩月博客51CTO博客,原文链接http://blog.51cto.com/ylw6006/769244如需转载请自行联系原作者


ylw6006

上一篇:为什么要前后端分离?有什么优缺点?


下一篇:MS SQL SERVER搜索某个表的主键所在的列名