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).