Oracle Configuration Discrepancies Check

Oracle Configuration Discrepancies Check AUGUST 2024 AUTHOR: Isidro Javier García Fernández University of Malaga SUPERVISORS: Andrzej Nowicki Miroslav Potocky CERN openlab Report // 2024 2 Oracle Configuration Discrepancies Check PROJECT SPECIFICATION The project is aimed to detect discrepancies between running setup and metadata repositories storing elements of configuration. The system needs to detect differences between OEM information, run-time information and configuration management data which is stored in Syscontrol LDAP. The discrepancies might lead to service instability or unexpected behavior of the environment. Maintenance or monitoring scripts could potentially be affected as well. This is why it’s crucial to detect such misconfigurations in an efficient manner. CERN openlab Report // 2024 3 Oracle Configuration Discrepancies Check ABSTRACT This project is part of the 2024 CERN OpenLab Summer Student program, conducted over a 9-week internship. It aims to address the challenges of managing complex database setups within the ORACLE service at CERN, which involve clusters and multiple nodes. The primary objective is to develop a solution that continuously identifies discrepancies between the live databases configuration and external configuration sources. Using an existing automation solution (Rundeck tool), the project implements a system to integrate data from various sources, ensuring that runtime settings are consistently aligned with stored parameters. A key aspect of the project involves detecting mismatches, such as incorrect Oracle Home paths, which could lead to potential system instabilities or failures. The project focuses on automating the identification of configuration discrepancies and enhancing the reliability of database operations at CERN to improve the overall stability and performance of the Oracle database systems. CERN openlab Report // 2024 4 Oracle Configuration Discrepancies Check TABLE OF CONTENTS INTRODUCTION 05 DATA SOURCES 05 SYSCONTROL LDAP RUNNING CONFIGURATION OEM TOOLS 06 PROPOSED SOLUTION 06 COLLECTING SYSCONTROL LDAP DATA COLLECTING RUNNING CONFIGURATION DATA COLLECTING OEM DATA DISCREPANCIES OUTPUT 10 DISCREPANCIES RESOLUTION 11 CONCLUSION 12 REFERENCES 12 CERN openlab Report // 2024 5 Oracle Configuration Discrepancies Check 1. INTRODUCTION The ORACLE service at CERN is essential for supporting the CERN community by providing over 100 databases, most of which are RAC (Real Application Clusters) databases. This setup involves a complex infrastructure with clusters and multiple nodes within each cluster. Ensuring the reliability and consistency of these databases is crucial for the effective operation of CERN's scientific research. In such a dynamic environment, the Oracle databases run from specific paths, involving the CRS (Cluster Ready Services) and Oracle home directories. These directories contain the necessary binaries and libraries for the database's operation. As updates and environmental changes occur within clusters and databases, it is essential to ensure that all configurations remain synchronized across various nodes and clusters. Any discrepancies in these configurations can lead to performance issues, system inefficiencies, or even failures, seriously impacting CERN's operations. Failed or manual updates can lead to synchronization issues between running configuration and metadata repositories, resulting in errors. Additionally, some repositories may incorrectly report databases as unavailable while they are available. This project, conducted as part of the 2024 CERN OpenLab Summer Student program, seeks to address these challenges by developing a solution that continuously identifies discrepancies between live database configurations and external configuration sources. By integrating data from multiple systems, the project aims to ensure that runtime settings consistently align with stored parameters. This synchronization is necessary for maintaining the integrity, performance, and stability of the Oracle database systems at CERN. Using a tool like Rundeck, this project automates the detection of mismatches, such as incorrect Oracle home paths and ensures that any changes in the environment are accurately reflected across all places where the configuration is stored. This approach not only reduces potential issues but also enhances the overall efficiency of database management processes at CERN. After collecting the data, a report has to be prepared and made available to the Database team. 2. DATA SOURCES In the context of managing Oracle databases at CERN, it is necessary to understand the roles of Syscontrol LDAP [1] (Lightweight Directory Access Protocol), Running Configuration (RC), and OEM (Oracle Enterprise Manager). Each of these components plays a significant role in ensuring the efficient operation, monitoring, and synchronization of CERN's database infrastructure. a. SYSCONTROL LDAP At CERN, LDAP is utilized for storing and managing scripts used in the automation and configuration of Oracle database environment. The project called Syscontrol acts as a repository that maintains critical information about system configurations, ensuring that updates and changes are consistently applied across all database instances. Syscontrol LDAP can be efficiently used to manage and script maintenance tasks, which are essential for maintaining database security and functionality. b. RUNNING CONFIGURATION Running Configuration (RC) refers to the current setup and state of the Oracle databases as they operate within the CERN environment. It includes details such as the paths to Oracle Home and the versions of the binaries being used. RC provides real-time information on the configuration of each server and database instance, highlighting what is actively running across the clusters. It is important for monitoring the performance of the databases, CERN openlab Report // 2024 6 Oracle Configuration Discrepancies Check ensuring that the operational settings are aligned with the expected configurations. Accurate running configurations enable rapid identification of discrepancies, allowing for timely corrective actions to prevent database disruptions. c. OEM OEM [2] is a management and monitoring tool designed for Oracle database environments. It provides a graphical interface to manage and supervise the entire database infrastructure. At CERN, OEM serves as a vital repository containing detailed information about database clusters, instances, and configurations. It allows administrators to perform essential tasks such as monitoring the status of databases, detecting anomalies, and generating performance reports. OEM ensures optimal database system performance by quickly identifying and resolving issues. OEM system is also the source of alerting in case of database unavailability or other problems with the service delivered by the team. 3. TOOLS To address the challenge of finding the misconfigurations, this project proposes a structured approach using Rundeck [3] automation tool. Rundeck can be used to manage and execute tasks across a distributed network of servers. It provides direct server connectivity, allowing administrators to efficiently automate tasks across multiple nodes. This is very important for configuration updates, executing diagnostic checks, and providing consistency in the Oracle database infrastructure at CERN. Moreover, Rundeck is already configured within the CERN team, enhancing the efficiency and reliability of database management by automating routine tasks. Rundeck will execute jobs based on Bash and Python scripts, in addition to processing SQL statements. 4. PROPOSED SOLUTION The solution involves creating several jobs in Rundeck. A main Rundeck job will launch multiple smaller jobs called helpers. Each helper job will collect data from different repositories and store it into a dedicated table in an Oracle schema created for this project. This setup ensures that all relevant data is aggregated, and discrepancies are identified efficiently. a. COLLECTING SYSCONTROL LDAP DATA The first step in the proposal involves implementing a job helper that collects data from the Syscontrol LDAP repository using the vegas-cli command. This command is developed internally and is the main way of accessing the information stored in Syscontrol LDAP. The Rundeck helper job gathers essential configuration details of each database entity. The job is collecting information about versions and Oracle Home paths, related to Oracle database setup. Once the data is collected, it is processed to ensure that it is relevant and accurate before being stored in the project's Oracle database. This process allows for the effective monitoring and comparison of live configurations against the stored parameters. In addition to discrepancies in the information between different repositories, some mismatches can be found between the SC_VERSION parameter and the SC_ORACLE_HOME parameter. This is an example of some discrepancies found in LDAP: CERN openlab Report // 2024 7 Oracle Configuration Discrepancies Check Figure 1. Table of LDAP discrepancies In a normal situation, the LDAP_VERSION should contain the last part of the path mentioned in the LDAP_HOME_PATH column. However, due to some manual operations, sometimes the LDAP_VERSION column contains values different than expected. This could be caused by some script failed runs or potential typos while doing manual interventions by the database team. As the next step, the helper job connects to a dedicated Oracle database schema discrepancies_project and inserts discrepancies and clean data into two different tables, ensuring that the information reflects the latest LDAP configuration status. This is an example overview of some LDAP rows of data: Figure 2. Table of LDAP data. b. COLLECTING RUNNING CONFIGURATION DATA The second job helper focuses on gathering Running Configuration (RC) data, which involves connecting to all servers within the database environment. The job is designed to execute a script on each available server to collect essential configuration details, such as cluster names, database unique names, instance names, and Oracle Home paths. Once connected to the servers, the script is executed on these nodes, and it extracts key information about the databases and their configurations. This includes details from the olr.loc configuration file, where the information about installed Oracle Clusterware is stored. The script also extracts other information about CERN openlab Report // 2024 8 Oracle Configuration Discrepancies Check the Oracle Clusterware environment, e.g. the cluster name. The collected data is then sent back to the main rundeck job, where it is processed to ensure accuracy and relevance. A discrepancy in the Running Configuration is identified when there is a mismatch between the Clusterware location used by different database nodes in the cluster. This situation is theoretically possible (and accepted) during the clusterware patching/upgrade process. However, it’s not an accepted situation outside of the short period of the patching process. To simplify, if the Clusterware ORACLE_HOME path indicates a different location in two nodes of the same cluster, it is marked as a discrepancy. Another type of discrepancy happens when no database instances are running on a server, which could mean a potential infrastructure problem. These discrepancies are shown as rows containing the database name and its home path, but with multiple rows for each expected instance, where the data from the servers is null. This is an example of some discrepancies found in RC: Figure 3. Table of RC discrepancies. The script also connects to a dedicated Oracle database schema discrepancies_project and inserts discrepancies and clean data into two different tables, ensuring that the information reflects the latest RC configuration status. This is an overview of RC rows of data: Figure 4. Table of RC data. CERN openlab Report // 2024 9 Oracle Configuration Discrepancies Check c. COLLECTING OEM DATA The third job helper focuses on gathering data from Oracle Enterprise Manager (OEM), a tool for managing and monitoring Oracle databases. This job is designed to collect configuration details from OEM and identify discrepancies between the reported settings and the actual runtime configurations. A key part of this process involves creating and using a database link to access data stored in the OEM repository. A database link is a schema object that allows queries to be executed on a remote database as if they were running locally. The following SQL command creates the database link using the credentials of a read-only user: CREATE DATABASE LINK oem_db CONNECT TO sysman_ro IDENTIFIED BY "password" USING 'em_service_prod'; Once the database link is established, it can be used to query tables in the OEM database. To differentiate data accessed through the link, the table names in the query are appended with '@oem_db'. This job helper retrieves target names, types, and Oracle Home paths for various database environments, including Oracle databases, RAC clusters, and standalone instances. The job helper processes the data to detect discrepancies, particularly by comparing the rac_database and oracle_database entries. If there is a mismatch between the Oracle Home paths for a RAC database and its corresponding instances, it is marked as a discrepancy. These discrepancies are shown as rows containing the database name and its home path, but with multiple rows for each instance, each with a different home path. Such discrepancies can be fixed by re-applying the OEM target Oracle Home path configuration or running a dedicated Rundeck job. At the moment of writing this report, it was unclear what created that type of discrepancies in internal OEM repository. An example of some discrepancies found in OEM is shown below: Figure 5. Table of OEM discrepancies. The script also connects to a dedicated Oracle database schema discrepancies_project and inserts discrepancies and clean data into two different tables, ensuring that the information reflects the latest OEM configuration status. This is an overview of OEM rows of data: