Find total uptime

[GremlinHunter]

Is there a way to find out total uptime on a database? I have one turning 7 years old tomorrow and I was curious how much of that was with database online.

 

[SantaMufasa]

Is there a way to find out total uptime on a database?

Oracle does not increment and store any counter that totals how many days/hours/minutes/seconds a database runs.


You can, however, see the date and time that the most recent startup occurred with:

select to_char(startup_time,'yyyy-mm-dd hh24:mi:ss')startup
from v$instance;

STARTUP
-------------------
2007-12-04 00:13:43

For total uptime, about the best you can hope for is if you archive (or never overwrite) your ALERT_<sid>.log file (that resides in the path identified by BACKGROUND. You could then process that flat file as an Oracle EXTERNAL table and apply this logic:[ul][li]For each database-instance STARTUP, step back one line to obtain and store the DATE and TIME of that startup.[/li][li]Step backward in the ALERT_<sid>.log file from the STARTUP timestamp to the previous timestamp. Finding the difference between these two times represents approximate "DOWNTIME" (since the previous timestamp might not be for a graceful shutdown, in which case the timestamp simply respresents when the previous message appeared in the alert log, not necessarity when the instance went down.)[/li][li]Total up the downtime values and subtract the downtime SUM from the total elapsed time between the current time and the first STARTUP. This yields approximate UPTIME.[/li][/ul]Let us know if any of this helps.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[GremlinHunter]

Ah well. With nothing in v$database or v$instance I didn't really have much hope.

Thanks for the logic for computing it from the archive logs. I can probably use that with one of our "newer" systems. Sadly the archive logs from the box in question have been deleted over the years for hard drive space.

 

[SantaMufasa]

Actually, GremlinHunter, it's not the archive logs (which would take massive amounts of disk space) about which I am speaking, it is the alert_<SID>.log...the messages files for the instance. That file would take relatively little space and I can't imagine anyone deciding that they were irrelevant or over-consumptive of disk space.

If it turns out that you do still have your alert_<SID>.log file(s), I'd be happy to help code up a routine to total the uptime.

Let us know,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[GremlinHunter]

Sorry about that. Was not watching what I was typing. Been fighting with backup issues lately and had archive logs on the mind.

Actually our alert log's do get to be farily big. I last saved off this databases alert log back on 11-SEP-07. It is at 95MB now. Tiny compared to many of our external tables but a bit a bit big to open with a text editor. Storage aside myself of my old coworker may well have deleted the really old ones as they were just too big to open with the tools we had at the time.

At any rate I only have a smattering of alert logs beyond a year ago and none before 2004.

 

[sem]

You may move this file to Unix and use tail command to see only latest entries. You also don't need "an editor" (did you mean MS Word? ), that need this file in memory, but rather a viewer that in most cases is far less memory greedy.

BTW how many instances do you have? Database doesn't have an uptime but an instance does. For multiple instances you should investigate gv* views.

Regards, Dima

http://seminihin.narod.ru

 

[GremlinHunter]

Do you remember offhand what view to check in sem? The two most likely looking views gv_$instance and gv_$mystat do not seem to have anything like uptime.

 

[sem]

OK, I suppose I made the same mistake as Dave. So you need the total uptime? This term is a bit vague: e.g is database considered up when it's mounted exclusively?
In any case you may grep this file to get most important entries like ALTER DATABASE OPEN or ALTER DATABASE CLOSE. Unless you have daily cold backups you shouldn't expect a lot of entries. Each of them is prefixed with the line containing a time stamp you need.
Of course you should check their order as some unpaired OPEN may occur (power off or so). In such cases you need more sensitive analysis.

Regards, Dima

http://seminihin.narod.ru