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;