Monitoring of alert log file

alertsoracle

I have to generate automated script for alert log file, which will display the contents of alert log file. Here is the script for that.

while :
clear
do
echo "\n"
echo "ORA - Error"
echo "~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~\n"
sed -n "/ORA-/p" "alert log fiel path"
sleep 4
clear

My issue is that I am getting all the errors. I only want errors which occured today. How can I do this?

Best Answer

1) If you are using Oracle 11g or later:

Starting from 11g Oracle, was been introduced a fixed table, X$DBGALERTEXT, from which you can retrieve all the data what alert.log does. Here is a simple query that retrieves all errors which occured today, like you want.

SET linesize 200 pagesize 200
col RECORD_ID FOR 9999999 head ID
col MESSAGE_TEXT FOR a120 head Message

SELECT record_id, message_text
FROM X$DBGALERTEXT
WHERE originating_timestamp > systimestamp - 1 AND regexp_like(message_text, '(ORA-|error)')

As Twinkles mentioned in his answer, you can take advantage of ADRCI utility to select alert filters that you need (in fact, this utility queries against the X$DBGALERTEXT table). For example, you can build a simple function and call it from OS prompt:

vealert() {
  adrci exec="set home diag\/rdbms\/$DB_UNIQUE_NAME/$ORACLE_SID\; \
   show alert -p \\\"message_text like '%ORA-%' \
     and originating_timestamp > systimestamp - $1\\\""
}

in file .bashrc, for example:

export DB_UNIQUE_NAME=<unique_name>
. $HOME/bin/vealert

Then:

bash
vealert 1

..
Tue Dec 24 17:46:05 2013
ORA-28 signalled during: ALTER TABLESPACE DATA_APP_199903 READ ONLY...
..

2) Otherwise:

Go and have a look at this web page. There is a script that can be used to check any ORA- errors are there in the alertlog file for the present day.

To schedule this job, you can add a line in your cron table specifying the time you want the job to execute. Alternatively, you can take advantage of the Oracle Scheduler.

Related Question