Oracle Shell Scripting

I remember seeing Jon Emmons' announcement on the Oracle News Aggregator and I've had it in my "wanted" list on bookjetty for ages. This week I discovered Jon's Oracle Shell Scripting: Linux and UNIX Programming for Oracle (Oracle In-Focus series) at the NLB and have just enjoyed a good read of it. I wish more DBAs had read this book. In fact it should be mandatory to get an OCP certification! Let's face it, most Oracle installations are running on a *nix variant, and you can't be a DBA if you are not comfortable at both the SQL*Plus and shell prompt. To be a good and efficient DBA in my book, I want to see evidence of thinking smart, and repetitive task automation. When I see so-called DBAs who are happy to type the same "select .. from v$.." query every day of their working life, I doubt their brain is switched on, and I find it really, really scary to think they have the sys/system passwords! They say tool usage is a sure sign of advanced intelligence in birds. And the same applies to all of us in IT. The three examples I look for at an Oracle Database installation are: RMAN Grid Control Shell scripts

If none of these are present, then I tend to presume the real DBA has long left the building. Even if you are using third-party alternatives, do you continue to re-evaluate the Oracle capabilities with each new release?

Jon Emmons' book is of course more focused than this. It perfectly fills a niche, with an approachable, practical and comprehensive coverage of shell scripting from a DBA's perspective.

I can see the ideal audience for this book is people who are reasonable familiar with Oracle administration but are new to shell scripting. This book will rapidly teach you all you need to know on the scripting side (and let you skip alot of stuff you can learn later).

In other words, if you are a DBA who has just been assigned to manage a Unix-based system for the first time in your career: get this book. Forget all the (great) general Linux/Unix/shell scripting books for now. Don't even think the Oracle docs will teach you what you need to know. Oracle Shell Scripting: Linux and UNIX Programming for Oracle (Oracle In-Focus series) is what you need!

If you are coming the other way though - an experienced Linux admin being told that from Monday you also need to manage an Oracle database - I'd say this book probably doesn't have much to teach you. There's much more you'd need to learn about Oracle first (after telling your manager he's crazy), and there are really no scripting tricks in the book that you shouldn't already know. The main benefit you get would probably be a few pages in chapter 6 that cover the tricks of using sqlplus in a shell script - all in one place rather than having to tease it out of the Oracle docs (or see this related question on stackoverflow).

Originally posted on It's a Prata Life.

Find and tail the Oracle alert log

"Where's the alert log?" .. usually the first thing you want to know when looking at a new database.

Oracle's Grid Control solves this problem very well with it's web interface. But not always available.

Or in my case recently, where I had 6 databases setup on a single machine for various testing scenarios, I was getting tired of cd'ing all over the place, forgetting paths, or ending up with too many windows open for my own good. Getting well sick of this, I did a quick hunt for scripts to help but surprisingly didn't find much. So diving in, I created oraAlertLog.sh and now I'm happy;)

This script is for running on the database server. The Oracle environment must be set, and - at least for the first call - the database must be available so that the "background_dump_dest" parameter can be obtained. The script will cache the alert log location so that it will still work if the database happens to be down.

After getting this running I thought "duh!", should have been in perl so it would be possible to run on any platform supported by Oracle. Here's a version in Perl: oraAlertLog.pl. It requires Perl with DBI and DBD::Oracle .. the Perl distribution included with Oracle Database is fine for this (you just need to make sure the environment is properly configured).

Who's bound to that port?

Recently wanted to track down the details of the process that had a specific port open. I checked out the O'Reilly Linux Server Hacks book, and hack #56 was pretty much what I wanted. I scriptified it somewhat as follows. Note that this only looks at tcp:

#!/bin/bash
port=$1
procinfo=$(netstat --numeric-ports -nlp 2> /dev/null | grep ^tcp | grep -w ${port} | tail -n 1 | awk '{print $7}')

case "${procinfo}" in
"")
echo "No process listening on port ${port}"
;;
"-")
echo "Process is running on ${port}, but current user does not have rights to see process information."
;;
*)
echo "${procinfo} is running on port ${port}"
ps -uwep ${procinfo%/*}
;;
esac

As you can see, this works by getting a little bit of process info from netstat, then using ps to get the full details. Download the script here: whosOnPort.sh

Handling range parameters in Bash

I've come across a few occasions where I wanted to specify a "range parameter" for Bash scripts. Like "1..4" meaning do for 1,2,3 and 4.

Here's a simple trick that uses the (relatively obscure) variable expansion and substring replacement capabilities of the shell.

#!/bin/bash
v_range="1..3"         # or you could have taken it as a script parameter
v_start=${v_range%%.*} # chomp everything from the left-most matching "."
v_end=${v_range##*.}   # chomp everything up to the right-most matching "."

The repeated %% and ## basically mean you will get a "greedy" match, so you can say "1..4" or "1....5"; it doesn't matter how many repeats you have of the range delimiter. Of course, you can choose other delimiters such as a hyphen, as in "5-10", if you wish.

Now that you have extracted the start and end indices, you can then loop or whatever to your hearts content:

for ((a=v_start; a <= v_end ; a++))
do
   echo "Looping with a=$a"
done

For more information on variable expansion, see 9.3 in the Advanced Bash Scripting Guide.

Postscript 5-Feb-2008:
We live and learn! Hat tip to buford for alerting me to the seq utility, which simplifies the iteration over a range, as in:

for a in `seq 1 10`
do
  echo "Looping with a=$a"
done 

You still need to determine the start and end values of the range, which is the whole point of the variable expansions approach posted here.

Generating a temp filename in Bash

Here's a function that simplifies the process of generating temporary filenames in shell scripts (I use bash). This function will generate a temporary filename given optional path and filename prefix parameters. If the file happens to already exist (a small chance), it recurses to try a new filename.

It doesn't cleanup temp files or anything fancy like that, but it does have the behaviour that if you never write to the temp file, it is not created. If you don't like that idea, touch the file before returning the name.

This procedure demonstrates the use of variable expansion modifiers to parse the parameters, and the $RANDOM builtin variable.

function gettmpfile()
{
  local tmppath=${1:-/tmp}
  local tmpfile=$2${2:+-}
  tmpfile=$tmppath/$tmpfile$RANDOM$RANDOM.tmp
  if [ -e $tmpfile ]
  then
    # if file already exists, recurse and try again
    tmpfile=$(gettmpfile $1 $2)
  fi
  echo $tmpfile
}

By default (with no parameters specified), this function will return a random filename in the /tmp directory:

[prompt]$ echo $(gettmpfile)
/tmp/324003570.tmp

If you specify a path and filename prefix, these are used to generate the name:

[prompt]$ echo $(gettmpfile /my_tmp_path app-tmpfile)
/my_tmp_path/app-tmpfile-276051579.tmp