StartUp Shutdown Multiple DB's

[lelzie1]

I have 5 databases on one server and I am having trouble starting up and shutting down databases as I need to with out effecting the other databases.

I am using the following command:svrmgrl
connect internal/sys@cmsdemo2
shutdown abort;
startup open pfile=c:\oracle\ora81\database\initcmsdemo2.ora;
shutdown;

If there is a more efficient way to shut down one database at a time or all at once, it would be appreciated.

Leon Elzie

[sreddy]

You can write your own custom script to do so embedding all the sid's environment and sid list, else use Oracle Provided dbshut/dbstart scripts. Read documenatation before attempting to do it.

Any specific reason to use 'shutdown abort' insead 'shutdown immediate' ?? Not advisable to use abort option unless its mandatory/no alternative situation.

[sambavan]

Here is one script that I created for my internal use, thought might help you. This is an interactive script, and if your shop runs OEM, then replace the sysman's password of "Enterthepasswordhere" with your password. Also make sure that you specify you
ORACLE_HOME, OEM_HOME, ORATAB_LOC path and other home paths correctly. Also make sure that you have specified the oratab file path correctly.

++++++++++++++++++++++++++++++++++++++++

#!/bin/ksh

################################################################################
#########
#
#
# Purpose: This shell program would allow a user to startup/shutdown some of the
#
# databases and related applications, in an interactive manner. #
# Some of the applications are as follows
#
#
#
# Oracle Database Instances
#
# Oracle Default Listener
#
# Oracle Intelligent Agent
#
# Oracle Data Gatherer
#
# Oracle Enterprise Management Server
#
# <b>Use at your own risk </b>
################################################################################
#########


#######################################################
# #
# Set the Environment variables #
# #
#######################################################
#set -a

ORACLE_HOME=oracle_home_path_here
if [ ! -d $ORACLE_HOME ]; then

echo "Cannot find the $ORACLE_HOME.\n Exiting. "
exit 1;

fi
export ORACLE_HOME

OEM_HOME="oracle_home_path_here"
if [ ! -d $OEM_HOME ]
then

echo "Cannot find the $OEM_HOME.\n Exiting"
exit 1;

fi
export OEM_HOME


ORATAB_LOC=ora_tab_file_location
HOSTN=`uname -n`
################################################################################
#########
#
#
# Interactive mode Module
#
#
#
#
#
# Description: This module would post questions to the user and based on the
#
# replies perform the operation, against the database. The way
#
# this module had been designed is as follows
#
#
#
# 1. User would be prompted for the type of operation (start/stop)
#
#
#
# 2. The user would then be provided with the series of operation
#
# that he/she could perform form his/her previous choice of
#
# start/stop. Say for example that the user wants to perform
#
# a startup of listeners, then he/she would have to choose
#
# "lsnr". If the opertion is to start/stop the database then
#
# the user would be prompted with the different instances that
#
# he/she could start
#
#
#
################################################################################
#########

echo "Please specify the type of operation that you would like to perform ?\n"
echo "\t start\n\t stop\n\n"
echo "Enter your choice: "; read RESPONSE

case "$RESPONSE" in

'start') echo "Your choice was $RESPONSE"
echo "\n\n\tPlease specify the application that you would like to start:\n"
echo "\t1) Database db\n"
echo "\t2) Listener lsnr\n"
echo "\t3) OEM oem\n"
echo "\t4) OAS oas\n"
echo "\t5) All all\n\n"

echo "\tEnter your choice :"
read APPLICATION

case "$APPLICATION" in

'db') echo "\tPlease choose the database that you want to $RESPONSE\n"

#------------------------------------------------------------------------------------
#
# Read all the database instance names from the oratab file and then check to
# make sure that they are not up and running. Lists only those instance names
# to the user to perform the statup operation.
#
#----------------------------------------------------------
--------------------------
FLAG_SET=0
cat "$ORATAB_LOC" | sed -n '/^[^#||^*]/ p'| awk -F: '{if ($2 -eq $ORACLE_HOME) print $1}'|sort |&
while read -p ORASID
do
ORAUP=`ps -ef |grep ora_pmon_"$ORASID"| grep -v grep`

if [ -z "$ORAUP" ]
then
echo "\t\t$ORASID"
FLAG_SET=1
fi
done

if [ $FLAG_SET -eq 0 ]; then
echo "\n\n\t All your database instances are up and running."
echo "\t If you need to start, then you first have to shutdown the instance\n\n"
exit 0;
fi

#-------------------------------------------------------
#
# Read the instance names from the user input and
# start those instances on the host system
#
#-------------------------------------------------------
echo "Enter the database name(s):"
read ORASID

for SID in $ORASID
do
echo "\n";
ORACLE_SID=$SID
export ORACLE_SID
echo $RESPONSE"ing database instance $SID"
$ORACLE_HOME/bin/svrmgrl <<- END_OF_FILE
connect internal
startup
select instance_name, version, database_status from v\$instance;
END_OF_FILE
done
break;;

'lsnr') #----------------------------------------------------------
#
# Start the listeners that are being used by different
# database applications
#
#---------------------------------------------------------

$ORACLE_HOME/bin/lsnrctl start
$ORACLE_HOME/bin/lsnrctl services
$ORACLE_HOME/bin/lsnrctl dbsnmp_start
$ORACLE_HOME/bin/lsnrctl dbsnmp_status
$ORACLE_HOME/bin/vppcntl -start

#======================================
# Make sure that the process had been
# started on the system
#======================================
ORALSNR=`ps -ef|grep $ORACLE_HOME | grep -v grep |awk '$(NF-2) ~/tnslsnr/ {print $(NF-1)}'`
if [ -z "$ORALSNR" ]
then

echo "\n\nThe listener had not beed started. "

fi

#-----------------------------------------------------
# Check the status of the intelligent agent
#-----------------------------------------------------
ORAGENT=`ps -ef|grep dbsnmp|grep -v grep`
if [ -z "$ORAGENT" ]; then

echo "\n\nThe oracle intelligent agent had not been started."
echo "Perform a trouble shoot "

fi

#-------------------------------------------
# Check the status of the Data gatherer
#-------------------------------------------
ORAGATHER=`ps -ef|grep $ORACLE_HOME | grep -v grep |awk -F/ '{if ($NF == "vppdc") print $NF}'`
if [ -z "$ORAGATHER" ]
then

echo "\n\nThe oracle data gathereer is not started"
fi
break;;

'oem') #-----------------------------------------------------------
#
# Start the Oracle Enterprise Management Server
#
#----------------------------------------------------------
$OEM_HOME/bin/oemctrl start oms |&wait
$OEM_HOME/bin/oemctrl status oms sysman/Enterthepassword

ORAOEM=`ps -ef |grep $ORACLE_HOME |grep -v grep |awk -F/ '{ if($NF == "launchOms") print $NF}'`
if [ -z "$ORAOEM" ]
then
echo "\n\nThe Oracle Enterprise Manager had not been started "
fi
break;;

'oas') echo "execute the environment setup script and proceed further"
echo "force clean"
echo "start the node manager"
echo "start the oracle web application server"
break;;

'all') #----------------------------------------------------------------------------------------
#
# This would start all the databases that are down and then the listeners, the oracle
# intelligent agent, oracle data gatherer and then the oracle management server
#
#---------------------------------------------------------------------------------------
cat "$ORATAB_LOC" | sed -n '/^[^#||^*]/ p'| awk -F: '{if ($2 -eq $ORACLE_HOME) print $1}' | sort |&
while read -p ORASID
do

ORAUP=`ps -ef|grep ora_pmon_"$ORASID"| grep -v grep`

if [ -z "$ORAUP" ]
then
echo "\n\n"
echo $RESPONSE"ing the instance $ORASID"
ORACLE_SID=$ORASID
export ORACLE_SID
$ORACLE_HOME/bin/svrmgrl <<- EOF
connect internal
startup
select instance_name, version, database_status from v\$instance;
EOF
fi
done
echo "\n\n Instance startup successful"

#---------------------------------------
#
# Start the listeners on the host
#
#---------------------------------------

echo "\n Starting the Listeners "
$ORACLE_HOME/bin/lsnrctl << EOF
start
dbsnmp_start
services
dbsnmp_status
EOF
#======================================
# Make sure that the process had been
# started on the system
#======================================
ORALSNR=`ps -ef|grep $ORACLE_HOME|grep -v grep |awk '$(NF-2) ~/tnslsnr/ {print $(NF-1)}'`
if [ -z "$ORALSNR" ]
then

echo "\n\nThe listener had not beed started. "

fi

#
# Check the status of the intelligent agent
#
ORAGENT=`ps -ef|grep dbsnmp |grep -v grep`
if [ -z "$ORAGENT" ]; then

echo "\n\nThe oracle intelligent agent had not been started."
echo "Perform a trouble shoot "

fi

echo "\n Starting the datagatherer "
$ORACLE_HOME/bin/vppcntl -start
$ORACLE_HOME/bin/vppcntl -ping

#-------------------------------------------------
# Check the status of the Data gatherer
#-------------------------------------------------
ORAGATHER=`ps -ef|grep $ORACLE_HOME |grep -v grep |awk -F/ '{if ($NF == "vppdc") print $NF}'`
if [ -z "$ORAGATHER" ]
then

echo "\n\nThe oracle data gathereer is not started"
fi

echo "\n\nStarting up of listeners successfull"

#---------------------------------------------------
#
# Start the oracle enterprise management server
#
#---------------------------------------------------

echo "Starting Oracle Enterprise Management Server\n"
$OEM_HOME/bin/oemctrl start oms|&wait
$OEM_HOME/bin/oemctrl status oms sysman/Enterthepasswordhere

ORAOEM=`ps -ef |grep $ORACLE_HOME |grep -v grep |awk -F/ '{ if($NF == "launchOms") print $NF}'`
if [ -z "$ORAOEM" ]
then
echo "\n\nThe Oracle Enterprise Manager had not been started "
fi

exit 0;;

*) echo "Sorry you have to specify the operation"
exit 1;;

esac
exit 0;;


'stop') echo "Your choice was $RESPONSE"
echo "\n\n\tPlease specify the application that you would like to $RESPONSE:\n"
echo "\t1) Database db\n"
echo "\t2) Listener lsnr\n"
echo "\t3) OEM oem\n"
echo "\t4) OAS oas\n"
echo "\t5) ALL all\n\n"

echo "\tEnter your choice :"
read APPLICATION

case "$APPLICATION" in

'db') echo "\tPlease choose the database that you want to $RESPONSE\n"

#---------------------------------------------------------------------------
#
# Read the Instance name from the oratab file and display it to the user
# only those instances that are up and running currently, to allow him/her
# to do a shutdown operation
#
#----------------------------------------------------------------------------
FLAG_SET=0
cat "$ORATAB_LOC" | sed -n '/^[^#||^*]/ p'| awk -F: '{if ($2 -eq $ORACLE_HOME) print $1}'|sort |&
while read -p ORASID
do
ORADOWN=`ps -ef |grep ora_pmon_"$ORASID" |grep -v grep`

if [ -n "$ORADOWN" ]
then
echo "\t\t$ORASID"
FLAG_SET=1
fi
done

if [ $FLAG_SET -eq 0 ]; then
echo "\n\n\t All your database instances were down now."
echo "\t They need to be running inorder to do a shutdown\n\n"
exit 0;
fi

#---------------------------------------------------
#
# If the databases are up and if the user gives
# the instance names, then read them one by one
# and stop the instance(s)
#
#----------------------------------------------------
echo "Enter the database name(s):"
read ORASID

for SID in $ORASID
do
echo "\n";
ORACLE_SID=$SID
export ORACLE_SID
echo $RESPONSE"ing database instance $SID"
$ORACLE_HOME/bin/svrmgrl <<- END_OF_FILE
connect internal
select instance_name, version, database_status from v\$instance;
shutdown immediate
END_OF_FILE
done
break;;

'lsnr') #------------------------------------------------------
# Stop the listeners that the database has been
# using for different kind of purposes
#
#-----------------------------------------------------

$ORACLE_HOME/bin/lsnrctl << EOF
stop
dbsnmp_stop
services
dbsnmp_status
EOF
#======================================
# Make sure that the process had been
# stopped on the system
#======================================
ORALSNR=`ps -ef|grep $ORACLE_HOME |grep -v grep |awk '$(NF-2) ~/tnslsnr/ {print $(NF-1)}'`
if [ -n "$ORALSNR" ]
then

echo "\n\nThe listener had not beed stopped. "

fi

#------------------------------------------------
# Check the status of the intelligent agent
#------------------------------------------------
ORAGENT=`ps -ef|grep dbsnmp |grep -v grep`
if [ -n "$ORAGENT" ]; then

echo "\n\nThe oracle intelligent agent had not been stopped."

fi

$ORACLE_HOME/bin/vppcntl -stop

#
# Check the status of the Data gatherer
#
ORAGATHER=`ps -ef|grep $ORACLE_HOME | grep -v grep |awk -F/ '{if ($NF == "vppdc") print $NF}'`
if [ -n "$ORAGATHER" ]
then

echo "\n\nThe oracle data gathereer is not stopped"
fi

break;;


'oem') $OEM_HOME/bin/oemctrl stop oms sysman/Enterthepasswordhere
$OEM_HOME/bin/oemctrl status oms sysman/Enterthepasswordhere

ORAOEM=`ps -ef |grep $ORACLE_HOME | grep -v grep |awk -F/
'{ if($NF == "launchOms") print $NF}'`
if [ -n "$ORAOEM" ]
then
echo "\n\nThe Oracle Enterprise Manager had not been stopped "
fi
break;;

'oas') echo "execute the environment setup script and proceed further"
echo "force clean"
echo "start the node manager"
echo "start the oracle web application server"
break;;

'all') #----------------------------------------------------------------------------------------
#
# This would stop all the databases that are up and then the listeners, the oracle
# intelligent agent, oracle data gatherer and then the oracle management server
#
#---------------------------------------------------------------------------------------
cat "$ORATAB_LOC" | sed -n '/^[^#||^*]/ p'| awk -F: '{if ($2 -eq $ORACLE_HOME) print $1}' | sort |&
while read -p ORASID
do

ORADOWN=`ps -ef|grep ora_pmon_"$ORASID" | grep -v grep`

if [ -n "$ORADOWN" ]
then
echo $RESPONSE"ing the instance $ORASID"
ORACLE_SID=$ORASID
export ORACLE_SID
$ORACLE_HOME/bin/svrmgrl <<- EOF
connect internal
select instance_name, version, database_status from v\$instance;
shutdown immediate
EOF
fi
done
echo "\n\n Instance shutdown successful"

#---------------------------------------
#
# Stop the listeners on the host
#
#---------------------------------------

echo "\n Stopping the Listeners "
$ORACLE_HOME/bin/lsnrctl << EOF
services
dbsnmp_status
stop
dbsnmp_stop
EOF
#======================================
# Make sure that the process had been
# stopped on the system
#======================================
ORALSNR=`ps -ef|grep $ORACLE_HOME | grep -v grep |awk '$(NF-2) ~/tnslsnr/ {print $(NF-1)}'`
if [ -n "$ORALSNR" ]
then

echo "\n\nThe listener had not beed stopped. "

fi

#--------------------------------------------------
# Check the status of the intelligent agent
#-------------------------------------------------
sleep 2
ORAGENT=`ps -ef|grep dbsnmp`
if [ -n "$ORAGENT" ]; then

echo "\n\nThe oracle intelligent agent had not been stopped."

fi


echo "\n Stopping the datagatherer "
$ORACLE_HOME/bin/vppcntl -stop
$ORACLE_HOME/bin/vppcntl -ping

#----------------------------------------------
# Check the status of the Data gatherer
#----------------------------------------------
ORAGATHER=`ps -ef|grep $ORACLE_HOME | grep -v grep |awk -F/ '{if ($NF == "vppdc") print $NF}'`
if [ -n "$ORAGATHER" ]
then

echo "\n\nThe oracle data gathereer is not stopped"
fi



#---------------------------------------------------
#
# Stop the oracle enterprise management server
#
#---------------------------------------------------

echo "Stopping Oracle Enterprise Management Server\n"
$OEM_HOME/bin/oemctrl stop oms sysman/Enterthepasswordhere

ORAOEM=`ps -ef |grep $ORACLE_HOME | grep -v grep |awk -F/ '{ if($NF == "launchOms") print $NF}'`
if [ -n "$ORAOEM" ]
then
echo "\n\nThe Oracle Enterprise Manager had not been stopped "
fi

exit 0;;



*) echo "Sorry you have to specify the operation"
exit 1;;

esac

exit 0;;

*) echo "Please enter the operation you want to perform"
exit 1;;
esac

++++++++++++++++++++++++++++++++++++++++

Hope this would help you. Since I didn't have much time, I didn't bother to refine the script. If any one would like to, please go ahead and do so, and also it would really be appreciated, if you could post the refined one, here.

Sam

[Dave_A]

I noticed that it looks as though you are routinely performing a shutdown abort on your database. *Do not do this* unless you absolutely have to (shutdown immediate failing). A shutdown immediate is far safer - a shutdown abort is almost akin to chopping out all the background processes from under Oracle, and I have seen it cause database corruption in the past.

HTH

David.