TAF Failover Configuration and Testing

Configure the service on RAC servers for a failover

TNS Client side config

PROD =
(DESCRIPTION =
(enable=broken)
(LOAD_BALANCE = yes)
(ADDRESS = (PROTOCOL = TCP)(HOST = oravip01.oracledbasupport.com)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = oravip02.oracledbasupport.com)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = prod)
(failover_mode=(type=select)(method=basic))
)
)

Let’s test a Failover – Connect to an Oracle Instance 1 or 2

oracle@ora02 ~]$ showcrs
HA Resource Target State
———– —— —–
ora.ora01.ASM1.asm ONLINE ONLINE on ora01
ora.ora01.LISTENER_ora01.lsnr ONLINE ONLINE on ora01
ora.ora01.gsd ONLINE UNKNOWN on ora01
ora.ora01.ons ONLINE UNKNOWN on ora01
ora.ora01.vip ONLINE ONLINE on ora01
ora.ora02.ASM2.asm ONLINE ONLINE on ora02
ora.ora02.LISTENER_ora02.lsnr ONLINE ONLINE on ora02
ora.ora02.gsd ONLINE UNKNOWN on ora02
ora.ora02.ons ONLINE UNKNOWN on ora02
ora.ora02.vip ONLINE ONLINE on ora02
ora.prod.db ONLINE ONLINE on ora01
ora.prod.prod.cs ONLINE ONLINE on ora02
ora.prod.prod.prod1.srv ONLINE ONLINE on ora01
ora.prod.prod.prod2.srv ONLINE ONLINE on ora02
ora.prod.prod1.inst ONLINE ONLINE on ora01
ora.prod.prod2.inst ONLINE ONLINE on ora02

SQL> select instance_name from v$instance;
INSTANCE_NAME
—————-
prod2

[oracle@ora02 ~]$ crs_stop ora.prod.prod2.inst
Attempting to stop `ora.prod.prod2.inst` on member `ora02`
Stop of `ora.prod.prod2.inst` on member `ora02` succeeded.
At this stage the connections are diverted to prod1 instance.

SQL> select instance_name from v$instance;
INSTANCE_NAME
—————-
prod1

[oracle@ora02 ~]$ showcrs
HA Resource Target State
———– —— —–
ora.ora01.ASM1.asm ONLINE ONLINE on ora01
ora.ora01.LISTENER_ora01.lsnr ONLINE ONLINE on ora01
ora.ora01.gsd ONLINE UNKNOWN on ora01
ora.ora01.ons ONLINE UNKNOWN on ora01
ora.ora01.vip ONLINE ONLINE on ora01
ora.ora02.ASM2.asm ONLINE ONLINE on ora02
ora.ora02.LISTENER_ora02.lsnr ONLINE ONLINE on ora02
ora.ora02.gsd ONLINE UNKNOWN on ora02
ora.ora02.ons ONLINE UNKNOWN on ora02
ora.ora02.vip ONLINE ONLINE on ora01
ora.prod.db ONLINE ONLINE on ora01
ora.prod.prod.cs ONLINE ONLINE on ora02
ora.prod.prod.prod1.srv ONLINE ONLINE on ora01
ora.prod.prod.prod2.srv ONLINE OFFLINE
ora.prod.prod1.inst ONLINE ONLINE on ora01
ora.prod.prod2.inst OFFLINE OFFLINE

[oracle@ora02 ~]$ crs_start ora.prod.prod2.inst
Attempting to start `ora.prod.prod2.inst` on member `ora02`
Start of `ora.prod.prod2.inst` on member `ora02` succeeded.

What happens if Server is restarted?

I am connected to prod2 instance and a reboot migrates my connection to prod1 automatically.

SQL> select instance_name from v$instance;
INSTANCE_NAME
—————-
prod2

SQL> select count(*) from
(select * from dba_source union select * from dba_source union select * from dba_source union select * from dba_source union select * from dba_source)
COUNT(*)
———-
292465

SQL> select instance_name from v$instance;
INSTANCE_NAME
—————-
prod1

Let’s see how a RAC Load balancing works? Write a small sql test Script (verify.sql) like below

REM the following query is for TAF connection verification
col sid format 999
col serial# format 9999999
col failover_type format a13
col failover_method format a15
col failed_over format a11
SELECT   sid,
 serial#,
 failover_type,
 failover_method,
 failed_over
 FROM   v$session
 WHERE   username = 'SU';

REM the following query is for load balancing verification
SELECT   instance_name FROM v$instance;
exit

REM We can also combine two queries:
col inst_id format 999
col sid format 999
col serial# format 9999999
col failover_type format a13
col failover_method format a15
col failed_over format a11
SELECT   inst_id,
 sid,
 serial#,
 failover_type,
 failover_method,
 failed_over
 FROM   gv$session
 WHERE   username = 'SU';

REM a simple select to see the distribution of users when testing connection : load balancing
 SELECT   inst_id, COUNT ( * )
 FROM   gv$session
GROUP BY   inst_id;

Write loop.sh file to make number SQL connections. Please copy and paste at least 100 entries of line below. Oracle Listener will load balance connections by diverting new connections to least loaded oracle RAC instance.

nohup sqlplus system/0ra01@failover @verify.sql &
sleep 1
nohup sqlplus system/0ra01@failover @verify.sql &
sleep 1
nohup sqlplus system/0ra01@failover @verify.sql &
sleep 1
nohup sqlplus system/0ra01@failover @verify.sql &
sleep 1

Run loop.sh and note down connections shared between RAC 1 & RAC 2 nodes

[oracle@ora01 scripts]$ grep prod2 nohup.out | wc -l
35
[oracle@ora01 scripts]$ grep prod1 nohup.out | wc -l
41