Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
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
add comment

3 Answers

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.

share|improve this answer
add comment

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

share|improve this answer
    
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
add comment

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.

share|improve this answer
    
You should make it clear if you are associated with the website you link to. –  Max Vernon Feb 18 at 21:53
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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