Table of content

www.kudos.be

Shutting down Oracle databases quickly in parallel

Most folks running a larger(r) amount of Oracle (single) instances on the one and same server use a sequential method for shutting down their databases at for example backup time or during system shutdown. This can pose some inconveniences and at worst a real problem if it takes a long while before these SHUTDOWN IMMEDIATE commands complete. A concrete example may be the requirement to quickly shutdown your system during a power failure whilst still running on UPS batteries.

To work around this issue I have made a small script that can be used to shutdown a whole bunch of databases in a much faster but yet still tidy fashion. This is achieved by running all database shutdown in parallel and in the background using a helper script and the nohup facility.

Here's what the code of the main shutdb_all.ksh script looks like:

#!/usr/bin/ksh
print "Database(s) shutdown starting"
for ORA_DB in ${MY_DB_LIST}
do
	# shutdown databases in parallel
	print "Shutting down database ${ORA_DB} in the background ..."
	nohup ~oracle/shutdb.sh ${ORA_DB} &
done
# check status of shutdown child processes
SHUTDB_ACTIVE=$(pgrep -f "shutdb" | wc -l | tr -d '[[:space:]]')
COUNTER=0
while [[ ${SHUTDB_ACTIVE} -gt 0 ]]
do
	print "Shutdown of database(s) is still ongoing (${SHUTDB_ACTIVE} processes running) ..."
	sleep 20
	SHUTDB_ACTIVE=$(pgrep -f "dbshut" | wc -l | tr -d '[[:space:]]')
	COUNTER=$((COUNTER + 1))
	if [[ ${COUNTER} -eq 12 ]]
	then
		print "Databases are still not shutdown after $((COUNTER \* 20)) seconds."
		print "*PLEASE CHECK!* Continuing with the rest of the script!"
		break
	fi
done
print "Database(s) shutdown finished"

Configure the $MY_DB_LIST variable with the names of your Oracle database instances, for example:

MY_DB_LIST="db1 db2 db3 db4"

The above script will also need a helper script called shutdb.sh whose primary task it is to shutdown a single database instance. This helper script may look something like this and should go into the HOME directory of the oracle user:

#!/usr/bin/ksh
ORAENV_ASK="NO"
ORACLE_SID="$1"
EXEC_DBA="sqlplus /nolog"
. oraenv
# check for database online
STATUS=$(ps -fu oracle | grep -w "ora_[a-z]*_${ORACLE_SID}")
if [[ $? -eq 0 ]]
then
	# database is down
	print "Database is already down. Continuing."
else
	# database is up
	echo "Shutting down immediate."
	$EXEC_DBA </dev/null >>EOS
	connect /as sysdba
	alter system switch logfile;
	shutdown immediate;
	exit
	EOS
	STATUS=$(ps -fu oracle | grep -w "ora_[a-z]*_${ORACLE_SID}" )
	if [[ $? -eq 0 ]]
	then
		print "Error in database shutdown. Exiting."
		exit 1
	fi
fi
exit 0



Backlinks: Projects