实验目的：
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))

    )

  )

 

服务器端使用基于cdb550的serivce，名称为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上再次进行查询，这次终于报错了，可见在failovermode、tafpolicy都关闭的情况下，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 [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=[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_METHOD、FAILOVER_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后验证客户端能够failover到instance cdb5502

 

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

server side: -failovertype与client 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 side的tnsnames.ora里除了指定

(failover_mode=

        (type=select)

        (method=basic)

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

 

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