Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 
 

Oracle alert log error e-mail

Oracle Tips by Burleson Consulting

 

Its important to monitor the Oracle alert log and send alerts when serious errors are logged into the alert log.  Also note that you must manage multiple alert logs in RAC.

It can take weeks to write a comprehensive set of intelligent scripts to scan the alert logs for important massages.  If you don't want to write your own custom scripts, see my Oracle script download.

There are many approaches to detecting alert log messages and sending e-mail alerts:

  • Ion for Oracle - The Ion tool has complete mechanisms for setting custom alert log extracts.
  • Scripts - Write OS shell scripts to detect alert log messages.
  • PL/SQL - You can write a SQL*Plus procedure using utl_file to read the alert log.
  • In 11g and beyond, you can directly query the alert log with SQL, using x$dbgalertext.

 Also see these notes on reading the alert log file and sending alert messages:

Also see my notes on using the dbms_server_alert package.

Sample alert log monitoring scripts

Laurent Baylac has this example script to use utl_file to scan for alert log messages:

set head     on
set echo     off
set verify   off
set feedback off
set pagesize 5000
set tab      off

 
rem ---
rem alerts.sql
rem 
rem Comments:    Show errors in alert.log from the last n hours.
rem              It works for both UNIX and Windows databases.
rem             For Oracle 7.3 and higher.
rem              Check the format of the alert log file before.
rem
rem Required:    * utl_file_dir must contain background_dump_dest
rem              * background_dump_dest parameter must be a complete directory
rem                name, in the form '?/rdbms/log' or '%RDBMS73%' etc
rem 
rem ---

set serverout on

exec dbms_output.enable( 1000000) 

DECLARE
   v_sid v$database.name%TYPE; /* database */
   v_bdumpdest v$parameter.value%TYPE; /* directory of alert.log */
   v_count NUMBER;
   v_alertname VARCHAR2( 19); /* name of the alert.log */
   v_file_handle utl_file.file_type; /* handle tp alert.log */
   v_eof BOOLEAN := FALSE;
   v_text VARCHAR2( 2000);
   v_date_try DATE; /* Help var. for date line in de alert.log */
   v_date DATE; /* Keep de date line from alert.log */
   v_date_is_new BOOLEAN; /* Datum to be displayed */
   v_interesting BOOLEAN := FALSE; /* Relevant content from the alert.log */
   c_aantal_uren NUMBER := &1; /* Total hours from the time the contents have to be displayed */ 
   FUNCTION file_does_exist
   ( location_in IN VARCHAR2
   , filename_in IN VARCHAR2
   ) 
  RETURN BOOLEAN AS
      v_file_handle utl_file.file_type;

  BEGIN
      v_file_handle := utl_file.fopen
                       ( location => location_in
                       , filename => filename_in
                       , open_mode => 'R'
                       );
      utl_file.fclose( file => v_file_handle);
      RETURN TRUE;
   EXCEPTION
      WHEN OTHERS THEN
         RETURN FALSE;
   END file_does_exist;

BEGIN

   SELECT name
   INTO v_sid
   FROM v$database
   ;

   SELECT value
   INTO v_bdumpdest
   FROM v$parameter
   WHERE name = 'background_dump_dest'
   ;

   SELECT COUNT(*)
   INTO v_count
   FROM v$version
   WHERE banner LIKE '%Windows%'
   ;

   IF v_count = 0 THEN
      /*
      || This is not a Windows machine. The name of the alert log is alert_SID.log
      || sometimes alert_sid.log
      */


      IF file_does_exist
      ( location_in => v_bdumpdest
      , filename_in => 'alert_' || v_sid || '.log'
      )
      THEN
         v_alertname := 'alert_' || v_sid || '.log';
      ELSE
         v_alertname := 'alert_' || LOWER( v_sid) || '.log';
      END IF;
   ELSE
      /* Windows machine */      v_alertname := v_sid || 'ALRT.LOG';
   END IF;

   BEGIN
      v_file_handle := utl_file.fopen
                       ( location => v_bdumpdest
                       , filename => v_alertname
                       , open_mode => 'R'
                       );

   EXCEPTION
      WHEN utl_file.invalid_mode THEN
         RAISE_APPLICATION_ERROR
         ( -20001, 'the open_mode string was invalid')
   END;

   WHILE NOT v_eof
   LOOP
      BEGIN
         utl_file.get_line
         ( file => v_file_handle
         , buffer => v_text
         );
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            v_eof := TRUE;
         WHEN value_error THEN
            utl_file.fclose_all;
            RAISE_APPLICATION_ERROR
            ( -20001, 'line too long to store in buffer');
         WHEN utl_file.invalid_operation THEN
            utl_file.fclose_all;
            RAISE_APPLICATION_ERROR
            ( -20001, 'file is not open for reading');
         WHEN utl_file.read_error THEN
            utl_file.fclose_all;
            RAISE_APPLICATION_ERROR
            ( -20001, 'OS error occurred during read');
      END;

      IF v_interesting THEN
         IF SUBSTR( v_text, 4, 1) = '-' /* 'ORA-..., PLS-..., etc. */
         THEN
            IF v_date_is_new THEN
               dbms_output.put_line( CHR( 10));
               dbms_output.put_line
               ( INITCAP( TO_CHAR( v_date, 'DY MON DD HH24:MI:SS YYYY')));
            END IF;
            v_date_is_new := FALSE;
            dbms_output.put_line( SUBSTR( v_text, 1, 80));
         END IF;
      END IF;

      BEGIN
         v_date_try := TO_DATE( v_text, 'DY MON DD HH24:MI:SS YYYY');
         IF v_date != v_date_try OR v_date IS NULL
         THEN
            v_date := v_date_try;
            v_date_is_new := TRUE;
            IF v_date > SYSDATE - ( c_aantal_uren / 24) THEN
               v_interesting := TRUE;
            END IF;
         ELSE
            v_date_is_new := FALSE;
         END IF;
      EXCEPTION
         WHEN OTHERS THEN
            NULL; /* There is no date. The file is read further. */
      END;
   END LOOP;

   utl_file.fclose( file => v_file_handle);

EXCEPTION
   WHEN utl_file.invalid_path THEN
      dbms_output.put_line
      ( 'alerts.sql: utl_file.invalid_path encountered. ' ||
        'Check utl_file_dir.'
      );
   WHEN utl_file.invalid_operation THEN
      IF v_bdumpdest LIKE '%$%'
      OR v_bdumpdest LIKE '%?%'
      OR v_bdumpdest LIKE '%\%%' ESCAPE '\'
      THEN
         dbms_output.put_line
         ( 'alerts.sql: Unable to read alert.log because ' ||
           'of background_dump_dest parameter ' || 
           v_bdumpdest || '.'
         );
      ELSE
         dbms_output.put_line
         ( 'alerts.sql: utl_file.invalid_operation encountered.');
      END IF;
END;
/
 
Get the Complete
Oracle SQL Tuning Information 

The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.

 


 

 

��  
 
 
 
 

 
 
 

 
 
Oracle performance tuning software 
 
oracle dba poster
Oracle Linux poster
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright ? 1996 -  2014

All rights reserved by Burleson

Oracle ? is the registered trademark of Oracle Corporation.