OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » Oracle alerts (11.2.0.3)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Oracle alerts [message #603822]

Thu, 19 December 2013 10:54

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?

Report message to a moderator

Re: Oracle alerts [message #603825 is a reply to message #603822]

Thu, 19 December 2013 11:04

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

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/

Report message to a moderator

Re: Oracle alerts [message #603828 is a reply to message #603822]

Thu, 19 December 2013 11:52

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

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.

Report message to a moderator

Re: Oracle alerts [message #603840 is a reply to message #603825]

Thu, 19 December 2013 13:15

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

Report message to a moderator

Re: Oracle alerts [message #603842 is a reply to message #603828]

Thu, 19 December 2013 13:28

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

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.

Report message to a moderator

Re: Oracle alerts [message #603847 is a reply to message #603842]

Thu, 19 December 2013 17:25

cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester

Senior Member

alert file will list deadlocks, shouldn't display the other two.

Report message to a moderator

Re: Oracle alerts [message #603939 is a reply to message #603842]

Fri, 20 December 2013 09:35

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

Report message to a moderator

Re: Oracle alerts [message #603941 is a reply to message #603847]

Fri, 20 December 2013 09:36

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

Report message to a moderator

Re: Oracle alerts [message #603942 is a reply to message #603941]

Fri, 20 December 2013 10:16

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

Report message to a moderator

Re: Oracle alerts [message #603945 is a reply to message #603942]

Fri, 20 December 2013 10:24

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

to avoid deadlock message then do as below

grep -v -i deadlock $logdir/alert_email_$ORACLE_SID.txt

Report message to a moderator

Re: Oracle alerts [message #603950 is a reply to message #603945]

Fri, 20 December 2013 11:11

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

Report message to a moderator

Re: Oracle alerts [message #603951 is a reply to message #603950]

Fri, 20 December 2013 11:56

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

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?

Report message to a moderator

Re: Oracle alerts [message #603952 is a reply to message #603951]

Fri, 20 December 2013 12:01

ivojames
Messages: 7
Registered: December 2013
Location: Dallas

Junior Member

The alerts are generated from ORACLE alerts in the trace file

Report message to a moderator

Re: Oracle alerts [message #603953 is a reply to message #603951]

Fri, 20 December 2013 12:03

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

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.

Report message to a moderator

Re: Oracle alerts [message #603956 is a reply to message #603953]

Fri, 20 December 2013 12:24

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

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.

Report message to a moderator

Previous Topic:	Query taking more time
Next Topic:	Performance Issue

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Nov 23 21:21:12 CST 2024