Find Oracle Database Uptime & Startup History

Oracle database normal startup and shutdown history is ideally captured in the instance alert log. Oracle database uptime and startup time can also be checked using query mentioned below.

When My Oracle Database Instance was Last Restarted

Using below query you can check when database was last started.

SQL> select instance_name,
to_char(startup_time,'mm/dd/yyyy hh24:mi:ss') as startup_time
from v$instance;
INSTANCE_NAME     STARTUP_TIME
------------------------      ----------------------
orahowdb 09/03/2020 03:24:03

Oracle Database Startup History

You can use below query to check Oracle database uptime history.

SQL> set lines 200
SQL> col instance_name for a50
SQL> select * from
( select STARTUP_TIME FROM dba_hist_database_instance ORDER BY startup_time DESC)
WHERE rownum < 10;

STARTUP_TIME
-----------------------------
03-SEP-20 03.24.43.000 AM
03-SEP-20 03.24.27.000 AM
01-SEP-20 02.32.32.000 PM
01-SEP-20 02.32.31.000 PM
08-AUG-20 02.35.45.000 PM
08-AUG-20 02.35.44.000 PM
08-AUG-20 09.05.24.000 AM
08-AUG-20 08.02.43.000 AM
03-AUG-20 01.25.07.000 PM
9 rows selected.

Oracle Database Uptime History

Below query will show you the number of days instance is up and running.

SQL> set lines 200
SQL> col host_name for a20
SQL> col instance_name for a15
SQL> SELECT host_name, instance_name,
TO_CHAR(startup_time, 'DD-MM-YYYY HH24:MI:SS') startup_time,
FLOOR(sysdate-startup_time) days
FROM sys.v_$instance;

HOST_NAME            INSTANCE_NAME     STARTUP_TIME     DAYS
---------------- -------------------------- ---------------------  
dmmmcrkpac-eefpp1       orahowins1   03-09-2020 03:24:03   34

Conclusion:

  • You can check startup time and uptime using v$instance view.
  • Oracle instance startup and shutdown history is captured in alert log and same can be checked from the query using dba_hist_database_instance view.