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

From Wikibooks, open books for an open world
Jump to navigation Jump to 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>