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

 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';

 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))

 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) )  )

 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?

 Terminate all of the sqlplus sessions by running these two commands. After you run the second command, press  after you start seeing the message "no more job". killall sqlplus while true; do fg; done 

