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@rac11gr2drnode1 ~]$ srvctl add service -d orcl -r orcl1 -a orcl2 -s oraclel
[oracle@rac11gr2drnode1 ~]$ srvctl start service -d orcl -s oraclel

It has only 1 preferred instance:

[oracle@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@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@rac11gr2drnode1 ~]$ srvctl stop instance -d orcl -i orcl1 -o abort
[oracle@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@rac11gr2drnode1 oracle-l]$ srvctl modify service -d orcl -s oraclel -n -i orcl1,orcl2 -a orcl3
[oracle@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@rac11gr2drnode1 oracle-l]$ srvctl start service -d orcl -s oraclel
[oracle@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@rac11gr2drnode1 ~]$ srvctl stop instance -d orcl -i orcl2 -o abort
[oracle@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@xxxxxxxxxxxxxxxxx]


Other related posts: