Instructions on how to get the Oracle errors from the alert.log as email or SMS
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 19

Thread: Instructions on how to get the Oracle errors from the alert.log as email or SMS

Hybrid View

  1. #1
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    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.

    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;
    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.

    Connect to SQL*Plus as sys (or user with the DBA role for example) and run (replace your own email address):

    exec send_email('< julian@domain.com >','< julian@domain.com >','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
    /
    Code:
    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)
    /
    Step 6. Create the following procedure which will read the alert.log. Replace
    'c:\oracle9i\admin\JM9\bdump' with you BDUMP directory.

    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('< julian@domain.com >','< julian@domain.com >','JD9 ERROR',filedata);
      -- This line is meant for SMS messages. Anything sent to julian@GSM.net will
      -- be forwared to my mobile phone. Almost all operators in the world offer such
      -- email address to their clients:
      --      send_email('< julian@domain.com >','< julian@GSM.net >','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;
    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:
    variable jobno number; 
    begin 
    dbms_job.submit(:jobno,'read_file(''ALERT.LOG'');',sysdate,'sysdate+(90/(24*60*60))'); 
    COMMIT; 
    end; 
    /
    You can verify the job with select * from dba_jobs;

    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:

    Code:
    SQL> col alert_rivi for A77
    SQL> col remark for A30
    SQL> select * from alert_historia;
    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!

    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 julian@GSM.net 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 123456789@operator.com, where 123456789 is your GSM number.

    Comments and complains to ocp_9i@yahoo.com :-)

    Thanks!
    Julian

    [Edited by julian on 05-14-2002 at 03:59 AM]
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  2. #2
    Join Date
    Jan 2002
    Location
    Netherlands
    Posts
    1,587
    Very well written, a good candidate for the HOW TO forum.
    Tarry Singh
    I'm a JOLE(JavaOracleLinuxEnthusiast)
    TarryBlogging
    --- Everything was meant to be---

  3. #3
    Join Date
    Feb 2002
    Location
    Dallas , Texas
    Posts
    158
    Excellent !!!

    It was expected from you.

    Take Care.

  4. #4
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    Nice article.

    If you are using Oracle Enterprise Manager (OEM) this is all built in.

    Register the "Alert" event against your database(s) and the OMS will be notified of any new errors in the alert log.

    If you want these errors to be sent to you via email or a pager simply add your SMTP server details to the "Configure Paging/Email" dialog:

    Configuration -> Configure Paging/Email

    You can adjust the content of the mail by updating the settings in the following dialog:

    Configuration -> Preferences -> Notification Tab

    I always use this and I've had no problems so far.

    Cheers

    Tim...
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    other option is stored your alert file in database and run query against it

  6. #6
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    I've used DIY$ views to query the alert log before:

    http://www.oracle-base.com/Articles/...namicViews.asp

    I was unaware there was an option to have it written directly to the database. How do you do it? Or are you just loading it in after it's been written?

    Cheers
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Code:
    create global temporary table alert_log
    ( line   int primary key,
      text   varchar2(4000)
    )
    on commit preserve rows
    /
    
    create or replace procedure load_alert
    as
        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;
    begin
        select a.value, 'alert_' || b.instance || '.log'
          into l_background_dump_dest, l_filename
          from v$parameter a, v$thread b
         where a.name = '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
        loop
    
            dbms_application_info.set_client_info( l_line || ', ' ||
            to_char(round((dbms_utility.get_time-l_start)/100, 2 ) ) 
            || ', '||
            to_char((dbms_utility.get_time-l_start)/l_line)
            );
            l_current := dbms_lob.instr( l_bfile, '0A', l_last, 1 );
            exit when (nvl(l_current,0) = 0);
    
            insert into alert_log
            ( line, text )
            values
            ( l_line, 
              utl_raw.cast_to_varchar2( 
                  dbms_lob.substr( l_bfile, l_current-l_last+1, 
                                                        l_last ) )
            );
            l_last := l_current+1;
        end loop;
    
        dbms_lob.fileclose(l_bfile);
    end;
    /
    loading it, not writing it

    courtesy from Tom Kyte

  8. #8
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    Right! Can't argue with Tom
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  9. #9
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Pando, ok, you store the file in the database. You read it. How do you know where from to start reading?
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  10. #10
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    select * from xxx where text like '%ORA-%' :-?

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