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 :
echo "\n"
echo "ORA - Error"
echo "~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~\n"
sed -n "/ORA-/p" "alert log fiel path"
sleep 4

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

  • You could do a logrotate everyday :). Otherwise you need to parse the day and get the first line, start from there.
    – Munchi
    Dec 6 '13 at 12:48
  • If you must go this route, after first scan, you could save the last error reported location (line, or characters from beginning etc) into another file. Next scan, you start after that point in the alert log and scan from that point onwards. lather. rinse. repeat.
    – Raj
    Dec 10 '13 at 20:37
  • @Raj, rather than try to remember where you are, you can rotate the alert log by moving it to a different location in the OS. Then you have a small alert log since the last alert, and you have a comprehensive alert log history.
    – Adam Musch
    Dec 23 '13 at 15:57

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
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


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.


If you are using Oracle 11g or later, consider using the ADRCI utility instead.

  • How ADRCI will help in 'monitoring' alert log files? just curious, especially OP seems to be interested in continuous monitoring it seems.
    – Raj
    Dec 11 '13 at 19:15
  • It's better suited than sed for filtering by date.
    – Twinkles
    Dec 12 '13 at 5:02
  • A command line example perhaps would help clarify.
    – Raj
    Dec 12 '13 at 13:49
  • 1
    @Raj: A simple command that shows all errors (in alertlog file) which occured today, using ADRCI, is: adrci> show alert -p "message_text like '%ORA-%' and originating_timestamp > systimestamp - 1"
    – Shaunyl
    Dec 24 '13 at 20:18

You can use this script to monitor the alertlog for all databases and listeners running on the server: http://dba-tips.blogspot.ae/2014/02/database-monitoring-script-for-ora-and.html

This script will send you an email whenever any error appear in the alertlogs for all database, it is also well coded to send you only the new errors without replicate the same error message in the next run.

  • You should make it clear if you are associated with the website you link to.
    – Hannah Vernon
    Feb 18 '14 at 21:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.