So RAC has a pretty cool tool called Server Control or better known as srvctl, with this you can manage the services in a RAC farm, and one of the sometimes and much needed is when you have several ORACLE_HOMEs and you only need to shutdown one, as you are going to apply a patch to that one, no problem

srvctl stop home -o -s -n [-t ] [-f]

But what happens to the rest of the people who don’t have this tool and are still in a single node environment, you can still do this with this simple Unix shell script. I won’t say this more than once, use at your own discretion and revise before using it, this should always be tested before using it on a production environment.

The most important thing is you need to have a directory in which you have you environment variables for each of your Databases that are in the host, in this case is in ~/environment_vars/.

This script accepts 4 inputs

  • DB_LIST .- This can be the name of the Database that you will shutdown or you can input oratab to cycle through the oratab file and execute the same command
  • INIT_COMMAND.- This is either STARTUP or SHUTDOWN
  • INIT_MODE.- This depends on the previous command, as if its STARTUP you can have NOMOUNT, MOUNT, OPEN or if its SHUTDOWN it can be IMMEDIATE , TRANSACTIONAL , ABORT
  • HOME_TO_FILTER.- This will filter the oratab by the type of ORACLE_HOME that you want to shutdown

#!/bin/ksh
#set -x
#########################################################################################
# cycle_sid_list ()
# This function will cycle through the DBs in the oratab or set the DB to start/shutdown
#########################################################################################
cycle_sid_list()
{
if [ ${DB_LIST} = "oratab" ]; then
dblist=`cat $ORATABLOC | grep -v # | grep -v * | grep "${HOME_TO_FILTER}" | grep ":Y" | awk -F: '{print $1}' `
else
dblist=${DB_LIST}
fi


for dbn in ${dblist}
do
echo "${dbn}"
. ~/environment_vars/${dbn}
echo "*******"
start_shut ${dbn} ${INIT_COMMAND} ${INIT_MODE}
done
}

################################################################################
# start_shut ()
# This function will stop/start the DB via sqlplus
################################################################################
start_shut()
{
ORACLE_UNQNAME=$1
. ~/environment_vars/${ORACLE_UNQNAME}

echo "========>${INIT_COMMAND} ${ORACLE_UNQNAME} in ${INIT_MODE} mode "
echo
sqlplus /nolog <<EOF >${LOG_DIR}/ora.error.$$
connect / as sysdba
${INIT_COMMAND} ${INIT_MODE};
exit
EOF
if [ ${INIT_COMMAND} = "SHUTDOWN" ]; then
oracle_error=`cat ${LOG_DIR} /ora.error.$$ | grep -v "ORA-01507" | grep -v "ORA-01034" | grep -v "ORA-27101" | grep "ORA-" | wc -l `
else
oracle_error=`cat ${LOG_DIR}/ora.error.$$ | grep "ORA-" | wc -l`
fi
if [ ${oracle_error} -gt 0 ]; then
echo "###############################################################"
echo "## ERROR ====> ${INIT_COMMAND} of Oracle failed "
cat ${LOG_DIR}/ora.error.$$ | grep ORA- | awk '{print "##", $0}'
echo "###############################################################"
else
echo "========> ${INIT_COMMAND} of ${ORACLE_UNQNAME} Succesfull "
echo
fi
}

print_header()
{
echo " *************************************************"
echo " Database ORACLE HOME IN `hostname` STATUS"
echo " `date '+DATE: %m/%d/%y TIME:%H:%M:%S'`"
echo " *************************************************"
}
################################################################################
## ------------------------------------------------------------------------ ##
## MAIN SCRIPT EXECUTION ##
## ------------------------------------------------------------------------ ##
################################################################################


DB_LIST=$1
typeset -u INIT_COMMAND=$2
typeset -u INIT_MODE=$3
HOME_TO_FILTER=$4
LOG_DIR=/audit

if [ ${DB_LIST} = "oratab" ]; then
if [ "`uname -a | cut -c1-3`" = "Sun" ]; then
ORATABLOC="/var/opt/oracle/oratab"
else
ORATABLOC="/etc/oratab"
fi
fi

export DB_LIST INIT_COMMAND INIT_MODE HOME_TO_FILTER ORATABLOC LOG_DIR
print_header
cycle_sid_list

You will see a result similar to this one

oracle $ start_stop.sh TESTDB STARTUP NOMOUNT 11.2.0.3
*************************************************
CLIENT ORACLE HOME IN servidor1.oracleenespanol.blogspot.com STATUS
DATE: 07/05/12 TIME:08:47:11
*************************************************
TESTDB
*******
========>STARTUP TESTDB in NOMOUNT mode

========> STARTUP of TESTDB Succesfull