签到成功

知道了

CNDBA社区CNDBA社区

用oracle用户配置Service-Side TAF(Transparent Application Failover) 示例

2017-05-15 11:40 2093 1 原创 Oracle 11g
作者： leo
本文链接：http://www.cndba.cn/cndba/leo1990/article/1912

1 创建service
用oracle 用户创建

http://www.cndba.cn/cndba/leo1990/article/1912

[oracle@rac1 ~]$ srvctl add service -d leo -s leo_taf -r "leo1" -a "leo2" -P basic -e select -m basic -w 5 -z 180

2 启动 server_taf服务

[oracle@rac1 ~]$ srvctl start service -d leo -s leo_taf
3 检查service 运行情况

http://www.cndba.cn/cndba/leo1990/article/1912

http://www.cndba.cn/cndba/leo1990/article/1912http://www.cndba.cn/cndba/leo1990/article/1912

--服务管理命令
#srvctl enable/disable service -d database-name -s service-name -i instance-name 

#srvctl start service -d
4  确认service ID 

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

NAME		SERVICE_ID
--------------- ----------
leo_taf 		 3
5  确认参数修改

http://www.cndba.cn/cndba/leo1990/article/1912 

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
--------------- ----------- ---------- -------- ---------- -------- -----
leo_taf 	BASIC	    SELECT	    180 NONE	   LONG     NO
6 检查service 注册情况 

[oracle@rac1 ~]$ lsnrctl services

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 15-MAY-2017 10:48:25

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac1)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "leo" has 2 instance(s).
  Instance "leo1", status READY, has 2 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.61)(PORT=1521))
  Instance "leo2", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.63)(PORT=1521))
Service "leoXDB" has 2 instance(s).
  Instance "leo1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER 
   
   
     (ADDRESS=(PROTOCOL=tcp)(HOST=rac1)(PORT=48877)) Instance "leo2", status READY, has 1 handler(s) for this service... Handler(s): "D000" established:0 refused:0 current:0 max:1022 state:ready DISPATCHER 
    
      (ADDRESS=(PROTOCOL=tcp)(HOST=rac2)(PORT=20352)) Service "leo_taf" has 1 instance(s). Instance "leo1", status READY, has 2 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 state:ready LOCAL SERVER "DEDICATED" established:0 refused:0 state:ready REMOTE SERVER (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.61)(PORT=1521)) The command completed successfully

7 配置查看service 参数：service_names

http://www.cndba.cn/cndba/leo1990/article/1912
http://www.cndba.cn/cndba/leo1990/article/1912

alter system set service_names='leo,leo_taf';

SQL> show parameter service;

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
service_names			     string	 leo,leo_taf
8 要配置remote_listener 参数 

在tnsnames.ora 
目录：/u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
添加一下内容
TEST=
(DESCRIPTION =
 (ADDRESS_LIST =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.61)(PORT = 1521))
  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.63)(PORT = 1521))
 )
)

SQL> show parameter listener;

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
listener_networks		     string
local_listener			     string	  (ADDRESS=(PROTOCOL=TCP)(HOST=
						 192.168.1.61)(PORT=1521))
remote_listener 		     string	 rac-scan:1521

SQL> alter system set remote_listener='TEST';

System altered.

SQL> show parameter listener

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
listener_networks		     string
local_listener			     string	  (ADDRESS=(PROTOCOL=TCP)(HOST=
						 192.168.1.61)(PORT=1521))
remote_listener 		     string	 TEST
9 在客户端TNS 配置

http://www.cndba.cn/cndba/leo1990/article/1912 

rac=
 (DESCRIPTION =
  (LOAD_BALANCE = yes)
   (ADDRESS_LIST =
   (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.61)(PORT = 1521))
   (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.63)(PORT = 1521))
   )
   (CONNECT_DATA =
    (SERVICE_NAME =leo_taf)
    )
  )
10 测试 

服务器执行
SQL> create user leo identified by leo;

User created.

SQL> grant connect,resource to leo;

Grant succeeded.

SQL> grant dba to leo;

Grant succeeded.

Windows 客户端执行
SQL> conn leo/leo@rac
已连接。

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

       SID
----------
        41

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

FAILOVER_TYPE FAILOVER_M FAI
------------- ---------- ---
SELECT        BASIC      NO


注意这里最开始是为NO的。

当把这里对应的实例关闭之后，在查看，就会变成YES.

注意：要用shutdown abort，不用用srvctl 关闭。

并且更换session id。

Windows 客户端执行 当前会话
SQL> show parameter instance_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
instance_name                        string      leo1

服务器节点1
SQL> shutdown abort
ORACLE instance shut down.

Windows 客户端执行 在同一个会话执行测试，不需要切换窗口
SQL> show parameter instance_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
instance_name                        string      leo2

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

       SID
----------
        60

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

FAILOVER_TYPE FAILOVER_M FAI
------------- ---------- ---
SELECT        BASIC      YES
注意事项：

1). 通过远端去连
2). 不要正常关闭db
3). 不要使用系统用户
sys,system 这里切记 测试是用非系统用户测试，否则即使配置正确，也无法达到测试结果，
我用sys 测试结果怎么就是不对，系统用户登录之后发现执行结果都是none 很诧异
SQL> select failover_type,failover_method,failed_over from v$session where sid=47;http://www.cndba.cn/cndba/leo1990/article/1912

FAILOVER_TYPE FAILOVER_M FAI
------------- ---------- ---
NONE          NONE       NO
发现原来不是非系统用户登录查询有结果
SQL> select failover_type,failover_method,failed_over from v$session where sid=41;http://www.cndba.cn/cndba/leo1990/article/1912

FAILOVER_TYPE FAILOVER_M FAI
------------- ---------- ---
SELECT        BASIC      NO

4). 一定要配置remote_listener 参数

版权声明：本文为博主原创文章，未经博主允许不得转载。

用户评论
* 以下用户言论只代表其个人观点，不代表CNDBA社区的观点或立场
leo

leo

关注

坚持你的坚持

  • 202
    原创
  • 0
    翻译
  • 39
    转载
  • 16
    评论
  • 访问：766279次
  • 积分：1270
  • 等级：核心会员
  • 排名：第8名
精华文章

    达梦数据库运维实战

    Oracle 18c 必须掌握的新特性：管理与实战

    Oracle数据库问题解决方案和故障排除手册
    最新问题
    查看更多+
    热门文章
      热门用户
      推荐用户
        友情链接： CNDBA社区 | openGauss | openEuler | openAnolis | 广告联系: database@ustc.edu
        Copyright © 2016 All Rights Reserved. Powered by CNDBA · 皖ICP备2022006297号-1·

        QQ交流群

        注册联系QQ