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
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)
Other related posts:
