Sunday, June 20, 2021

Script Run datapatch against all running instance in parallel

This script can be used to run datapatch against all currently running instances in parallel to apply sql patches after patches are applied on Oracle homes. It is helpful for post patching operations. It works for different instances running out of different Oracle homes and different home with different owners.

The script accepts one optional parameter as log file path, it will save log files under /tmp if no parameter is presented while the script is started.

In order to have the script succeed, please be advised about following limitations,

1. The script is only tested on Linux and AIX, it does not work on Solaris.

2. Only root or Oracle database home owner is supposed to run this script. If the instances are running out of different Oracle homes which are owned by different OS users, root user is recommended. Otherwise, the instances running out of Oracle home which owner is different from current user will be excluded.

3. Oracle database home is 12c or higher which supports datapatch

4. The script runs following command as root to retrieve Oracle home path
   /bin/ls
   Therefore, if Oracle home owner (normally oracle) runs this script, sudo has to be configure to grant the user privilege to run '/bin/ls' as root without being asked for password.

Script code source,
#!/bin/bash
# ----------------------------------------------
# @ dbaplus.ca
#
# Script file name: runDatapatch.sh
#
# Usage: runDatapatch.sh [log file directory]
# ---------------------------------------------
#
# If log directory not given, use /tmp
LOG_DIR=${1:-/tmp}
#
# Current user who starts this script
CURRENT_USER=`whoami`
#
# non-root user needs sudo privilege to retrieve Oracle Home path
# Configure sudo to allow the user who will run this script to
# run command '/bin/ls -l' as root without being asked for password 
if [ $CURRENT_USER = root  ]; then
   SUDO=
else
   SUDO=/usr/bin/sudo
fi
#
declare -a PID_LIST
declare -A PID_INSTANCE
declare -A PID_LOGFILE
#
OLD_PATH=$PATH
# Loop for each pmon process id
for PMON_PID in `ps -ef | grep ora_pmon | grep -v grep | awk -F' ' '{print $2}'`
do
  # Retrieve Oracle SID
  ORACLE_SID=`ps -hfp $PMON_PID | awk -F' ' '{print $NF}' |cut -b 10-`
  #
  # Retrieve Oracle home where current pmon process is running out of
  ORACLE_HOME=`$SUDO /bin/ls -l /proc/$PMON_PID/cwd | awk -F' ' '{print $NF}'|sed 's/\/dbs$//'`
  #
  # Owner of Oracle Home
  HOME_OWNER=`ls -l $ORACLE_HOME/bin/oracle | awk '{print $3}'`
  RUN_DATAPATCH=""
  #
  if [ $CURRENT_USER = $HOME_OWNER ]; then
     RUN_DATAPATCH="$ORACLE_HOME/OPatch/datapatch -verbose"
  else  # If home owner is different from currrent user
     if [ $CURRENT_USER = root  ]; then
        RUN_DATAPATCH="su ${HOME_OWNER} -c \"$ORACLE_HOME/OPatch/datapatch -verbose\""
     else
        echo "  [info] The instance ${ORACLE_SID} will be excluded from datapatch"
        echo "         because the owner (${HOME_OWNER}) of home ${ORACLE_HOME}"
        echo "         is different from current user (${CURRENT_USER})."
        echo "         Only root can datapatch for different home owners."
        continue
     fi
  fi
  #
  # Setup Oracle environment
  PATH=$OLD_PATH:$ORACLE_HOME/bin
  LD_LIBRARY_PATH=$ORACLE_HOME/lib
  export PATH ORACLE_SID ORACLE_HOME LD_LIBRARY_PATH
  LOGFILE="$LOG_DIR/${ORACLE_SID}_`date '+%Y%m%d%H%M%S'`.log"
  #
  # Start datapatch in background for current instance from its home
  ${RUN_DATAPATCH} > $LOGFILE &
  # Save the PID of process running datapatch in background
  CHILD_PID=$!
  #
  # Save Oracle SID and datapatch output log file
  PID_LIST+=($CHILD_PID)
  PID_INSTANCE+=([$CHILD_PID]=$ORACLE_SID)
  PID_LOGFILE+=([$CHILD_PID]=$LOGFILE)
  #
  echo "  datapatch started for instance $ORACLE_SID"
done
#
# Loop to check if datapatch background process is completed
# until all datapatch processes complete
while [ ${#PID_LIST[@]} -gt 0 ]
do
  sleep 30
  for i in "${!PID_LIST[@]}"
  do
     # Determine if the process is running
     CHILD_PID=`ps -hp ${PID_LIST[$i]} -o pid`
     #
     # The process is not running any more
     if [ ! T"$CHILD_PID" = T"${PID_LIST[$i]}" ]; then
        echo "  datapatch completed for instance" ${PID_INSTANCE[${PID_LIST[$i]}]} ", check log file for details: "
        echo "    " ${PID_LOGFILE[${PID_LIST[$i]}]}
        # Remove finished process from saved process id list
        unset 'PID_LIST[$i]'
     fi
  done
  # Remove all finished processes from saved process id list and enter next round of while-loop
  PID_LIST=("${PID_LIST[@]}")
  # Simulate progress bar to release the frustration of waiting
  echo "  ... "
done
echo "  All datapatch jobs are completed."

No comments: