Skip to content
Home » Oracle » How to Check Oracle Database Startup Time

How to Check Oracle Database Startup Time

  • Oracle

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.

  1. Instance Startup Time
  2. Database Startup Time
  3. Database Open History

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.

Leave a Reply

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