Results 1 to 3 of 3
  1. #1
    Join Date
    May 2003

    Unanswered: Need help with alert.log monitor script

    I am trying to create a SQL and PLSQL script to monitor the alert.log for errors. The goal is to check for ORA errors and send mail to dba on call. Here is what I have thus far:

    drop table alert_log;

    create global temporary table alert_log
    ( line int primary key,
    text varchar2(4000)
    on commit preserve rows

    create or replace procedure load_alert
    l_background_dump_dest v$parameter.value%type;
    l_filename varchar2(255);
    l_bfile bfile;
    l_last number;
    l_current number;
    l_start number := dbms_utility.get_time;
    select a.value, 'alert_' || b.instance || '.log'
    into l_background_dump_dest, l_filename
    from v$parameter a, v$thread b
    where = 'background_dump_dest';

    execute immediate
    'create or replace directory x$alert_log$x as
    ''' || l_background_dump_dest || '''';

    dbms_output.put_line( l_background_dump_dest );
    dbms_output.put_line( l_filename );

    delete from alert_log;

    l_bfile := bfilename( 'X$ALERT_LOG$X', l_filename );
    dbms_lob.fileopen( l_bfile );

    l_last := 1;
    for l_line in 1 .. 50000

    dbms_application_info.set_client_info( l_line || ', ' ||
    to_char(round((dbms_utility.get_time-l_start)/100, 2 ) )
    || ', '||
    l_current := dbms_lob.instr( l_bfile, '0A', l_last, 1 );
    exit when (nvl(l_current,0) = 0);

    insert into alert_log
    ( line, text )
    ( l_line,
    dbms_lob.substr( l_bfile, l_current-l_last+1,
    l_last ) )
    l_last := l_current+1;
    end loop;


    The goal is to store the Oracle errors from alert.log with datestamp and error that occurs and to mail the dba oncall the information for problem resolution. Can anyone help me out with this script?

  2. #2
    Join Date
    Aug 2003
    Where the Surf Meets the Turf @Del Mar, CA
    Provided Answers: 1
    >Can anyone help me out with this script?
    It appears you chose to ignore the advice in the #1 sticky note & have not visited

    Please consider that you are not the first to want such a capability.
    GOOGLE is your friend; but only if you use it!

    You're On Your Own (YOYO)!
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jun 2003
    Toronto, Canada
    Provided Answers: 1
    Using PL/SQL for this kind of stuff looks like an overkill. You could accomplish the same task with a shell one-liner, like this:
    tail -f alert$(ORACLE_SID).log | grep "ORA-" | while read A ; do (tail -50 alert$(ORACLE_SID).log | mail -s "ALERT: $A"; done
    Launch it in the background at system startup and wait for the messages to come.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts