Hi,

I've written a script to search for an Oracle ORA- error on a log file, print that line and the .trc file associated with it as well as the dateline of when I assumed the error occured. In most it is the first dateline previous to the error.

Unfortunately, this is not a fool proof script. I assumed that if the ORA- error appears on line n. line n-1 is where the .trc line is and n-2 is the date.

Below is an excerpt of the script.

Code:

grep -in "$error_to_search" $alert_log | tee -a $tmp_01

...
...
...

   while read line
   do
      line_err=`echo $line | awk -F":" '{ print $1 }'`
      let line_trc=$line_err-1
      let line_date=$line_err-2
      tracefile=`sed -n "${line_trc}p" $alert_log | awk '{ print $4 }' | awk -F":" '{ print $1 }'`
...
...
...

         sed -n "${line_date}p" $alert_log
         sed -n "${line_trc}p" $alert_log
         sed -n "${line_err}p" $alert_log


...
...
...
   done < $tmp_01

An excerpt of the $alert_log below is as below. The assumption of the error line, dateline, tracefile line is correct on the first three (3) ORA- errors but not on the fourth one, the one in RED.

Since the 4th one happens immediately after the 3rd one, it should be considered as to have occurred on the same date and can be ignored. To get it to work, if the ORA- error line are consecutive, it should only consider the first one and ignore the others but I don't know how to do this?

Code:

Wed May 28 14:35:56 2014
Errors in file /db/abcd/dba/udump/abcd_ora_28306.trc:
ORA-01017: invalid username/password; logon denied
Wed May 28 14:39:59 2014
Errors in file /db/abcd/dba/udump/abcd_ora_4506.trc:
ORA-01017: invalid username/password; logon denied
Wed May 28 14:43:30 2014
Errors in file /db/abcd/dba/udump/abcd_ora_4506.trc:
ORA-01017: invalid username/password; logon denied
ORA-01017: invalid username/password; logon denied
Wed May 28 15:11:25 2014
Thread 1 advanced to log sequence 51621 (LGWR switch)
  Current log# 4 seq# 51621 mem# 0: /db/abcd/redolog/abcd_redo_4a.dbf
  Current log# 4 seq# 51621 mem# 1: /db/abcd/mirrlog/abcd_redo_4b.dbf

In summary, what I am hoping to achieve with the script is:

01 - Search for a specific ORA- errors and print that line, i.e. line n.
02 - Search backward and search for the tracefile/.trc file, that should be the tracefile associated to this error. At the moment I am assuming this is line n-1. How do I search from line n backward and print the line that has the .trc line?
03 - Search backward for the first dateline and that should be the date when this ORA- error occurred. At the moment, I am assuming this is line n-2. How do I search from line n backward and print the first dateline? From the excerpt, the dateline format starts with day of the week, Mon [Month] [Day], Tue [Month] [Day], Wed [Month] [Day] etc.