Play with my toy 10g RAC - Test Transparent Application Failover (TAF)

Finally I got my first toy 10g RAC set up based on Vincent Chan's excellent document (published in 2007, this doc has been not availabe on the original OTN websitr any more). This toy RAC has two nodes, which are VMWare server 2.0 Virtual Machines with CentOS 4.7 hosted on Windows XP. The next steps are of course to explore various RAC specific features on it as much as possible. I plan to re-visit all the hands-on labs I experienced when I was in an Oracle University Course "Oracle Database 10g: RAC for Adminstrators" about 2.5 years ago. I have been planning to set up a RAC since then, however, the actual action has been delayed again and again. Now the world is talking about 11g RAC though, I am still excited that I finally got this little out-dated toy done.

Vicent's document also provided a test to demonstrate TAF, which I repeated here as a warm-up for my exploration:



(1) Create a Service and start it


Service Name : CRM
Database Name : devdb
Preferred Instance : devdb1
Available Instance : devdb2
TAF Policy : BASIC

[oracle@rac1 ~]$ srvctl add service -d devdb -s CRM -r devdb1 -a devdb2 -P BASIC
[oracle@rac1 ~]$ srvctl config service -d devdb -s CRM -a
CRM PREF: devdb1 AVAIL: devdb2 TAF: BASIC
[oracle@rac1 ~]$ srvctl start service -d devdb -s CRM
[oracle@rac1 ~]$ ./crs_rep.sh
HA Resource                                   Target     State
-----------                                   ------     -----
ora.devdb.CRM.cs                              ONLINE     ONLINE on rac1
ora.devdb.CRM.devdb1.srv                      ONLINE     ONLINE on rac1
ora.devdb.CRM.devdb2.srv                      ONLINE     ONLINE on rac2
ora.devdb.db                                  ONLINE     ONLINE on rac1
ora.devdb.devdb1.inst                         ONLINE     ONLINE on rac1
ora.devdb.devdb2.inst                         ONLINE     ONLINE on rac2
ora.rac1.ASM1.asm                             ONLINE     ONLINE on rac1
ora.rac1.LISTENER_RAC1.lsnr                   ONLINE     ONLINE on rac1
ora.rac1.gsd                                  ONLINE     ONLINE on rac1
ora.rac1.ons                                  ONLINE     ONLINE on rac1
ora.rac1.vip                                  ONLINE     ONLINE on rac1
ora.rac2.ASM2.asm                             ONLINE     ONLINE on rac2
ora.rac2.LISTENER_RAC2.lsnr                   ONLINE     ONLINE on rac2
ora.rac2.gsd                                  ONLINE     ONLINE on rac2
ora.rac2.ons                                  ONLINE     ONLINE on rac2
ora.rac2.vip                                  ONLINE     ONLINE on rac2

(2) Add the following entry in the client tnsnames.ora

CRM =
(DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.31)(PORT = 1521))
   (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.32)(PORT = 1521))
   (LOAD_BALANCE = yes)
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = CRM)
     (FAILOVER_MODE =
        (TYPE = SELECT)
        (METHOD = BASIC)
        (RETRIES = 180)
        (DELAY = 5)
     )
   )
)

(3) Connect the first session using CRM Service

C:\Documents and Settings\denis>sqlplus denis@CRM

SQL*Plus: Release 11.1.0.6.0 - Production on Tue Apr 6 14:48:54 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> select
  2   failover_type,
  3   failover_method,
  4   failed_over
  5   from v$session
  6   where username='DENIS';

FAILOVER_TYPE FAILOVER_M FAI
------------- ---------- ---
SELECT        BASIC      NO


SQL> select
  2   instance_number instance#,
  3   instance_name,
  4   host_name,
  5   status
  6   from v$instance;

 INSTANCE# INSTANCE_NAME    HOST_NAME            STATUS
---------- ---------------- -------------------- ------------
         1 devdb1           rac1.localdomain     OPEN

(4) Shutdwon instance devdb1 from another session login as SYS

sys@DEVDB> select
  2   instance_number instance#,
  3   instance_name,
  4   host_name,
  5   status
  6  from v$instance;

 INSTANCE# INSTANCE_NAME    HOST_NAME            STATUS
---------- ---------------- -------------------- ------------
         1 devdb1           rac1.localdomain     OPEN


sys@DEVDB> shutdown abort
ORACLE instance shut down.

(5) Verify the session has failed over.
In the first session:

SQL> select
  2   instance_number instance#,
  3   instance_name,
  4   host_name,
  5   status
  6  from v$instance;

 INSTANCE# INSTANCE_NAME    HOST_NAME            STATUS
---------- ---------------- -------------------- ------------
         2 devdb2           rac2.localdomain     OPEN


SQL> select
  2    failover_type,
  3    failover_method,
  4    failed_over
  5   from v$session
  6  where username='DENIS';

FAILOVER_TYPE FAILOVER_M FAI
------------- ---------- ---
SELECT        BASIC      YES

(6) Relocate the CRM service back to the preferred instance.

After devdb1 is brought back up, the CRM service does not automatically relocate
back to the preferred instance. This means any new connections using CRM service will be on devdb2, instead of the preferred instance
devdb1. This can be achieved by the following command:

$ srvctl relocate service -d devdb -s CRM -i devdb2 -t devdb1

The meaning of the command options can be seen here:

[oracle@rac1 ~]$ srvctl relocate service -h
Usage: srvctl relocate service -d <name> -s <service_name> -i <old_inst_name> -t <new_inst_name> [-f]
    -d <name>           Unique name for the database
    -s <service>        Service name
    -i <old_inst>       Old instance name
    -t <new_inst>       New instance name
    -f                  Disconnect all sessions during stop or relocate service operations
    -h                  Print usage