Open main menu

Wikibooks β

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

< RAC Attack - Oracle Cluster Database at Home

  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 "" has 1 instance(s).
      Instance "RAC1", status READY, has 2 handler(s) for this service...
          "DEDICATED" established:0 refused:0 state:ready
             REMOTE SERVER
          "DEDICATED" established:0 refused:0 state:ready
             LOCAL SERVER
      Instance "RAC2", status READY, has 1 handler(s) for this service...
          "DEDICATED" established:0 refused:0 state:ready
             REMOTE SERVER

  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 =
        (ADDRESS = (PROTOCOL = TCP)(HOST = collab-scan)(PORT = 1521))
        (LOAD_BALANCE = no)
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME =

  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 &

    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