RE: RAC design question

There is an error in your post.
When shutting down an oracle instance using srvctl, the session will no longer failover in 11.2 but instead will be stopped (unless you use the -f flag).

See the update on the end of the following blog post: https://freekdhooge.wordpress.com/2011/08/11/rac-investigations-part-i/

Normally your (select) session will correctly failover with a preferred / available config and TAF. I hope to be able to create a test case this afternoon.

Regards,

Freek D'Hooge
Uptime
Oracle Database Administrator
email: freek.dhooge_at_uptime.be
tel +32(0)3 451 23 82
http://www.uptime.be
disclaimer: www.uptime.be/disclaimer

---
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Martin Bach
Sent: dinsdag 16 augustus 2011 21:38
To: oracle-l_at_freelists.org; Frits Hoogland; toon.koppelaars_at_rulegen.com
Subject: Re: RAC design question

Very sorry if that's a cross post, but I haven't seen my reply come back so posting again... I was asked if the SCAN listeners made a difference in 11.2-they don't really, at least in this case. Here's the "lost post"

The SCAN listeners don't have an effect on TAF except it's easier to write tnsnames.ora files.

Let's have an example-3 node RAC 11.2.0.2 on Linux, database orcl, service oraclel, TAF configuration in tnsnames.ora. Creating the new service:



[oracle_at_rac11gr2drnode1 ~]$ srvctl add service -d orcl -r orcl1 -a orcl2 -s oraclel

[oracle_at_rac11gr2drnode1 ~]$ srvctl start service -d orcl -s oraclel


It has only 1 preferred instance:



[oracle_at_rac11gr2drnode1 ~]$  srvctl config service -d orcl -s oraclel

Service name: oraclel
Service is enabled
Server pool: orcl_oraclel
Cardinality: 1
Disconnect: false
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: false
Failover type: NONE
Failover method: NONE
TAF failover retries: 0
TAF failover delay: 0
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: NONE
TAF policy specification: NONE
Edition:
Preferred instances: orcl1
Available instances: orcl2

local TNSNames.ora:

ORACLEL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = drclusterscan.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = oraclel)
      (failover_mode = basic)(failover_type = session)
    )
  )

Connect to the database:

sqlplus martin_at_oraclel

SQL> select inst_id,sid,serial#,FAILOVER_TYPE, failover_method, failed_over
  2  from gv$session where sid=(select distinct sid from v$mystat)
  3  /

   INST_ID        SID    SERIAL# FAILOVER_TYPE FAILOVER_M FAI
---------- ---------- ---------- ------------- ---------- ---
         1         33        393 SESSION       BASIC      NO

SQL> r
  1* select * from v$active_instances


INST_NUMBER INST_NAME
----------- ------------------------------------------------------------
          1 rac11gr2drnode1.localdomain:orcl1
          2 rac11gr2drnode3.localdomain:orcl2
          3 rac11gr2drnode4.localdomain:orcl3
 

Let's kill the instance:



[oracle_at_rac11gr2drnode1 ~]$ srvctl stop instance -d orcl -i orcl1 -o abort

[oracle_at_rac11gr2drnode1 ~]$ srvctl status database -d orcl

Instance orcl1 is not running on node rac11gr2drnode1
Instance orcl2 is running on node rac11gr2drnode3
Instance orcl3 is running on node rac11gr2drnode4

what happened to the session?

SQL> r
  1* select * from v$active_instances
select * from v$active_instances
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 9770
Session ID: 33 Serial number: 393

You will lose your session with only 1 preferred instance and TAF.

Let's change the service to have 2 preferred instances:



[oracle_at_rac11gr2drnode1 oracle-l]$ srvctl modify service -d orcl -s oraclel -n -i orcl1,orcl2 -a orcl3

[oracle_at_rac11gr2drnode1 oracle-l]$ srvctl config service -d orcl -s oraclel

Service name: oraclel
Service is enabled
Server pool: orcl_oraclel
Cardinality: 2
Disconnect: false
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: false
Failover type: NONE
Failover method: NONE
TAF failover retries: 0
TAF failover delay: 0
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: NONE
TAF policy specification: NONE
Edition:
Preferred instances: orcl1,orcl2
Available instances: orcl3



[oracle_at_rac11gr2drnode1 oracle-l]$ srvctl start service -d orcl -s oraclel

[oracle_at_rac11gr2drnode1 oracle-l]$ srvctl status service -d orcl -s oraclel

Service oraclel is running on instance(s) orcl1,orcl2

we now have 2 preferred instances. Let's retry the test:

SQL> select inst_id,sid,serial#,FAILOVER_TYPE, failover_method, failed_over
  2   from gv$session where sid=(select distinct sid from v$mystat)
  3  and inst_id = sys_context('userenv','instance')
  4  /

   INST_ID        SID    SERIAL# FAILOVER_TYPE FAILOVER_M FAI
---------- ---------- ---------- ------------- ---------- ---
         2        151         23 SESSION       BASIC      NO




[oracle_at_rac11gr2drnode1 ~]$ srvctl stop instance -d orcl -i orcl2 -o abort

[oracle_at_rac11gr2drnode1 ~]$ srvctl status database -d orcl

Instance orcl1 is running on node rac11gr2drnode1
Instance orcl2 is not running on node rac11gr2drnode3
Instance orcl3 is running on node rac11gr2drnode4

back to my session:

SQL> /
select inst_id,sid,serial#,FAILOVER_TYPE, failover_method, failed_over
*
ERROR at line 1:
ORA-25408: can not safely replay call


SQL> /

   INST_ID        SID    SERIAL# FAILOVER_TYPE FAILOVER_M FAI
---------- ---------- ---------- ------------- ---------- ---
         1        153         11 SESSION       BASIC      YES


Don't know why the ORA-25408 appears, I didn't ask for read consistent failover. In your application you have to catch the 25408 in a SQLException or you will be bailed out anyway.

Hope this helps,

Martin
http://uk.linkedin.com/in/martincarstenbach
http://martincarstenbach.wordpress.com


On 16/08/2011 15:23, Walker, Jed S wrote: 
I thought TAF was built-in to RAC services, so that would be news to me (though I'm only a month into RAC). I have a trial system I will have to test this on myself. If you get a chance I'd be curious to see what you get. I've been creating services like
 
srvctl add service -d rdvrprd -s rdvrprd_atlt -r rdvrprd1 -a rdvrprd2,rdvrprd3,rdvrprd4,rdvrprd5
 
From: Martin Bach [mailto:development_at_the-playground.de] 

--
http://www.freelists.org/webpage/oracle-l