Oracle Database Discussions

MariaKarpa(MK) wrote:

Hi all,

11.2.0.3.11
aix6

Can you share me your script to monitor alert log  and email the dba when there is an "ORA-"  occurring in the alert log?


Thanks all,
mk

unwilling or incapable to use GOOGLE yourself?

http://www.orafaq.com/scripts/perl/trlogmon.txt

Thanks sol,

Will that script runs on AIX and other unix OS?

What if there is a hidden bomb in that script the removes all .dbf files in all the local folders

regards,

>Will that script runs on AIX and other unix OS?

PERL is OS independent; so it works on AIX, other *NIX, & even Windows!

>What if there is a hidden bomb in that script the removes all .dbf files in all the local folders

The nice thing about any script is that they are human readable so you can do code review & TEST prior to any deployment.

You can also map the alert_SID.log file as EXTERNAL TABLE & write the parser code in PL/SQL

Thanks Sol for the wonderful script.

How do I run the script in unix? and in windows?

Regards,

I found another script in google, courtesy of BURLESON CONSULTING

Checking the Alert Log

The check_alert_log.sh script shown below checks for any ORA- errors in the alert logof a given database.  It will take a SID as an argument, or if no SID is specified, it will use the current one if set.  In large environments, it may be more desirable to set up a GRID Controller and use that to monitor multiple databases. However, if only a handful of instances need to be monitored, it can be much easier to just set up monitoring scripts like this one.

In order to avoid reporting the same error twice, this script copies the contents out of the alert log, checks for errors and then saves them in a log file by the same name, but with .1 appended to it.

#!/bin/bash
# Add /usr/local/bin to the PATH variable so the oraenv command can be found
PATH=$PATH:/usr/local/bin; export PATH
# If a SID is provided as an argument it will be set and oraenv run
# otherwise we will use the current SID.  If no SID is set or provided
# an error message is displayed and the script exits with a status of 1
if [ $1 ]
then
    ORACLE_SID=$1
    ORAENV_ASK=NO
    . oraenv
else
    if [ ! $ORACLE_SID ]
    then
           echo "Error: No ORACLE_SID set or provided as an argument"
           exit 1
    fi
fi
# Set the ORACLE_BASE variable
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
cd $ORACLE_BASE/admin/$ORACLE_SID/bdump
# Copy the current alert log into a temporary file and empty the original
cp alert_$ORACLE_SID.log alert_$ORACLE_SID.log.temp
cp /dev/null alert_$ORACLE_SID.log
# Check the copy in the temporary file for ORA- errors
grep 'ORA-' alert_$ORACLE_SID.log.temp > /dev/null
# If found, email the Oracle user with the contents of the alert log
if [ $? = 0 ]
then
    mail -s "$ORACLE_SID database alert log error" oracle < \
           alert_$ORACLE_SID.log.temp
fi
# Move the contents of the temp file onto the permanent copy of the log
# and remove the temp file.
cat alert_$ORACLE_SID.log.temp >> alert_$ORACLE_SID.log.1
rm alert_$ORACLE_SID.log.temp

The block of code which starts with the first if statementis one that will be seen in many of the scripts in this book.  If a SID is provided, the $1 variable exists and the oraenv command is executed.   If not, the second if statement within the ELSE clause checks to see if a SID has already been set.  If it has not, an error message is printed and the script exited with a result code of 1, indicating failure.  If this script fails when executed from a crontab, the error message is automatically emailed to the crontab owner.

Since this will likely be run from a crontab entry, you typically provide a SID as an argument.  The following crontab entry causes this script to be run every 15 minutes.

00,15,30,45 * * * * /u01/app/oracle/admin/common/check_alert_log.sh oss

=============

Can you assess which one is better?

Thanks,

mk

>Can you assess which one is better?

which metric at what value measures better?

Accuracy, Dependability, Reliability, Clarity, User friendly, and can send SMS text

unchanged software performs the same.

Oracle does not know what SMS is.

Hi Sb,

Kind followup please on how to run your perl script, and schedule it on  cron?

Thanks,

mk

Maria Karpa, are you aware of the man facility in Unix?

If so, why do you continue to abuse this community?

Also, why you are requesting a script, when Oracle already has this in their database control?

In short, why are you challenging the tolerance of this community, to answer RTFM questions?

Are you 'yxes2013'?

Can you, PLEASE, PLEASE, PLEASE, STOP your ONGOING ABUSE of this community?

Sybrand Bakker

Senior Oracle DBA

sybrand_b wrote:
...
Are you 'yxes2013'?
...

Most likely, yes. And also petraK.

The day after the account yxes2013 was locked, petraK was created. After that one was locked again, MariaKarpa was created.

Still not convinced ? Have a search against the forum for PCIDSS https://community.oracle.com/search.jspa?q=PCIDSS&place=%2Fplaces%2F1254&depth=ALL&customTheme=otn

That terms is not very used, except by all those three accounts.

Nicolas.

You might want to consider querying the alert log with SQL. Look at v$diag_alert_ext, much easier than running shell scripts.

Yikes.

Ok, I will ignore this 'person'

And I hope everyone will do the same, to discourage this kind of abuse.

Sybrand Bakker

Senior Oracle DBA

MariaKarpa(MK) wrote:

I found another script in google, courtesy of BURLESON CONSULTING 

Well then, ask them to help you.  I'm sure they love people copying their scripts here, and other people pointing out any problems.

(that would be sarcasm, they once called me up on the phone and threatened to sue, and I'm not the only one.)

You might check out dbakevlar.com for some ideas on how to monitor with em.

Thanks ALL,

I think, I got the best script of them all.

Courtesy of UX forums by radoulov.

#!/bin/bash

ORACLE_HOME=<your_oracle_home>

ORACLE_SID=<your_oracle_sid>

ORAENV_ASK=NO

export ORACLE_HOME ORACLE_SID ORAENV_ASK

# you may need to adjust the path to oraenv

. "$ORACLE_HOME"/bin/oraenv

_mailto=<your_email_address>

_mail_subject_ok="No ORA- for $ORACLE_SID on $HOSTNAME"

_mail_subject_ko="ORA- for $ORACLE_SID on $HOSTNAME"

_today=$(

  date '+%Y-%m-%d 00:00:00'

  )

_yesterday=$(

  date -d yesterday +'%Y-%m-%d 00:00:00'

  )

_my_result=$(

  adrci exec="

    set home $ORACLE_SID;

    show alert -term -P \\\"MESSAGE_TEXT like '%ORA%' and ORIGINATING_TIMESTAMP between '$_yesterday' and '$_today'\\\"

   "

  )

[[ $_my_result == *ORA-* ]] &&

  _mail_subject=$_mail_subject_ko ||

    _mail_subject=$_mail_subject_ok

mailx -s "$_mail_subject" "$_mailto" <<< "$_my_result"

jgarry wrote:

You might check out dbakevlar.com for some ideas on how to monitor with em.

Agree with this. I use EM to monitor my Alert Log for errors. Plus it monitors tons of other things.

Cheers,
Brian

Which begs a question "What about errors that don't start with ORA-?"

Hi Jg,

So far, we only have  ORA- errors that are the primary show stoppers.

I have not remember others that caused issues to our prod database.

Thanks

>What if there is a hidden bomb in that script the removes all .dbf files in all the local folders

If that was intentionally, it's obvious what to do.

If it wasn't, tell him to THINK before you write scripts. I know that some people don't trust any script, because of the power they have. But, in that case, I think IT is not the correct business to be in.

If the above happens : restore the database from backup, fix the script.

What else do you expect ? And, have a good word with the writer of that script. Tell him not to do that again.

Well, you put a second GREP in the script that looks for other things. You know, it's not that hard

grep -i ora- alert.log

grep -i error alert.log

grep -i fatal alert.log

etc.

etc.

Variables' values like the actual ORACLE_HOME and such may never be hardcoded in scripts. If you ever change ORACLE_HOME, which is default action by upgrade because upgrades happen out-of-place, you'll need to fix all your scripts ...

It's a shame, because it's a variable already, what about treating it as one ?

Yes, and how do you query that table in an automated manner ?

Yeah, well they are not forced to put code on the net, are they ? You can put as many legal notice as you want, it'll be removed, the code changed (because of bugs or other improvements, adaptations, etc.) and then it's your code. Not my fault they gave it away on the net. It's like distributing paper on the sidewalk, then complaining somebody read it. Don't distribute it ..

Thanks TV,

Can you help me how to interpret the progra, or  what does this line of the script above is doing? Why does it not contain "if"?

[[ $_my_result == *ORA-* ]] &&

  _mail_subject=$_mail_subject_ko ||

    _mail_subject=$_mail_subject_ok

mailx -s "$_mail_subject" "$_mailto" <<< "$_my_result"

I am thinking it checks if _my_result has string like '%ORA-%'

But I can not understand the && + ||  and assigning "_mail_subject" to two different values??

Thanks,

It's written in such a way that it is technically correct, but hard to understand for the average code writer.

&& means : AND if the following is true

|| means : OR if the following is true

It sets the value to KO if the first check is true, it sets to OK if the first check is not true.

All of that can be written in a way that is both technically correct, AND being understood by the average code writer/reader.

It'll take more lines, more characters ... Then it'll include the IF command. Check out the TEST command (man test). That is actually the real command that is being used, the IF and [] are just leading to that command.

Thanks tv,

I only want it to email me if it is KO meaning it has found "ORA-%" in the alert log. But both conditions it will send email which is not good

Can I put "IF" such that:

IF [[ $_my_result == *ORA-* ]] &&

  _mail_subject=$_mail_subject_ko

THEN  mailx -s "$_mail_subject" "$_mailto" <<< "$_my_result"

FI

or can you revise it correctly for me?

Thanks,

It's hard when you don't know ahead of time what the errors will be, or worse, think that the only show-stoppers will be what you've already seen.

In other words, you sit there fat, dumb and happy, thinking everything is ok when it's not, because some script takes care of notifications.  The converse is being slammed by questionable hits, as can happen with EM.

To recevie email alerts with Enterprise Manager you need licensed Oracle Database management packs options,

like the Oracle Diagnostics Pack for each database. I have heard many use these packs without getting the licenses

but it is copyright software and can bring trouble to your company if Oracle decides to do an license review or audit.

Regards,

Kent D. Sorber, OCP

Thanks Jg,

As long as the script delivers,(meaning It can email me all the occurence of "ORA-" on an hourly check) I am very comfortable with that and be at peace of mind.