Database uptime

Job details

Name:	Database uptime
Platform:	Oracle
Category:	Availability Availability
Description:	Collects database uptime statistics for all instances mounted on the database.
Long description:	Collects database uptime statistics for all instances mounted on the database.
Version:	3.3
Default schedule:	* * * *
Requires engine install:	Yes
Compatibility tag:	.[type=‘instance’ & databasetype=‘oracle’]/.[hasengine=‘YES’ & ten_and_above = ‘1′ & _priv_read_sys_gv_instance = ‘1′]

Name	Default value	Description
week uptime calculation	24	Number of hours between each calculation of up-time statistics.
register maintenance period		This parameter is used to register a new maintenance period. Parameter value should be in the following format: NAME;PERIOD START;PERIOD END;COMMENT, period start and end in format YYYY-MM-DD HH24:MI:SS. Use “;” as a separator between the fields. Example of parameter setup: Monthly maintenace;2020-05-17 10:30:00;2020-05-17 16:30:00;patching of all database servers
delete maintenance period		This parameter is used to deletes a maintenance period. Use the following format: NAME;PERIOD START;PERIOD END;COMMENT, period start and end in format YYYY-MM-DD HH24:MI:SS. Use “;” as a separator between the fields. Example of parameter setup --> Monthly maintenace;2020-05-17 10:30:00;2020-05-17 16:30:00;patching of all database servers

Purpose: The purpose of this job is to monitor and collect statistics on database uptime, down periods, weekly and monthly uptime performance, as well as any registered maintenance periods for all instances mounted on the Oracle database.
Why: This job is important because it ensures continuous monitoring of database availability, tracking downtime and uptime statistics, which is essential for maintaining the overall health and performance of the database environment. If these thresholds are breached, it might indicate underlying issues that could affect the database’s reliability and performance.
Manual checking: You can check this manually in the database by querying the various tables and views created and used by this job, such as “dbw_db_uptime”, “dbw_db_uptime_histr”, “dbw_cluster_periods_td”, and others.

dbw_db_uptime: Tracks current uptime statistics like instance name, startup time, and last check.
dbw_db_uptime_histr: Stores historical uptime data.
dbv_db_uptime_histr: View combining current and historical uptime data.
DBW_CLUSTER_PERIODS_TD: Stores cluster-wide uptime and downtime periods.
dbw_week_uptime_td and dbw_month_uptime_td: Hold uptime statistics broken down by week and month.
DBW_MAINTENANCE_PERIODS_TD: Keeps a record of maintenance periods.

get_cluster_periods: Procedure to calculate periods of uptime and downtime across the cluster.
get_week_stat and get_month_stat: Initialize statistical tables for tracking weekly and monthly statistics.
get_week_uptime and get_month_uptime: Calculate uptime statistics using previously populated tables.
f_db_sec_down & f_db_availability: Functions to help calculate down seconds and uptime percentage.

Dependencies are set up to handle failures by cleaning up relevant data, ensuring that temporary disruptions do not permanently corrupt monitoring statistics.

System overview, availability statistics, cluster periods, and detailed logs for uptime and maintenance periods are formatted into reports for better visibility.

The procedure “db_uptime” and associated structures and views are designed to be dynamically updated as per changing monitoring requirements.

This job is tailored for environments where Oracle database reliability is crucial and is suitable where detailed tracking of operational statistics is required.

The default schedule for monitoring runs every minute, reflecting the system’s design towards real-time monitoring and quick response to state changes in the database environment.