How to check database uptime?

In my previous articles i have given many answers about how to related to oracle and sql server as well. In this article i would like to give you the basic idea about – How to check database uptime? I would like to give multiple important queries for getting answer of the important interview question – How to check database uptime? Initially the oracle system tables and views will help us finding the correct up-time for the oracle database.

Real life industry scenario for How to check database uptime?

We require to create a report which will give us the information about system uptime and start-time.We require to fetch the database history information and create a report. Question is- Is there any database views which will give you the information about the database history?

We need to utilize the v$instance system view to get the historical information about the database in detail.You will get the startup information from following query ,

Query :

Select instance_name as “Database_name”,to_char(startup_time,’dd-mm-yyyy hh24:mi:ss’) as “The_Startup_Time” from v$instance;

Output :

Database_name The_Startup_time

Amit_prod 19-08-2020 04:32:32

You can check the startup history as well using following query,

SELECT INSTANCE_NAME as “Name_of_Instance”,TO_CHAR(STARTUP_TIME, ‘HH24:MI DD-MMM-YY’) as “Database_Startup_History” FROM DBA_HIST_DATABASE_INSTANCE ORDER BY STARTUP_TIME DESC;

Using V$Session system view :

Select database_name as “DB_Name”,TO_CHAR(logon_time,’DD-MMM-YYYY HH24:MI:SS’) from V$Session where program like ‘ %PMON%’;

SELECT database_name as “DB_Name”, to_char(logon_time,’DD/MM/YYYY HH24:MI:SS’) FROM v$session WHERE sid=1;

So the question is you require to calculate the uptime. Oracle has so many functions like sysdate. Following equation will calculate the Uptime for the database,

Sysdate – The_Startup_time

The problem here is to calculate the shutdown time. We always require to check the periodic daemon for a PMON or SMON process.The startup time and shutdown time are written in the alert logs.But you can create a different external table to track that using following steps,

create directory Start_Time as ‘/u01/app/oracle/admin/mysid/bdump’;

create table
   T_Shutdown_Startup ( msg varchar2(80) )
organization external (
   type oracle_loader
   default directory Start_Time
   access parameters (
      records delimited by newline
   )
   location(‘alrt_mysid.log’)
)
reject limit 1000;

These are some useful queries. You can calculate the uptime correctly using following query,

select
   host_name as “Name_of_Host”
  instance_name as”Name_Of_DBInstance”
  to_char(startup_time,’DD-MON-YYYY HH24:MI:SS’) as “Start_Time”
  floor(sysdate – startup_time) as “Uptime” || ‘ days ‘ ||
   trunc( 24*((sysdate-startup_time) –
   trunc(sysdate-startup_time))) || ‘ hours ‘ ||
   mod(trunc(1440*((sysdate-startup_time) –
   trunc(sysdate-startup_time))), 60) ||’ minutes ‘ ||
   mod(trunc(86400*((sysdate-startup_time) –
   trunc(sysdate-startup_time))), 60) ||’ seconds’ as “Uptime_Total”
from
   sys.v_$instance;


The above query is useful query to calculate the correct database uptime. If you like this article or if you have any issues with the same kindly comment in comments section.