How Oracle Check Startup Time

  • by

To check Oracle database startup time, we have to know the difference between instance startup time and database open time. Additionally, we also check the startup history of the database in the last section of this post.

Instance Startup Time

To check instance startup time, here we can query the instance under NOMOUNT state.

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> select status, startup_time from v$instance;

STATUS       STARTUP_TIME
------------ --------------------
STARTED      2021-09-15 23:39:30

STARTED means NOMOUNT state. Let's mount the controlfile.

SQL> alter database mount;

Database altered.

SQL> select status, startup_time from v$instance;

STATUS       STARTUP_TIME
------------ --------------------
MOUNTED      2021-09-15 23:39:30

It's the same except the state is MOUNT now.

Database Startup Time

For database, the startup time is actually the open time. Here we wait for about 10 minutes, then open the database.

SQL> alter database open;

Database altered.

SQL> alter session set nls_timestamp_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> column open_time format a20;
SQL> select max(startup_time) open_time from dba_hist_database_instance;

OPEN_TIME
--------------------
2021-09-15 23:48:23

As you can see, the database open time was lagged about 10 minutes behind the instance startup time in this case. If you found the difference of your case is only a few seconds, this is very normal for starting a database from idle to completely open.

Database Open History

To view all startup history of the database, we may extend the above query.

SQL> select startup_time open_time from dba_hist_database_instance order by 1 desc;

OPEN_TIME
--------------------
2021-09-15 23:48:23
2021-08-05 21:30:22
2020-12-16 01:54:17
2020-12-16 01:26:29
2020-08-10 01:55:13
2020-08-10 01:15:39
2020-07-22 03:56:15

The startup history is listed from the latest to the oldest one.

Leave a Reply

Your email address will not be published. Required fields are marked *