Apps DBA wish - adpatch under my influence

Update: This script also works without modification on Release 12. Fixed missing slashes in command to generate adpatchdef.txt.

I like shortcuts.

I like to keep track of what a I do.

So a typical Apps DBA session will be a flurry of PuTTY, Notepad, Firefox and lots of alt-tab, control-c, shift-ins key combos.

People look over my shoulder from time to time and struggle to keep up ;-)

Anyway, I digress ...

One of the most laborious Apps DBA is applying patches.

I'd like to just think about it and the patch gets applied automatically ... not quite gonna happen just yet.

In the early days of Apps I automated adpatch sessions by piping "stored answer" files into stdin ie. "adpatch << EOF". Later versions of adpatch with non-interactive mode mean thats no longer necessary. I really like adpatch non-interactive mode.

However, there were still a few things missing that I found myself repetitively doing:

1. Typing "adpatch ..." to many times
2. Unzipping the patch archive
3. Tracking adpatch runs, by patch applied and cumulative time to apply
4. Switching on and off maintenance mode (at least on the concurrent processing server)
5. Answering "Yes" at "do you want to restart"
6. Getting frustrated at different DBAs patch log file naming standards

So I threw together a very quick shell script that would do these plus provide a standard place to keep adpatch options, and make it easy to string together a list of patches to apply at midnight.

Admitedly its a bit blunt, but the number of times I use it has been well worth it.

So throw the script in your PATH and try it out on your next patch.

To apply a patch just type:
aa 1234567 u
where 1234567 = patch number you want to apply with zip file in /patch
and u = driver to apply ie. u1234567.drv

Here's the script:

#!/bin/sh
#
# adpatch_auto a.k.a aa
#
# Ver Date      Author     Description
# --- --------- ---------- ---------------------------
# 1.0 30-OCT-00 G. Roberts Created
# 1.1 09-MAY-07 G. Roberts Extended for zip files
#
# Automated adpatch for R11i
# Only use for patches you've already done manually, or in non-PROD environments
# Notes: You must have your environment set correctly
# You must have precreated defaults.txt
# Patch must be in PT (see config below)
# Patch driver number must be same as patch number

if [ "$#" != "2" -a "$#" != "3" -a "$#" != "4" -a "$#" != "5" ]; then
  echo "Usage: aa PATCH_NUM DRIVERFILE_PREFIX [RESTART=YN [INTERACTIVE=YN]]"
  echo " Simple  e.g: aa 123456 u"
  echo " Restart e.g: aa 123456 u Y Y"
  exit 1
fi

# CONFIGURATION ITEMS
# Set PT to where your base patch directory is
PT=/patch; export PT
# LOGFILE is a file keeping track of aa run history
LOGFILE=$APPL_TOP/admin/p.log; export LOGFILE
# DEFFILE is your defaults.txt probably defaults.txt or adalldefaults.txt
DEFFILE=$APPL_TOP/admin/${TWO_TASK}/adalldefaults.txt
# Set DOUNZIP to Y if you want aa to search for and unzip p*.zip
DOUNZIP=Y; export DOUNZIP
# Set SETMAINT to Y to automatically switch on and off maintenance mode
# Only needed for late'ish versions 11.5.10 or higher
SETMAINT=Y; export SETMAINT
# Date format for logging
DF="+%y%m%d %H:%M:%S"

# Check CONTEXT_NAME is set
if [ -z "${CONTEXT_NAME}" ]; then
  echo "CONTEXT_NAME is not set!"
  exit 1
else
  CTXT=${CONTEXT_NAME}; export CTXT
fi

ORIGDIR="`pwd`"

PNUM=$1; export PNUM
DRIV=$2; export DRIV
PATCH=$PNUM; export PATCH
if [ ! -z "$3" ]; then
  if [ "$3" = "N" ]; then
    REST="restart=no"; export REST
  else
    REST="restart=yes"; export REST
  fi
fi
if [ "$#" = "4" ]; then
  if [ "$4" = "Y" ]; then
    INTERACT=yes; export INTERACT
  else
    INTERACT=no; export INTERACT
  fi
else
  INTERACT=no; export INTERACT
fi

echo "Patch number set to $PNUM"
echo "Driver set to $DRIV"
echo "Restart set to $REST"
echo "Interactive set to $INTERACT"
echo
echo `date "$DF"`" Starting $DRIV $PNUM $REST $INTERACT"
echo `date "$DF"`" Starting $DRIV $PNUM $REST $INTERACT" >> $LOGFILE

cd $PT
if [ "$DOUNZIP" = "Y" ]; then
 if [ ! -d "$PNUM" ]; then
  echo `date "$DF"`" Looking for zip files to unzip"
  ZFILE=`ls -tr *$PNUM*.zip  awk '{print $1}'`; export ZFILE
  if [ -n "$ZFILE" ]; then
   if [ -f "$ZFILE" ]; then
    echo `date "$DF"`" Unzipping $ZFILE"
    echo `date "$DF"`" Unzipping $ZFILE" >> $LOGFILE
    unzip "$ZFILE"
   else
    echo `date "$DF"`" ERROR: File $PT/*$PNUM*.zip does not exist."
    echo `date "$DF"`" ERROR: File $PT/*$PNUM*.zip does not exist." >> $LOGFILE
    exit 1
   fi
  else
   echo `date "$DF"`" ERROR: File $PT/*$PNUM*.zip does not exist."
   echo `date "$DF"`" ERROR: File $PT/*$PNUM*.zip does not exist." >> $LOGFILE
   exit 1
  fi
 fi
fi

if [ ! -d $PNUM ]; then
  echo `date "$DF"`" ERROR: Directory $PT/$PNUM does not exist."
  echo `date "$DF"`" ERROR: Directory $PT/$PNUM does not exist." >> $LOGFILE
  exit 1
fi

if [ "$SETMAINT" = "Y" ]; then
  echo `date "$DF"`" Enable maintenance mode"
  echo `date "$DF"`" Enable maintenance mode" >> $LOGFILE
  sqlplus -s apps/`get_pw db apps` @$AD_TOP/patch/115/sql/adsetmmd.sql ENABLE
fi

echo `date "$DF"`" Calling adpatch"
echo `date "$DF"`" Calling adpatch" >> $LOGFILE
adpatch \
defaultsfile=$DEFFILE \
logfile=${DRIV}${PNUM}_$CTXT.log \
patchtop=$PT/$PATCH \
driver=${DRIV}${PNUM}.drv \
workers=6 interactive=$INTERACT options=novalidate $REST
#workers=6 interactive=$INTERACT options=novalidate,nocheckfile $REST

if [ -d $PT/$PATCH/backup ]; then
  echo `date "$DF"`" mv $PT/$PATCH/backup to $PT/$PATCH/backup_$CTXT"
  echo `date "$DF"`" mv $PT/$PATCH/backup to $PT/$PATCH/backup_$CTXT" >>$LOGFILE
  mv $PT/$PATCH/backup $PT/$PATCH/backup_$CTXT
fi

if [ "$SETMAINT" = "Y" ]; then
  echo `date "$DF"`" Disable maintenance mode"
  echo `date "$DF"`" Disable maintenance mode" >> $LOGFILE
  sqlplus -s apps/`get_pw db apps` @$AD_TOP/patch/115/sql/adsetmmd.sql DISABLE
fi

cd "$ORIGDIR"

echo `date "$DF"`" Finished applying $DRIV $PNUM $INTERACT"
echo `date "$DF"`" Finished applying $DRIV $PNUM $INTERACT" >> $LOGFILE

Some notes - ensure your system password is set in the $APPL_TOP/admin/${TWO_TASK}adalldefaults.txt file - for Production runs I'd recommend switching fully into maintenance mode. - this works for 11.5.x, but may require some tinkering for different versions - If you want a new defaults file run adpatch as follows to the point where it asks for the directory where the Oracle Applications patch has been unloaded. Type abort at this prompt.

adpatch options=hotpatch defaultsfile=$APPL_TOP/admin/${TWO_TASK}/adpatchdef.txt

In this script I reference a centralized password script - get_pw. Create this in your path and set the permissions as restrictive as possible. This script looks something like this:

if [ $# = 2 ]; then
TYP=$1
USR=$2
elif [ $# = 1 ]; then
TYP=DB
USR=$1
else
echo invalid_arguments
exit 1
fi
if [ "`echo $TYP  tr [A-Z] [a-z]`" = "db" ]; then
if [ "`echo $USR  tr [A-Z] [a-z]`" = "apps" ]; then
echo apps
exit 0
else
echo unknown_db_user
exit 1
fi
fi

For more details on AD utilities in non-interactive mode see:

Oracle Applications Maintenance Procedures

PS. Steven Chan recently posted adpatch non-interactive mode as number 5 on his list of Top 7 ways of reducing patching downtimes for Apps.