Home » RDBMS Server » Performance Tuning » Oracle alerts (11.2.0.3)
Oracle alerts [message #603822] Thu, 19 December 2013 10:54 Go to next message
ivojames
Messages: 7
Registered: December 2013
Location: Dallas
Junior Member
Question.
I have a script that email all alerts on our database. I was ask to edit script not to display latches, death locks and missing temporary objects.
Any idea on what to do?
Re: Oracle alerts [message #603825 is a reply to message #603822] Thu, 19 December 2013 11:04 Go to previous messageGo to next message
BlackSwan
Messages: 21938
Registered: January 2009
Senior Member
>Any idea on what to do?
change the SELECT statement.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: Oracle alerts [message #603828 is a reply to message #603822] Thu, 19 December 2013 11:52 Go to previous messageGo to next message
Lalit Kumar B
Messages: 1081
Registered: May 2013
Location: Hi-tech city
Senior Member
ivojames wrote on Thu, 19 December 2013 22:24
Question.
I have a script that email all alerts on our database. I was ask to edit script not to display latches, death locks and missing temporary objects.
Any idea on what to do?


Could you please create a DB link between my DB and yours? So that I would peep inside the script and fix it.
Re: Oracle alerts [message #603840 is a reply to message #603825] Thu, 19 December 2013 13:15 Go to previous messageGo to next message
ivojames
Messages: 7
Registered: December 2013
Location: Dallas
Junior Member
Here the script I working on

export ORAENV_ASK=NO
. oraenv

PATH=$PATH:$ORACLE_HOME/bin;export PATH
logdir=$DBA/$ORACLE_SID/log
logfile=$logdir/$script_name.$rdate.log
diag_dest=$ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/trace

. $DBA/scripts/ais100r.sh

echo "Oracle Home is $ORACLE_HOME" >> $logfile
echo "OSTYPE is $OSTYPE " >> $logfile
rw=`date '+%u'`
echo "Day of week is "$rw >> $logfile
echo "Day 1 is Monday" >> $logfile
tod=`date '+%y%m%d'`
echo "Todays date is " $tod >>$logfile

grep ORA- \
$diag_dest/alert_$ORACLE_SID.log>\
$logdir/alert_$ORACLE_SID.err.txt

if [[ ! -f $logdir/alert_$ORACLE_SID.old.txt ]]
then touch $logdir/alert_$ORACLE_SID.old.txt
fi

if [[ ! -s $logdir/alert_$ORACLE_SID.err.txt ]]
then step=" No errors to report"
echo $step >> $logfile
else
if
[[ -f $logdir/alert_$ORACLE_SID.old.txt ]]
then diff $logdir/alert_$ORACLE_SID.err.txt $logdir/alert_$ORACLE_SID.old.txt>\
$logdir/alert_email_$ORACLE_SID.txt
rm $logdir/alert_$ORACLE_SID.old.txt
cp $logdir/alert_$ORACLE_SID.err.txt $logdir/alert_$ORACLE_SID.old.txt
else
touch $logdir/alert_$ORACLE_SID.old.txt
echo "touched old.txt">>$logfile
fi
fi

########### New error routine
if [[ -s $logdir/alert_email_$ORACLE_SID.txt ]]
then
typeset -L3 timeout=`grep ORA-3136 $logdir/alert_email_$ORACLE_SID.txt|wc -l`
typeset -L3 kc=`grep ORA- $logdir/alert_email_$ORACLE_SID.txt|wc -l`
echo "kc $kc" >> $logfile
echo "timeout $timeout" >> $logfile
if [[ $timeout -gt 1 ]]
then
echo "timeout gt 1 ">> $logfile
mesg="$kc new error(s) found. $timeout timeouts (ORA-3136). Please check $diag_dest/alert_$ORACLE_SID.log"
report=1
elif
[[ $kc -gt $timeout ]]
then mesg="$kc new error(s) found. Please check $diag_dest/alert_$ORACLE_SID.log"
echo "kc greater than timeout " >> $logfile
report=1
fi
fi

if [[ $report == 1 ]]
then
step="alert_$ORACLE_SID.log on `date '+%D %R'`"
echo $step >> $logfile
echo $mesg |
$e_mail -s "$step" `cat $MAIL_LIST`
fi
############# End of New Error Routine

if [[ $rw = 1 ]]
## This is true if it is Monday
then
if [[ ! -a $diag_dest/alert_$ORACLE_SID.$tod.log ]]
then
echo "Backing up current alert log and creating a new one ">>$logfile
echo "Todays day of week is `date '+%u'`" >>$logfile
echo "Build new alert and comparision file " >>$logfile
mv $diag_dest/alert_$ORACLE_SID.log \
$diag_dest/alert_$ORACLE_SID.$tod.log
touch $diag_dest/alert_$ORACLE_SID.log
rm $logdir/alert_$ORACLE_SID.old.txt
touch $logdir/alert_$ORACLE_SID.old.txt
else
echo "Alert log file with todays date has already been created " >>$logfile
fi
else
echo "New alerts log files are not created on this day of the week" >>$logfile
fi
echo "Leave new trace files on the system for 20 days" >>$logfile
echo "Leave old alerts on the system for 90 days" >>$logfile
echo "Leave log files on the system for 30 days" >>$logfile
echo "Leave logs from $script_name on the system for 1 day" >>$logfile
{
find $diag_dest/ -name "*.trc" -mtime +20
find $diag_dest/ -name "alert*.log" -mtime +90
find $diag_dest/ -name "*.trm" -mtime +20
find $ORACLE_BASE/admin/$ORACLE_SID/adump/ -name "*.aud" -mtime +60
find $logdir/ -name "*.log" -mtime +30
find $logdir/ -name "$script_name*.log" -mtime +1
}>> $logfile

{
find $diag_dest/ -name "*.trc" -mtime +20 -exec rm -f {} \;
find $diag_dest/ -name "alert*.log" -mtime +90 -exec rm -f {} \;
find $diag_dest/ -name "*.trm" -mtime +20 -exec rm -f {} \;
find $ORACLE_BASE/admin/$ORACLE_SID/adump/ -name "*.aud" -mtime +60 -exec rm -f {} \;
find $logdir/ -name "*.log" -mtime +30 -exec rm -f {} \;
find $logdir/ -name "$script_name*.log" -mtime +1 -exec rm -f {} \;
}

step="Completed successfully"
echo $script_name " " $step>>$logfile

exit
Re: Oracle alerts [message #603842 is a reply to message #603828] Thu, 19 December 2013 13:28 Go to previous messageGo to next message
BlackSwan
Messages: 21938
Registered: January 2009
Senior Member
>I was ask to edit script not to display latches, death locks and missing temporary objects.
(BTW, what is "death lock"? deadlock perhaps?)

Please provide at least 1 example where each of the above is being displayed within email message.
AFAIK, alert_SID.log file does not contain details about any of the above.
Re: Oracle alerts [message #603847 is a reply to message #603842] Thu, 19 December 2013 17:25 Go to previous messageGo to next message
cookiemonster
Messages: 10571
Registered: September 2008
Location: Rainy Manchester
Senior Member
alert file will list deadlocks, shouldn't display the other two.
Re: Oracle alerts [message #603939 is a reply to message #603842] Fri, 20 December 2013 09:35 Go to previous messageGo to next message
ivojames
Messages: 7
Registered: December 2013
Location: Dallas
Junior Member
Here is the message

DEADLOCK DETECTED ( ORA-00060 )
[Transaction Deadlock]
The following deadlock is not an ORACLE error...

No action taken!

-----Original Message-----
From: AIS Zeus [mailto:aiszeus@utdallas.edu]
Sent: Sunday, December 15, 2013 5:05 PM
To: IR - AIS - DBAs
Subject: alert_ppersb.log on 12/15/13 17:05

1 new error(s) found. Please check /u01/oracle/diag/rdbms/ppersb/ppersb/trace/alert_ppersb.log
Re: Oracle alerts [message #603941 is a reply to message #603847] Fri, 20 December 2013 09:36 Go to previous messageGo to next message
ivojames
Messages: 7
Registered: December 2013
Location: Dallas
Junior Member
Here the error

DEADLOCK DETECTED ( ORA-00060 )
[Transaction Deadlock]
The following deadlock is not an ORACLE error...

No action taken!

-----Original Message-----
From: AIS Zeus [mailto:aiszeus@utdallas.edu]
Sent: Sunday, December 15, 2013 5:05 PM
To: IR - AIS - DBAs
Subject: alert_ppersb.log on 12/15/13 17:05

1 new error(s) found. Please check /u01/oracle/diag/rdbms/ppersb/ppersb/trace/alert_ppersb.log
Re: Oracle alerts [message #603942 is a reply to message #603941] Fri, 20 December 2013 10:16 Go to previous messageGo to next message
ivojames
Messages: 7
Registered: December 2013
Location: Dallas
Junior Member
Here is another alert

DEADLOCK DETECTED ( ORA-00060 )
[Transaction Deadlock]
The following deadlock is not an ORACLE error...

No action taken!

-----Original Message-----
From: AIS Zeus [mailto:aiszeus@utdallas.edu]
Sent: Sunday, December 15, 2013 5:05 PM
To: IR - AIS - DBAs
Subject: alert_ppersb.log on 12/15/13 17:05

1 new error(s) found. Please check /u01/oracle/diag/rdbms/ppersb/ppersb/trace/alert_ppersb.log
##############################################################################################################################

ORA-16957: SQL Analyze time limit interrupt

No action taken!

-----Original Message-----
From: AIS Zeus [mailto:aiszeus@utdallas.edu]
Sent: Thursday, December 19, 2013 10:10 PM
To: IR - AIS - DBAs
Subject: alert_ppersa.log on 12/19/13 22:10

2 new error(s) found. Please check /u01/oracle/diag/rdbms/ppersa/ppersa/trace/alert_ppersa.log
Re: Oracle alerts [message #603945 is a reply to message #603942] Fri, 20 December 2013 10:24 Go to previous messageGo to next message
BlackSwan
Messages: 21938
Registered: January 2009
Senior Member
to avoid deadlock message then do as below

grep -v -i deadlock $logdir/alert_email_$ORACLE_SID.txt
Re: Oracle alerts [message #603950 is a reply to message #603945] Fri, 20 December 2013 11:11 Go to previous messageGo to next message
ivojames
Messages: 7
Registered: December 2013
Location: Dallas
Junior Member
Blackswan
The for your response. Do you know where in my script I can add the line. Paste my script below

rdate=`date '+%y%m%d%H%M%S'`
. $HOME/.profile
unset ORACLE_BASE
unset TERM

export script_name=ais911_1u
export ORACLE_SID=$1
##if [[ $OSTYPE = "linux" ]]
##then export ORACLE_HOME=`cat /etc/oratab|grep $ORACLE_SID:|cut -f2 -d':'`
##else
##export ORACLE_HOME=`cat /var/opt/oracle/oratab|grep $ORACLE_SID:|cut -f2 -d':'`
##fi
export ORAENV_ASK=NO
. oraenv

PATH=$PATH:$ORACLE_HOME/bin;export PATH
logdir=$DBA/$ORACLE_SID/log
logfile=$logdir/$script_name.$rdate.log
diag_dest=$ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/trace

. $DBA/scripts/ais100r.sh

echo "Oracle Home is $ORACLE_HOME" >> $logfile
echo "OSTYPE is $OSTYPE " >> $logfile
rw=`date '+%u'`
echo "Day of week is "$rw >> $logfile
echo "Day 1 is Monday" >> $logfile
tod=`date '+%y%m%d'`
echo "Todays date is " $tod >>$logfile

grep ORA- \
$diag_dest/alert_$ORACLE_SID.log>\
$logdir/alert_$ORACLE_SID.err.txt

if [[ ! -f $logdir/alert_$ORACLE_SID.old.txt ]]
then touch $logdir/alert_$ORACLE_SID.old.txt
fi

if [[ ! -s $logdir/alert_$ORACLE_SID.err.txt ]]
then step=" No errors to report"
echo $step >> $logfile
else
if
[[ -f $logdir/alert_$ORACLE_SID.old.txt ]]
then diff $logdir/alert_$ORACLE_SID.err.txt $logdir/alert_$ORACLE_SID.old.txt>\
$logdir/alert_email_$ORACLE_SID.txt
rm $logdir/alert_$ORACLE_SID.old.txt
cp $logdir/alert_$ORACLE_SID.err.txt $logdir/alert_$ORACLE_SID.old.txt
else
touch $logdir/alert_$ORACLE_SID.old.txt
echo "touched old.txt">>$logfile
fi
fi

########### New error routine
if [[ -s $logdir/alert_email_$ORACLE_SID.txt ]]
then
typeset -L3 timeout=`grep ORA-3136 $logdir/alert_email_$ORACLE_SID.txt|wc -l`
typeset -L3 kc=`grep ORA- $logdir/alert_email_$ORACLE_SID.txt|wc -l`
echo "kc $kc" >> $logfile
echo "timeout $timeout" >> $logfile
if [[ $timeout -gt 1 ]]
then
echo "timeout gt 1 ">> $logfile
mesg="$kc new error(s) found. $timeout timeouts (ORA-3136). Please check $diag_dest/alert_$ORACLE_SID.log"
report=1
elif
[[ $kc -gt $timeout ]]
then mesg="$kc new error(s) found. Please check $diag_dest/alert_$ORACLE_SID.log"
echo "kc greater than timeout " >> $logfile
report=1
fi
fi

if [[ $report == 1 ]]
then
step="alert_$ORACLE_SID.log on `date '+%D %R'`"
echo $step >> $logfile
echo $mesg |
$e_mail -s "$step" `cat $MAIL_LIST`
fi
############# End of New Error Routine

if [[ $rw = 1 ]]
## This is true if it is Monday
then
if [[ ! -a $diag_dest/alert_$ORACLE_SID.$tod.log ]]
then
echo "Backing up current alert log and creating a new one ">>$logfile
echo "Todays day of week is `date '+%u'`" >>$logfile
echo "Build new alert and comparision file " >>$logfile
mv $diag_dest/alert_$ORACLE_SID.log \
$diag_dest/alert_$ORACLE_SID.$tod.log
touch $diag_dest/alert_$ORACLE_SID.log
rm $logdir/alert_$ORACLE_SID.old.txt
touch $logdir/alert_$ORACLE_SID.old.txt
else
echo "Alert log file with todays date has already been created " >>$logfile
fi
else
echo "New alerts log files are not created on this day of the week" >>$logfile
fi
echo "Leave new trace files on the system for 20 days" >>$logfile
echo "Leave old alerts on the system for 90 days" >>$logfile
echo "Leave log files on the system for 30 days" >>$logfile
echo "Leave logs from $script_name on the system for 1 day" >>$logfile
{
find $diag_dest/ -name "*.trc" -mtime +20
find $diag_dest/ -name "alert*.log" -mtime +90
find $diag_dest/ -name "*.trm" -mtime +20
find $ORACLE_BASE/admin/$ORACLE_SID/adump/ -name "*.aud" -mtime +60
find $logdir/ -name "*.log" -mtime +30
find $logdir/ -name "$script_name*.log" -mtime +1
}>> $logfile

{
find $diag_dest/ -name "*.trc" -mtime +20 -exec rm -f {} \;
find $diag_dest/ -name "alert*.log" -mtime +90 -exec rm -f {} \;
find $diag_dest/ -name "*.trm" -mtime +20 -exec rm -f {} \;
find $ORACLE_BASE/admin/$ORACLE_SID/adump/ -name "*.aud" -mtime +60 -exec rm -f {} \;
find $logdir/ -name "*.log" -mtime +30 -exec rm -f {} \;
find $logdir/ -name "$script_name*.log" -mtime +1 -exec rm -f {} \;
}

step="Completed successfully"
echo $script_name " " $step>>$logfile

exit
Re: Oracle alerts [message #603951 is a reply to message #603950] Fri, 20 December 2013 11:56 Go to previous messageGo to next message
Lalit Kumar B
Messages: 1081
Registered: May 2013
Location: Hi-tech city
Senior Member
First and foremost thing, please do not PM regarding forum questions, please post your queries in the appropriate forum. That is what the forums are meant for.

Ok, I went through the entire thread and concluded that :
1. Per your original post, you want to get rid of the alerts related to deadlocks etc.
2. If point 1 is true, then your next step is to get rid of the functionality which is writing these alerts into the log file.
3. Once point 2 is done, you won't have any such alerts being logged into the log file and thus you won't get alerted at all.

So, how are these alerts getting logged into the log file?
Re: Oracle alerts [message #603952 is a reply to message #603951] Fri, 20 December 2013 12:01 Go to previous messageGo to next message
ivojames
Messages: 7
Registered: December 2013
Location: Dallas
Junior Member
The alerts are generated from ORACLE alerts in the trace file
Re: Oracle alerts [message #603953 is a reply to message #603951] Fri, 20 December 2013 12:03 Go to previous messageGo to next message
BlackSwan
Messages: 21938
Registered: January 2009
Senior Member
>So, how are these alerts getting logged into the log file?
ORA-00060 errors are written to alert log file as default DB behavior.
I don't know how to disable this behavior or even if it is possible to do so.
Re: Oracle alerts [message #603956 is a reply to message #603953] Fri, 20 December 2013 12:24 Go to previous message
BlackSwan
Messages: 21938
Registered: January 2009
Senior Member
http://www.orafaq.com/wiki/ORA-00060
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:3932525800346405986

application must be modified to stop ORA-000060 from occurring.
Previous Topic: Query taking more time
Next Topic: Performance Issue
Goto Forum:
  


Current Time: Wed Apr 16 10:09:43 CDT 2014

Total time taken to generate the page: 0.12076 seconds