DBMS uptime

Job details

Name:	DBMS uptime
Platform:	Oracle
Category:	Availability
Description:	Collects uptime statistics in database.
Long description:	Task collects uptime statistics in database.
Version:	3.0
Default schedule:	* * * *
Requires engine install:	Yes
Compatibility tag:	.[type=‘instance’ & databasetype=‘oracle’]/.[hasengine=‘YES’ & _priv_read_v_session = ‘1′]

Parameters

Name	Default value	Description
dbw username	dbwatch	Database user name used by the dbWatch Server when connecting to the dbWatch Engine.
dbw user id	0	Database user id used by the dbWatch Server when connected to the dbWatch Engine.
end_availability_timestamp	00:00	Availability timestamp (hh24:mi),used to define allowed DBMS shutdown period.
end_availability_duration	0	Availability duration in minutes.

Job Summary

Purpose: The purpose of this monitoring job is to collect and manage uptime statistics for Oracle database instances that are monitored by the dbWatch system.
Why: This job is critical to ensure that uptime data is accurately recorded, processed, and available for analysis. Proper monitoring and reporting of uptime can help in assessing the health and availability of databases, which is essential for maintaining business continuity and minimizing downtime.
Manual checking: You can check uptime data manually in the database by using the following SQL commands:

SELECT event_name, event_date, period FROM dbw_dbms_uptime_#instance~id#

Implementation Details

This job involves creating tables and views that store and represent uptime data and statistics. Procedures are implemented to process and log events such as database starts, stops, and checks.
Dependencies include views and tables such as dbw_dbms_uptime_#instance~id# and dbv_dbms_monitored_#instance~id#, which help in maintaining historical data and calculating statistics like downtime and total uptime.

Critical SQL Statements Used in Job

The creation of a table to log events related to the database’s operational status:

CREATE TABLE dbw_dbms_uptime_#instance~id# (event_name VARCHAR2(64), event_date DATE, period NUMBER)

* A procedure that is crucial for collecting and updating uptime data by checking the database status at regular intervals:

CREATE OR REPLACE PROCEDURE dbms_uptime_#instance~id# /* Procedure details hidden for brevity */

* Views created for detailed and summarized views of uptime history and current status:

CREATE OR REPLACE VIEW dbv_dbms_uptime_histr_#instance~id# AS SELECT EVENT_NAME, EVENT_DATE, PERIOD FROM dbw_dbms_uptime_#instance~id# UNION SELECT EVENT_NAME, EVENT_DATE, PERIOD FROM dbw_dbms_uptime_histr_#instance~id#

Job Dependencies

This job has several dependencies on other database objects, primarily views and tables that assist in generating the necessary uptime statistics and reports. These dependencies ensure that the job has access to all required data for accurate processing and output.

Reporting and Output

Report Title: DBMS Uptime
Description: This report is based on all tasks and checks related to database uptime and provides a comprehensive view of system availability.
Scheduled Frequency: Reports are scheduled to run every hour.
Details: The report presents data such as the total uptime, periods monitored, downtime, and percentage uptime.

Database Metric	Details
DBMS uptime	Length of time the DBMS has been up and running without interruption.
Monitored	Total time for which the database monitoring was active.
Down	Total time the database was down.
Uptime	Overall percentage of time the database was operational in relation to total monitored time.

Relevance to Database Health

The uptime data collected and processed by this job is crucial for maintaining an overview of the database’s availability and operational status. It helps database administrators make informed decisions related to performance tuning, maintenance schedules, and overall IT infrastructure planning.

This detailed setup ensures that all aspects of Oracle DBMS uptime are meticulously monitored, logged, and reported, contributing to effective database management and reliability.