Hi everyone,

Hope you’re doing good!

Ensuring high availability of your Oracle databases is crucial for maintaining optimal performance and meeting Service Level Agreements (SLAs). Oracle Enterprise Manager (OEM) provides a comprehensive way for monitoring and managing database uptime. With that said, I received a call from a client and he was asking this:

  • Is there any way I can calculate the DB uptime in the last 30 days using a simple and fast approach?

Well, the answer is: yes!

OEM has the uptime info from the Graphical Interface, where you can navigate to the targets and check it. I’m not a huge expert on OEM, but I’d say that you may can create some report (or maybe already exists) about availability.

OK, but we are DBA’s, we like to work using SSH and SQL*Plus, isn’t? So, the question is:

  • Is there any way I can calculate the DB uptime in the last 30 days using SQL statements?

Again, the answer is: yes! Definitely!

If you are using Oracle Enterprise Manager, you can query run a SELECT statement directly against the OEM views to calculate uptime manually.

This blog post will guide you through the process of calculating database uptime and SLA percentage using SQL queries on OEM views.

Below, we’ll walkthrough the SQL query used to calculate database uptime for both individual databases and RAC (Real Application Clusters) databases over the last 30 days.

You must connect to OEM DB repository.

The complete query is here:

COLUMN target_name FORMAT a11
COLUMN uptime_hours FORMAT 9999
SET LINESIZE 200
SET PAGESIZE 500

WITH availability AS (
  SELECT 
    target_name,
    target_type,
    CASE
      WHEN INSTR(target_name, '_') > 0 THEN SUBSTR(target_name, 1, INSTR(target_name, '_') - 1)
      ELSE target_name
    END AS cluster_name,
    availability_status,
    start_timestamp,
    NVL(end_timestamp, SYSTIMESTAMP) AS end_timestamp
  FROM 
    SYSMAN.MGMT$AVAILABILITY_HISTORY
  WHERE 
    target_type = 'oracle_database'
    AND start_timestamp >= TRUNC(SYSDATE) - 30
),
downtime AS (
  SELECT 
    cluster_name,
    SUM(CASE WHEN availability_status != 'Target Up' THEN (end_timestamp - start_timestamp) * 24 ELSE 0 END) AS downtime_hours
  FROM 
    availability
  GROUP BY 
    cluster_name
),
adjusted_uptime AS (
  SELECT 
    cluster_name,
    ROUND(720 - SUM(downtime_hours), 0) AS uptime_hours
  FROM 
    downtime
  GROUP BY 
    cluster_name
)
SELECT 
  cluster_name AS target_name,
  uptime_hours,
  ROUND((uptime_hours / 720) * 100, 2) AS sla_percentage
FROM 
  adjusted_uptime
ORDER BY 
  cluster_name;

Let’s now break down the query. The SQL query consists of three main parts:

  1. Extracting and Normalizing Intervals
  2. Calculating Downtime
  3. Calculating Uptime and SLA Percentage

Let’s start with:

  1. Extracting and Normalizing Intervals:

This part of the query extracts relevant data from the MGMT$AVAILABILITY_HISTORY view and normalizes the cluster name for RAC databases.

WITH availability AS (
  SELECT 
    target_name,
    target_type,
    CASE
      WHEN INSTR(target_name, '_') > 0 THEN SUBSTR(target_name, 1, INSTR(target_name, '_') - 1)
      ELSE target_name
    END AS cluster_name,
    availability_status,
    start_timestamp,
    NVL(end_timestamp, SYSTIMESTAMP) AS end_timestamp
  FROM 
    SYSMAN.MGMT$AVAILABILITY_HISTORY
  WHERE 
    target_type = 'oracle_database'
    AND start_timestamp >= TRUNC(SYSDATE) - 30
)

Explanation:

  • target_name: The name of the database or instance;
  • target_type: The type of the target, which is ‘oracle_database’;
  • cluster_name: Normalized name for RAC clusters by extracting the part of target_name before the first underscore. For single-instance databases, the target_name itself is used;
  • availability_status: The status of the database (e.g., ‘Target Up’, ‘Target Down’);
  • start_timestamp: The start time of the status interval;
  • end_timestamp: The end time of the status interval, defaulting to the current timestamp if it is null.

Let’s now go to:

2. Calculating Downtime

This part aggregates downtime hours for each cluster.

downtime AS (
  SELECT 
    cluster_name,
    SUM(CASE WHEN availability_status != 'Target Up' THEN (end_timestamp - start_timestamp) * 24 ELSE 0 END) AS downtime_hours
  FROM 
    availability
  GROUP BY 
    cluster_name
)

Explanation:

  • downtime_hours: Total hours the database was down (I’m assuming that DB is down if availability_status is different than ‘Target Up’).

This part of query will sum the “downtime” hours for each cluster.

Let’s now go to:

3. Calculating Uptime and SLA Percentage

This part calculates the adjusted uptime and SLA percentage based on the total possible hours in a month, 30 days (720 hours):

adjusted_uptime AS (
  SELECT 
    cluster_name,
    ROUND(720 - SUM(downtime_hours), 0) AS uptime_hours
  FROM 
    downtime
  GROUP BY 
    cluster_name
)
SELECT 
  cluster_name AS target_name,
  uptime_hours,
  ROUND((uptime_hours / 720) * 100, 2) AS sla_percentage
FROM 
  adjusted_uptime
ORDER BY 
  cluster_name;

Explanation:

  • uptime_hours: Total possible hours (720) minus the downtime hours;
  • sla_percentage: The SLA percentage calculated as (uptime_hours / 720) * 100.

By following this approach, you can accurately calculate the database uptime and SLA percentage for your Oracle databases using OEM views. This method is particularly useful for generating uptime reports and ensuring that your databases meet the required availability standards.

Below we have an output as example. The DB names were replaced by security reasons:

TARGET_NAME UPTIME_HOURS SLA_PERCENTAGE
----------- ------------ --------------
DB01		    701 	 97.36
DB02		    681 	 94.58
DB03		    708 	 98.33
DB04		    711 	 98.75
DB05		    705 	 97.92
DB06		    692 	 96.11
DB07		    711 	 98.75
DB08		    701 	 97.36
DB09		    705 	 97.92
DB10		    703 	 97.64
DB11		    689 	 95.69
DB12		    701 	 97.36
DB13		    713 	 99.03
DB14		    714 	 99.17
DB15		    714 	 99.17
DB16		    714 	 99.17
DB17		    714 	 99.17
DB18		    714 	 99.17
DB19		    714 	 99.17
DB20		    714 	 99.17
DB21		    708 	 98.33
DB22		    547 	 75.97

Hope this helps.

Peace!

Vinicius