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 on Oracle database design and architecture

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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