Alert Log Mining for Downtime

  • From: JApplewhite@xxxxxxxxxxxxx
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Thu, 8 Nov 2007 13:43:10 -0600

Some auditor or other is wanting total downtime for our production 
financials database for the past two fiscal years( Sep 1 thru Aug 31 for 
us).  I know of no data dictionary or hidden table that tracks the 
date/time of, for example, every database startup - the shutdowns couldn't 
accurately be recorded because of server crashes, etc.  If you know of 
such an internal source, please let me know.

The option that occurs to me is mining all the old alert logs, which we 
have, for database shutdown and database open times.  I'd use a text 
editor to filter only alert log lines with the following strings:
Shutting down instance
... or the lines following the format of...
Mon Oct  8 05:15:44 2007

I'd use SQL*Loader to load those lines, in order, with a 
sequence-generated ID, into a table.  Then I'd use some kind of analytic 
SQL to sum up the time differences between the times just before the 
Shutting down and just after DATABASE OPEN lines.  I'd just have to find 
the last recorded time line before a DATABASE OPEN to account for database 
crashes when there's no Shutting down line since the previous DATABASE 
OPEN line.

I need to automate this because we've bounced that database three times 
per week for years - first as a workaround for an old 8i space leak bug on 
HPUX, then as a workaround for some quirks our Financials software 
exhibits now that it's a 9i DB.  Anyway, that makes too many shutdowns and 
startups for manual examination of the alert logs, so I want ot use SQL to 
do the mining.

Any ideas?

Thanks for any guidance on this.

Jack C. Applewhite - Database Administrator
Austin I.S.D.
414.9715 (phone) / 935.5929 (pager)

Other related posts: