Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Database up longer that host?
Stephen Andert wrote:
>
> I use a script named db_uptime.sql (I think I got it from the list here)
> to calculate how long the database has been up. The output compares
> nicely to the unix uptime command.
>
> We had some maintenance last Wed night that bounced the host (Tru64
> 5.1a cluster) and naturally the database as well. But when I looked at
> the host uptime compared to the db_uptime.sql, it looks like the
> database has been up longer than the host. The db is 8.1.7.3. The host
> is a Tru64 Compaq (er HP) GS160 AlphaWildfire machine. We are in a
> cluster, but the databases are all still running on one node.
>
> 1* select STARTUP_TIME from v$instance
> SQL> /
> 27-NOV-2002 18:30:12
>
> SQL> @db_uptime
> Database Uptime
> --------------------------------------------------------------------------------
> Host Name : dgrdb01.firsthealth.com
> Instance Name : CLAIM
> Uptime : 4days 21hours 53minutes
>
> SQL> !uptime
> 15:40 up 4 days, 14:52, 7 users, load average: 4.13, 6.93, 7.08
>
> SQL> select sysdate from dual;
>
> SYSDATE
> --------------------
> 02-DEC-2002 15:40:39
>
> SQL>
>
> Is there something wrong with the script or just a misalignment in the
> starts?
>
> Stephen Andert
> (confused in AZ)
>
> -- db_uptime.sql
> select
> 'Host Name : '||host_name||chr(10)||
> 'Instance Name : '||instance_name||chr(10)||
> 'Uptime : ' ||floor(xx)||'days '
> ||floor((xx-floor(xx))*24)||'hours '
>
> ||round(((xx-floor(xx)*24)-floor((xx-floor(xx)*24)))*60)
> ||'minutes'
> "Database Uptime"
> from (
> select host_name,instance_name ,(sysdate-STARTUP_TIME) xx
> from v$instance
> )
>
Stephen,
I hope that the query doesn't come from the list, because it is
wrong. The error is to apply floor() before multiplying by 24 or 60 -
you have tremendous rounding errors.
My own database has not been up long enough to be 100% sure about it but
I believe the following to be correct :
select
'Host Name : '||host_name||chr(10)|| 'Instance Name : '||instance_name||chr(10)|| 'Uptime : ' ||floor(xx)||'days ' ||floor(xx * 24 - floor(xx * 24))||'hours ' ||round((xx * 86400)/60) ||'minutes' "Database Uptime" from ( select host_name,instance_name ,(sysdate-STARTUP_TIME) xx from v$instance )
-- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: sfaroult_at_oriole.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Mon Dec 02 2002 - 17:09:08 CST
![]() |
![]() |