# RAC Attack - Oracle Cluster Database at Home/Runtime Failover

Prev: Clusterware Testing
* Install Instant Client
* Service Failover
* Connection Failover
* Runtime Failover
* Client Load Balancing
* Server Load Balancing
Next: RAC SQL and PLSQL
* Power on collabn1 from the VMware Console. After it has started, login as the oracle user and failback the svctest service which we created earlier in this lab. [root@collabn1 ~]# srvctl status database -d RAC Instance RAC1 is running on node collabn1 Instance RAC2 is running on node collabn2 collabn1:/home/oracle[RAC1]$ srvctl status service -d RAC -s svctest Service svctest is running on instance(s) RAC2 collabn1:/home/oracle[RAC1]$ srvctl relocate service -d RAC -s svctest -i RAC2 -t RAC1 collabn1:/home/oracle[RAC1]$ srvctl status service -d RAC -s svctest Service svctest is running on instance(s) RAC1
* On your local computer edit the TNSNAMES.ORA file used by the Instance Client. Add a new entry called SVCTEST which connects to the svctest service and make sure that the connection works. Also check your TAF settings after connecting. (Side note: we did not configure this service with a domain name, but you can't connect to it unless you specify one in the TNSNAMES entry. Try it. Where did this domain name come from?) C:\instantclient_11_2> notepad c:\instantclient_11_2\tnsnames.ora SVCTEST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = collab-scan)(PORT = 1521)) (LOAD_BALANCE = yes) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = svctest.vm.ardentperf.com) ) ) SVCTEST-NOVIP = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = collabn1)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = collabn2)(PORT = 1521)) (LOAD_BALANCE = yes) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = svctest.vm.ardentperf.com) ) ) C:\instantclient_11_2> sqlplus sh/sh@svctest SQL> select instance_name from v$instance; INSTANCE_NAME ---------------- RAC1 SQL> col service_name format a20 SQL> col username format a10 SQL> select username, service_name, failover_method, failover_type 2 from v$session where sid=(select max(sid) from v$mystat); USERNAME SERVICE_NAME FAILOVER_M FAILOVER_TYPE ---------- -------------------- ---------- ------------- SH svctest NONE NONE
* From your Windows SQLPlus session, update the server-side TAF (Transparent Application Failover) settings for the svctest service. begin dbms_service.modify_service( service_name=>'svctest', failover_type=>dbms_service.failover_type_select, failover_method=>dbms_service.failover_method_basic, failover_delay=>5, failover_retries=>60 ); end; / PL/SQL procedure successfully completed.
* Reconnect and check your session's TAF settings again. SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production With the Partitioning, Real Application Clusters, OLAP, Data Mining and Real Application Testing options C:\instantclient_11_2> sqlplus sh/sh@svctest SQL> select instance_name from v$instance; INSTANCE_NAME ---------------- RAC1 SQL> col service_name format a20 SQL> col username format a10 SQL> select username, service_name, failover_method, failover_type 2 from v$session where sid=(select max(sid) from v$mystat); USERNAME SERVICE_NAME FAILOVER_M FAILOVER_TYPE ---------- -------------------- ---------- ------------- SH svctest BASIC SELECT
* Simultaneously open a second sqlplus session connected to the NOVIP service. C:\> cd \instantclient_11_2 C:\instantclient_11_2> set TNS_ADMIN=c:\instantclient_11_2 C:\instantclient_11_2> sqlplus sh/sh@svctest-novip SQL> select instance_name from v$instance; INSTANCE_NAME ---------------- RAC1 SQL> col service_name format a20 SQL> col username format a10 SQL> select username, service_name, failover_method, failover_type 2 from v$session where sid=(select max(sid) from v$mystat); USERNAME SERVICE_NAME FAILOVER_M FAILOVER_TYPE ---------- -------------------- ---------- ------------- SH svctest BASIC SELECT
* Start a long-running query in both queries. While the query is running, Power Down the node that they are connected to. What happens to each session? select c.cust_last_name, p.prod_name, s.quantity_sold from products p, sales s, customers c where p.prod_id = s.prod_id and c.cust_id = s.cust_id;