• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer navigation

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

How to check Oracle Database uptime

by admin

There are various ways to check database uptime using a SQL query. We will basically use the view v$instance and v$session to determine the database uptime. You can also view the alert log of the database to view the startup time of the database.

Using v$instance view

The startup_time field from v$instance view provides the time at which databases was started.

SQL> select instance_name,
to_char(startup_time,'mm/dd/yyyy hh24:mi:ss') as startup_time 
from v$instance;

Sample output:

check oracle database uptime

You can look at difference between current time and startup time.

select instance_name, trunc(sysdate-startup_time) as "Days uptime" from v$instance;

You can also apply various math and formatting to get more detailed time breakdowns. For example:

select instance_number,instance_name, trunc(sysdate-startup_time) as "Days",
trunc( 24*((sysdate-startup_time)- trunc(sysdate-startup_time))) as "Hours",
mod(trunc(1440*((sysdate-startup_time)- trunc(sysdate-startup_time))), 60) as "Minutes",
mod(trunc(86400*((sysdate-startup_time)- trunc(sysdate-startup_time))), 60) as "Seconds"
from  gv$instance
order by instance_number;
INSTANCE_NUMBER INSTANCE_NAME         Days     Hours      Minutes    Seconds
--------------- ---------------- ---------- ---------- ---------- ----------
          1      orclcdb           8         3           59       4

Using v$session view

The “logon_time” from v$session also provides us with the database uptime.

SQL> SELECT database_name, 
TO_CHAR(logon_time, 'DD-MM-YYYY HH24:MI:SS') 
FROM v$session WHERE program LIKE '%PMON%';

Sample output:

logon_time oracle database

OR

SQL> SELECT database_name, 
to_char(logon_time,'DD/MM/YYYY HH24:MI:SS') 
FROM v$session WHERE sid=1 ;

here sid=1 refers to PMON ( Oracle background process)

Sample Output:

check database uptime for oracle database

Filed Under: oracle

Some more articles you might also be interested in …

  1. Oracle Interview Questions : Recovery catalog for RMAN backup
  2. Oracle SQL Script to Report Tablespace Free and Fragmentation
  3. How to Change sys password in oracle 12c on RAC and dataguard
  4. What is OCFS or OCFS2
  5. Unable to create spfile for Oracle ASM instance
  6. What are Command Rules in oracle Database
  7. How to Clone a Pluggable Database from an RMAN Container Database Backup
  8. Oracle Grid 12c: Read Only Instances on Leaf Nodes
  9. RMAN-06059 During RMAN Backup of archivelogs ( How to backup archivelogs moved to a different location)
  10. How to find Cluster Name and Grid Version in Oracle RAC

You May Also Like

Primary Sidebar

Recent Posts

  • grpck command – Remove corrupt or duplicate entries in the /etc/group and /etc/gshadow files.
  • xxd command – Expressed in hexadecimal form
  • sesearch: command not found
  • macof: command not found

© 2022 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright