-
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
-
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!
-
I am not too pl/sql savvy, would it be possible to have a look and use the code pls?
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|