RE: writing job dependencies shell script

From: <Stephen.Lee_at_DTAG.Com>
Date: Thu, 19 Feb 2004 10:13:00 -0600
Message-ID: <D6339830FC73944E889CC3CEADDB205B079091F4@bu-dtagpo1.tracs.com>

> -----Original Message-----
> Hi,
> I'm trying to figure out how I can write a shell script that 
> checks the success or failure of  DML statements.

How hairy do you want to get with this.
Here is the function in my database monitoring script that runs the test. I think this will cover the subject reasonably well. The status numbers being printed correspond to an associative array. That way I can add new statuses by just adding to the array. I don't have to change other parts of the script (which is over 2,100 lines) which print out the status. I can just echo ${STATUS_LIST[$STATUS]}. The main script is for monitoring connectivity and free space, OR if there are any broken jobs. Which it does depends on what name it is run as; i.e. conn_check.ksh or job_mon.ksh.

If you all see something that could be made better or is just plain stupid, let me know.

######################### BEGIN TEST_THE_SID ######################

TEST_THE_SID () {

 LOCAL_DB_USER="$1"
 LOCAL_DB_PASS="$2"
 LOCAL_SID="$3"

## Try to connect to database
## The sqlplus connection test is run in the background in case it hangs.
## Because it is running in the background, edits to the sid_status file
## by the background process cannot be done since we might have multiple
## background processes trying to edit the file at the same time.
## So we must APPEND to an intermediate test_results file.
## Then do the sid_status edits later.

 (
 ulimit -s unlimited
## Start by assuming we have unknown error (status=2),
## then prove otherwise.

 STATUS=2
 {
 sqlplus -s <<-XXX
 ${LOCAL_DB_USER}/${LOCAL_DB_PASS}@${LOCAL_SID}   whenever sqlerror exit failure
  whenever oserror exit failure
  set serveroutput on trim on trims on
  set heading off feedback off
  select /*+RULE*/ name from v\$database;   declare
  v_count number := 0;
  begin
  dbms_output.enable(200000);
  select /*+RULE*/ count(tablespace_name) into v_count from dba_segments outer
  where greatest(nvl(next_extent,0),30000000) > (select nvl(max(bytes),0) from dba_free_space where tablespace_name = outer.tablespace_name)   and segment_type not in ('ROLLBACK','TEMPORARY');   if v_count > 0 then
  dbms_output.put_line('BLOWOUT IMMINENT');   end if;
  end;
  /

      exit;
      XXX
      } | sed '/^$/d; s/^[    ]*//g' | while read LINE; do
         # The above sed deletes blank lines and removes tabs and spaces at
the beginning of a line.
         #  which is probably overkill since trim and trims are set to on.
         # Note that the square brackets in the sed should have a space and
a tab; check this if you cut and paste.
         #
         # For each status, there should be a matching STATUS_LIST array
entry.
         # Statuses in the following awk section are:
         # 0 = OK
         # 1 = tnsping failed. If tested, will be in another place in
script.
         # 4 = low free space
         # 5 = Listener up; database down. (oracle not available)
         # 6 = unable to allocate shared memory
         # 7 = startup or shutdown in progress
         # 8 = listener could not resolve sid in connect descriptor
         # 9 = TNS:could not resolve service name
         # 10 = no listener
         # 11 = protocol adapter error (target box is probably down)
         # 12 = maximum number of processes exceeded.
         # 13 = invalid username/password
         # 14 = TNS: No appropriate service handler found.
         # 15 = TNS: Failed to start dedicated server process.
         # 16 = IO error reading file.
         # 17 = File cannot be read.
         # 18 = Error writing to audit trail.
         # 19 = Snapshot too old.
         # 20 = Shutdown in progress (different from status #7)
         # 21 = Immediate Shutdown in progress (different from status #7 and
#20)
         # 22 = end-of-file on communication channel
         # 23 = archiver error. Connect internal only, until freed.
         # 24 = ora-00600 internal error.
         # 25 = PMON process terminated with error
         # 26 = cannot open the specified controlfile
         # 27 = shared memory realm does not exist
         # 28 = tns operation timed out
         # NOTE: for 28, a status of 0 is being recorded since this is
almost always the result of
         #       sqlplus getting "stuck" on the Linux box.
         # 2 = Got error, but none of the above
         # 97 = invalid sqlplus option.  Usually the result of another error
but the other error
         #      was not seen or caught.  Might need another search line in
the awk script.
         #      Sometimes get this error for no 'apparent' reason.
         # In case you are wondering: 3 = sqlplus hung. That case is handled
by another section.
         #
         # We examine only the first three characters of LOCAL_SID to permit
the limited use of aliases.
         #
         # Since some errors are the result of previous errors, we would
like the STATUS to reflect
         # the most significant error.  We have to make some assumptions
about the error messages
         # and the order in which they will be received.
         # SP2-0306 is at the bottom, and status=2 is next to bottom for
this reason.
         #
         STATUS=`echo "$LINE" | awk '
            substr($0,1,3) == substr(LOCAL_SID,1,3) {print "0"; exit}
            $0 == "BLOWOUT IMMINENT" {print "4"; exit}
            /^ORA-01034/ {print "5"; exit}
            /^ORA-04031/ {print "6"; exit}
            /^ORA-01033/ {print "7"; exit}
            /^ORA-12505/ {print "8"; exit}
            /^ORA-12514/ {print "9"; exit}
            /^ORA-12541/ {print "10"; exit}
            /^ORA-12560/ {print "11"; exit}
            /^ORA-00020/ {print "12"; exit}
            /^ORA-01017/ {print "13"; exit}
            /^ORA-12519/ {print "14"; exit}
            /^ORA-12500/ {print "15"; exit}
            /^ORA-01115/ {print "16"; exit}
            /^ORA-00376/ {print "17"; exit}
            /^ORA-02002/ {print "18"; exit}
            /^ORA-01555/ {print "19"; exit}
            /^ORA-01090/ {print "20"; exit}
            /^ORA-01089/ {print "21"; exit}
            /^ORA-03113/ {print "22"; exit}
            /^ORA-00257/ {print "23"; exit}
            /^ORA-00600/ {print "24"; exit}
            /^ORA-00472/ {print "25"; exit}
            /^ORA-00210/ {print "26"; exit}
            /^ORA-27101/ {print "27"; exit}
            /^ORA-12535/ {print "0"; exit}
            /[Ee][Rr][Rr][Oo][Rr]/ {print "2"; exit}
            /^SP2-0306/ {print "97"; exit}
            ## If no matches above then keep current value of status.
            {print STATUS}
         ' LOCAL_SID="$LOCAL_SID" STATUS="$STATUS"`

         ## The STATUS should never be empty (Got a bug if it is)
         if [ -n "$STATUS" ]; then
            ## Check if STATUS is a number (Got a bug if it isn't)
            if [ "`echo $STATUS | sed 's/[^0-9]//g'`" = "$STATUS" ]; then
               ## Send any non-zero status to log file.
               ## Note: status is sent to test results after read loop ends.
               if [ $STATUS -ne 0 ]; then
                  echo "==> ${LOCAL_SID}: STATUS = $STATUS" >> "$LOG_FILE"
                  echo "    ${LOCAL_SID}: $LINE" >> "$LOG_FILE"
               fi
               ## If status is 2 (unknown error) or 0, continue parsing
input through the awk
               ## script to see if any known errors are identified.
               ## If we have known error, stop parsing and send remaining
read LINEs to log file.
               if [ $STATUS -ne 0 -a $STATUS -ne 2 ]; then
                  while read LINE; do
                     echo "    ${LOCAL_SID}: $LINE" >> "$LOG_FILE"
                  done
               fi
            ######## Below this point is bug situation. ########
            else
               ## If STATUS is not empty and if it is anything but numbers,
we have a bug.
               echo "-----------" >> "$LOG_FILE"
               echo "NON-Numeric result of TEST_THE_SID awk for $LOCAL_SID
follows:" >> "$LOG_FILE"
               echo "STATUS = ->${STATUS}<-" >> "$LOG_FILE"
               echo "The line that caused the situation is:" >> "$LOG_FILE"
               echo "LINE = ->${LINE}<-" >> "$LOG_FILE"
               echo "-----------" >> "$LOG_FILE"
            fi
         else
            ## If STATUS is empty, we have a bug.
            echo "-----------" >> "$LOG_FILE"
            echo "EMPTY STATUS in result of TEST_THE_SID awk for $LOCAL_SID
follows:" >> "$LOG_FILE"
            echo "STATUS = ->${STATUS}<-" >> "$LOG_FILE"
            echo "The line that caused the situation is:" >> "$LOG_FILE"
            echo "LINE = ->${LINE}<-" >> "$LOG_FILE"
            echo "-----------" >> "$LOG_FILE"
         fi
      done

      ## If status is blank assign status = 96 to it. (this should never
happen)
      if [ -z "$STATUS" ]; then
         STATUS=96
         echo "$LOCAL_SID 96" >> "$TEST_RESULTS"
         echo "$LOCAL_SID: STATUS was not assigned a value in TEST_THE_SID"
>> "$LOG_FILE"
      fi
      ## Log the status to the test results
      echo "$LOCAL_SID $STATUS" >> "$TEST_RESULTS"

   )&

   return 0
}

######################### END TEST_THE_SID ######################

   STATUS_LIST[0]='OK'
   ## The following are for connection and space test.

   STATUS_LIST[1]='TNSPING FAILED'
   STATUS_LIST[2]='UNSPECIFIED ERROR'
   STATUS_LIST[3]='SQLPLUS HUNG'
   STATUS_LIST[4]='LOW FREE SPACE'
   STATUS_LIST[5]='ORACLE NOT AVAILABLE'
   STATUS_LIST[6]='FAILED TO ALLOCATE SHARED MEMORY'
   STATUS_LIST[7]='STARTUP OR SHUTDOWN IN PROGRESS'
   STATUS_LIST[8]='LISTENER COULD NOT RESOLVE SID'
   STATUS_LIST[9]='TNS COULD NOT RESOLVE NAME'
   STATUS_LIST[10]='NO LISTENER'
   STATUS_LIST[11]='PROTOCOL ADAPTER ERROR'
   STATUS_LIST[12]='MAX PROCESSES EXCEEDED'
   STATUS_LIST[13]='INVALID USERNAME/PASSWORD'
   STATUS_LIST[14]='NO APPROPRIATE SERVICE HANDLER'
   STATUS_LIST[15]='FAILED TO START DEDICATED SERVER'
   STATUS_LIST[16]='IO ERROR READING FILE'
   STATUS_LIST[17]='FILE CANNOT BE READ'
   STATUS_LIST[18]='ERROR WRITING TO AUDIT TRAIL'
   STATUS_LIST[19]='SNAPSHOT TOO OLD'
   STATUS_LIST[20]='SHUTDOWN IN PROGRESS'
   STATUS_LIST[21]='IMMEDIATE SHUTDOWN IN PROGRESS'
   STATUS_LIST[22]='END OF FILE ON COMMUNICATION'
   STATUS_LIST[23]='ARCHIVER ERROR. CONNECT INTERNAL ONLY'
   STATUS_LIST[24]='ORA-00600 INTERNAL ERROR'
   STATUS_LIST[25]='PMON PROCESS TERMINATED WITh ERROR'
   STATUS_LIST[26]='CANNOT OPEN SPECIFIED CONTROLFILE'
   STATUS_LIST[27]='SHARED MEMORY REALM DOES NOT EXIST'
   STATUS_LIST[28]='TNS OPERATION TIMED OUT'

   ## The following are for job monitioring.

   STATUS_LIST[31]='CONNECT ERROR'
   STATUS_LIST[32]='JOB BROKEN'
   STATUS_LIST[33]='SQLPLUS ERROR'

   ## The following are common to both.

   STATUS_LIST[96]='BLANK STATUS'
   STATUS_LIST[97]='INVALID SQLPLUS OPTION'
   STATUS_LIST[98]='JUST TESTING'
   STATUS_LIST[99]='CONFIG FILE ERROR'
----------------------------------------------------------------

Please see the official ORACLE-L FAQ: http://www.orafaq.com

---

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Thu Feb 19 2004 - 10:13:00 CST