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.