Here are detailed instructions on how to configure your database in order to get every 'ORA-'-error from the alert.log to your email address or mobile phone as an SMS message. I have tested all that with 8i and 9i databases and it works fine. Probably my solution is not the optimal one, so all suggestions for improvement are more than wellcome.
Step 1. If JVM (Java Virtual Machine) has been already installed go to Step 2.
If not, then we consider the two cases: 8i and 9i. If you run 8i, then as internal run (d:\oracle\ora81 is my Oracle home directory, you will have to replace that with yours):
@d:\oracle\ora81\javavm\install\initjvm.sql
Then at OS level run:
loadjava -force -verbose -user sys/password@db d:\oracle\ora81\plsql\jlib\plsql.jar
Now, again log as internal and run:
@d:\oracle\ora81\rdbms\admin\initplsj.sql
In 9i, run only whatever\javavm\install\initjvm.sql!
Step 2. Create the following procedure which sends email. Replace '10.5.7.276' with the IP address of your mail host.
Step 3. Let's test if it works. This is a moment where you might face an error due to either improper intstallation of JVM or some problems with the mailhost.Code:PROCEDURE send_email (P_SENDER in varchar2,
P_RECIPIENT in varchar2,
P_SUBJECT in varchar2,
P_MESSAGE in varchar2)
is
mailhost varchar2(30) := '10.5.7.276';
mail_conn utl_smtp.connection;
crlf varchar2(2):= CHR(13)||CHR(10);
mesg varchar2(4000);
BEGIN
mail_conn := utl_smtp.open_connection(mailhost,25);
mesg:= 'Date: '||to_char(sysdate,'dd Mon yy hh24:mi:ss' )||crlf
||'FROM: '||P_SENDER||' >'||crlf||'Subject: '||P_SUBJECT||crlf ||'To: '||P_RECIPIENT
||crlf||''|| crlf ||P_MESSAGE;
utl_smtp.helo(mail_conn,mailhost);
utl_smtp.mail(mail_conn,P_SENDER);
utl_smtp.rcpt(mail_conn,P_RECIPIENT);
utl_smtp.data(mail_conn,mesg);
utl_smtp.quit(mail_conn);
END send_email;
Connect to SQL*Plus as sys (or user with the DBA role for example) and run (replace your own email address):
exec send_email('< [email protected] >','< [email protected] >','TEST','Hello World!');
Step 4. In init.ora, set UTL_FILE_DIR to the directory where alert.log resides, set JOB_QUEUE_PROCESSESS to a some value (depending on how many jobs you have, often 10 is OK) and set JOB_QUEUE_INTERVAL to 60. Now, bounce the instance.
Note that in 9i, JOB_QUEUE_INTERVAL is obsolete!
Step 5. We will use a table called alert_historia, where the rows of the alert.log will be stored. I decided to implement that in order to know which line from the alert.log was last read. We use Patrol, which has the bug that it reads the alert.log always from the beginning. Well, we don't want the same ORA- line sent to us all the time until we delete the alert.log, do we :-)
Run in SQL*Plus:
Code:CREATE SEQUENCE alert_seq
INCREMENT BY 1
START WITH 1
MINVALUE 1
MAXVALUE 9999999999999
NOCYCLE
NOORDER
CACHE 20
/
Step 6. Create the following procedure which will read the alert.log. ReplaceCode:CREATE TABLE alert_historia
(
lid NUMBER NOT NULL,
alert_rivi VARCHAR2(256),
dtpvm DATE NOT NULL,
remark VARCHAR2(100)
)
/
ALTER TABLE alert_historia
ADD CONSTRAINT pk_alert PRIMARY KEY (lid)
/
'c:\oracle9i\admin\JM9\bdump' with you BDUMP directory.
Step 6. Decide on how often you want Oracle to wake up to check for errors in the alert.log. For every 90 seconds run in SQL*Plus:Code:PROCEDURE read_file(P_NAME in varchar2) AS
id UTL_FILE.FILE_TYPE;
name VARCHAR2(20);
err VARCHAR2(100);
num NUMBER;
max_lid NUMBER;
filedata VARCHAR2(2000);
w_filedata VARCHAR2(2000);
k PLS_INTEGER := 0;
BEGIN
name := P_NAME;
select count(*) into MAX_LID from ALERT_HISTORIA;
id := UTL_FILE.FOPEN('c:\oracle9i\admin\JM9\bdump',name,'r');
LOOP
BEGIN
UTL_FILE.GET_LINE(id,filedata); k := k+1;
if k > max_lid then
insert into alert_historia values(alert_seq.nextval,filedata,sysdate,null);
commit work;
if instr(filedata,'ORA-') > 0 then
send_email('< [email protected] >','< [email protected] >','JD9 ERROR',filedata);
-- This line is meant for SMS messages. Anything sent to [email protected] will
-- be forwared to my mobile phone. Almost all operators in the world offer such
-- email address to their clients:
-- send_email('< [email protected] >','< [email protected] >','JD9 error:',filedata);
end if;
end if;
-- DBMS_OUTPUT.PUT_LINE(filedata);
EXCEPTION
WHEN NO_DATA_FOUND THEN EXIT;
END;
END LOOP;
UTL_FILE.FCLOSE(id);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('NO DATA FOUND');
UTL_FILE.FCLOSE(ID);
WHEN UTL_FILE.INVALID_PATH THEN
DBMS_OUTPUT.PUT_LINE('UTL_FILE.INVALID_PATH');
UTL_FILE.FCLOSE(ID);
WHEN UTL_FILE.READ_ERROR THEN
DBMS_OUTPUT.PUT_LINE('UTL_FILE.READ_ERROR');
UTL_FILE.FCLOSE(ID);
WHEN UTL_FILE.WRITE_ERROR THEN
DBMS_OUTPUT.PUT_LINE('UTL_FILE.WRITE_ERROR');
UTL_FILE.FCLOSE(ID);
WHEN OTHERS THEN
err := SQLERRM;
num := SQLCODE;
DBMS_OUTPUT.PUT_LINE(err);
DBMS_OUTPUT.PUT_LINE(num);
DBMS_OUTPUT.PUT_LINE('Error trying to read file');
END;
You can verify the job with select * from dba_jobs;Code:variable jobno number;
begin
dbms_job.submit(:jobno,'read_file(''ALERT.LOG'');',sysdate,'sysdate+(90/(24*60*60))');
COMMIT;
end;
/
Note: Replace above ALERT.LOG with the name of your alert.log. It might not be ALERT.LOG.
Done. After 90 seconds you should have in ALERT_HISTORIA the rows from the alert.log:
You should also get all errors from the alert.log via email or straight to you mobile phone at that moment. If you delete your alert.log, you should truncate alert_historia at the same moment, remember this!Code:SQL> col alert_rivi for A77
SQL> col remark for A30
SQL> select * from alert_historia;
Final remark: In the procedure read_file, there is a commented line meant for SMS messages. If you want to use SMS, not email, then comment out the previous email line and use the SMS line. In that procedure, anything sent to [email protected] will be forwared to my mobile phone. As almost all operators in the world offer such email address to their clients, you should register that service with your GSM operator. In Finland, I did that in Internet in a couple of minutes. I just had to type in my GSM number, a got back an activation code as an SMS message, then I entered that code and chose my email address at that operator. Usually, it is
by default [email protected], where 123456789 is your GSM number.
Comments and complains to [email protected] :-)
Thanks!
Julian
[Edited by julian on 05-14-2002 at 03:59 AM]
