ITPub博客

首页 > 数据库 > Oracle > server side and client side TAF特性测试

server side and client side TAF特性测试

原创 Oracle 作者:oliseh 时间:2014-09-29 23:24:25 0 删除 编辑

实验目的:
12c RAC
环境,为RAC数据库增加一个service,在另一台oracle客户端机器上使用Sqlplus连接这个服务名,测试在数据库宕库情况下,客户端侧所体现出来的Failover特性,并验证创建service时的三个参数-tafpolicy -failovertype-failovermethod到底哪一个在发挥TAF的作用

客户端使用的tns连接串

cdb550svc =

  (DESCRIPTION =

    (FAILOVER=off)

    (ADDRESS_LIST =

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

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

    )

    (CONNECT_DATA =

      (Service_name = cdb550svc)

      (failover_mode=(type=none))

    )

  )

 

服务器端使用基于cdb550serivce,名称为cdb550svc

1、 Server Side TAF test—只留failovertype

###创建、启动service

srvctl add service -db cdb550 -service cdb550svc -preferred "cdb5501,cdb5502" -tafpolicy BASIC -clbgoal long -failovertype SELECT -failovermethod BASIC

 

srvctl start service -db cdb550 -service cdb550svc

        

    root@qc550705:/>crsctl status res ora.cdb550.cdb550svc.svc -t

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

Name           Target  State        Server                   State details

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

Cluster Resources

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

ora.cdb550.cdb550svc.svc

         1        ONLINE  ONLINE       qc550705                 STABLE

         2        ONLINE  ONLINE       qc550707                 STABLE

 

###查看service属性

col name format a25

col network_name format a25

col failover_method format a25

col failover_type format a25

set linesize 150

select name,network_name,failover_method,failover_type,goal,enabled,clb_goal from dba_services where name='cdb550svc';

NAME                      NETWORK_NAME              FAILOVER_METHOD           FAILOVER_TYPE             GOAL         ENA CLB_G

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

cdb550svc                 cdb550svc                 BASIC                     SELECT                    NONE         NO  LONG

 

 ####客户端sqlplus连接到cdb550svc

oracle@QC_P570_710:/oracle/app/oracle/product/11.2.0/db_1/network/admin>sqlplus system/773946@cdb550svc

SQL> select INSTANCE_NUMBER,INSTANCE_NAME,HOST_NAME from v$instance;

 

INSTANCE_NUMBER INSTANCE_NAME

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

HOST_NAME

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

              1 cdb5501

qc550705

 

 #### 关闭instance cdb5501

SQL> show parameter service_name

NAME                                 TYPE        VALUE

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

service_names                        string      cdb550

SQL> shutdown abort

ORACLE instance shut down.

 

   ####客户端已经切换到了instance cdb5502

   SQL> r

  1* select INSTANCE_NUMBER,INSTANCE_NAME,HOST_NAME from v$instance

 

INSTANCE_NUMBER INSTANCE_NAME

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

HOST_NAME

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

              2 cdb5502

qc550707

  

   ####关闭-failovermethod-tafpolicy,只留下-failovertype的情况下客户端依然能够切换

cdb5502

srvctl modify service -db cdb550 -service cdb550svc -failovermethod NONE

srvctl modify service -db cdb550 -service cdb550svc -tafpolicy NONE

….关闭instance cdb5501的步骤省略了

 

   ####直至关闭-failovertype后客户端就不能正常切换了

   srvctl modify service -db cdb550 -service cdb550svc -failovertype NONE

 

###再次关闭cdb5501

SQL> show parameter service_name

NAME                                 TYPE        VALUE

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

service_names                        string      cdb550

SQL> shutdown abort

ORACLE instance shut down.

 

###client上再次进行查询,这次终于报错了,可见在failovermodetafpolicy都关闭的情况下,failovertype依然能起到TAF的作用了

 

SQL> r

1* select INSTANCE_NUMBER,INSTANCE_NAME,HOST_NAME from v$instance

select INSTANCE_NUMBER,INSTANCE_NAME,HOST_NAME from v$instance

*

ERROR at line 1:

ORA-03113: end-of-file on communication channel

Process ID: 9044346

Session ID: 363 Serial number: 31

 

2、 Server Side TAF test—只留TAFPOLICY

###仅设置Tafpolicy参数

oracle@qc550705:/home/oracle>    crsctl status res ora.cdb550.cdb550svc.svc ora.cdb550.db -t

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

Name           Target  State        Server                   State details

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

Cluster Resources

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

ora.cdb550.cdb550svc.svc

      1        ONLINE  ONLINE       qc550705                 STABLE

      2        ONLINE  ONLINE       qc550707                 STABLE

ora.cdb550.db

      1        ONLINE  ONLINE       qc550707                 Open,STABLE

      2        ONLINE  ONLINE       qc550705                 Open,STABLE

 

col name format a25

col network_name format a25

col failover_method format a25

col failover_type format a25

set linesize 150

select name,network_name,failover_method,failover_type,goal,clb_goal from dba_services where name='cdb550svc';

NAME                      NETWORK_NAME              FAILOVER_METHOD           FAILOVER_TYPE             GOAL         CLB_G

------------------------- ------------------------- ------------------------- ------------------------- ------------ ----- cdb550svc                 cdb550svc                 NONE                      NONE                      NONE         LONG

 

srvctl modify service -db cdb550 -service cdb550svc -tafpolicy BASIC

 

###查看Tafpolicy参数设置后的效果

select name,network_name,failover_method,failover_type,goal,clb_goal from dba_services[C1] ;

 

NAME                      NETWORK_NAME              FAILOVER_METHOD           FAILOVER_TYPE             GOAL         CLB_G

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

SYS$BACKGROUND                                                                                                       LONG

SYS$USERS                                                                                                            LONG

cdb550                    cdb550                                                                                     LONG

cdb550svc                 cdb550svc                 NONE                      NONE                      NONE         LONG

 

 oracle@qc550705:/home/oracle>srvctl config[C2]  service -db cdb550 -service cdb550svc

Service name: cdb550svc

Service is enabled

Server pool: cdb550_cdb550svc

Cardinality: 2

Disconnect: false

Service role: PRIMARY

Management policy: AUTOMATIC

DTP transaction: false

AQ HA notifications: false

Global: false

Commit Outcome: false

Failover type: NONE

Failover method: NONE

TAF failover retries:

TAF failover delay:

Connection Load Balancing Goal: LONG

Runtime Load Balancing Goal: NONE

TAF policy specification: BASIC

Edition:

Pluggable database name:

Maximum lag time: ANY

SQL Translation Profile:

Retention: 86400 seconds

Replay Initiation Time: 300 seconds

Session State Consistency:

Preferred instances: cdb5501,cdb5502

Available instances:

 

###测试instance cdb5501 shutdown后,客户端无法重连上

SQL> shutdown abort

ORACLE instance shut down.

 

SQL> r

1* select instance_number,instance_name,host_name from v$instance

select instance_number,instance_name,host_name from v$instance

*

ERROR at line 1:

ORA-03113: end-of-file on communication channel

Process ID: 14156206

Session ID: 222 Serial number: 15

 

3、 Server Side TAF test—只留failovermethod

###仅设置failovermethod参数

srvctl modify service -db cdb550 -service cdb550svc -tafpolicy NONE -failovermethod BASIC

 

###查看服务属性

col network_name format a25

col failover_method format a25

col failover_type format a25

set linesize 150

select name,network_name,failover_method,failover_type,goal,clb_goal from dba_services where name=’cdb550svc’;

NAME                      NETWORK_NAME              FAILOVER_METHOD           FAILOVER_TYPE             GOAL         CLB_G

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

cdb550svc                 cdb550svc                 BASIC                     NONE                      NONE         LONG

 

###关闭inst cdb5501

SQL> shutdown abort

ORACLE instance shut down.

 

###客户端依然无法reconnect

  1* select instance_number,instance_name,host_name from v$instance

select instance_number,instance_name,host_name from v$instance

*

ERROR at line 1:

ORA-03113: end-of-file on communication channel

Process ID: 6226240

Session ID: 364 Serial number: 341

 

4、 Client Side TAF test

###保持-tafpolicy=NONE -failovertype=NONE-failovermethod=BASIC设置

oracle@qc550705:/home/oracle>    crsctl status res ora.cdb550.cdb550svc.svc ora.cdb550.db -t

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

Name           Target  State        Server                   State details

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

Cluster Resources

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

ora.cdb550.cdb550svc.svc

      1        ONLINE  ONLINE       qc550705                 STABLE

      2        ONLINE  ONLINE       qc550707                 STABLE

ora.cdb550.db

      1        ONLINE  ONLINE       qc550707                 Open,STABLE

      2        ONLINE  ONLINE       qc550705                 Open,STABLE

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

oracle@qc550705:/home/oracle>sqlplus '/as sysdba'

select name,network_name,failover_method,failover_type,goal,clb_goal from dba_services;SQL> SQL> SQL> SQL> SQL>

 

NAME                      NETWORK_NAME              FAILOVER_METHOD           FAILOVER_TYPE             GOAL         CLB_G

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

SYS$BACKGROUND                                                                                                       LONG

SYS$USERS                                                                                                            LONG

cdb550                    cdb550                                                                                     LONG

cdb550svc                 cdb550svc                 BASIC                     NONE                      NONE         LONG

 

###client Side侧的tnsnames.ora里重新配置一个带有TAF功能的service_name

cdb550svc =

  (DESCRIPTION =

    (FAILOVER=on[C3] )

    (ADDRESS_LIST =

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

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

    )

    (CONNECT_DATA =

      (Service_name = cdb550svc)

      (failover_mode=(type=select))

    )

  )

 

###测试instance cdb5501 shutdown后客户端能否重连

SQL> set linesize 140

SQL> select instance_number,instance_name,host_name from v$instance;

 

INSTANCE_NUMBER INSTANCE_NAME    HOST_NAME

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

              1 cdb5501          qc550705

 

SQL> shutdown abort

ORACLE instance shut down.

 

###client side[C4]  依然无法切换到cdb5502

  SQL> select count(1) from all_users;

select count(1) from all_users

*

ERROR at line 1:

ORA-03113: end-of-file on communication channel

Process ID: 8847660

Session ID: 293 Serial number: 45

 

###重建cdb550svc,不指定-tafpolicy-failovertype-failovermethod三个参数

srvctl stop service -db cdb550 -service cdb550svc

 

srvctl remove service -db cdb550 -service cdb550svc

 

srvctl add service -db cdb550 -service cdb550svc -preferred "cdb5501,cdb5502"

 

srvctl start service -db cdb550 -service cdb550svc

 

###检查dba_services中新建的cdb550svc服务属性,FAILOVER_METHODFAILOVER_TYPE值均为空

SQL> col name format a25

col network_name format a25

col failover_method format a25

col failover_type format a25

set linesize 150

select name,network_name,failover_method,failover_type,goal,enabled,clb_goal from dba_services where name='cdb550svc';SQL> SQL> SQL> SQL> SQL>

 

NAME                      NETWORK_NAME              FAILOVER_METHOD           FAILOVER_TYPE             GOAL         ENA CLB_G

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

cdb550svc                 cdb550svc                                                                     NONE         NO  LONG

 

### 通过srvctl config检查cdb550svc服务属性

oracle@qc550705:/home/oracle>srvctl config service -db cdb550 -service cdb550svc

Service name: cdb550svc

Service is enabled

Server pool: cdb550_cdb550svc

Cardinality: 2

Disconnect: false

Service role: PRIMARY

Management policy: AUTOMATIC

DTP transaction: false

AQ HA notifications: false

Global: false

Commit Outcome: false

Failover type:

Failover method:

TAF failover retries:

TAF failover delay:

Connection Load Balancing Goal: LONG

Runtime Load Balancing Goal: NONE

TAF policy specification: NONE

Edition:

Pluggable database name:

Maximum lag time: ANY

SQL Translation Profile:

Retention: 86400 seconds

Replay Initiation Time: 300 seconds

Session State Consistency:

Preferred instances: cdb5501,cdb5502

Available instances:

 

### instance cdb5501 shutdown abort后验证客户端能够failoverinstance cdb5502

 

5、 server side: -failovertypeclient side: failover=on使用说明

server side: -failovertypeclient side: failover=on之间的关系是,当创建服务时-failovertype被指定为none的时候,无论server side侧的-failovermethod-tafpolicyclient参数取值如何、无论client side侧的tnsnames.ora如何配置,在一个instance宕掉的情况下,client是不会切换到另一个instance的;

当创建服务时-failovertype被指定为SESSION | SELECT | TRANSACTION值的时候,client side侧的Failover可以配置为:failover=off或者failover=on;此种情况client依然可以切换到另一个instance

当创建服务时-failovertype为空值,那么在client sidetnsnames.ora里除了指定

(failover_mode=

        (type=select)

        (method=basic)

 )外还必须指定Failover=on(on为缺省值不指定也可),才能实现instances间的切换

 

结论:server side侧实现TAF一定要配置-failovertype参数,另两个参数-failovermethod-tafpolicy作用不大;client sidefailover=on参数最好一直打开着;Server sideTAF参数优先级要高于client side的同功能TAF参数

 


 [C1]设置-tafpolicy BASIC之后dba_services视图中没能反应出此变更

 [C2]只有srvctl config service结果中能体现出tafpolicy的值

 [C3]此处一定要是on,否则client无法切换到另一个instance

 [C4]原因在于failovertype=noneserver-side的策略优先级要高于client-side

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/53956/viewspace-1285395/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
不仅仅专注Oracle database技术, member of SHOUG

注册时间:2014-04-06

  • 博文量
    128
  • 访问量
    1617111