Shell
Script to start/stop All or One Oracle database on the server.
This
is a menu driven program to perform following operations.
1.
Stop All Databases.
2.
Start All Databases.
3.
Stop Database.
4.
Start Database.
5.
Check Databases.
This
script start/stop either All databases on server or the database with specified
SID.
Below
is the script used.
After
the script we can find the sample execution of script.
Please
let me know if the script helped you.
#!/bin/bash
## ---------------------------------------------------------------------
## File Name : /home/oracle/scripts/db_start_stop.sh
## Description : Script to start / stop All or One database
on server
## Call Syntax : sh /home/oracle/scripts/db_start_stop.sh
## Last Modified: 9/15/2017
## ---------------------------------------------------------------------
## Revision history:
## ---------------------------------------------------------------------
PATH=$PATH:/usr/local/bin
export PATH
export PATH=${PATH}:${ORACLE_HOME}/bin
export SCRIPT_HOME=/home/oracle/scripts
log_dir=$SCRIPT_HOME/logs
LOG_FILE=$log_dir/db_stop_start.txt
####################################################################
# function to log messages
# Along with messages displayed on console,
all logs will be recorded in
# a log file created under log directory
# This file is always appended to retain
previous execution log
# log will contain the username who has
executed and
# date and time when it was executed
####################################################################
logit()
{
if [
! -d "$log_dir" ]; then
if
[ ! -f "$LOG_FILE" ]; then
echo "Log directory $log_dir or Log file $LOG_FILE does not exist.
Please create and re-execute."
exit ;
fi
fi
echo
"[${USER}][`date`] - ${*}" >> ${LOG_FILE}
}
####################################################################
# check if log directory exist
# if log directory does not exist, exit the
code with message
# displayed on console
####################################################################
logit "Checking log directory"
if [ ! -d "$log_dir" ]; then
logit "Log directory $log_dir does not
exist. Please create and re-execute"
echo "Log directory $log_dir does not
exist. Please create and re-execute"
exit ;
fi
####################################################################
# Script usage check
# check if script has been executed with
proper parameters
# If not, exit the code
####################################################################
if [ "$#" -ne 0 ]; then
echo
"Usage: sh db_start_stop.sh"
exit
1
fi
####################################################################
# check if logged on with oracle user
# If not, exit the code
####################################################################
if [ `whoami` != "oracle" ]; then
echo
"Must be logged on as oracle to run this script."
exit
1
fi
####################################################################
# check if ORATAB exist
# If not, exit the code
####################################################################
logit "Checking ORATAB"
ORATAB=/etc/oratab
if [ ! $ORATAB ] ; then
echo
"$ORATAB not found"
exit
;
fi
####################################################################
# Create start.sh, stop.sh, asmstart.sh and
asmstop.sh
#
####################################################################
if [ -f "$SCRIPT_HOME/dbstart.sh"
]; then
rm
$SCRIPT_HOME/dbstart.sh
fi
if [ -f "$SCRIPT_HOME/dbstop.sh" ];
then
rm
$SCRIPT_HOME/dbstop.sh
fi
if [ ! -f "$SCRIPT_HOME/dbstart.sh"
]; then
logit "Creating dbstart.sh"
cat > $SCRIPT_HOME/dbstart.sh <<
EOF1
sqlplus '/as sysdba'<<!
startup
show parameter db_name
select open_mode from v\$database ;
exit
!
EOF1
chmod 755 $SCRIPT_HOME/dbstart.sh
fi
if [ ! -f "$SCRIPT_HOME/dbstop.sh"
]; then
logit "Creating dbstop.sh"
cat > $SCRIPT_HOME/dbstop.sh << EOF2
sqlplus '/as sysdba'<<!
select name, open_mode from v\$database ;
show parameter db_name
shut immediate
exit
!
EOF2
chmod 755 $SCRIPT_HOME/dbstop.sh
fi
####################################################################
# Fuction stopalldb
# Function to stop All databases residing on
server
####################################################################
stopalldb (){
echo "stopalldb";
logit
"Entering: Stop All Databases"
run_list=`ps -ef | grep pmon | grep -v grep | grep -v +ASM | awk -F_ '{
print $3 }'`
for
DB in $run_list
do
export ORACLE_SID=$DB
export ORACLE_HOME=`grep "^${DB}:" /etc/oratab|cut -d: -f2 -s`
export PATH=$ORACLE_HOME/bin:$PATH
echo "Shutting down database \"$ORACLE_SID\""
logit "Shutting down database \"$ORACLE_SID\""
nohup sh $SCRIPT_HOME/dbstop.sh &
COUNT=0
running=`ps
-ef | grep pmon_$ORACLE_SID | grep -v grep | awk -F_ '{ print $3 }'|wc -l`
while [ "$running" != 0 ];
do
COUNT=`expr $COUNT + 1`
if [ $COUNT = 30 ] ; then
# 30 tries with 10 sec interval => 5 minutes timeout
logit "Timed out waiting to stop db instance $ORACLE_SID"
logit " DB not able to
stop."
exit $COUNT
fi
logit "Waiting for Oracle database to be stopped"
logit " DB instance $ORACLE_SID. Wait $COUNT."
echo "Oracle database $ORACLE_SID stopping. Please wait...."
sleep 10
running=`ps -ef | grep pmon_$ORACLE_SID | grep -v grep | awk -F_ '{
print $3 }'|wc -l`
done
running=`ps -ef | grep pmon_$ORACLE_SID | grep -v grep | awk -F_ '{
print $3 }'|wc -l`
if
[ "$running" = 0 ] ; then
logit "\"${ORACLE_SID}\" database successfully
stopped."
echo "\"${ORACLE_SID}\" database successfully
stopped."
fi
done
}
####################################################################
# Fuction startalldb
# Function to start All databases residing on
server
####################################################################
startalldb (){
echo "startalldb";
logit
"Entering: Start All Databases"
db_list=`cat /etc/oratab|grep -v "^#"|grep -v
"N$"|grep -v +ASM|cut -f1 -d: -s`
for
DB in $db_list
do
export ORACLE_SID=$DB
export ORACLE_HOME=`grep "^${DB}:" /etc/oratab|cut -d: -f2 -s`
export PATH=$ORACLE_HOME/bin:$PATH
echo "Starting up database
\"$ORACLE_SID\""
logit "Starting up database \"$ORACLE_SID\""
nohup sh $SCRIPT_HOME/dbstart.sh &
COUNT=0
running=`ps -ef | grep pmon_$ORACLE_SID | grep -v grep | awk -F_ '{
print $3 }'|wc -l`
while
[ "$running" = 0 ];
do
COUNT=`expr $COUNT + 1`
if [ $COUNT = 30 ] ; then
# 30 tries with 10 sec interval => 5 minutes timeout
logit "Timed out waiting to start db instance $ORACLE_SID"
logit " DB not able to
start."
exit $COUNT
fi
logit "Waiting for Oracle database to be Started"
logit " DB instance $ORACLE_SID. Wait $COUNT."
echo "Oracle database $ORACLE_SID Starting. Please wait...."
sleep 10
running=`ps -ef | grep pmon_$ORACLE_SID | grep
-v grep | awk -F_ '{ print $3 }'|wc -l`
done
running=`ps -ef | grep pmon_$ORACLE_SID | grep -v grep | awk -F_ '{
print $3 }'|wc -l`
if
[ "$running" != 0 ] ; then
logit "\"${ORACLE_SID}\" database successfully
Started."
echo "\"${ORACLE_SID}\" database successfully
Started."
fi
done
}
####################################################################
# Fuction stopdb
# Function to stop a database SID entered by
user
####################################################################
stopdb (){
echo "Currently running Oracle databases
on this server:"
echo `ps -ef | grep pmon | grep -v grep |
grep -v +ASM | awk -F_ '{ print $3 }'`
logit "Entering: Stop selected
database"
echo " "
echo "Enter Oracle SID to stop: "
read sid
db_chk=0;
for DB in $db_list
do
if [
$DB = ${sid,,} ]; then
db_chk=1;
fi
done
if [ "$db_chk" = 0 ] ; then
logit
"\"${sid}\" database does not exist on this server."
echo
"\"${sid}\" database does not exist on this server."
exit
1;
fi
running=`ps -ef | grep -i pmon_$sid | grep -v
grep | awk -F_ '{ print $3 }'|wc -l`
if [ "$running" = 0 ] ; then
logit
"\"${sid}\" database is not running."
echo
"\"${sid}\" database is not running."
exit;
fi
DB=`ps -ef | grep -i pmon_$sid | grep -v grep
| awk -F_ '{ print $3 }'`
export ORACLE_SID=$DB
export ORACLE_HOME=`grep "^${DB}:"
/etc/oratab|cut -d: -f2 -s`
export PATH=$ORACLE_HOME/bin:$PATH
echo "Stopping database
\"$ORACLE_SID\""
logit "Stopping database
\"$ORACLE_SID\""
nohup sh $SCRIPT_HOME/dbstop.sh &
COUNT=0
running=`ps -ef | grep pmon_$ORACLE_SID |
grep -v grep | awk -F_ '{ print $3 }'|wc -l`
while [ "$running" != 0 ];
do
COUNT=`expr $COUNT + 1`
if [
$COUNT = 30 ] ; then
#
30 tries with 10 sec interval => 5 minutes timeout
logit "Timed out waiting to stop db instance $ORACLE_SID"
logit " DB not able to
stop."
exit $COUNT
fi
logit
"Waiting for Oracle database to be stopped"
logit
" DB instance $ORACLE_SID. Wait $COUNT."
echo
"Oracle database $ORACLE_SID stopping. Please wait...."
sleep
10
running=`ps -ef | grep pmon_$ORACLE_SID | grep -v grep | awk -F_ '{
print $3 }'|wc -l`
done
running=`ps -ef | grep pmon_$ORACLE_SID |
grep -v grep | awk -F_ '{ print $3 }'|wc -l`
if [ "$running" = 0 ] ; then
logit
"\"${ORACLE_SID}\" database successfully Stopped."
echo
"\"${ORACLE_SID}\" database successfully Stopped."
fi
}
####################################################################
# Fuction startdb
# Function to start a database SID entered by
user
####################################################################
startdb (){
logit "Entering: Start selected
database"
db_list=`cat /etc/oratab|grep -v
"^#"|grep -v "N$"|cut -f1 -d: -s`
echo "Databases on this server:"
for DB in $db_list
do
export ORACLE_SID=$DB
export ORACLE_HOME=`grep "^${DB}:" /etc/oratab|cut -d: -f2 -s`
export PATH=$ORACLE_HOME/bin:$PATH
echo
"ORACLE_SID:$ORACLE_SID | ORACLE_HOME:$ORACLE_HOME"
done
echo " "
echo "Enter Oracle SID to start: "
read sid
db_chk=0;
for DB in $db_list
do
if [
$DB = ${sid,,} ]; then
db_chk=1;
fi
done
if [ "$db_chk" = 0 ] ; then
logit
"\"${sid}\" database does not exist on this server."
echo
"\"${sid}\" database does not exist on this server."
exit
1;
fi
running=`ps -ef | grep -i pmon_$sid | grep -v
grep | awk -F_ '{ print $3 }'|wc -l`
if [ "$running" != 0 ] ; then
logit
"\"${sid}\" database is already started."
echo
"\"${sid}\" database is already started."
exit
1;
fi
DB=`cat /etc/oratab|grep -v
"^#"|grep -v "N$"|grep -i $sid|cut -f1 -d: -s`
export ORACLE_SID=$DB
export ORACLE_HOME=`grep "^${DB}:"
/etc/oratab|cut -d: -f2 -s`
export PATH=$ORACLE_HOME/bin:$PATH
echo "Starting database
\"$ORACLE_SID\""
logit "Starting database
\"$ORACLE_SID\""
nohup sh $SCRIPT_HOME/dbstart.sh &
COUNT=0
running=`ps -ef | grep pmon_$ORACLE_SID |
grep -v grep | awk -F_ '{ print $3 }'|wc -l`
while [ "$running" = 0 ];
do
COUNT=`expr $COUNT + 1`
if [
$COUNT = 30 ] ; then
#
30 tries with 10 sec interval => 5 minutes timeout
logit "Timed out waiting to start db instance $ORACLE_SID"
logit " DB not able to start."
exit $COUNT
fi
logit
"Waiting for Oracle database to be started"
logit
" DB instance $ORACLE_SID. Wait $COUNT."
echo
"Oracle database $ORACLE_SID starting. Please wait...."
sleep
10
running=`ps -ef | grep pmon_$ORACLE_SID | grep -v grep | awk -F_ '{
print $3 }'|wc -l`
done
running=`ps -ef | grep pmon_$ORACLE_SID |
grep -v grep | awk -F_ '{ print $3 }'|wc -l`
if [ "$running" != 0 ] ; then
logit
"\"${ORACLE_SID}\" database successfully Started."
echo
"\"${ORACLE_SID}\" database successfully Started."
fi
}
####################################################################
# Check database status
# This function will display all databases
residing on server
# And will also display currently running
databases
####################################################################
checkdb (){
db_list=`cat /etc/oratab|grep -v
"^#"|grep -v "N$"|cut -f1 -d: -s`
for DB in $db_list
do
export ORACLE_SID=$DB
export ORACLE_HOME=`grep "^${DB}:" /etc/oratab|cut -d: -f2 -s`
export PATH=$ORACLE_HOME/bin:$PATH
echo
"Databases on this server"
echo
"ORACLE_SID:$ORACLE_SID | ORACLE_HOME:$ORACLE_HOME"
done
echo
" "
echo
"Running Databases:"
n=`ps -ef | grep pmon | grep -v grep | grep -v +ASM | awk -F_ '{ print
$3 }'|wc -l`
if [
$n = 0 ]; then
echo "No databases running"
else
echo `ps -ef | grep pmon | grep -v grep | grep -v +ASM | awk -F_ '{
print $3 }'`
fi
}
####################################################################
# Drawing menu function
# This function will draw memu on console to
select option either
# to start/stop db instance
####################################################################
draw_menu(){
NORMAL=`echo "\033[m"`
MENU=`echo "\033[36m"` #Blue
NUMBER=`echo "\033[33m"` #yellow
FGRED=`echo "\033[41m"`
RED_TEXT=`echo "\033[31m"`
ENTER_LINE=`echo "\033[33m"`
echo -e
"${MENU}*********************************************${NORMAL}"
echo -e "${MENU} Database Resources Start / Stop
${NORMAL}"
echo -e
"${MENU}*********************************************${NORMAL}"
echo -e "${MENU}**${NUMBER} 1)${MENU} Stop All Database
${NORMAL}"
echo -e "${MENU}**${NUMBER} 2)${MENU} Start All Database ${NORMAL}"
echo -e "${MENU}**${NUMBER} 3)${MENU} Stop Database ${NORMAL}"
echo -e "${MENU}**${NUMBER} 4)${MENU} Start Database
${NORMAL}"
echo -e "${MENU}**${NUMBER} 5)${MENU} Check Databases
${NORMAL}"
echo -e "${MENU}*********************************************${NORMAL}"
echo -e "${ENTER_LINE}Please enter a menu option and enter or
${RED_TEXT}enter to exit. ${NORMAL}"
read Choice
}
function choice_select() {
COLOR='\033[01;31m' # bold red
RESET='\033[00;00m' # normal white
MESSAGE=${@:-"${RESET}Error: No message passed"}
echo -e "${COLOR}${MESSAGE}${RESET}"
}
####################################################################
# Main function to execute the code to select
the appropriate option
####################################################################
clear
draw_menu
while [ Choice != '' ]
do
if [[ $Choice = "" ]]; then
exit;
else
case $Choice in
1) choice_select "Stopping all Databases";
stopalldb;
draw_menu;
;;
2) choice_select "Starting all Databases";
startalldb;
draw_menu;
;;
3) choice_select "Stop Database";
stopdb;
draw_menu;
;;
4) choice_select "Start Database";
startdb;
draw_menu;
;;
5) choice_select "Show Databases";
checkdb;
draw_menu;
;;
x)exit;
;;
\n)exit;
;;
*)clear;
choice_select "Pick an option from the menu";
draw_menu;
;;
esac
fi
done
SAMPLE EXECUTION:
[oracle@dclnpratedb8-old
scripts]$ sh db_start_stop.sh
*********************************************
Database
Resources Start / Stop
*********************************************
** 1) Stop All Database
** 2) Start All Database
** 3) Stop Database
** 4) Start Database
** 5) Check Databases
*********************************************
Please enter a menu option and
enter or enter to exit.
5
Show
Databases
Databases
on this server
ORACLE_SID:oraprod
| ORACLE_HOME:/u01/app/oracle/product/11.2.0/dbhome_1
ORACLE_SID:oradb1
| ORACLE_HOME:/u01/app/oracle/product/11.2.0/dbhome_1
Running
Databases:
oraprod
oradb1
*********************************************
Database
Resources Start / Stop
*********************************************
** 1) Stop All Database
** 2) Start All Database
** 3) Stop Database
** 4) Start Database
** 5) Check Databases
*********************************************
Please enter a menu option and
enter or enter to exit.
1
Stopping
all Databases
stopalldb
Shutting
down database "oraprod"
nohup:
appending output to `nohup.out'
Oracle
database oraprod stopping. Please wait....
Oracle
database oraprod stopping. Please wait....
Oracle
database oraprod stopping. Please wait....
Oracle
database oraprod stopping. Please wait....
"oraprod"
database successfully stopped.
Shutting
down database "oradb1"
nohup:
appending output to `nohup.out'
Oracle
database oradb1 stopping. Please wait....
Oracle
database oradb1 stopping. Please wait....
Oracle
database oradb1 stopping. Please wait....
Oracle
database oradb1 stopping. Please wait....
"oradb1"
database successfully stopped.
*********************************************
Database
Resources Start / Stop
*********************************************
** 1) Stop All Database
** 2) Start All Database
** 3) Stop Database
** 4) Start Database
** 5) Check Databases
*********************************************
Please enter a menu option and
enter or enter to exit.
2
Starting
all Databases
startalldb
Starting
up database "oraprod"
nohup:
appending output to `nohup.out'
Oracle
database oraprod Starting. Please wait....
Oracle
database oraprod Starting. Please wait....
Oracle
database oraprod Starting. Please wait....
Oracle
database oraprod Starting. Please wait....
Oracle
database oraprod Starting. Please wait....
Oracle
database oraprod Starting. Please wait....
Oracle
database oraprod Starting. Please wait....
Oracle
database oraprod Starting. Please wait....
"oraprod"
database successfully Started.
Starting
up database "oradb1"
nohup:
appending output to `nohup.out'
Oracle
database oradb1 Starting. Please wait....
Oracle
database oradb1 Starting. Please wait....
Oracle
database oradb1 Starting. Please wait....
Oracle
database oradb1 Starting. Please wait....
Oracle
database oradb1 Starting. Please wait....
"oradb1"
database successfully Started.
*********************************************
Database
Resources Start / Stop
*********************************************
** 1) Stop All Database
** 2) Start All Database
** 3) Stop Database
** 4) Start Database
** 5) Check Databases
*********************************************
Please enter a menu option and
enter or enter to exit.
5
Show
Databases
Databases
on this server
ORACLE_SID:oraprod
| ORACLE_HOME:/u01/app/oracle/product/11.2.0/dbhome_1
ORACLE_SID:oradb1
| ORACLE_HOME:/u01/app/oracle/product/11.2.0/dbhome_1
Running
Databases:
oraprod
oradb1
*********************************************
Database
Resources Start / Stop
*********************************************
** 1) Stop All Database
** 2) Start All Database
** 3) Stop Database
** 4) Start Database
** 5) Check Databases
*********************************************
Please enter a menu option and enter or enter to exit.
3
Stop
Database
Currently
running Oracle databases on this server:
oraprod
Enter
Oracle SID to stop:
oradb
"oradb"
database does not exist on this server.
*********************************************
Database
Resources Start / Stop
*********************************************
** 1) Stop All Database
** 2) Start All Database
** 3) Stop Database
** 4) Start Database
** 5) Check Databases
*********************************************
Please enter a menu option and
enter or enter to exit.
3
Stop
Database
Currently
running Oracle databases on this server:
oraprod
Enter
Oracle SID to stop:
oraprod
Stopping
database "oraprod"
nohup:
appending output to `nohup.out'
Oracle
database oraprod stopping. Please wait....
Oracle
database oraprod stopping. Please wait....
Oracle
database oraprod stopping. Please wait....
Oracle
database oraprod stopping. Please wait....
"oraprod"
database successfully Stopped.
*********************************************
Database
Resources Start / Stop
*********************************************
** 1) Stop All Database
** 2) Start All Database
** 3) Stop Database
** 4) Start Database
** 5) Check Databases
*********************************************
Please enter a menu option and
enter or enter to exit.
4
Start
Database
Databases
on this server:
ORACLE_SID:oraprod
| ORACLE_HOME:/u01/app/oracle/product/11.2.0/dbhome_1
ORACLE_SID:oradb1
| ORACLE_HOME:/u01/app/oracle/product/11.2.0/dbhome_1
Enter
Oracle SID to start:
abhi1
"abhi1"
database does not exist on this server.
[oracle@dclnpratedb8-old
scripts]$ sh db_start_stop.sh
*********************************************
Database
Resources Start / Stop
*********************************************
** 1) Stop All Database
** 2) Start All Database
** 3) Stop Database
** 4) Start Database
** 5) Check Databases
*********************************************
Please enter a menu option and
enter or enter to exit.
4
Start
Database
Databases
on this server:
ORACLE_SID:oraprod
| ORACLE_HOME:/u01/app/oracle/product/11.2.0/dbhome_1
ORACLE_SID:oradb1
| ORACLE_HOME:/u01/app/oracle/product/11.2.0/dbhome_1
Enter
Oracle SID to start:
oraprod
Starting
database "oraprod"
nohup:
appending output to `nohup.out'
Oracle
database oraprod starting. Please wait....
Oracle
database oraprod starting. Please wait....
Oracle
database oraprod starting. Please wait....
Oracle
database oraprod starting. Please wait....
Oracle
database oraprod starting. Please wait....
Oracle
database oraprod starting. Please wait....
Oracle
database oraprod starting. Please wait....
Oracle
database oraprod starting. Please wait....
"oraprod"
database successfully Started.
*********************************************
Database
Resources Start / Stop
*********************************************
** 1) Stop All Database
** 2) Start All Database
** 3) Stop Database
** 4) Start Database
** 5) Check Databases
*********************************************
Please enter a menu option and
enter or enter to exit.
4
Start
Database
Databases
on this server:
ORACLE_SID:oraprod
| ORACLE_HOME:/u01/app/oracle/product/11.2.0/dbhome_1
Enter
Oracle SID to start:
oraprod
"oraprod"
database is already started.
*********************************************
ReplyDeleteDatabase Resources Start / Stop
*********************************************
** 1) Stop All Database
** 2) Start All Database
** 3) Stop Database
** 4) Start Database
** 5) Check Databases
*********************************************
Please enter a menu option and enter or enter to exit.
5
temp_script.sh: line 458: syntax error: unexpected end of file
Please check if you have pasted complete script. The last line in the script is "done"
ReplyDeleteHi will this script start all databases parallely or it will do one by one?
ReplyDeleteThe scripts worked well to stop all databases, but was not able to start anything.
ReplyDeleteoh, I got it. "cat /etc/oratab|grep -v "^#"|grep -v "N$"|cut -f1 -d: -s". This line controls it. If the database use "N" option in the oratab, it will be ignored.
ReplyDeleteOption 4 to start one database has issue: I have two databases, one with the SID as test, the other with the SID as abctest. abctest is running, and I just need to start test. It seem to grab "test abctest" as the SID and try to start... still trying to figure out how to fix it. Also, after I stop test, when I tried to use option 3 to stop other databases on the server, it always complain "database does not exist on this server"
ReplyDeleteCan we include the listener part also ?
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteThis comment has been removed by the author.
ReplyDeletePlease enter a menu option and enter or enter to exit.
ReplyDelete3
Stop Database
Currently running Oracle databases on this server:
MSMGOLD MSMDEV2 MSMSIT MSMDEV1 MSMST MSMAUT
Enter Oracle SID to stop:
MSMGOLD
"MSMGOLD" database does not exist on this server.
[oracle@AZESLPDUKD03 scripts]$
This Script is very Usefull !!!
ReplyDeleteIt really work for me.
Big thanks to you !!!
Perfect solution for multi-instance environment. Thank you.
ReplyDelete