Just test connection with FAILOVER on RAC

Someone asked me something about connection with Database Service (FAILOVER), When instance crashed. OK!!! I tested 11gR2 with SCAN.
On tnsnames.ora file

service1_by_scan =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = db-scan)(PORT = 1521))
(FAILOVER=on)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = service1)
)
)

Case 1: Nothing on database service (***just created and started it***)

SQL> select name, failover_method from dba_services where name='service1';

NAME FAILOVER_METHOD
---------------------------------------------------------------- ----------------------------------------------------------------
service1 NONE

$ sqlplus system@service1_by_scan
SQL*Plus: Release 11.2.0.2.0 Production on Sun Jul 10 19:26:51 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.

Enter password:

SQL> SELECT INST_ID, username, program, machine, service_name, FAILOVER_TYPE FROM gv$session where username='SYSTEM';

INST_ID USERNAME PROGRAM MACHINE SERVICE_NAME FAILOVER_TYPE
---------- ------------------------------ ------------------------------------------------ --------------- --------------- -------------
2 SYSTEM oracle@db02 (PZ99) client service1 NONE
2 SYSTEM sqlplus@client (TNS V1-V3) client service1 NONE
1 SYSTEM oracle@db01 (PZ99) client service1 NONE

Connect by SYS user:

SQL> show user;
USER is "SYS"

SQL> SELECT INST_ID, username, program, machine, service_name FROM gv$session where username='SYSTEM';

INST_ID USERNAME PROGRAM MACHINE SERVICE_NAME
---------- ------------------------------ ------------------------------------------------ --------------- ---------------
2 SYSTEM sqlplus@client (TNS V1-V3) client service1

Test Instance on db02 Crashed!!! *** don't do this on Production ***

$ ps -aef | grep smon |grep db
oracle 5582 1 0 Jun09 ? 00:02:09 ora_smon_db2

$ kill -9 5582

Go back to "system" User session.

SQL> /
SELECT INST_ID, username, program, machine, service_name, FAILOVER_TYPE FROM gv$session where username='SYSTEM'
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 28757
Session ID: 200 Serial number: 7

Case2: Modified database service with DBMS_SERVICE.MODIFY_SERVICE -> failover_type => DBMS_SERVICE.FAILOVER_TYPE_SELECT, failover_retries => 10,failover_delay => 1

SQL> exec DBMS_SERVICE.MODIFY_SERVICE(service_name=>'service1',goal => DBMS_SERVICE.GOAL_THROUGHPUT, failover_method => DBMS_SERVICE.FAILOVER_METHOD_BASIC, failover_type => DBMS_SERVICE.FAILOVER_TYPE_SELECT, failover_retries => 10,failover_delay => 1, clb_goal => DBMS_SERVICE.CLB_GOAL_LONG);

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> select name, failover_method from dba_services where name='service1';

NAME FAILOVER_METHOD
---------------------------------------------------------------- ----------------------------------------------------------------
service1 BASIC

Test again... with database service.

$ sqlplus system@service1_by_scan
SQL*Plus: Release 11.2.0.2.0 Production on Sun Jul 10 19:36:39 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.

Enter password:

SQL> SELECT INST_ID, username, program, machine, service_name, FAILOVER_TYPE FROM gv$session where username='SYSTEM';

INST_ID USERNAME PROGRAM MACHINE SERVICE_NAME FAILOVER_TYPE
---------- ------------------------------ ------------------------------------------------ --------------- --------------- -------------
1 SYSTEM oracle@db01 (PZ99) client service1 NONE
1 SYSTEM sqlplus@client (TNS V1-V3) client service1 SELECT
2 SYSTEM oracle@db02 (PZ99) client service1 NONE

Connect by SYS user:

SQL> show user;
USER is "SYS"

SQL> SELECT INST_ID, username, program, machine, service_name FROM gv$session where username='SYSTEM';

INST_ID USERNAME PROGRAM MACHINE SERVICE_NAME
---------- ------------------------------ ------------------------------------------------ --------------- ---------------
1 SYSTEM sqlplus@client (TNS V1-V3) client service1

Test Instance on db01 Crashed!!! *** don't do this on Production ***

$ ps -aef | grep smon | grep db
oracle 29353 1 0 Jun09 ? 00:02:15 ora_smon_db1

$ kill -9 29353

Go back to "system" User session.

SQL> /

INST_ID USERNAME PROGRAM MACHINE SERVICE_NAME FAILOVER_TYPE
---------- ------------------------------ ------------------------------------------------ --------------- --------------- -------------
1 SYSTEM oracle@db01 (PZ99) client service1 NONE
1 SYSTEM sqlplus@client (TNS V1-V3) client service1 SELECT
2 SYSTEM oracle@db02 (PZ99) client service1 NONE

SQL> /

INST_ID USERNAME PROGRAM MACHINE SERVICE_NAME FAILOVER_TYPE
---------- ------------------------------ ------------------------------------------------ --------------- --------------- -------------
2 SYSTEM sqlplus@client (TNS V1-V3) client service1 SELECT

SQL> /

INST_ID USERNAME PROGRAM MACHINE SERVICE_NAME FAILOVER_TYPE
---------- ------------------------------ ------------------------------------------------ --------------- --------------- -------------
2 SYSTEM sqlplus@client (TNS V1-V3) client service1 SELECT

SQL> /

INST_ID USERNAME PROGRAM MACHINE SERVICE_NAME FAILOVER_TYPE
---------- ------------------------------ ------------------------------------------------ --------------- --------------- -------------
2 SYSTEM oracle@db02 (PZ99) client service1 NONE
2 SYSTEM sqlplus@client (TNS V1-V3) client service1 SELECT
1 SYSTEM oracle@db01 (PZ99) client service1 NONE

Use SYS user to check.

SQL> show user;
USER is "SYS"

SQL> SELECT INST_ID, username, program, machine, service_name FROM gv$session where username='SYSTEM';

INST_ID USERNAME PROGRAM MACHINE SERVICE_NAME
---------- ------------------------------ ------------------------------------------------ --------------- ---------------
2 SYSTEM sqlplus@client (TNS V1-V3) client service1

This case, I used "DBMS_SERVICE.MODIFY_SERVICE" for server side. but You can set on client side.
On tnsnames.ora file.

service1_by_scan =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = db-scan)(PORT = 1521))
(FAILOVER=on)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = service1)
(FAILOVER_MODE =
(TYPE = SELECT)
(METHOD = BASIC)
(RETRIES = 10)
(DELAY = 1)
)
)
)

$ sqlplus system@service1_by_scan
SQL*Plus: Release 11.2.0.2.0 Production on Sun Jul 10 19:45:28 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.

Enter password:

SQL> select name, failover_method from dba_services where name='service1';

NAME FAILOVER_METHOD
---------------------------------------------------------------- ----------------------------------------------------------------
service1 NONE

SQL> SELECT INST_ID, username, program, machine, service_name, FAILOVER_TYPE FROM gv$session where username='SYSTEM';

INST_ID USERNAME PROGRAM MACHINE SERVICE_NAME FAILOVER_TYPE
---------- ------------------------------ ------------------------------------------------ --------------- --------------- -------------
1 SYSTEM oracle@db01 (PZ99) client service1 NONE
1 SYSTEM sqlplus@client (TNS V1-V3) client service1 SELECT
2 SYSTEM oracle@db02 (PZ99) client service1 NONE

This case, After test instance crashed... Connection still work(Not lost connection). FAILOVER work... When I used failover_type -

(FAILOVER_MODE =
(TYPE = SELECT)
(METHOD = BASIC)
(RETRIES = 10)
(DELAY = 1)
)

Good Idea with FAILOVER on client side with FAILOVER_MODEL: TYPE + METHOD + RETRIES + DELAY.