dba: Shell Script to start/stop All or one Oracle Database

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 ;

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 ;

####################################################################

# 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

####################################################################

# 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

####################################################################

# check if ORATAB exist

# If not, exit the code

####################################################################

logit "Checking ORATAB"

ORATAB=/etc/oratab

if [ ! $ORATAB ] ; then

echo "$ORATAB not found"

exit ;

####################################################################

# Create start.sh, stop.sh, asmstart.sh and asmstop.sh

####################################################################

if [ -f "$SCRIPT_HOME/dbstart.sh" ]; then

rm $SCRIPT_HOME/dbstart.sh

if [ -f "$SCRIPT_HOME/dbstop.sh" ]; then

rm $SCRIPT_HOME/dbstop.sh

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

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

####################################################################

# 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

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 ];

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

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."

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

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 ];

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

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."

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

if [ $DB = ${sid,,} ]; then

db_chk=1;

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;

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;

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 ];

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

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."

}

####################################################################

# 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

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

if [ $DB = ${sid,,} ]; then

db_chk=1;

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;

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;

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 ];

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

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."

}

####################################################################

# 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

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 }'`

}

####################################################################

# 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 != '' ]

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

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.

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.

Stopping all Databases

stopalldb

Shutting down database "oraprod"

nohup: appending output to `nohup.out'

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....

"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.

Starting all Databases

startalldb

Starting up database "oraprod"

nohup: appending output to `nohup.out'

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....

"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.

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.

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.

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....

"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.

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.

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....

"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.

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.

12 comments:

AnonymousFebruary 24, 2018 at 2:30 PM
*********************************************
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
temp_script.sh: line 458: syntax error: unexpected end of file
AJFebruary 24, 2018 at 9:51 PM
Please check if you have pasted complete script. The last line in the script is "done"
AnonymousNovember 14, 2018 at 9:27 AM
Hi will this script start all databases parallely or it will do one by one?
AnonymousJanuary 2, 2019 at 4:33 PM
The scripts worked well to stop all databases, but was not able to start anything.
AnonymousJanuary 2, 2019 at 5:08 PM
oh, 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.
AnonymousJanuary 3, 2019 at 9:14 AM
Option 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"
UnknownMarch 21, 2021 at 12:14 PM
Can we include the listener part also ?
SillaDecember 10, 2021 at 10:09 AM
This comment has been removed by the author.
SillaDecember 10, 2021 at 10:10 AM
This comment has been removed by the author.
SillaDecember 10, 2021 at 10:11 AM
Please enter a menu option and enter or enter to exit.
3
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]$

AnonymousApril 23, 2024 at 2:28 AM
This Script is very Usefull !!!
It really work for me.
Big thanks to you !!!
AnonymousApril 26, 2024 at 8:39 AM
Perfect solution for multi-instance environment. Thank you.

dba

Views

Saturday, September 16, 2017

Shell Script to start/stop All or one Oracle Database

12 comments:

Subscribe

Search This Blog

Blogs I Follow