Oracle service dashboards and alerts in Grafana using Oracle Enterprise Manager Plugin

Oracle service dashboards and alerts in Grafana using Oracle Enterprise Manager Plugin August 2024 AUTHOR: Nourhene Jallali National Engineering School of Tunis, TU Braunschweig SUPERVISOR: Miroslav Potocky Andrzej Nowicki CERN 2 PROJECT SPECIFICATION Grafana, Oracle, Oracle Enterprise Manager (OEM), Data Visualization, Oracle Services, Alerting, Monitoring, Webhook, Slack 3 ABSTRACT The integration of Oracle Enterprise Manager (OEM) with Grafana aims to create dynamic and real-time dashboards and alerts for monitoring Oracle services. This project leverages the comprehensive monitoring capabilities of OEM and the powerful data visualization and alerting features of Grafana to enhance the visibility and management of Oracle service metrics. By connecting Grafana to OEM through the Oracle Enterprise Manager Plugin, key metrics are visualized, enabling administrators to monitor performance and reliability effectively. 4 TABLE OF CONTENTS INTRODUCTION 6 PROJECT OVERVIEW 7 INSTALLATION AND CONFIGURATION 8 Install the OEM Plugin for grafana Enable the OEM Plugin for grafana Add the OEM as a Data source View Predefined Dashboards RESULTS 10 Metrics Identification Oracle Service Dashboards Grafana Alerts CONCLUSION 20 PATHS OF FUTURE DEVELOPMENT 21 BIBLIOGRAPHY 22 5 1. INTRODUCTION Today, keeping track of IT systems is more important than ever. Companies rely heavily on their databases to run smoothly and perform well. Oracle databases are crucial in this setup, storing essential business data and supporting critical applications. To ensure these databases run efficiently, we need powerful tools to monitor their health, performance, and configurations. Grafana is a popular open-source tool that helps visualize and analyze data from different sources. It’s known for its flexibility and powerful features, allowing users to create custom dashboards to monitor their systems. The Oracle Enterprise Manager (OEM) plugin for Grafana is valuable, making it easy to connect Grafana to Oracle databases and pull in important metrics. This project, supported by CERN, aims to use Grafana and the OEM plugin to build clear and useful dashboards for monitoring Oracle services. CERN, the European Organization for Nuclear Research, is renowned for its advanced research and technological innovations. By integrating Grafana with Oracle Enterprise Manager, CERN seeks to enhance its ability to monitor and manage critical database systems effectively. These dashboards will help database administrators, managers, and other users keep track of key metrics and respond quickly to any issues. Additionally, we will set up alerts to notify users of any problems, helping to maintain smooth and efficient operations. 6 2. PROJECT OVERVIEW Grafana is a popular open-source tool that helps visualize and analyze data from different sources. It’s known for its flexibility and powerful features, allowing users to create custom dashboards to monitor their systems. The Oracle Enterprise Manager (OEM) plugin for Grafana is a valuable tool that makes it easy to connect Grafana to Oracle databases and pull in important metrics. This project aims to use Grafana and the OEM plugin to build clear and useful dashboards for monitoring Oracle services. These dashboards will help database administrators, managers, and other users keep track of key metrics and respond quickly to any issues. Additionally, we will set up alerts to notify users of any problems, helping to maintain smooth and efficient operations. The figure 1 illustrates the project's architecture for monitoring Oracle databases using Grafana and the Oracle Enterprise Manager (OEM) plugin. Metric data from Oracle databases is integrated into Grafana through the OEM plugin, which generates various dashboards for service health, performance, and configuration. Grafana also features a unified alerting system that notifies users of critical issues. Alerts are configured to be sent via email or Mattermost through webhooks, ensuring that users receive timely notifications based on predefined thresholds and metrics. The entire system is designed to be flexible and customizable, allowing for adjustments to meet evolving organizational needs and ensuring that users can efficiently manage and respond to database performance and configuration challenges. Figure 1. Project Overview 7 3. INSTALLATION AND CONFIGURATION This section will describe how to install and configure Grafana and the Oracle Enterprise Manager plugin and set up the data source. a. Install the Oracle Enterprise Manager Plugin for Grafana You can install the Oracle Enterprise Manager App for Grafana using the Grafana command line interface (preferred) or manually. . Using the Grafana CLI 1. Navigate to $GRAFANA_HOME/bin 2. Install the Oracle Enterprise Manager App for Grafana, [1]. Using --pluginUrl option through grafana-cli ./grafana-cli --pluginUrl<FULL_PATH>/oracle-emcc-app-<VERSION>.zip plugins install oracle-emcc-app Without the Grafana CLI 1. Navigate to the Grafana plugins directory cd $GRAFANA_HOME/data/plugins 2. Extract the content of the OEM App forGrafana zip file in the Grafana plugin directory. cd $GRAFANA_HOME/data/plugins unzip <EM App for Grafana>.zip 3. Restart the Grafana server. cd $GRAFANA_HOME/bin ./grafana-server Note: The Oracle Enterprise Manager (OEM) plugin for Grafana is installed using RPM-based methods as configured by the IT-DA team at CERN. This RPM-based installation approach is managed according to their procedures and was not configured by me. b. Enable the Oracle Enterprise Manager Plugin for Grafana To enable the Oracle Enterprise Manager App for Grafana, simply go to the Grafana plugin settings and click the Enable button for the Oracle Enterprise Manager App. Then, make sure to adjust the settings on your Enterprise Manager site to allow Grafana to access it. To ensure optimal performance, manage data requests and API calls to prevent overloading the system. c. Add the Oracle Enterprise Manager as a Data Source Before creating a dashboard, you need to specify an Enterprise Manager App from which the dashboard will pull data. To add the Oracle Enterprise Manager as a data source: 1. From the left tool bar, choose Configuration > Data Sources. 2. Click Add data source. A list of data source types is displayed. 3. Select Oracle Enterprise Manager. 8 Figure 2. Add Oracle Enterprise Manager as a Data Source d. View Predefined Dashboards The Oracle Enterprise Manager App for Grafana ships with predefined dashboards that let you visualize key operational data about Oracle services. You can click on one of the sample dashboards below to view them. Additionally, you can create copies of these dashboards and customize them to suit your specific needs. Note: Depending on the number of users viewing these dashboards and the selected timeframe, OEM API request limits may need to be adjusted accordingly. For detailed guidance on managing these limits, please refer to the Oracle documentation, [2]. Figure 3. Predefined Dashboards 9 Figure 10. Notification Policies 4. Contact Points: Finally, we defined the contact points, which are the channels through which notifications are sent. For this project, we chose to use email and webhook as our contact points to ensure alerts are delivered promptly to the right people. ● Email: We created a new contact point, chose "Email" as the type, and entered the appropriate email address. We customized the message template to include all relevant information about the alert. Finally, we tested the email notification to confirm that the alerts were correctly sent out to this email address. Figure 11. Email Configuration When the alert state is firing, a notification will be sent to the email that we’ve configured. We can check the dashboard to investigate the issue and silence the alert if it's not critical. We’ve included an example of an alert for the latency panel. 16 Figure 12. Alert Notification (Email) ● Mattermost: To integrate Grafana alerts with Mattermost using a webhook, we first need to configure a webhook in Mattermost. Here's how we can achieve this: Step 1: Set Up Webhook Integration in Mattermost: ● Create a new Mattermost channel where we want to receive alerts. ● Select "Integrations" from the dropdown menu. ● Choose "Incoming Webhooks" and click "Add Incoming Webhook." ● Fill in the required details, such as the title, the channel, and the description of the webhook. ● Copy the generated webhook URL. This URL will be used in Grafana to send alerts to Mattermost. 17 Figure 13. Adding Incoming Webhook Step 2: Configure Grafana to Use the Webhook: ● In Grafana, go to the "Alerting" section and select "Contact points." ● Click on "New contact point" and choose "Slack" as the type. ● Paste the Mattermost webhook URL that we copied earlier into the URL field. ● Customize the message template to include relevant information about the alert. This ensures that the alert messages sent to Mattermost are informative and actionable. ● Test the webhook notification to confirm that alerts are correctly sent to the specified Mattermost channel. ● When the alert state is firing, we will receive a notification in the Mattermost channel that we already use. ● We have included an example of an alert for the latency panel. ● We can check the source of the alert, the panel, or the dashboard and decide if it needs to be reviewed further or silenced. 18 Figure 14. Configuring Webhook in Grafana Figure 15. Alert Notification (Mattermost) 19 5. CONCLUSION This project successfully integrated Oracle services with Grafana to monitor and manage system performance. We built dashboards to visualize crucial metrics like database status, throughput, and latency, which help us understand and improve system health. By setting up alerts, we ensured that any issues, such as high latency or dropped throughput, are detected quickly and addressed promptly. This monitoring setup makes our Oracle services more reliable and helps us maintain high performance, ultimately improving user experience. 20 6. PATHS OF FUTURE DEVELOPMENT For future work, we suggest creating a Grafana dashboard specifically for Oracle Cloud Infrastructure to better monitor cloud resources. Adding features to show HOT/COLD tablespace files in the database could give more detailed insights into storage use. We also recommend using AI and machine learning to analyze past data and predict potential issues before they happen. This way, problems can be caught early and fixed quickly, helping to keep the system running smoothly. 21 7. BIBLIOGRAPHY [1] Oracle Entreprise Manager App for Grafana: https://www.oracle.com/enterprise-manager/technologies/grafana/ [2] Cloud Control Administrator’s Guide: https://docs.oracle.com/en/enterprise-manager/cloud-control/enterprise-manager-cloud-control/13.4/ emadm/application-api-throttling.html 22