DBA Script Coding Considerations for Best Compatibility to any Platform

Do you find yourself trying to convert your DBA scripts to work on other OS or UNIX flavors? This is a typical issue that DBAs cope with.



The solutions can be:

  1. Each system has its own best scripting code (e.g., PowerShell for Windows, bash for Linux), so let’s keep some versions of the same code.
  2. Write the code in a common language for all systems:
  • Shell: In the past, I used the old Posix shell (sh), which existed in all Unix systems. These days I use bash, as it is very common.
  • Perl: The Oracle installation already has its own Perl; there’s no need to install it.
  • Java: The Oracle installation already has its own Java; there’s no need to install it.
  • Python: Python should be installed, but the libraries that can be used are huge, and you can write shorter code.
  1. Write in sql/plsql as much as you can; the external code will be minimal (cmd / sh).
  2. Work remotely from your convenient operating system, using your scripts written in the language you mastered.
  3. Use a few macros injected into your terminal.
  4. Why use scripts: I use Toad / Cloud Control; it’s much easier.
  5. Leave me alone: I like typing the same commands over and over.

I use most of the above options 🙂 but I try to have one version of script.

I am writing in bash, but have started moving to Python (for new and complicated needs).

Recently I needed to work on Solaris. Because I believe in using one code for all, I started to modify my code with a lot of IFs. Above that, I needed to adjust external utilities like “sed” and “awk” since they do not work the same (Solaris vs Linux).

Luckily I found that from Solaris 11 there are built-in gnu commands that are compatible with Linux. These commands are located in a different directory and start with the letter “g”. “sed” is “gsed”, “awk” is “gawk,” and so on.

I decided that the gnu utility commands will be a variable in all of my scripts. All scripts will call a master environment file that decides which version to use.


cat platform.env
platform=`uname -s`
case "$platform"
in
"SunOS") os=Solaris
ORATAB=/var/opt/oracle/oratab
AWK=gawk
GREP=ggrep
EGREP=gegrep
SED=gsed
HOST_NAME=$(hostname)
DF=gdf
FIND=gfind
USER=${LOGNAME}
CAT=gcat
CHOWN=gchown
CHMOD=gchmod
CP=gcp
ECHO=gecho
MKDIR=gmkdir
CUT=gcut
SUDO=/usr/local/bin/sudo
;;
"Linux") os=Linux
ORATAB=/etc/oratab
AWK=awk
GREP=grep
EGREP=egrep
SED=sed
HOST_NAME=$(hostname -f)
DF=df
FIND=find
CAT=cat
CHOWN=chown
CHMOD=chmod
CP=cp
ECHO=echo
MKDIR=mkdir
CUT=cut
SUDO=sudo
;;
#"HP-UX") os=hpunix;;
# "AIX") os=aix;;
*) echo "Sorry, $platform is not currently supported." | tee -a $LOGFILE
exit 1;;
esac

In my Basic Commands for DBAs – Part 1 – Finding the Instance blog, I discussed identifying $ORACLE_SID and $ORACLE_HOME. Now we will see a generic script version that uses the variables above:


#!/bin/bash
export SCRIPT_DIR="$( cd "$( dirname "${BASH_SOURCE[0]}" )" && pwd )"
source ${SCRIPT_DIR}/platform.env
if [ -f ${ORATAB} ] then
for ORA_SID in `${CAT} ${ORATAB} | ${GREP} -v ^'#' | ${GREP} -v ^$ | ${AWK} -F ":" ' { print $1 }' | uniq`
do
if [[ ! "${ORA_SID^^}" == *"ASM"* ]] && [[ ! "${ORA_SID^^}" == *"MGMTDB"* ]] then
echo working on ${ORA_SID}
export ORACLE_SID=${ORA_SID}
export ORACLE_HOME=`${CAT} $ORATAB | ${GREP} ^${ORACLE_SID}: | ${GREP} -v "^#" | ${GREP} -v "^$" | ${CUT} -d":" -f 2`
Put your code here, using the variables above
fi
done
fi

Please comment and tell us about the way you use DBA scripts.
Yossi Nixon