Shell Programming and Scripting

export SID=$ORACLE_SID
export TIMESTAMP=`date +%M%H%d%m%y`;
export mon_dir=$ORACLE_BASE/admin/DCTMTEST/dbmonitor
export fn=$mon_dir/email_body.tmp
export alertck=$mon_dir/alertck.log
touch $alertck*
touch $fn
touch $ORACLE_BASE/admin/DCTMTEST/dbmonitor/donot_delete.log 
EMAIL='name@company.com'
adrci << EOF
spool $mon_dir/alert_$TIMESTAMP.log
set termout off
set homepath diag/rdbms/$ORACLE_SID/$ORACLE_SID
SHOW ALERT -P "MESSAGE_TEXT LIKE '%ORA-%'" -term
spool off
exit
EOF
export c_log=`wc -l $mon_dir/alert_$TIMESTAMP.log awk '{ print $1 }'`
export c_tmp=`wc -l $mon_dir/donot_delete.log awk '{ print $1 }'`
echo $c_log
echo $c_tmp
if (($(($c_log)) > $(($c_tmp)))); then
comm -23 $mon_dir/alert_$TIMESTAMP.log $mon_dir/donot_delete.log grep ORA- sort -u > $alertck
exec 3< $alertck
# read until the end of the file 
until [ $done ] 
do 
read <&3 ERR_LINE 
if [ $? != 0 ] then 
done=1 
continue 
fi 
echo $ERR_LINE >> $fn 
export ERR_NO=`echo $ERR_LINE awk '{ print $1 }'awk -F- '{ print $2 }' awk -F: '{ print $1 }'` 
echo " Oracle error is : ORA-$ERR_NO" 
oerr ora $ERR_NO >> $fn 
echo " " >> $fn 
echo "-----------------------------------------------------" >> $fn 
echo " " >> $fn 
'done' 
echo "ERRORS: sending mail!" 
SUBJECT="$SID - ORA Error Message found in alert.log on `date`"
echo $SUBJECT
# from a Linux server, use following line to send email:
cat $fn mail -s "$SUBJECT" $EMAIL
else
echo "No Oracle error is found in alert.log since last time this script was run." 
'fi'
mv $mon_dir/alert_$TIMESTAMP.log $mon_dir/donot_delete.log
rm $fn
echo "The script was executed successfully."

adrci << EOF

#!/bin/bash


ORACLE_HOME=<your_oracle_home>
ORACLE_SID=<your_oracle_sid>
ORAENV_ASK=NO
export ORACLE_HOME ORACLE_SID ORAENV_ASK
_my_first_date='1990-01-01 00:00:00'
_my_last_date_file=_my_last_date

[ -f "$_my_last_date_file" ] ||
  printf > ./"$_my_last_date_file" '%s\n' "$_my_first_date"
  
_last_date=$(<./"$_my_last_date_file")

printf > ./"$_my_last_date_file" '%s\n' "$(
  date '+%Y-%m-%d %H:%M:%S'
  )"

_mailto=<your_email_address>
_mail_subject="Alert for $ORACLE_SID on $HOSTNAME"


_my_result=$(
  adrci exec="
    set home $ORACLE_SID;
    show alert -term -P \\\"MESSAGE_TEXT like '%ORA%' and ORIGINATING_TIMESTAMP >= '$_last_date'\\\"
   "
  )

case $_my_result in 
  ( *ORA-* ) mailx -s "$_mail_subject" "$_mailto" <<< "$_my_result"  
esac

[oracle@ukedxdtmtdbs01a bin]$ #!/bin/bash
[oracle@ukedxdtmtdbs01a bin]$
[oracle@ukedxdtmtdbs01a bin]$
[oracle@ukedxdtmtdbs01a bin]$ ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
[oracle@ukedxdtmtdbs01a bin]$ ORACLE_SID=DCTMTEST1
[oracle@ukedxdtmtdbs01a bin]$ ORAENV_ASK=NO
[oracle@ukedxdtmtdbs01a bin]$ export ORACLE_HOME ORACLE_SID ORAENV_ASK
[oracle@ukedxdtmtdbs01a bin]$ _my_first_date='1990-01-01 00:00:00'
[oracle@ukedxdtmtdbs01a bin]$ _my_last_date_file=_my_last_date
[oracle@ukedxdtmtdbs01a bin]$
[oracle@ukedxdtmtdbs01a bin]$ [ -f "$_my_last_date_file" ] ||
>   printf > ./"$_my_last_date_file" '%s\n' "$_my_first_date"
-bash: ./_my_last_date: Permission denied
[oracle@ukedxdtmtdbs01a bin]$
_l[oracle@ukedxdtmtdbs01a bin]$ _last_date=$(<./"$_my_last_date_file")
-bash: ./_my_last_date: No such file or directory
[oracle@ukedxdtmtdbs01a bin]$
p[oracle@ukedxdtmtdbs01a bin]$ printf > ./"$_my_last_date_file" '%s\n' "$(
>   date '+%Y-%m-%d %H:%M:%S'
 >   )"
-bash: ./_my_last_date: Permission denied
[oracle@ukedxdtmtdbs01a bin]$
_m[oracle@ukedxdtmtdbs01a bin]$ _mailto=name@company.com
[oracle@ukedxdtmtdbs01a bin]$ _mail_subject="Alert for $ORACLE_SID on $HOSTNAME"

[oracle@ukedxdtmtdbs01a bin]$ _my_result=$(
>   adrci exec="
>     set home $ORACLE_SID;
>     show alert -term -P \\\"MESSAGE_TEXT like '%ORA%' and ORIGINATING_TIMESTAMP >= '$_last_date'\\\"
>    "
>   )
[oracle@ukedxdtmtdbs01a bin]$
[oracle@ukedxdtmtdbs01a bin]$ case $_my_result in
 >   ( *ORA-* ) mailx -s "$_mail_subject" "$_mailto" <<< "$_my_result"
> esac
[oracle@ukedxdtmtdbs01a bin]$