Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Jan 23, 2003

That Darn Alert Log - Page 2

By James Koopmann

How to Use

  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.

» See All Articles by Columnist James Koopmann



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM


















Thanks for your registration, follow us on our social networks to keep up-to-date