Skip to main content

Create dynamic stop/start db instances and TAF services script

My coworker created this script that can dynamically generate stop/start many database instances registered in /etc/oratab, this is a RAC server that has 12c and 11g oracle_homes RDBMS, the generated scripts will loop /etc/oratab and stop the TAF services gracefully first then stop the instances:

 -sh-4.1$ more stop_all_instances.sh
#!/bin/sh
#
# Program: stopallinstances.sh
# Purpose: stop all database instances on the server
# -----------------------------------------------------------------------------
#
# History
#
# Date      Rev  Who                  Comments
# --------- ---- -------------- -----------------------------------------------
# 04Jan2018 1.0  Bing Ge   Created
# 03Sep2019 2.0  Bing Ge   change to start the db in parallel.
#
# ---------------------------------------------------
#
LOCATION=/home/oracle/bge
STARTDB=$LOCATION/scripts/start_db.sh
STOPDB=$LOCATION/scripts/stop_db.sh
timestamp=`date  +%Y_%m_%d_%H_%M_%S`
hostname=`hostname`
LOG_LOCATION=$LOCATION/logs
LOG_FILE=$LOG_LOCATION/stopallinstances_$hostname_$timestamp.log
rm $STARTDB
rm $STOPDB
/net/ACEDBA/ACEDBA/bge/scripts/create_scripts_for_db_in_oh.sh -e prd -v 12 -o /u01/app/oracle/product/12.1.0.2/ACEPRD
/net/ACEDBA/ACEDBA/bge/scripts/create_scripts_for_db_in_oh.sh -e prd -v 12 -o /u01/app/oracle/product/12.1.0
/net/ACEDBA/ACEDBA/bge/scripts/create_scripts_for_db_in_oh.sh -e prd -v 11 -o /u01/app/oracle/product/11.2.0.4
/net/ACEDBA/ACEDBA/bge/scripts/create_scripts_for_db_in_oh.sh -e prd -v 11 -o /u01/app/oracle/product/11.2.0/db_1
chmod +x $STARTDB
chmod +x $STOPDB
$STOPDB  | tee -a $LOG_FILE
while [[ "$(ps -ef | grep stopinstance | grep -v grep)" != "" ]] ;
do
  sleep 5
  echo -n '.'
done
echo "All DB in the list should be down, please check."
exit

The following are the scripts of create_scripts_for_db_in_oh.sh being called above:

-sh-4.1$ more /net/ACEDBA/ACEDBA/bge/scripts/create_scripts_for_db_in_oh.sh
#!/bin/sh
#
# Program: create_scripts_for_db_in_oh.sh
# Purpose: create start/stop scripts for all databases running in the input ORACLE HOME.
# -----------------------------------------------------------------------------
#
# History
#
# Date      Rev  Who                  Comments
# --------- ---- -------------- -----------------------------------------------
# 04DSep2019 1.0  Bing Ge   Created
#
# ---------------------------------------------------
#

timestamp=`date  +%Y_%m_%d_%H_%M_%S`
hostname=`hostname`
ORATAB=/etc/oratab_dba
HOSTNAME=`hostname`
LOCATION=$HOME/bge
STARTALLDB=$LOCATION/scripts/start_db.sh
STOPALLDB=$LOCATION/scripts/stop_db.sh
LOG_LOCATION=$LOCATION/logs
LOG_FILE=$LOG_LOCATION/create_scripts_for_db_in_oh_$hostname_$timestamp.log
RAC=y

usage()
{
    echo "usage: create_scripts_for_db_in_oh.sh [-e dev|tst|sit|uat|stg|prd -v 11|12 -r y|n -o oracle_home -p patch_list -m email] | [-h]]"
}

while [ "$1" != "" ]; do
    case $1 in
        -e | --env )   shift
                        ENV=$1
                                ;;
        -v | --version )   shift
                        VERSION=$1
                                ;;
        -r | --rac )   shift
                       RAC=$1
                       echo $RAC
                                ;;

        -o | --oracle_home )   shift
                                export ORACLE_HOME_P=$1
                                export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:/usr/ccs/bin:$PATH
                                export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
                                ;;
        -p | --patch_number )   shift
                                PATCHES_LIST=$1
                                echo $PATCHES_LIST
                                ;;
        -m | --email )   shift
                         To_EMAIL_ADDR=$1
                                ;;
        -h | --help )           usage
                                exit
                                ;;
        * )                     usage
                                echo "111$1222"
                                exit 1
    esac
    shift
done

echo ${VERSION}
echo ${ENV}
echo "RAC=${RAC}"

STARTDB=$LOCATION/scripts/start_${VERSION}_${ENV}_db.sh
STOPDB=$LOCATION/scripts/stop_${VERSION}_${ENV}_db.sh
DBPATCH=$LOCATION/scripts/dbpatch_${VERSION}_${ENV}.sh

echo $DBPATCH

rm $STARTDB
rm $STOPDB
rm $DBPATCH

if [ $VERSION == "11" ]
then
  PATCH_LOCATION="/net/ACEDBA/ACEDBA/patches/11.2.0.4"
else
  PATCH_LOCATION="/net/ACEDBA/ACEDBA/patches/12.1.0.2"
fi

echo " creating start/stop/datapatch script for dbs running in $ORACLE_HOME."
cat $ORATAB | while read LINE
do
case $LINE in
  \#*)                ;;        #comment-line in oratab
  *)
  DB_NAME=`echo $LINE | awk -F: '{print $1}' -`
  ORACLE_HOME_1=`echo $LINE | awk -F: '{print $2}' -`

  if [ "$DB_NAME" == '*' ] ; then
    # same as NULL SID - ignore this entry
    ORACLE_SID=""
    continue
  fi
#echo ${DB_NAME}
#echo ${ORACLE_HOME_1}
#echo ${ORACLE_HOME_P}
  if [ "$ORACLE_HOME_1" == "${ORACLE_HOME_P}" ] ; then
     . ${ORACLE_HOME_1}/${DB_NAME}.env
     echo ${DB_NAME}
     if [ ${ORACLE_SID} == "LSNR" ] ; then
        echo "do nothing for ${ORACLE_SID}"
     else
        stopinstance="$LOCATION/scripts/stopinstance${ENV}${ORACLE_SID}";
        startinstance="$LOCATION/scripts/startinstance${ENV}${ORACLE_SID}";
        datapatch="$LOCATION/scripts/datapatch${ENV}${ORACLE_SID}";
        rm $stopinstance
        rm $startinstance
        rm $datapatch

        echo "export ORACLE_SID=${ORACLE_SID}">>$stopinstance
        echo "export ORAENV_ASK=NO">>$stopinstance
        echo ". /usr/local/bin/oraenv">>$stopinstance

        echo "export ORACLE_SID=${ORACLE_SID}">>$startinstance
        echo "export ORAENV_ASK=NO">>$startinstance
        echo ". /usr/local/bin/oraenv">>$startinstance

        echo "export ORACLE_SID=${ORACLE_SID}">>$datapatch
        echo "export ORAENV_ASK=NO">>$datapatch
        echo ". /usr/local/bin/oraenv">>$datapatch

        if [ ${DB_NAME} == "BTCRPRD" -o ${DB_NAME} == "BTCRSTG" ]; then
          echo "do nothing"
        else
          for PATCH_NUMBER in $PATCHES_LIST
          do
            echo "$PATCH_LOCATION/$PATCH_NUMBER/datapatch.sh"
            echo "$PATCH_LOCATION/$PATCH_NUMBER/datapatch.sh">>$datapatch
          done
        fi

        if [ ${RAC} == "y" -o ${RAC} == "Y" ]; then
           if [ $ENV == "dev" -o $ENV == "tst" -o $ENV == "sit" ]; then
             echo "srvctl stop database -d $DB_NAME" >> $stopinstance
             echo "srvctl start database -d $DB_NAME" >> $startinstance
           else
             echo "/net/ACEDBA/ACEDBA/bge/scripts/stop_ace_services.sh $DB_NAME $ORACLE_SID" >> $stopinstance
             if [ $VERSION == "12" ] ; then
               echo "srvctl stop instance -d $DB_NAME -i $ORACLE_SID -stopoption transactional" >> $stopinstance
               echo "srvctl start instance -d $DB_NAME -i $ORACLE_SID" >> $startinstance
             else
               echo "srvctl stop instance -d $DB_NAME -i $ORACLE_SID -o transactional" >> $stopinstance
               echo "srvctl start instance -d $DB_NAME -i $ORACLE_SID" >> $startinstance
             fi
             echo "/net/ACEDBA/ACEDBA/bge/scripts/start_ace_services.sh $DB_NAME $ORACLE_SID" >> $startinstance
           fi
        else
          echo "/net/ACEDBA/ACEDBA/bge/scripts/start_database.sh ${ORACLE_SID}" >>$startinstance
          echo "/net/ACEDBA/ACEDBA/bge/scripts/stop_database.sh ${ORACLE_SID}" >> $stopinstance
        fi
        chmod +x ${stopinstance}
        echo "nohup ${stopinstance}&">>${STOPDB}
        chmod +x ${startinstance}
        echo "nohup ${startinstance}&">>${STARTDB}
        chmod +x ${datapatch}
        echo "${datapatch}">>${DBPATCH}
     fi
  fi
  ;;
esac

done
chmod +x $STARTDB
chmod +x $STOPDB
chmod +x $DBPATCH


cat $STARTDB >> $STARTALLDB
cat $STOPDB >> $STOPALLDB


Comments

Post a Comment

Popular posts from this blog

non-existent process lock port on windows server

I have a database link created between oracle and sqlserver using oracle tg4odbc, the product is installed on windows server and run as service "OracleOraGtw11g_home1TNSListener", but sometime the service cannot started, the root cause of this problem is that the port number 1521 is used by an non-existent process. The first step is to use netstat -bano|find "1521" to get the process id, in my case it's 5844, which shows the connection is from my oracle server 10.8.0.169 H:\>netstat -bano|find "1521"   TCP    0.0.0.0:1521           0.0.0.0:0              LISTENING       5844   TCP    10.14.45.33:1521       10.8.0.169:42987       ESTABLISHED     5844 however the process id does not show in either task manager or process explorer. The next step is to run tcpview, which shows non-existent under process column, there are three rows, two show status as "listening", the other one shows status "established", right click and k
3 comments
Read more

Opatch apply/lsinventory error: oneoff is corrupted or does not exist

I am applying the quarterly patch for 19c RDBMS, I tried using napply but failed, but somehow it corrupted the inventory though nothing applied. further apply and lsinventory command ran into error like this: $ ./OPatch/opatch lsinventory Oracle Interim Patch Installer version 12.2.0.1.21 Copyright (c) 2020, Oracle Corporation.  All rights reserved. Oracle Home       : /u02/app/oracle/19.0.0 Central Inventory : /u01/app/oraInventory    from           : /u02/app/oracle/19.0.0/oraInst.loc OPatch version    : 12.2.0.1.21 OUI version       : 12.2.0.7.0 Log file location : /u02/app/oracle/19.0.0/cfgtoollogs/opatch/opatch2020-09-08_13-35-59PM_1.log Lsinventory Output file location : /u02/app/oracle/19.0.0/cfgtoollogs/opatch/lsinv/lsinventory2020-09-08_13-35-59PM.txt -------------------------------------------------------------------------------- Inventory load failed... OPatch cannot load inventory for the given Oracle Home. LsInventorySession failed: Unable to create patchObject Possible ca
Post a Comment
Read more

shell script to clean up oracle dumpfile

https://github.com/iacosta/personal/blob/master/shells/cleanhouse.sh #!/bin/ksh # # Script used to cleanup any Oracle environment. # # Cleans:      audit_file_dest #              background_dump_dest #              core_dump_dest #              user_dump_dest #              Clusterware logs # # Rotates:     Alert Logs #              Listener Logs # # Scheduling:  00 00 * * * /networkdrive/dba/scripts/purge/cleanup.sh -d 7 > /u01/dba/bin/cleanup.log 2>&1 # # Created By:  Lei Dao # # # RM="rm -f" RMDIR="rm -rf" LS="ls -l" MV="mv" TOUCH="touch" TESTTOUCH="echo touch" TESTMV="echo mv" TESTRM=$LS TESTRMDIR=$LS SUCCESS=0 FAILURE=1 TEST=0 HOSTNAME=`hostname` ORAENV="oraenv" TODAY=`date +%Y%m%d` ORIGPATH=/usr/local/bin:$PATH ORIGLD=$LD_LIBRARY_PATH export PATH=$ORIGPATH # Usage function. f_usage(){   echo "Usage: `basename $0` -d DAYS [-a DAYS] [-b DAYS] [
Post a Comment
Read more