Re: RAC design question

From: Martin Bach <development_at_the-playground.de>
Date: Tue, 16 Aug 2011 15:32:47 +0100
Message-ID: <4E4A7F8F.10501_at_the-playground.de>

Kumar,

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

<http://uk.linkedin.com/in/martincarstenbach> On 16/08/2011 14:12, Kumar Madduri wrote:

> Hi Martin
> With the introduction of SCAN listeners, this behavior should not  
> happen or it should have minimum impact. Is this right?
> Thank you
> Kumar
>
> On Tue, Aug 16, 2011 at 3:52 AM, Martin Bach 
> <development_at_the-playground.de <mailto:development_at_the-playground.de>> 
> wrote:
>
>     Hi Jed,
>
>     indeed, the service will fail over in case your preferred instance
>     crashes. However, your sessions won't-it's quite simple to test.
>     Define TAF at the service level, define 1 preferred and n (where n
>     > 0) available instances, start the service, connect to the
>     service and then kill the instance. You can do this in SQLPlus,
>     and you'll see that your connection lost contact.
>
>     Does that quick reply make sense? I don't have a system available
>     right now, but can do a test if you like and share the output.
>
>     Best regards,
>
>     Martin
>
>
>     On 15/08/2011 16:04, Walker, Jed S wrote:
>>
>>     Thank you Martin, Frits, Toon, and Kumar.
>>
>>     Question though, If I have a service with one preferred instance
>>     and multiple available instances I believe it should still
>>     failover with TAF to any of the available instances shouldn�t it?
>>     My understanding is that multiple preferred instances is for
>>     spreading the load across multiple instances, not failover.
>>
>>     *From:*Martin Bach [mailto:development_at_the-playground.de]
>>     *Sent:* Saturday, August 06, 2011 7:33 AM
>>     *To:* Frits Hoogland; toon.koppelaars_at_rulegen.com
>>     <mailto:toon.koppelaars_at_rulegen.com>
>>     *Cc:* Walker, Jed S; oracle-l_at_freelists.org
>>     <mailto:oracle-l_at_freelists.org>
>>     *Subject:* Re: RAC design question
>>
>>     Hi,
>>
>>     I would like to put one or two more points into the discussion.
>>
>>     If you would like to prevent connections from dropping by
>>     employing TAF you need at least 2 preferred instances.  I also
>>     got best results from FCF with the same setup, plus it could give
>>     you runtime load balancing. But then again I haven't heard of
>>     anyone using FCF (and UCP) in real world applications ...
>>
>>     Since you didn't tell us more about your application you need to
>>     decide if these points are applicable.
>>
>>     If you really only needed higher availability you could have with
>>     an active passive cluster and saved on licenses...
>>
>>     How this helps,
>>
>>     Martin
>>
>>     Martin Bach
>>
>>     Martin Bach Consulting
>>     http://martincarstenbach.wordpress.com
>>     <http://martincarstenbach.wordpress.com/>
>>     http://www.linkedin.com/in/martincarstenbach
>>
>>     ----- Reply message -----
>>     From: "Frits Hoogland" <frits.hoogland_at_gmail.com>
>>     <mailto:frits.hoogland_at_gmail.com>
>>     Date: Sat, Aug 6, 2011 08:57
>>     Subject: RAC design question
>>     To: "toon.koppelaars_at_rulegen.com"
>>     <mailto:toon.koppelaars_at_rulegen.com>
>>     <toon.koppelaars_at_rulegen.com> <mailto:toon.koppelaars_at_rulegen.com>
>>     Cc: "Jed_Walker_at_cable.comcast.com"
>>     <mailto:Jed_Walker_at_cable.comcast.com>
>>     <Jed_Walker_at_cable.comcast.com>
>>     <mailto:Jed_Walker_at_cable.comcast.com>, "oracle-l_at_freelists.org"
>>     <mailto:oracle-l_at_freelists.org> <oracle-l_at_freelists.org>
>>     <mailto:oracle-l_at_freelists.org>
>>
>>
>>     With the clusterware you can setup a service for every schema
>>     which can fail
>>     over to another instance.
>>
>>     Frits Hoogland
>>
>>     http://fritshoogland.wordpress.com
>>     <http://fritshoogland.wordpress.com/>
>>     mailto:frits.hoogland_at_gmail.com <frits.hoogland_at_gmail.com
>>     <mailto:frits.hoogland_at_gmail.com>>
>>     cell: +31 6 53569942 <tel:%2B31%206%2053569942>
>>
>>     Op 6 aug. 2011 om 08:22 heeft Toon Koppelaars
>>     <toon.koppelaars_at_rulegen.com <mailto:toon.koppelaars_at_rulegen.com>>
>>     het volgende geschreven:
>>
>>     I think you've answered that design question very wisely.
>>
>>
>>     On Sat, Aug 6, 2011 at 12:49 AM, Walker, Jed S
>>     <Jed_Walker_at_cable.comcast.com
>>     <mailto:Jed_Walker_at_cable.comcast.com%0b>> wrote:
>>
>>     >  Hi,****
>>     >
>>     > ** **
>>     >
>>     > I�m new to RAC, but have a question. We have a 5 node RAC that
>>     supports
>>     > multiple markets each of which has its own schema. Due to each
>>     market having
>>     > its own schema, there is no sharing of blocks between markets.
>>     As such, I am
>>     > thinking that it would make sense to have each market work on
>>     only one node
>>     > because that would avoid having blocks passed between nodes,
>>     and thus should
>>     > be good for performance. (Note: the intent behind RAC was for high
>>     > availability, not for scaling, each node can handle the
>>     workload of multiple
>>     > markets).****
>>     >
>>     > ** **
>>     >
>>     > Thoughts?****
>>     >
>>     > ** **
>>     >
>>     > **-          **Jed****
>>     >
>>     > ** **
>>     >
>>
>>
>>
>>     -- 
>>     Toon Koppelaars
>>     RuleGen BV
>>     Toon.Koppelaars_at_RuleGen.com <mailto:Toon.Koppelaars_at_RuleGen.com>
>>     www.RuleGen.com <http://www.rulegen.com/>
>>     TheHelsinkiDeclaration.blogspot.com
>>     <http://thehelsinkideclaration.blogspot.com/>
>>
>>     (co)Author: "Applied Mathematics for Database Professionals"
>>     www.rulegen.com/am4dp-backcover-text
>>     <http://www.rulegen.com/am4dp-backcover-text>
>>
>
>

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

Received on Tue Aug 16 2011 - 09:32:47 CDT

This message: [ Message body ]
Next message: Tim Gorman: "Re: help with a GES deadlock"
Previous message: Bobak, Mark: "ADMINISTRIVIA: "Message exceeded allowed body size""
Maybe in reply to: Walker, Jed S: "RAC design question"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message