Startup Time Varies
To check Oracle database startup or restart 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.
Let's see what're the differences.
Instance Startup Time
To check instance startup time, here we can query the instance under NOMOUNT state.
First of all, we format the date time of current session.
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
Then we perform the query.
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> 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.