About

Many folks running a large(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.sh script looks like:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
#!/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
    print "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

Comments