Oracle uptime tips Oracle Database Tips by Donald BurlesonJuly 14,  2015

Question:  I am seeking a way to measure my instance startup time and shutdown times so that I can make reports of system uptime.  Are there any views in the V$, AWR or ASH tables that gives us the complete history regarding the startup and shutdown of databases.

Answer:  Startup information is stored in v$instance.startup_time, and uptime can be easily computed as sysdate - startup_time, but shutdown data is problematic.

During a normal shutdown, Oracle writes message to the alert log, but under an emergency abort (i.e. un-plug the server), nothing appears in the logs, nor within Oracle. Oracle stops abruptly, and there is no way to see it, except periodic daemon polling for a PMON or SMON process.

Startup and shutdown are written to the alert log, externally, but you could define the alert log as an external table and then make a SQL query to parse out the shutdown and startup messages.

Or, try this query to see uptime, from v$instance and compute the total uptime for the database.

select
   'Hostname : ' || host_name
   ,'Instance Name : ' || instance_name
   ,'Started At : ' || to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') stime
   ,'Uptime : ' || floor(sysdate - startup_time) || ' days(s) ' ||
   trunc( 24*((sysdate-startup_time) -
   trunc(sysdate-startup_time))) || ' hour(s) ' ||
   mod(trunc(1440*((sysdate-startup_time) -
   trunc(sysdate-startup_time))), 60) ||' minute(s) ' ||
   mod(trunc(86400*((sysdate-startup_time) -
   trunc(sysdate-startup_time))), 60) ||' seconds' uptime
from
   sys.v_$instance;

If you assume that PMON startup time is the same as the database startup time, you can get the uptime here:

select
   to_char(logon_time,'DD/MM/YYYY HH24:MI:SS')
from
   v$session
where
   sid=1;

---