free shell code - timed oracle commands

From: mark rostron <markro2_at_atlantis.rosemount.com>
Date: 1996/01/12
Message-ID: <1996Jan12.221430.6984_at_rosevax.rosemount.com>#1/1

Hi, the following code is made available for those who, like me, have run into buggy shell scripts containing sqlplus commands which hang for some reason. The two reasons which come to mind are either invalid login/pwd, or invalid name/parameter values on sqlplus scripts executed with the _at_ syntax.

The example sql code here returns the id's of concurrent managers, but could just as easily have been a database startup/shutdown command (a timed shutdown command is mandatory for a backup program to work properly).

mr

#! /bin/sh


# ------------------------------------------------------------------------

# FILE  : get_pid

# HIST  : 27 dec 95 mark rostron created under HP-UX 9.04

# DESC : Get Unix PID for concurrent managers
# Usage : get_pid <ORACLE_SID>
# ------------------------------------------------------------------------
 

usage()
{
  echo "  

    USAGE ERROR: $*       get_pid <ORACLE_SID> - get process id for internal manager  

"
  exit 1
}    

# -------------------------------------------
# set the database name
# set up the environment
# check sqlplus works ok
 

db_nm=${1:?"`usage first parameter required`"} grep "^${db_nm}:" /etc/oratab > /dev/null || usage $db_nm non-existant in /etc/oratab  

ORACLE_SID=$db_nm; export ORACLE_SID
ORAENV_ASK=NO; . oraenv;ORAENV_ASK=  

which sqlplus > /dev/null || usage cant find sqlplus  

# -------------------------------------------
# Set up AOL owner
# note that the demo database
 

uname=applsys
echo $db_nm | grep demo && uname=appdemo pass=fnd    

# -------------------------------------------
# Get Conc process id
 

FIFO=/tmp/get_pid.$$
rm -f $FIFO
/etc/mknod $FIFO p
exec 2>/dev/null  

# watchdog

{
  sleep 10
  [ -p $FIFO ] && echo get_conc_pid_watchdog > $FIFO } >/dev/null 2>&1 &
watchdog=$!  

# sqlplus

{
  sqlplus -s $uname/$pass <<! > $FIFO
whenever sqlerror exit failure
set feedback off
set verify off
set heading off
set pagesize 0
SELECT os_process_id FROM fnd_concurrent_processes /
exit
!
} > /dev/null 2>&1 &
sqlplus=$!  

# wait for the first process to respond
 

read response < $FIFO

# kill any processes still running
 

ps -p $watchdog >/dev/null && kill $watchdog ps -p $sqlplus >/dev/null && kill $sqlplus

rm -f $FIFO

# work out what happened
 

case $response in
 get_conc_pid_watchdog) echo watchdog expired; exit 1;;

 *"invalid username/password"*)   echo user $uname/$pass in $db_nm invalid; exit 1;;
 *"ORACLE not available"*)        echo database $db_nm not running; exit 1;;
 *)                               echo $response; exit 0;;

esac   Received on Fri Jan 12 1996 - 00:00:00 CET