Last modified on 5 April 2013, at 05:54

RAC Attack - Oracle Cluster Database at Home/Client Load Balancing


Note: This lab was written for 11.1 and does not show the same behavior in 11.2 - it needs to be updated!

--

  1. Power on both nodes from the VMware Console.

    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
    


  2. Open a SQLPlus session on the database and confirm that there are no sessions for the SH user.

    SQL> select inst_id, count(*) from gv$session where username='SH' group by inst_id;
     
    no rows selected
    


  3. Disable server-side load balancing on both instances by clearing the REMOTE_LISTENER init param and re-registering. Before registering with the listeners, restart them to reset the connection statistics.

    SQL> alter system set remote_listener=;
     
    System altered.
     
    collabn1:/home/oracle[RAC1]$ lsnrctl stop
    collabn1:/home/oracle[RAC1]$ lsnrctl start
     
    SQL> -- instance RAC1
    SQL> alter system register;
     
    System altered.
     
    collabn1:/home/oracle[RAC1]$ lsnrctl services
    Service "RAC.vm.ardentperf.com" has 1 instance(s).
      Instance "RAC1", status READY, has 1 handler(s) for this service...
        Handler(s):
          "DEDICATED" established:0 refused:0 state:ready
             LOCAL SERVER
    


    collabn2:/home/oracle[RAC2]$ lsnrctl stop
    collabn2:/home/oracle[RAC2]$ lsnrctl start
     
    SQL> -- instance RAC2
    SQL> alter system register;
     
    System altered.
     
    collabn2:/home/oracle[RAC2]$ lsnrctl services
    Service "RAC.vm.ardentperf.com" has 1 instance(s).
      Instance "RAC2", status READY, has 1 handler(s) for this service...
        Handler(s):
          "DEDICATED" established:0 refused:0 state:ready
             LOCAL SERVER
    


  4. In your other connected SQLPlus session, keep an eye on the balance of connections. At the same time, open a new shell session and run this script which will open 160 connections to the database.

    a=160; while [ $a -gt 0 ]; do
     sqlplus sh/sh@RAC &
     a=$((a-1))
    done
    

    How were the connections distributed between the database instances during client-side load balancing?

  5. Terminate all of the sqlplus sessions by running these two commands. After you run the second command, press <Ctrl-C> after you start seeing the message "no more job".

    killall sqlplus
    while true; do fg; done
    <Ctrl-C>