RAC Attack - Oracle Cluster Database at Home/Server Load Balancing
Prev: Clusterware Testing
Services, Failover and Load Balancing (e)
- Install Instant Client
- Service Failover
- Connection Failover
- Runtime Failover
- Client Load Balancing
- Server Load Balancing
Next: RAC SQL and PLSQL
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 <Ctrl-C> after you start seeing the message "no more job".
killall sqlplus while true; do fg; done <Ctrl-C>