Re: RAC design question

From: Martin Bach <development_at_the-playground.de>
Date: Tue, 16 Aug 2011 20:37:32 +0100
Message-ID: <4E4AC6FC.9090806_at_the-playground.de>



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
Received on Tue Aug 16 2011 - 14:37:32 CDT

Original text of this message