1. compile the package

   SQL> @that_darn_alert_log
   

2. set serveroutput on

   SQL> set serveroutput on

3. initial setup of directory object

   SQL> exec that_darn_alert_log.bdump_dir_setup

4. run to check the alert log for current date

   SQL> exec that_darn_alert_log.read_alertlog

Full Script:

CREATE OR REPLACE PACKAGE 
          that_darn_alert_log 
	  AS

 PROCEDURE bdump_dir_setup;
 PROCEDURE build_possible_alert_log_names;
 PROCEDURE check_alertlog_name;
 PROCEDURE read_alertlog;

END that_darn_alert_log;
/
CREATE OR REPLACE PACKAGE BODY 
          that_darn_alert_log 
	  AS

bdump_dir   VARCHAR2(500);
alertlist   VARCHAR2(4000);
sid         VARCHAR2(100);
comma       VARCHAR2(1);       
alertfile   VARCHAR2(255);
itexists    NUMBER;
fileat      BFILE;
pointa      INTEGER;
pointb      INTEGER;
v_length    INTEGER;
r_char10    RAW(4);
char10      VARCHAR2(1) := CHR(10);
startdate   VARCHAR2(100);
r_startdate RAW(100);
buffer      VARCHAR2(800);
buffer2     VARCHAR2(800);

PROCEDURE bdump_dir_setup IS
 BEGIN
  EXECUTE IMMEDIATE 
  'SELECT value '||
  '  FROM v$parameter '||
  ' WHERE name = ''background_dump_dest''' 
     INTO bdump_dir;
  EXECUTE IMMEDIATE 
  'CREATE DIRECTORY BDUMP_DIR '||
  '    AS '''||bdump_dir||'''';
END bdump_dir_setup;

PROCEDURE check_alertlog_name IS
BEGIN
 pointa    := 1;
 LOOP
  pointb   := INSTR(alertlist,
                    ',',
                    pointa,1);
  v_length := pointb - pointa;
  alertfile:= SUBSTR(alertlist,
                     pointa,
                     v_length);
  pointa   := pointb + 1;
  itexists := DBMS_LOB.FILEEXISTS(
              BFILENAME('BDUMP_DIR',alertfile));
  IF itexists = 1 THEN
   pointb  := 0;
   EXIT;
  END IF;
 END LOOP;
END check_alertlog_name;

 
PROCEDURE build_possible_alert_log_names IS
BEGIN
 alertlist := alertlist||
                        comma||
                        'alert_'||
                        lower(sid)||
                        '.log';
 comma     := ',';
 alertlist := alertlist||
                        comma||
                        upper(sid)||
                        'ALRT.LOG';
END build_possible_alert_log_names;

PROCEDURE read_alertlog IS

BEGIN
 DBMS_OUTPUT.ENABLE(1000000);
 alertlist := NULL;
 comma     := NULL;
 EXECUTE IMMEDIATE 
 'SELECT value '||
 '  FROM v$parameter '||
 ' WHERE name = ''db_name'''                      
    INTO sid;
 build_possible_alert_log_names;
 EXECUTE IMMEDIATE 
 'SELECT value '||
 '  FROM v$parameter '||
 ' WHERE name = ''instance_name'''
    INTO sid;
 build_possible_alert_log_names;
 EXECUTE IMMEDIATE 
 'SELECT substr(global_name,1,'||
 '        instr(global_name,''.'',-1)-1) '||
  ' FROM global_name' 
    INTO sid;
 build_possible_alert_log_names;

 r_char10    := UTL_RAW.CAST_TO_RAW(char10);

 check_alertlog_name;
 fileat       := BFILENAME('BDUMP_DIR',alertfile);
 DBMS_LOB.FILEOPEN(fileat,DBMS_LOB.FILE_READONLY);

 startdate  := TO_CHAR(SYSDATE,'Dy Mon DD');
 r_startdate  := UTL_RAW.CAST_TO_RAW(startdate);
 pointa := DBMS_LOB.INSTR(fileat,r_startdate,1,1);

 IF pointa != 0 THEN
  LOOP
   pointb  := DBMS_LOB.INSTR(fileat,r_char10,pointa,1);
   EXIT WHEN pointb = 0;
   v_length  := pointb - pointa;
   buffer  := DBMS_LOB.SUBSTR(fileat,v_length,pointa);
   buffer2 := utl_raw.cast_to_varchar2(buffer);
   DBMS_OUTPUT.PUT_LINE(buffer2);
   pointa  := pointb + 1;
  END LOOP;
 END IF;
 DBMS_LOB.FILECLOSE(fileat);

END read_alertlog;

BEGIN
  DBMS_OUTPUT.ENABLE(1000000);
END that_darn_alert_log;
/

The Oracle alert log contains loads of information. Even so, Oracle has yet to put in place an easy method for notifying DBAs when errors and important messages occur. On the flip side, even the best DBA with the best intentions will on occasion forget to view this log from time to. Do yourself a favor and put something in place to notify yourself so that you can avert potential database failure. I have given you one method to do just that. Please modify to your hearts content and let me know how it works.

Page 1

Back to DBAsupport.com