Problem solve Get help with specific problems with your technologies, process and projects.

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(&current_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 ( &timestamp_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 (&timestamp_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 last published in February 2002

Dig Deeper on Oracle database design and architecture

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close