Alert script for Oracle alert log on Windows
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Alert script for Oracle alert log on Windows

  1. #1
    Join Date
    Dec 2001
    Posts
    337

    Alert script for Oracle alert log on Windows

    Hi all,

    Can anyone point me in the direction of a VBScript alert log monitoring script in a Windows envirmonent? Have done a search and seen mostly alert script which email errors in the alert log on a Unix env, would like something similar for a Windows env!

    Thanks in advance,
    Chucks

  2. #2
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    I use DBMS_JOB to run a package every 5 mins to check the alert log. This uses UTL_FILE to read the alert log and UTL_SMTP to email me.

    And it works too!

  3. #3
    Join Date
    Dec 2001
    Posts
    337
    I am not too pl/sql savvy, would it be possible to have a look and use the code pls?

  4. #4
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    The procedure to send the mail is:

    PROCEDURE SEND_MAIL IS
    (p_recipient IN VARCHAR2,
    p_sender IN VARCHAR2,
    p_sender_text IN VARCHAR2, -- Appear in e-mail as 'From:'
    p_subject IN VARCHAR2,
    p_message IN VARCHAR2)
    AS
    c utl_smtp.connection;
    v_msg_string VARCHAR2(4000);
    --
    BEGIN
    c := utl_smtp.open_connection('*** name of SMTP server ***');
    utl_smtp.helo(c, '*** name of SMTP server ***');
    utl_smtp.mail(c, p_sender);
    utl_smtp.rcpt(c, p_recipient);
    utl_smtp.open_data(c);
    utl_smtp.write_data(c, 'From' || ': ' || p_sender_text || utl_tcp.CRLF);
    utl_smtp.write_data(c, 'To' || ': ' || p_recipient || utl_tcp.CRLF);
    utl_smtp.write_data(c, 'Subject' || ': ' || p_subject|| utl_tcp.CRLF);
    utl_smtp.write_data(c, utl_tcp.CRLF || p_message);
    utl_smtp.close_data(c);
    utl_smtp.quit(c);
    --
    EXCEPTION
    --
    WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
    BEGIN
    utl_smtp.quit(c);
    EXCEPTION
    WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
    NULL; -- When the SMTP server is down or unavailable, we don't have
    -- a connection to the server. The quit call will raise an
    -- exception that we can ignore.
    END;
    RAISE_APPLICATION_ERROR(-20000,
    'Failed to send mail due to the following error: ' || SQLERRM);
    END;

    This is called by the Check Alert log procedure:

    IS
    mail_msg VARCHAR2(150);
    line_text VARCHAR2(250);
    var_count NUMBER := 0;
    alert_file UTL_FILE.file_type;
    alert_file_newname VARCHAR2(100);
    BEGIN
    alert_file := UTL_FILE.fopen('ALERT_LOG_DIR',**alert log file name','R');
    IF
    UTL_FILE.is_open(alert_file) THEN
    LOOP
    EXIT WHEN var_count > 5;
    --
    UTL_FILE.get_line(alert_file, line_text);
    --
    --
    -- This check is ONLY for 'ORA-' errors ...
    --
    IF
    instr(line_text,'ORA-') > 0 THEN
    var_count := var_count + 1;
    mail_msg := 'The following error has been detected in the Alert Log:' ||CHR(10)||line_text;
    SEND_MAIL('****recipient****','Null','xxxxx','xxxxxxx',
    TO_NUMBER(var_count)||' - '||TO_CHAR(SYSDATE,'DD-Mon-YY HH24:MI')||' - '||mail_msg );
    END IF;
    --
    END LOOP;
    UTL_FILE.fclose(alert_file);
    END IF;
    --
    --
    EXCEPTION
    WHEN utl_file.invalid_mode THEN
    RAISE_APPLICATION_ERROR (-20051, 'Invalid Mode Parameter');
    WHEN utl_file.invalid_path THEN
    RAISE_APPLICATION_ERROR (-20052, 'Invalid File Location');
    WHEN utl_file.invalid_filehandle THEN
    RAISE_APPLICATION_ERROR (-20053, 'Invalid Filehandle');
    WHEN utl_file.invalid_operation THEN
    RAISE_APPLICATION_ERROR (-20054, 'Invalid Operation');
    WHEN utl_file.read_error THEN
    RAISE_APPLICATION_ERROR (-20055, 'Read Error');
    WHEN utl_file.internal_error THEN
    RAISE_APPLICATION_ERROR (-20057, 'Internal Error');
    WHEN utl_file.charsetmismatch THEN
    RAISE_APPLICATION_ERROR (-20058, 'Opened With FOPEN_NCHAR
    But Later I/O Inconsistent');
    WHEN utl_file.file_open THEN
    RAISE_APPLICATION_ERROR (-20059, 'File Already Opened');
    WHEN utl_file.invalid_maxlinesize THEN
    RAISE_APPLICATION_ERROR(-20060,'Line Size Exceeds 32K');
    WHEN utl_file.invalid_filename THEN
    RAISE_APPLICATION_ERROR (-20061, 'Invalid File Name');
    WHEN utl_file.access_denied THEN
    RAISE_APPLICATION_ERROR (-20062, 'File Access Denied By');
    WHEN utl_file.invalid_offset THEN
    RAISE_APPLICATION_ERROR (-20063,'FSEEK Param Less Than 0');
    --
    -- No data found can be raised when the GET_LINE action reaches EOF...
    --
    WHEN no_data_found THEN
    NULL;
    WHEN others THEN
    RAISE_APPLICATION_ERROR (-20099, 'Unknown UTL_FILE Error'|| SQLERRM);
    END;

    Make sure you have ALERT_LOG_DIR already created as an External Directory, so that UTL_FILE can find the alert log

    As you can see I'm no PLSQL guru myself. It only checks for the first 4 ORA_errors as you may get your mail box filled in your absence!

Posting Permissions

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



Click Here to Expand Forum to Full Width