Re: RAC design question
- From: Martin Bach <development@xxxxxxxxxxxxxxxxx>
- To: Kumar Madduri <ksmadduri@xxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 16 Aug 2011 15:32:47 +0100
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@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
<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@xxxxxxxxxxxxxxxxx <mailto:development@xxxxxxxxxxxxxxxxx>>
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@xxxxxxxxxxxxxxxxx]
*Sent:* Saturday, August 06, 2011 7:33 AM
*To:* Frits Hoogland; toon.koppelaars@xxxxxxxxxxx
<mailto:toon.koppelaars@xxxxxxxxxxx>
*Cc:* Walker, Jed S; oracle-l@xxxxxxxxxxxxx
<mailto:oracle-l@xxxxxxxxxxxxx>
*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@xxxxxxxxx>
<mailto:frits.hoogland@xxxxxxxxx>
Date: Sat, Aug 6, 2011 08:57
Subject: RAC design question
To: "toon.koppelaars@xxxxxxxxxxx"
<mailto:toon.koppelaars@xxxxxxxxxxx>
<toon.koppelaars@xxxxxxxxxxx> <mailto:toon.koppelaars@xxxxxxxxxxx>
Cc: "Jed_Walker@xxxxxxxxxxxxxxxxx"
<mailto:Jed_Walker@xxxxxxxxxxxxxxxxx>
<Jed_Walker@xxxxxxxxxxxxxxxxx>
<mailto:Jed_Walker@xxxxxxxxxxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx"
<mailto:oracle-l@xxxxxxxxxxxxx> <oracle-l@xxxxxxxxxxxxx>
<mailto:oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxx <frits.hoogland@xxxxxxxxx
<mailto:frits.hoogland@xxxxxxxxx>>
cell: +31 6 53569942 <tel:%2B31%206%2053569942>
Op 6 aug. 2011 om 08:22 heeft Toon Koppelaars
<toon.koppelaars@xxxxxxxxxxx <mailto:toon.koppelaars@xxxxxxxxxxx>>
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@xxxxxxxxxxxxxxxxx
<mailto:Jed_Walker@xxxxxxxxxxxxxxxxx%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@xxxxxxxxxxx <mailto:Toon.Koppelaars@xxxxxxxxxxx>
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>
Other related posts:
