让人崩溃的rac server-side taf

ylw6006

refer: http://www.itpub.net/thread-1568414-1-1.html
参考MOS的How To Configure Server Side Transparent Application Failover [ID 460982.1] ，弄了N次都不成功，求教育！


[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

Maclean Liu(刘相兵

keyword  SYSDBA + Server Side TAF

Maclean Liu(刘相兵

ODM FINDING:

SYSDBA Sessions Do Not Failover with SRVCTL TAF Configured

Symptoms
SYSDBA sessions do not failover when Transparent Application Failover (TAF) on the Server side is configured with the Server Control Utility, SRVCTL. Sessions may report error ORA-3113.

However adding a client-side tnsnames entry with Failover properties will allow the session to connect with TAF enabled.

The following is a testcase example to better illustrate:

=========
TESTCASE:
=========

1. Create a server side TAF definition:

srvctl add service -d dbm -s test -r dbm2 -a dbm1 -P BASIC -l primary -y MANUAL -e SELECT -m BASIC -j LONG -B SERVICE_TIME


2. Start the service:

srvctl start service -d dbm -s test


3. Connect with this service:

sqlplus dbuser/dbuser@test


4. Verify the service and session connection have proper TAF settings in other session:

SQL> connect / as sysdba
Connected.
SQL> select NAME, NETWORK_NAME, FAILOVER_METHOD, FAILOVER_TYPE from dba_services;
...
NAME
----------------------------------------------------------------
NETWORK_NAME
----------------------------------------------------------------
FAILOVER_METHOD
----------------------------------------------------------------
FAILOVER_TYPE
----------------------------------------------------------------
test
test
BASIC
SELECT

SQL> select FAILOVER_TYPE, FAILOVER_METHOD, FAILED_OVER from gv$session where inst_id = 2 and username ='dbuser';

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



5. Then connect with SYSDBA privs:

SQL> connect dbuser@test as sysdba


6. Check the settings:

select FAILOVER_TYPE, FAILOVER_METHOD, FAILED_OVER from gv$session where inst_id = 2 and username ='SYS'

FAILOVER_TYPE FAILOVER_M FAI
------------- ---------- ---
NONE          NONE       NO
NONE          NONE       NO



7. WORKAROUND:  If client-side settings are added into tnsnames.ora, the connection will retain proper TAF settings; ie:

test2 =
(DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = dmorl2-scan)(PORT = 1521))
    (CONNECT_DATA =
        (SERVER = DEDICATED)
        (SERVICE_NAME = test)
    (FAILOVER_MODE =
    (TYPE = SELECT)(METHOD = BASIC)(RETRIES = 180)(DELAY = 5)
    )
  )
)

SQL> connect dbuser@test2 as sysdba
Enter password:
Connected.

SQL> select FAILOVER_TYPE, FAILOVER_METHOD, FAILED_OVER from gv$session where inst_id = 2 and username ='SYS'

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



Cause
The following bugs were filed, but were determined not to be bugs; in other words, this is expected behavior:

Bug 12731733 - SRVCTL TAF CONFIGURATION DOES NOT WORK FOR SYSDBA SESSIONS

Bug 8933733 - SYSDBA CONNECTION NOT FAILING OVER WITH SERVER SIDE TAF, ERROR ORA-03113

Solution
SYSDBA sessions cannot failover with server-side TAF configured using SRVCTL.

This is a documentation bug that will be fixed in a future release of the Oracle Database Net Services Administrator's Guide 11g Release 2 (11.2).

If TAF is required for your SYSDBA session, please implement client-side tnsnames settings as shown in the testcase above.


Scalability RAC Community
To discuss this topic further with Oracle experts and industry peers, we encourage you to review, join or start a discussion in the My Oracle Support Scalability RAC Community.




建议：
不要使用 SYSDBA去测试  Server side  Transparent Application Failover (TAF)  ，而需要使用 普通用户账户才能测试成功。

ylw6006

原帖由 maclean 于 2012-5-18 23:38 发表
ODM FINDING:

SYSDBA Sessions Do Not Failover with SRVCTL TAF Configured

Symptoms
SYSDBA sessions do not failover when Transparent Application Failover (TAF) on the Server side is configured with the ...

[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


这问题困扰了我好久，原来是这么简单的原因，看来有问题得及时找组织啊，感谢刘总！