Last modified on 5 April 2013, at 05:54

RAC Attack - Oracle Cluster Database at Home/Runtime Failover


  1. 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.

    RA-vmweb-poweron.png
    [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
    


  2. 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
    


  3. 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.
    


  4. 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
    


  5. 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
    


  6. 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;