Script to list sessions on a RAC instance by node

As part of the Exadata Patching, I needed to provide the application owners with a list of sessions running on the node.

This script does that - it uses the oratab to grab the database names then loops through them and runs a couple of SQL scripts.

I thought I'd put it up here because it can be adapted to run a SQL command against every database on a host, something I often have cause to do and end up having to write the script again.  You may need to change the 'dbhome_1' to 'db_1' depending on how you installed.

#!/bin/sh
#
# This script lists all sessions connected to the specified hostname in the query.
# It ignores the SYS, DBSNMP and PUBLIC users
#
# Hard code the ORACLE_HOME and a valid SID, and the host_name
#
export ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/dbhome_1
export ORACLE_SID=PROD1
export PATH=$ORACLE_HOME/bin:$PATH
rm sessions.lst
db_name=`grep dbhome_1 /etc/oratab | cut -f 1 -d ':' | grep "[1-9]\b" | grep -v "#"`

for db in $db_name
do
export ORACLE_SID=$db
sqlplus -s "/ as sysdba" <
col inst_id form 99
col sid form 99999
col serial# form 9999999
col program form a40
col username form a20
col host_name form a10
set lines 160
set feedback off
spool ${db}_sessions.txt
select instance_name from v\$instance;
set feedback on
select a.inst_id,b.host_name,a.sid,a.serial#,a.program,a.username
from gv\$session a, gv\$instance b where username is not null and a.inst_id=b.inst_id and b.host_name='xu1db06' and username
not in ('SYS','DBSNMP','PUBLIC') order by 1;
spool off
exit
EOF
done
for f in `ls *.txt`
do
cat $f >> sessions.lst
done
rm *sessions.txt

#
# End of Script
#

While I'm at it, here's a script that sets up the patching process - it lists the services running, and generates the scripts to stop the services, the instances, and then restart them at the end.


#!/bin/sh
#
# This script lists all services running on the node
# and generates scripts to stop and start them
#
# Hard code the node name in the line below where it says "NODENAME"
#

db_name=`grep dbhome_1 /etc/oratab | cut -f 1 -d ':' | grep -v "[1-9]\b" | grep -v "#"`

echo "Make sure you've changed the node name in the script - ctrl-c to exit this if you didn't, otherwise hit Enter"
read input

rm service_status.lst
rm stop_services.sh
rm services_before_patch.lst

echo Scanning for Services...will take a minute
for db in $db_name
do
srvctl status service -d $db >> service_status.tmp
tail -1 service_status.tmp
done

cp service_status.tmp services_before_patch.lst
grep -v not service_status.tmp > service_status.lst
rm service_status.tmp

echo
echo
echo "This has been saved in services_before_patch.lst"
#
# Hard code the node name here (replace NODENAME)
awk '{print $2" "$7}' service_status.lst | awk -F, '{print $1}' | sed 's/[0-9]$//' | awk '{print "srvctl stop service -d "$2" -s "$1" -n NODENAME "}' > stop_services.sh

sed 's/stop/start/g' stop_services.sh > start_services.sh

inst_name=`ps -ef | grep pmon | grep -v grep | grep -v perl |awk '{print $8}' | grep -v ASM | cut -c 10-`

for i_name in $inst_name
do
echo "srvctl stop instance -d "$i_name" -i "$i_name >> temp_si
done

cat temp_si | sed 's/[0-9]//' > temp_si_1
grep -v XUFSDB temp_si_1 > stop_instances.sh

rm temp_si temp_si_1

sed 's/stop/start/g' stop_instances.sh > start_instances.sh

echo
echo "You need to do the following:"
echo
echo "chmod +x stop_services.sh"
echo
echo "chmod +x start_services.sh"
echo
echo "chmod +x stop_instances.sh"
echo
echo "chmod +x start_instances.sh"
echo
echo "Then run them in this order once the application owners have been advised:"
echo
echo "./stop_services.sh"
echo
echo "./stop_instances.sh"
echo
echo "Stop the DBFS mount service and the XU1FSDB instance"
echo
echo "APPLY THE PATCH"
echo
echo "./start_services.sh"
echo
echo "Restart the DBFS mount service"
echo
echo "./start_instances.sh"
echo
echo "Compare the running services and instances before and after the stop/start"

#
# End of Script
#