RE: RAC design question
- From: D'Hooge Freek <Freek.DHooge@xxxxxxxxx>
- To: "development@xxxxxxxxxxxxxxxxx" <development@xxxxxxxxxxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>, Frits Hoogland <frits.hoogland@xxxxxxxxx>, "toon.koppelaars@xxxxxxxxxxx" <toon.koppelaars@xxxxxxxxxxx>
- Date: Wed, 17 Aug 2011 10:09:56 +0200
Martin,
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@xxxxxxxxx
tel +32(0)3 451 23 82
http://www.uptime.be
disclaimer: www.uptime.be/disclaimer
---
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Martin Bach
Sent: dinsdag 16 augustus 2011 21:38
To: oracle-l@xxxxxxxxxxxxx; Frits Hoogland; toon.koppelaars@xxxxxxxxxxx
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@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]
--
//www.freelists.org/webpage/oracle-l
Other related posts:
