Scraping the Oracle alert log in Windows

thecheapdba has been combing the net for scripts to scrape the Oracle alert log for the last few days. Ok, you have me here! It didn’t take long for this cheapdba to quickly realize that 99% of the scripts out there are for Unix type machines. AND I didn’t find one real solution that was freely available to scrape an alert log on Windows.

So what do we do?
Easy!
We build it ourselves!

Hopefully you can see that thecheapdba has a little bit of skills at scripting. Well this time thecheapdba will give you the initial step in scraping your Oracle alert log. Now, to my knowledge, there isn’t an easy way to scan a range of lines through a Window’s .BAT script. So in order to figure out where we are in a particular file (log file) you are you must assign line numbers to the file and then search on those line number.

So this posting will give you those few commands to assign a number to each and every line in a text file (log file) and then find the line you are looking for.

Ok, here we go:

in order to assign line numbers to each line in the Oracle alert log you need only issue the following command. And then redirect the output to another file for future searching.

DOS> TYPE alert_db10.log | FIND /N /V "" > numbered_alert_db10.log

AND here is a snippet of what you will get:
[214]Stopping background process MMON
[215]Starting background process MMON
[216]Starting background process MMNL
[217]MMON started with pid=10, OS id=10642
[218]Tue Sep 25 09:31:20 2007
[219]ALTER SYSTEM enable restricted session;
[220]MMNL started with pid=11, OS id=10644
[221]Tue Sep 25 09:31:20 2007
[222]alter database “db10″ open resetlogs
[223]Tue Sep 25 09:31:20 2007
[224]RESETLOGS after incomplete recovery UNTIL CHANGE 446074
[225]Tue Sep 25 09:31:20 2007
[226]Errors in file /opt/app/oracle/product/10.2.0/db_1/admin/db10/udump/db10_ora_10640.trc:
[227]ORA-00313: open failed for members of log group 1 of thread 1
[228]ORA-00312: online log 1 thread 1: ‘/oradata/db10/redo01.log’
[229]ORA-27037: unable to obtain file status
[230]Linux Error: 2: No such file or directory
[231]Additional information: 3
[232]Tue Sep 25 09:31:25 2007
[233]Errors in file /opt/app/oracle/product/10.2.0/db_1/admin/db10/udump/db10_ora_10640.trc:
[234]ORA-00313: open failed for members of log group 2 of thread 1
[235]ORA-00312: online log 2 thread 1: ‘/oradata/db10/redo02.log’
[236]ORA-27037: unable to obtain file status

As you can see every line nows has a line number associated with it. There is a lot of information here but as we know all we are concerned about are those errors that are reported in this log.

So now we must find those particular errors that are in our current alert log. To do this you can issue the following command. This also produces a numbered list in yet another file. Here is the command.
OH! before i give the command, wouldn’t it be nice if we had a set of things we are looking for? Sure you can search for the ORA- labels but it is much more efficient and extensible (like those words) if we reference a “list” of things we want to search for. So i create a little file i can reference called “alertstrings” that has the following two lines. Feel free to add your own as you could just search for specific things such as ORA-00600 errors!

My alertstrings file looks like this:
error
ORA-

Now onto the command to strip these out and create a list. Here is the command:

FINDSTR /G:alertstrings numbered_alert_db10.log | FIND /N /V "" > numbered_alert_errors

AND here is what it produces:
[1][116]ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL…
[2][227]ORA-00313: open failed for members of log group 1 of thread 1
[3][228]ORA-00312: online log 1 thread 1: ‘/oradata/db10/redo01.log’
[4][229]ORA-27037: unable to obtain file status
[5][234]ORA-00313: open failed for members of log group 2 of thread 1
[6][235]ORA-00312: online log 2 thread 1: ‘/oradata/db10/redo02.log’
[7][236]ORA-27037: unable to obtain file status
[8][241]ORA-00313: open failed for members of log group 3 of thread 1
[9][242]ORA-00312: online log 3 thread 1: ‘/oradata/db10/redo03.log’
[10][243]ORA-27037: unable to obtain file status

so now say we are looking for the 6th error in our alert log. Just issue the following command:
FINDSTR /B /C:[6] numbered_alert_errors

AND here is what you get:
[6][235]ORA-00312: online log 2 thread 1: ‘/oradata/db10/redo02.log’

Now this is the beginning of producing an effective Oracle log scraper. Hopefully you can see how you can wrap a little code around this and figure out new alert log errors have been written out. Now hook in my script on how to send email and you have a simple notification method.

AND if you can’t do that, come back and this cheapdba will provide you with a full script in the not too distant future.