RAC Attack - Oracle Cluster Database at Home/Runtime Failover

Services, Failover and Load Balancing (e)

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;