Construction of database monitoring System (I.)

This article first summarizes the database monitoring system construction Those things, the next article describes the detailed deployment process1. Background

as the core IT business modules, the importance of the database is beyond doubt, the stable operation of the database is directly related to the availability, stability and efficiency of the application system. Therefore, to ensure a stable and efficient operation of the database, we need to face the following two challenges

1. How to find out the risk factors during the database operation ?

2. How do I find out when a database fails or is in an unhealthy state due to some cause?

in order to solve the above two difficulties, it is necessary to establish a database monitoring system, and to monitor the running status of the database in real time, in case of problems or problems occurred in the database. Timely early warning by triggering events to enable operations personnel to be processed in a timely manner to ensure stable and efficient operation of the database

2. Database Monitoring Architecture

Explanatory notes

(1) The production library represents an online database for external service, i.e. a database that needs to be monitored

(2) Monitoring data acquisition database, centralized extraction of each production database monitoring and storage (through scheduling tasks scheduled to complete), and will meet the warning threshold monitoring data, after processing to send to "alarm push server"

(3) Alarm push Server, accept the alarm data from "Monitoring data Acquisition database", send the warning message to the operation and maintenance personnel in time by mail sending procedure .

3. Database monitoring types and properties

The types of monitoring and other properties deployed in the current database monitoring system are the following table

Serial number	Type of monitoring	Monitoring frequency	Note
1	Oracle database Undo Table Space Usage Alarm	10 minutes	Monitor Undo Table Space Usage
2	Oracle Database Temp Table Space usage alarm	10 minutes	Monitor the usage of temp table space
3	Oracle Database Sequence Usage alerts	Daily 9 o'clock in the morning	Monitoring Sequence Usage cleanup
4	Oracle database connection Number Exception alarm	10 minutes	Monitoring database connection Number conditions
5	Oracle Database tablespace Usage Alerts	20 minutes	Monitoring database table Space usage
6	Oracle database session undo Usage Alarm	5 minutes	Monitor session undo Usage
7	Oracle database user Password expiration alarm	Daily 9 o'clock in the morning	Monitor user Password expiration
8	Oracle Database PGA Usage alerts	10 minutes	Monitoring database PGA Usage
9	Oracle Database Lock Exception alert	5 minutes	Monitoring lock resource contention in the database
10	Oracle database Shared Pool usage alerts	10 minutes	Monitoring database Shared Pool usage
11	Oracle Database Active Connections Alarm	2 minutes	Monitoring database Two-minute activity connection status
12	Oracle Database Index Exception alert	30 minutes	Monitoring the status of database indexes
13	Oracle Database Performance Alerts	1 minutes	Monitor the performance of your database
14	Oracle database on-off alarm	1 minutes	Monitoring the availability of databases
15	Oracle Log Monitoring Alarms	10 minutes	Monitoring database Run log conditions

Note

Due to the length of the table, some other attributes will be followed, such as the thresholds for each type of monitoring and so on.

4. Database Monitoring System Features

everything can not be perfect, database monitoring system is the same, the current construction completed database monitoring system architecture, subject to professional and capacity constraints, The following deficiencies exist

1. timeliness, can not voice the alarm information to inform the operation and maintenance personnel (telephone mode), at present can only be sent in the form of e-mail alarm information, it is certain that the alarm information must be delayed detection, especially at night time period;

2. Robustness, the database monitoring architecture of the "Production library" and "Monitoring data Acquisition database" have been deployed on-off alarm, but "alarm Mail push Server" (database) due to temporarily not complete self-monitoring, in the event of a problem, will cause the entire monitoring system to collapse and not be found in time (currently only 9 and 6 points per day in the morning to send a monitoring status message from the check)

3. Professional, due to limited programming capacity, after the completion of the database monitoring system, there must be many bugs and unknown defects, not after a long run and test, can not further improve

4. Comprehensiveness, currently only the deployment of a variety of monitoring (based on experience), due to the specificity of the system, there must be a monitoring blind spot, Need to follow up the process in order to perfect step by step

The current database monitoring architecture has been built with the following features

1.   The alarm information is sent and received by mail (139e-mail), further receive email notifications and content via SMS
2.    each type of monitoring is done through a single package, with any type of monitoring exception (adjusted orBug), without affecting the normal operation of other monitoring types
3.    the monitoring data collection for each production depot is controlled by a "Monitoring database configuration table" that can be modifiedFlagfield to control the monitoring data collection of the corresponding production library or not, further control the production database alarm or not
4.    each monitoring type of data acquisition is through a "Monitoring type configuration Table" control, you can modifyFlagfield to control the monitoring data collection for the corresponding monitoring type of the corresponding production library
5.    Monitoring threshold Configuration table enables monitoring of the same category,can be based on the critical level of the system,different monitoring thresholds are set. That is, each type of monitoring can be modified by modifying the monitoring threshold field, the monitoring threshold of the personalized monitoring category, so that different databases of the same type of monitoring can be set different alarm thresholds (set different thresholds for subsequent system level importance)
6.    the "Alarm Send Configuration Table" can be used to collect monitoring data (for analysis), but does not send the purpose of the alarm message
7.    all monitoring data are collected and processed through the "Data acquisition database", and finally sent to "alarm push server", where the monitoring data is saved for the last half month for analysis and viewing

5. Database Monitoring System Construction steps

Database monitoring System Construction steps are summarized in the following table

Steps	Content
1	Identify the types of monitoring you need to deploy
2	Configuring TNS for all databases to be monitored on the Monitoring data acquisition database
3	Confirm the TNS accuracy of the configuration
4	Create a monitoring user on all databases to be monitored
5	Create a monitoring query view on all databases to be monitored
6	Select a database login to be monitored and check the validity of the view created by step four
7	Create a data acquisition user on the monitoring data acquisition database and create a database link to all databases to be monitored and verify the validity
8	Create a Data acquisition table on the monitoring data acquisition database and create a data acquisition table for each monitoring category
9	Create several important monitoring-related configuration tables on the Monitoring data acquisition database
10	Add basic data to the various configuration tables created in the previous step
11	Establish the channel with the alarm sending server and establish the channel connection in the database mode.
12	Set up all the monitoring data acquisition program and the alarm sending program
13	Set up scheduled tasks, collect monitoring data and send alarm information at regular intervals.
14	Establish a mail sender to complete the deployment of the mail sender on the mail sending server
15	Establish the "Monitoring and Acquisition database" of the on-off monitoring, to prevent the monitoring of the acquisition database anomalies caused by the entire alarm system crash self-test
16	Monitor the "Alert Send database Server" scenario, where it is only deployed to send a healthy message once every morning at 9 and 6 o'clock.
17	Database alert log monitoring deployment, individual deployment due to alert log monitoring specificity
18	Follow up work, periodically clean up monitoring data, create indexes, etc.

Note: This article introduces the Monitoring System construction scheme is only suitable for small and medium-sized environment, for the kind of timely, stable requirements of the environment, due to the monitoring of timeliness and other needs can not be met, must not be appropriate for the method described in this article. And this article only provides a monitoring system of the reference scheme, for your reference only!