Oracle alert log miner

A little C program that extracts lines from the alert log for a specified date (or date range).

This is a little C program that extracts lines from the Oracle alert log for a specified date (or date range)....

The lines extracted depend on the entries in a 'pattern' file. The following is the syntax and an example of a pattern file and a sample command. I hope the source code isn't too mangled by being pasted in here.

Syntax:

getalerts [[-a] [-f afile] [-p pfile]
          [-b mmddyyyy] [-e mmddyyyy]]

Options:

-a          This option says to extract all the lines from the alert 
            log within the specified date range.

-f afile    Specifies the alert log file to be used. If this option 
            is omitted then input is assumed to be from stdin.

-p pfile    Specifies the pattern file to be used. Patterns are 
            entered one-per-line in this file. Patterns may be 
            'regular expressions'. There is a maximum number of 
            patterns allowed; currently, this number is 40.  The 
            maximum length of any particular pattern is 80 characters.
            If this option is omitted and the -a option is not 
            present then only lines containing the string "ORA-" 
            are extracted. If the -a option is present then the -p 
            option is ignored.

-b mmddyyyy Specifies the beginning date for the range to be examined. 
            If this option is omitted then the current date is used.

-e mmddyyyy Specifies the ending date for the range to be examined. 
            If this option is omitted then the beginning date is used.

An example of a pattern file is as follows:

ORA-
cannot allocate new log
All online logs need
could not connect
encountered error
lost listener
Errors in file
Restarting
Starting ORACLE
Shutting down
WARNING:

Example execution:

getalerts -f /oracle/admin/faxdb/bdump/alert_faxdb.log
          -p /oracle/local/bin/alert_patterns.rex
          -b 01012002 -e 01312002

Here is the code:

/*
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Name:           getalerts
Author:         Bill Robillard          billrobill@hotmail.com

    ****************************************************************************
    * NOTE:   THE USAGE TEXT ABOVE IS REPEATED BELOW IN A VARIABLE AND IS USED *
    *         TO REPORT THE USAGE BACK TO THE USER. MAKE SURE YOU CHANGE IT    *
    *         IN THE VARIABLE TOO WHEN YOU CHANGE IT UP ABOVE.                 *
    ****************************************************************************

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
*/

/*
--------------------------------------------------------------------------------
        included files
--------------------------------------------------------------------------------
*/
#include 
 
  
#include 
  
   
#include 
   
    
#include 
    
     
#include 
     
      
#include 
      
       
#include 
       
        

/*
--------------------------------------------------------------------------------
        definitions
--------------------------------------------------------------------------------
*/
#define MAX_INPUT_LENGTH        2048
#define MAX_PATTERNS            40
#define MAX_PATTERN_LENGTH      80

#define TRUE                    1
#define FALSE                   0

#define DAYS_OF_WEEK     "(Mon|Tue|Wed|Thu|Fri|Sat|Sun) "
#define MONTHS_OF_YEAR   "(Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec) "

/*
--------------------------------------------------------------------------------
        global variables
--------------------------------------------------------------------------------
*/
/* Array for patterns */
char            pattern[MAX_PATTERNS][MAX_PATTERN_LENGTH];

char            default_pattern[] = "ORA-";

/* Input line, most recent timestamp line and timestamp breakdown */
char            inputline[MAX_INPUT_LENGTH];
char            previous_timestamp[MAX_INPUT_LENGTH];

char            Day[] = "Mon";
char            Mon[] = "Dec";
char            dd[] = "25";
char            hhmmss[] = "12:30:00";
char            yyyy[] = "2001";

/* Month array for calculating month number */
char            month_abbrev[] = "JanFebMarAprMayJunJulAugSepOctNovDec";

/* Timestamp regular expression */
char            alert_date_pattern[] = "^"
                                       DAYS_OF_WEEK
                                       MONTHS_OF_YEAR
                                       ".. "
                                       "..:..:.. "
                                       "....";

/* Convert timestamp dates to this format */
char            mmddyyyy[] = "12252001";

/* Command line options */
char            options[] = "af:p:b:e:";

/* Command line parameters */
int             a_flag   = FALSE;
char            *f_value = NULL;
char            *p_value = NULL;
char            *b_value = NULL;
char            *e_value = NULL;

/* Number of patterns */
int             number_of_patterns = 0;

/* Status flags */
int             option_error        = FALSE;
int             input_error         = FALSE;

int             patterns_loaded     = FALSE;

int             select_all          = FALSE;
int             select_default      = FALSE;

int             in_the_interval     = FALSE;
int             beyond_the_interval = FALSE;
int             timestamp_printed   = FALSE;

/* Epoch values */
long            bgn_epoch, end_epoch, inputline_epoch, tmp_epoch;

/* Structures */
time_t          current_time;

regex_t         timestamp_regular_exp;
regex_t         pattern_regular_exp[MAX_PATTERNS];

/* File Pointers */
FILE            *input_file, *pattern_file;

/* Usage Description */
char            usage_description[] =
  " [[-a] [-f afile] [-p pfile] [-b mmddyyyy] "
  "[-e mmddyyyy]]"
  "nn"
  "-a      Extract all the alert log lines for the specified date range evenn"
  "        if a pattern file is supplied.n"
  "-f      'afile' is the filespec for the alert log to be used.n"
  "        If this option is not specified then stdin is used.n"
  "-p      'pfile' is the filespec for the reg expr pattern file.n"
  "        If this option is not used then only ORA- lines are extractedn"
  "        unless the -a option is present.n"
  "        If the -a option is present then the -p option is ignored.n"
  "-b      mmddyyyy is the beginning date.n"
  "        If this option is not used then the current date is used.n"
  "-e      mmddyyyy is the ending date.n"
  "        If this option is not used then the begin date is used.n"
  " n"
  "        If begin date > end date then these dates are 'swapped'.n"
  "nn";

/* Miscellaneous */
char            *charptr;

int             i,j,k;

/*
--------------------------------------------------------------------------------
        subroutine prototypes
--------------------------------------------------------------------------------
*/
void usage(char *cmd);
long date2epoch(char *mmddyyyy);
int  epoch2date(long epoch, char *mmddyyyy);

/*
////////////////////////////////////////
        main
////////////////////////////////////////
*/
int main(int argc, char **argv)
{

   /*
   - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
        Process command line parameters
   - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
   */
   /* Handle unknown options in the program */
   opterr = 0;

   /* Show usage if no parameters are entered */
   if (argc < 2)
   {
      usage(argv[0]);
      exit(0);
   }

   /* Look at all the options */
   while ((i = getopt(argc,argv,options)) != -1)
   switch (i)
   {
      case 'a':
         a_flag = TRUE;                 /* Select All */
         break;

      case 'b':                         /* Begin date */
         b_value = optarg;
         break;

      case 'e':                         /* End date */
         e_value = optarg;
         break;

      case 'f':                         /* Alert log filespec */
         f_value = optarg;
         break;

      case 'p':                         /* Pattern filespec */
         p_value = optarg;
         break;

      case '?':                         /* Unknown option */
         option_error = TRUE;

         if (isprint(optopt))
            fprintf( stderr
                    ,"ERROR==> Unknown option or missing value: '-%c'n"
                    ,optopt
                   );
         else
            fprintf( stderr
                    ,"ERROR==> Unknown option or missing value: 'x%x'n"
                    ,optopt
                   );

         break;

      default:
         abort();
   }

   /* Check for trailing unknown stuff */
   for (j = optind; j < argc; j++)
   {
      option_error = TRUE;

      fprintf(stderr,"ERROR==> Non-option argument %sn", argv[j]);
   }

   /*
   - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
         Edit option values
   - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
   */

   /* Is the -a option present ? */
   if (a_flag)
   {
      select_all = TRUE;
   }

   /* Determine begin date */
   if (b_value == NULL)
   {
      /* Use current date if -b option is missing */
      time(¤t_time);
      j = epoch2date((long) current_time, mmddyyyy);
      bgn_epoch = date2epoch(mmddyyyy);
   }
   else
   {
      /* Use -b option date if present */
      bgn_epoch = date2epoch(b_value);

      if (bgn_epoch == 0)
      {
         fprintf( stderr
                 ,"ERROR==> Invalid begin date (%s): Format is mmddyyyyn"
                 ,b_value
                );
         option_error = TRUE;
      }
   }

   /* Determine end date */
   if (e_value == NULL)
   {
      /* Use begin date if -e option is missing */
      end_epoch = bgn_epoch;
   }
   else
   {
      /* Use -e option date if present */
      end_epoch = date2epoch(e_value);

      if (end_epoch == 0)
      {
         fprintf( stderr
                 ,"ERROR==> Invalid end date (%s): Format is mmddyyyyn"
                 ,e_value
                );
         option_error = TRUE;
      }
   }

   /* Swap the begin and end dates if begin date is later than end date */
   if (bgn_epoch > end_epoch)
   {
      tmp_epoch = bgn_epoch;
      bgn_epoch = end_epoch;
      end_epoch = tmp_epoch;
   }

   /* Open the input file */
   if (f_value == NULL)
   {
      /* Use stdin if -f option is missing */
      input_file = stdin;
   }
   else
   {
      input_file = fopen(f_value,"r");

      if (input_file == NULL)
      {
         fprintf(stderr,"ERROR==> Can't open input file (%s)n",f_value);
         option_error = TRUE;
      }
   }

   /* Open the pattern file if the -a option is not present */
   if (!select_all)
   {
      if (p_value == NULL)
      {
         /* Use default patterns if -p option is missing */
         select_default = TRUE;
      }
      else
      {
         pattern_file = fopen(p_value,"r");

         if (pattern_file == NULL)
         {
            fprintf(stderr,"ERROR==> Can't open pattern file (%s)n",p_value);
            option_error = TRUE;
         }
      }
   }

   /* Quit if there was an option error */
   if (option_error)
   {
      fprintf(stderr,"Quit due to option errorsn");
      usage(argv[0]);
      exit(1);
   }

   /*
   - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
        Load the patterns
   - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
   */
   if (!select_all)
   {
      if (select_default)
      {
         /* Use the default pattern */
         strncpy(pattern[0],default_pattern,MAX_PATTERN_LENGTH-1);
         j = regcomp ( &pattern_regular_exp[0]
                      ,pattern[0]
                      ,REG_EXTENDED | REG_NOSUB
                     );
         number_of_patterns++;
      }
      else
      {
         /* Read in the pattern file records */
         for (i = 0; i < MAX_PATTERNS-1; i++)
         {
            fgets(pattern[i],MAX_PATTERN_LENGTH-1,pattern_file);

            if (feof(pattern_file))
            {
               /* End of Pattern file */
               patterns_loaded = TRUE;
               fclose(pattern_file);
               break;
            }

            if (ferror(pattern_file))
            {
               /* IO error on Pattern file */
               input_error = TRUE;
               fprintf(stderr,"ERROR==> Reading pattern filen");
               break;
            }

            if (pattern[i][strlen(pattern[i])-1] != 'n')
            {
               /* No linefeed -- line is too long */
               input_error = TRUE;
               fprintf(stderr,"ERROR==> Pattern %d too longn",i+1);
               break;
            }
            else
            {
               /* Replace linefeed with null and compile regular expression */
               pattern[i][strlen(pattern[i])-1] = '0';
               j = regcomp ( &pattern_regular_exp[i]
                            ,pattern[i]
                            ,REG_EXTENDED | REG_NOSUB
                           );
               number_of_patterns++;
            }

         }

         if ((!patterns_loaded) && (!input_error))
         {
            /* Too many records in the Pattern file */
            input_error = TRUE;
            fprintf( stderr
                    ,"ERROR==> Max patterns (%d) exceededn"
                    ,MAX_PATTERNS
                   );
            fclose(pattern_file);
         }
      }
   }

   /* Quit if there was an error reading the patterns */
   if (input_error)
   {
      fprintf(stderr,"Quit due to pattern errorsn");
      exit(1);
   }

   /*
   - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
        Select the lines that fall between the selected dates
   - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
   */

   /*Compile the timestamp regular expression pattern */
   j = regcomp ( ×tamp_regular_exp
                ,alert_date_pattern
                ,REG_EXTENDED | REG_NOSUB
               );

   i = 0;

   if (
       (fgets(inputline, MAX_INPUT_LENGTH-1, input_file) == NULL)
       &&
       (!feof(input_file))
      )
      input_error = TRUE;

   /* Process input until EOF or input error or outside of date interval */
   while (!feof(input_file) && !input_error && !beyond_the_interval)
   {
      i++;

      /* Check line length */
      if (strlen(inputline) > MAX_INPUT_LENGTH)
      {
         input_error = TRUE;
         break;
      }

      /* Does this line look like a timestamp line? */
      j = regexec (×tamp_regular_exp, inputline, (size_t) 0, NULL, 0);

      if (j == 0)
      {
         /* Extract the parts of the timestamp line */
         j = sscanf(inputline,"%s%s%s%s%s",Day,Mon,dd,hhmmss,yyyy);

         /* Point to the Mon entry in the list of month abbreviations */
         charptr = strstr(month_abbrev,Mon);

         if (charptr != 0)
         {
            /* Calc the month number */
            k = (int) (1+(charptr - month_abbrev)/3);

            /* Format the date field and calc the epoch */
            j = sprintf(mmddyyyy,"%.2d%.2d%s",k,atoi(dd),yyyy);
            inputline_epoch = date2epoch(mmddyyyy);

            /* Is the date within the selected range? */
            if (   (inputline_epoch >= bgn_epoch)
                && (inputline_epoch <= end_epoch)
               )
            {
               /* Set the ind that we are in the range */
               in_the_interval = TRUE;

               /* Save the new timestamp and reset the printed status */
               /* if not the same timestamp as before */
               if (
                   (previous_timestamp != NULL)
                   &&
                   (strncmp(previous_timestamp,inputline,strlen(inputline)) != 0)
                  )
               {
                  strncpy(previous_timestamp,inputline,strlen(inputline));
                  timestamp_printed = FALSE;
               }
            }
            else if (inputline_epoch > end_epoch)
            {
               /* Must have passed beyond the range */
               beyond_the_interval = TRUE;
               in_the_interval = FALSE;
               break;
            }

         }
         else
         {
            fprintf( stderr
                    ,"SOFTWARE ERROR==> Can't find month %s in tablen"
                    ,Mon
                   );
         }
      }
      else if (in_the_interval)
      {
         /* Check for pattern match */
         for (k=0; k< number_of_patterns; k++)
         {
            /* Regular expression matching routine */
            j = regexec ( &pattern_regular_exp[k]
                         ,inputline
                         ,(size_t) 0
                         ,NULL,0
                        );

            if (j == 0)
            {
               /* Expression matched -- has timestamp been printed? */
               if (!timestamp_printed)
               {
                  printf("%s",previous_timestamp);
                  timestamp_printed = TRUE;
               }

               /* Print the matching line */
               printf("%s",inputline);
               break;
            }
         }
      }

      /* Are we in the interval and are we printing all lines? */
      if (in_the_interval && a_flag)
      {
         printf("%s",inputline);
      }

      /* Get the next line */
      if (
          (fgets(inputline, MAX_INPUT_LENGTH-1, input_file) == NULL)
          &&
          (!feof(input_file))
         )
         input_error = TRUE;

   }

   fclose(input_file);

   /*
   - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
        Check the status
   - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
   */
   switch (input_error)
   {
      case FALSE:
         break;

      case TRUE:
         fprintf( stderr
                 ,"ERROR==> Problem with input line %d; may be too longn"
                 ,i
                );
         exit(1);
         break;

      default:
         fprintf( stderr
                 ,"ERROR==> Error at input line %dn"
                 ,i
                );
         exit(1);
         break;
   }

   exit(0);
}

/*
================================================================================
        Subroutine: date2epoch
================================================================================
*/
long date2epoch(char *mmddyyyy)
{
   char         mm[]   = "12";
   char         dd[]   = "25";
   char         yyyy[] = "2001";

   int          n;

   time_t       t1;
   struct       tm date;

   /* Check length of date value */
   if (strlen(mmddyyyy) != 8)
      return(0);

   /* Check for numerics */
   for (n=0;n<=7;n++)
   {
      if isdigit(mmddyyyy[n])
         continue;
      else
         return(0);
   }

   /* Extract Month, Day and Year */
   mm[0]   = mmddyyyy[0];
   mm[1]   = mmddyyyy[1];
   mm[2]   = '0';
   dd[0]   = mmddyyyy[2];
   dd[1]   = mmddyyyy[3];
   dd[2]   = '0';
   yyyy[0] = mmddyyyy[4];
   yyyy[1] = mmddyyyy[5];
   yyyy[2] = mmddyyyy[6];
   yyyy[3] = mmddyyyy[7];
   yyyy[4] = '0';

   /* Fill in the Date structure */
   date.tm_mon  = atoi(mm) - 1;
   date.tm_mday = atoi(dd);
   date.tm_year = atoi(yyyy);
   date.tm_hour = 0;
   date.tm_min  = 0;
   date.tm_sec  = 0;

   /* Determine whether it is a 2digit or 4digit year */
   if (date.tm_year > 1900)
      date.tm_year -= 1900 ;         /* yes, adjust for 4digit year */
   else if (date.tm_year < 50)       /* 2000-2049 */
      date.tm_year += 100;

   date.tm_isdst = -1;               /* Determine if DST is in effect */
   t1 = mktime(&date);               /* From struct to time_t */

   return (long) t1;
}

/*
================================================================================
        Subroutine: epoch2date
================================================================================
*/
int epoch2date(long epoch, char *mmddyyyy)
{
   time_t CurrTime;
   struct tm TestTime;
   char *fmt = "%m%d%Y";
   char buff[256];

   CurrTime = epoch;

   memcpy(&TestTime, localtime(&CurrTime), sizeof(TestTime));
   strftime(buff, sizeof(buff), fmt, &TestTime);

   strncpy(mmddyyyy,buff,8);

   return(0);
}

/*
================================================================================
        Subroutine: usage
================================================================================
*/
void usage(char *cmd)
{
   printf("Usage:n");
   printf(cmd);
   printf(usage_description);

   return;
}

       
      
     
    
   
  
 

Reader Feedback

Peter O. writes: I have something similar to this program: My utility is written in Java and is thus smaller and portable. It's available at http://www.ondruska.com/alerts.htm. Source code is not available yet, but let me know if you want it.

For More Information

  • What do you think about this tip? E-mail the Editor at tdichiara@techtarget.com with your feedback.
  • The Best Oracle Web Links: tips, tutorials, scripts, and more.
  • Have an Oracle tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
  • Ask your technical Oracle questions--or help out your peers by answering them--in our live discussion forums.
  • Check out our Ask the Experts feature: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.

This was first published in February 2002

Dig Deeper

PRO+

Content

Find more PRO+ content and other member only offers, here.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close