RAC Attack - Oracle Cluster Database at Home/Runtime Failover

 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;

