#!/usr/bin/ksh #============================================================================ # File: chk_oerr.sh # Type: UNIX korn-shell script # Author: Tim Gorman (Evergreen Database Technologies, Inc) # Date: 02jul99 # # Description: # # Exit statuses: # 0 normal succesful completion # 1 ORACLE_SID not specified - user error # 2 ORACLE_SID not valid in ORATAB - user error # 3 "adhoc" directory not found # 4 "bdump" directory not found # 5 "cdump" directory not found # 6 "udump" directory not found # 7 "alert.log" file not found # 8 error messages detected! # # Modifications: #============================================================================ _Pgm=chk_oerr _LBin=/usr/local/bin _Host=`hostname` _OraSid=$1 export PATH=${_LBin}:/usr/bin:${PATH} # #---------------------------------------------------------------------------- # Define a korn-shell function to handle error messages... #---------------------------------------------------------------------------- _Echo() ### ...define shell-function "_Echo()"... { if [[ "$1" = "failure" ]] then if [ -r ${HOME}/.dbapage ] then sed '/^#/d' ${HOME}/.dbapage | while read _PageRcpt do date | mailx -s "${_Host}[${_OraSid}]: ${_Pgm}.sh $1" ${_PageRcpt} done fi fi if [ -r ${HOME}/.dbamail ] then sed '/^#/d' ${HOME}/.dbamail | while read _EmailRcpt do echo "$2" | mailx -s "${_Host}[${_OraSid}]: ${_Pgm}.sh $1" ${_EmailRcpt} done else echo "${_Host}[${_OraSid}]: ${_Pgm}.sh $1: $2" fi } ### ...end of definition of shell-function "_Echo()"... # #---------------------------------------------------------------------------- # Verify that the ORACLE_SID has been specified on the UNIX command-line... #---------------------------------------------------------------------------- if (( $# != 1 )) then _Echo warning "Usage: ${_Pgm}.sh ORACLE_SID; aborting..." exit 1 > /dev/null 2>&1 fi # #---------------------------------------------------------------------------- # Verify that the ORACLE_SID is registered in the ORATAB file... #---------------------------------------------------------------------------- if [ ! -x ${_LBin}/dbhome ] then _Echo warning "Script \"${_LBin}/dbhome\" not found or not executable; aborting..." exit 2 fi ${_LBin}/dbhome ${_OraSid} > /dev/null 2>&1 if (( $? != 0 )) then _Echo warning "\"${_OraSid}\" not local to this host; aborting..." exit 2 fi # #---------------------------------------------------------------------------- # Set up Oracle environment... #---------------------------------------------------------------------------- if [ ! -x ${_LBin}/oraenv ] then _Echo warning "Script \"${_LBin}/oraenv\" not found or not executable; aborting..." exit 2 fi export ORACLE_SID=${_OraSid} export ORAENV_ASK=NO . ${_LBin}/oraenv > /dev/null 2>&1 unset ORAENV_ASK # #---------------------------------------------------------------------------- # Locate the "admin/bin" directory and the script's log file... #---------------------------------------------------------------------------- if [[ "~${ORACLE_BASE}~" = "~~" ]] then _Echo warning "Env var \"ORACLE_BASE\" not set; aborting..." exit 2 fi if [[ "~${ORACLE_SID}~" = "~~" ]] then _Echo warning "Env var \"ORACLE_SID\" not set; aborting..." exit 2 fi _LogDir=${ORACLE_BASE}/admin/${ORACLE_SID}/adhoc if [ ! -d ${_LogDir} ] then _Echo warning "Logging directory \"${_LogDir}\" not found; aborting..." exit 3 > /dev/null 2>&1 fi # #---------------------------------------------------------------------------- # Locate the script's "log" file; if it doesn't exist, then initialize # it. If it already exists but has grown too large (i.e. over 100 lines), # then trim it by re-initializing it... # # The last line of the "log" file contains "contextual" information for this # script to use, namely three fields (separated by "~" characters) # containing: # # LN~HL~TM # # where: # LN is the last line number in the "alert.log" last checked # HL is the contents of the first line in the "alert.log" file # when last checked # TM is the time this script was last run, in the format of # "CCYYMMDDHH24MI" (the same format used by the "touch -am" # UNIX command)... # #---------------------------------------------------------------------------- _Log=${_LogDir}/${_Pgm}_state.log if [ -r ${_Log} ] then _NbrLines=`wc -l ${_Log} | awk '{print $1}'` if (( ${_NbrLines} >= 100 )) then _Line=`tail -1 ${_Log}` echo "# file re-initialized on \"`date`\"" > ${_Log} echo "# PLEASE DO NOT edit this file" >> ${_Log} echo ${_Line} >> ${_Log} fi else echo "# file initialized on \"`date`\"" > ${_Log} echo "# PLEASE DO NOT edit this file" >> ${_Log} echo "0~~9001010000" >> ${_Log} chmod 640 ${_Log} fi # #---------------------------------------------------------------------------- # Extract the three field values from the last line of the "log" file... #---------------------------------------------------------------------------- _LastLineNbr=`tail -1 ${_Log} | awk -F~ '{print $1}'` _LastHeadLine=`tail -1 ${_Log} | awk -F~ '{print $2}'` _LastTime=`tail -1 ${_Log} | awk -F~ '{print $3}'` # #---------------------------------------------------------------------------- # Trim the "log" file to only the most recent 10 lines... #---------------------------------------------------------------------------- _TrimLogFile=`tail -10 ${_Log}` echo "${_TrimLogFile}" > ${_Log} # #---------------------------------------------------------------------------- # Locate the "bdump" directory... #---------------------------------------------------------------------------- _BDump=${ORACLE_BASE}/admin/${ORACLE_SID}/bdump if [ ! -d ${_BDump} ] then _Echo warning "Dump directory \"${_BDump}\" not found; aborting..." exit 4 > /dev/null 2>&1 fi # #---------------------------------------------------------------------------- # Locate the "cdump" directory... #---------------------------------------------------------------------------- _CDump=${ORACLE_BASE}/admin/${ORACLE_SID}/cdump if [ ! -d ${_CDump} ] then _Echo warning "Dump directory \"${_CDump}\" not found; aborting..." exit 5 > /dev/null 2>&1 fi # #---------------------------------------------------------------------------- # Locate the "udump" directory... #---------------------------------------------------------------------------- _UDump=${ORACLE_BASE}/admin/${ORACLE_SID}/udump if [ ! -d ${_UDump} ] then _Echo warning "Dump directory \"${_UDump}\" not found; aborting..." exit 6 > /dev/null 2>&1 fi # #---------------------------------------------------------------------------- # Locate the "alert.log" file... #---------------------------------------------------------------------------- _AlertLog=${_BDump}/alert_${ORACLE_SID}.log if [ ! -r ${_AlertLog} ] then _Echo warning "\"${_AlertLog}\" file not found" exit 7 > /dev/null 2>&1 fi # #---------------------------------------------------------------------------- # Retrieve new values for the "log" file to be logged after the script # completes successfully... #---------------------------------------------------------------------------- _CurrLineNbr=`wc -l ${_AlertLog} | awk '{print $1}'` _CurrHeadLine=`head -1 ${_AlertLog}` _CurrTime=`date "+%y%m%d%H%M"` # #---------------------------------------------------------------------------- # Find new entries in the "alert.log" file since the last time this script # was executed. If the "alert.log" file has changed in any way (i.e. the # first line is different or the numbers of lines don't make sense), then # just read the whole thing... # # The text from the "alert.log" to be searched will be saved in a temporary # file... #---------------------------------------------------------------------------- _TmpFile=/tmp/${_Pgm}_$$.tmp if [[ "${_LastHeadLine}" = "${_CurrHeadLine}" ]] then if (( ${_LastLineNbr} <= ${_CurrLineNbr} && ${_LastLineNbr} > 0 )) then sed "1,${_LastLineNbr}d" ${_AlertLog} > ${_TmpFile} else cat ${_AlertLog} > ${_TmpFile} fi else cat ${_AlertLog} > ${_TmpFile} fi # #---------------------------------------------------------------------------- # Create a file to be used by the UNIX "find" command to find any files # modified since a specified time... #---------------------------------------------------------------------------- _NewerFile=/tmp/${_Pgm}_$$.newer touch -am -t ${_LastTime} ${_NewerFile} # #---------------------------------------------------------------------------- # Determine whether there are any ORA-00600 errors (except where the 1st # parameter is "12235", which is a user error), and ORA-00604 errors # (usually coming from PL/SQL), any ORA-01578 errors (file/block corruption), # or any ORA-07nnn errors (usually memory corruption)... # # Check the "alert.log" file and any new ".trc" files found in the "bdump" # or "udump" directories created since the last time this script was # executed... #---------------------------------------------------------------------------- _OutFile=/tmp/${_Pgm}_${ORACLE_SID}_${_CurrTime}.txt _HoldFile=/tmp/${_Pgm}_$$.hold for _f in ${_TmpFile} \ `find ${_BDump} -name "*.trc" -newer ${_NewerFile} -print` \ `find ${_UDump} -name "*.trc" -newer ${_NewerFile} -print` do grep "^ORA-0060" ${_f} | grep -v 12235 > ${_HoldFile} grep "^ORA-015" ${_f} >> ${_HoldFile} grep "^ORA-07" ${_f} >> ${_HoldFile} grep "^ORA-1" ${_f} >> ${_HoldFile} grep "^ORA-2" ${_f} >> ${_HoldFile} grep "^ORA-00060" ${_f} >> ${_HoldFile} if [ -s ${_HoldFile} ] then if [[ "${_f}" = "${_TmpFile}" ]] then echo "New errors detected in \"${_AlertLog}\":" >> ${_OutFile} else echo "New errors detected in \"${_f}\":" >> ${_OutFile} fi echo "===========================================================" >> ${_OutFile} cat ${_HoldFile} >> ${_OutFile} echo "" >> ${_OutFile} fi done # #---------------------------------------------------------------------------- # Also, make note of any new "core" file in the "cdump" directory... #---------------------------------------------------------------------------- find ${_CDump} -name core -newer ${_NewerFile} -print > ${_HoldFile} if [ -s ${_HoldFile} ] then echo "New core files in \"${_CDump}\":" >> ${_OutFile} echo "===========================================================" >> ${_OutFile} cat ${_HoldFile} >> ${_OutFile} fi # #---------------------------------------------------------------------------- # Get rid of temporary files... #---------------------------------------------------------------------------- rm -f ${_HoldFile} rm -f ${_TmpFile} rm -f ${_NewerFile} # #---------------------------------------------------------------------------- # Log new starting values to the "log" file for the next time this script # is executed... #---------------------------------------------------------------------------- echo "${_CurrLineNbr}~${_CurrHeadLine}~${_CurrTime}" >> ${_Log} # #---------------------------------------------------------------------------- # If an output file was created, then notify administrators, otherwise # exit quietly... #---------------------------------------------------------------------------- if [ -s ${_OutFile} ] then _Echo warning "`cat ${_OutFile}`" rm -f ${_OutFile} exit 8 > /dev/null 2>&1 else rm -f ${_OutFile} exit 0 > /dev/null 2>&1 fi