Alert Log Mining for Downtime

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
Completed: ALTER DATABASE OPEN
... 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)

--
http://www.freelists.org/webpage/oracle-l