RAC Attack - Oracle Cluster Database at Home/Connection Failover

Services, Failover and Load Balancing (e)

IMPORTANT NOTE: This lab was written for Oracle 11gR1 and the information here is crucial when working with this and older versions. It will demonstrate how failover works and the importance of using proper addresses in TNSNAMES. However, starting with 11gR2 the node VIPs should not be used to connect to the database – the SCAN VIP should always be used instead. The 11gR2 client has this same failover functionality built-in for multiple SCAN VIPs returned on a single DNS entry.

On your local computer edit the TNSNAMES.ORA file used by the Instance Client. Add two entries called CFTEST and CFTEST-NOVIP which connect to the RAC service with no load balancing. Explicitly enable connection failover even though it is already enabled by default anyway. Don't use the VIP's for the second entry (this is wrong but we'll test it to see what happens). C:\instantclient_11_2> notepad c:\instantclient_11_2\tnsnames.ora CFTEST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = collabn1-vip)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = collabn2-vip)(PORT = 1521)) (LOAD_BALANCE = no) (FAILOVER = yes) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = RAC.vm.ardentperf.com) ) ) CFTEST-NOVIP = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = collabn1)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = collabn2)(PORT = 1521)) (LOAD_BALANCE = no) (FAILOVER = yes) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = RAC.vm.ardentperf.com) ) )
On collabn1 check the number of established connections from the listener to the RAC service. Connect from Windows to CFTEST and CFTEST-NOVIP several times and then check the lsnrctl statistics again. All connections from the Windows machine are attaching to listener on collabn1 but this listener is spreading the connections between both instances. First look at the number of established connections on node 1. It's ok if they're not all zero. collabn1:/home/oracle[RAC1]$ lsnrctl services Service "RAC.vm.ardentperf.com" has 2 instance(s). Instance "RAC1", status READY, has 2 handler(s) for this service... Handler(s): "DEDICATED" 'established:0' refused:0 state:ready LOCAL SERVER "DEDICATED" 'established:0' refused:0 state:ready REMOTE SERVER (ADDRESS=(PROTOCOL=TCP)(HOST=collabn1.vm.ardentperf.com)(PORT=1521)) Instance "RAC2", status READY, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 state:ready REMOTE SERVER (ADDRESS=(PROTOCOL=TCP)(HOST=collabn2.vm.ardentperf.com)(PORT=1521)) Second, connect to the database several times in a row and use both service names. You can exit each session after you check how long it takes to connect. All of the sessions should connect quickly. Count the number of times you connect. C:\instantclient_11_2> sqlplus sh/sh@CFTEST C:\instantclient_11_2> sqlplus sh/sh@CFTEST C:\instantclient_11_2> sqlplus sh/sh@CFTEST-NOVIP C:\instantclient_11_2> sqlplus sh/sh@CFTEST-NOVIP C:\instantclient_11_2> sqlplus sh/sh@CFTEST C:\instantclient_11_2> sqlplus sh/sh@CFTEST-NOVIP Third, check the listener connections on node 1 again. Make sure that the total number of established connections shows an increase by at least the same number of sessions that you connected. (That is, confirm that all of your sessions connected to this node.) There might be more connections; that's ok. Also, notice how the listener is distributing connections to both instances - even though our client is only connecting to the listener on one node. It doesn't matter how many connections go to each instance; it's ok of you don't see 3 and 3. collabn1:/home/oracle[RAC1]$ lsnrctl services Service "RAC.vm.ardentperf.com" has 2 instance(s). Instance "RAC1", status READY, has 2 handler(s) for this service... Handler(s): "DEDICATED" 'established:3' refused:0 state:ready LOCAL SERVER "DEDICATED" 'established:0' refused:0 state:ready REMOTE SERVER (ADDRESS=(PROTOCOL=TCP)(HOST=collabn1.vm.ardentperf.com)(PORT=1521)) Instance "RAC2", status READY, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:3 refused:0 state:ready REMOTE SERVER (ADDRESS=(PROTOCOL=TCP)(HOST=collabn2.vm.ardentperf.com)(PORT=1521))
In the VMware Console, make sure that you have opened the node collabn1 which you are connecting to from Windows. Simulate a complete node failure by choosing Power Off from the Virtual Machine menu.
Wait about a minute (so that the cluster can detect the failed node). Then, from Windows, connect to the CFTEST and CFTEST-VIP services again. What happens when you use the NOVIP connection? C:\instantclient_11_2> sqlplus sh/sh@CFTEST C:\instantclient_11_2> sqlplus sh/sh@CFTEST C:\instantclient_11_2> sqlplus sh/sh@CFTEST-NOVIP C:\instantclient_11_2> sqlplus sh/sh@CFTEST-NOVIP C:\instantclient_11_2> sqlplus sh/sh@CFTEST C:\instantclient_11_2> sqlplus sh/sh@CFTEST-NOVIP