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

From Wikibooks, open books for an open world
Jump to: navigation, search


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

    If there are any sessions, you can kill them with the output of this SQL:

    select 'alter system disconnect session '''||
            sid||','||serial#||''' immediate;' 
     from v$session where username='SH';
    


  2. Re-enable server-side load balancing on both instances by setting the REMOTE_LISTENER init parameter back to its default (collab-scan:1521) and re-registering. Before registering with the listeners, restart them to reset the connection statistics.

    SQL> alter system set remote_listener='collab-scan:1521';
     
    System altered.
     
    collabn1:/home/oracle[RAC1]$ lsnrctl stop
    collabn1:/home/oracle[RAC1]$ lsnrctl start
     
    collabn2:/home/oracle[RAC2]$ lsnrctl stop
    collabn2:/home/oracle[RAC2]$ lsnrctl start
    

    Why is it true that you only need to run the "alter system" command once, but you need to run the lsnrctl command on both nodes?

    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 2 handler(s) for this service...
        Handler(s):
          "DEDICATED" established:0 refused:0 state:ready
             REMOTE SERVER
             (ADDRESS=(PROTOCOL=TCP)(HOST=collabn1.vm.ardentperf.com)(PORT=1521))
          "DEDICATED" established:0 refused:0 state:ready
             LOCAL SERVER
      Instance "RAC2", status READY, has 1 handler(s) for this service...
        Handler(s):
          "DEDICATED" established:0 refused:0 state:ready
             REMOTE SERVER
             (ADDRESS=(PROTOCOL=TCP)(HOST=collabn2.vm.ardentperf.com)(PORT=1521))
    


  3. Edit the TNSNAMES.ORA file on the server you're connected to. Add an entry called LBTEST that connects to the RAC service - but only uses one listener.

    [root@collabn1 ~]# vi $ORACLE_HOME/network/admin/tnsnames.ora
    LBTEST =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = collab-scan)(PORT = 1521))
        (LOAD_BALANCE = no)
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = RAC.vm.ardentperf.com)
        )
      )
    


  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 - but this time it will use the LBTEST connection.

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

    How were the connections distributed between the database instances during server-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>