What's the question? It sounds like you've got it all figured out already. Just write a little Perl/sed and you're all set. Finn On 11/8/07, JApplewhite@xxxxxxxxxxxxx <JApplewhite@xxxxxxxxxxxxx> wrote: > > > 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) >