Oracle Database Monitoring

Oracle Database is a database management system developed by Oracle Corporation. It provides high performance, scalability, and security for enterprise-level data management. Oracle supports a wide range of applications, from transactional processing to data warehousing, ensuring reliable and efficient data handling.

Monitoring the Oracle database helps maintain its performance, reliability, and security by providing real-time insights into database activities and resource utilization. IT-Conductor helps track its performance based on the collected metrics, alerts, availability, and overall system health.

Prerequisites

Before configuring an Oracle database for monitoring in IT-Conductor, follow the instructions below.

Account Requirements

Create a new Oracle monitoring account.
Grant the user the "CREATE SESSION" and "OEM_MONITOR" roles and the "SELECT ON V$DIAG_ALERT_EXT" privileges.

Note:

Perform these steps on the Oracle database.
Automation users may have varying requirements based on the nature of the automation activities.

Configure Oracle Database Monitoring in IT-Conductor

To configure Oracle database monitoring in IT-Conductor, follow the instructions below.

Add New Oracle Instance

Visit service.itconductor.com and enter your login credentials.
Navigate to Dashboards → Administrator to access the Administrator's Dashboard.
Locate the Oracle Instances actions panel and click the title to access the complete list.

Click the New Oracle Instance button to start adding a new instance for monitoring.

Fill out all the necessary information in the New Oracle Instance wizard. Once completed, click to add the instance.

Description - refers to any relevant information about the instance being added.
Organization - refers to an administrative structure that defines objects with a common goal or purpose. If you previously created an organization, please select it.
Role - refers to the environment where the database will be used.
Site - refers to a logical object that describes a particular area or location, depending on the context in which it is used.
Gateway - allows communication between the customer's site network and the IT-Conductor cloud platform. See Gateway Setup for more details.
Instance Host - refers to the server or system where the database instance runs.
Instance Port - refers to the port number through which the server communicates with IT-Conductor. This is typically set to 1521.
Read Timeout - refers to the duration for which a connection or operation will wait for a response before timing out.
TimeZone as Region - enables synchronization of date-and-time-related functions and operations within the database with the IT-Conductor timezone.

Provide the account information in the New Account screen and click to complete the configuration.

Description - refers to any relevant information about the user account being added.
Application - refers to the name given to the instance being added.
Person - refers to the name of the user who is adding the system.
User Name - refers to the user created aligned with the Account Requirements.
User Password - refers to the password of the previously created user.
Re-type Password - refers to the same password provided in the Password field.

Verify if the system was added to the Oracle Instances actions panel and check its status.

Note: The status will remain on Ready for a few minutes. Refresh the panel if needed. If there are configuration issues, such as incorrect connection parameters or an invalid account, the status will remain Ready. Click to display the logs and troubleshoot. If everything is configured correctly, the status will change to In Progress.

Navigate to the service grid and verify if the system was added under the Oracle Databases node.

Note: The system will appear in the service grid within 5-15 minutes.

Monitor Oracle Database in IT-Conductor

To view the availability and performance metrics of an Oracle database, locate the Oracle Databases node in the service grid.

Oracle Database Key Metrics

Availability - refers to the operational state and accessibility of the Oracle database.
Connection Failures - refer to the events where attempts to establish a connection are unsuccessful.
Heartbeat - refers to the periodic signal sent to the system, enabling real-time detection of server downtime.
Missing Account - refers to the absence of a required user account or resource within the Oracle database.
Retriever Failures - refer to the errors or issues encountered during the retrieval process of essential data or information within the Oracle database.

Note: All metrics will not show data when a server is unavailable.

To access a historical view of each of these metrics, click and a pop-up chart will be displayed.

Alerts

To display the active alerts generated by a specific database, expand the Alerts node by clicking .

Alternatively, click Alerts, and you will be redirected to a page, as shown in Figure 8.

Note: The same navigation principles apply to other metrics.

Performance

To view the performance metrics of a specific database, expand or click the Performance node.

Top 5 Objects By Wait - refers to the top five database objects that contribute the most to wait events, helping administrators pinpoint performance bottlenecks and prioritize optimization efforts.
Top 5 SQL Statements - refers to the top five SQL statements that consume the most resources or cause the highest wait times, enabling administrators to optimize query performance and improve overall database efficiency.
Buffer Cache Hit Ratio - refers to the percentage of requests for data that are satisfied by blocks already in the buffer cache. A higher ratio indicates efficient memory use and reduced disk I/O, while a lower ratio may suggest memory pressure or inefficient cache usage.
Dictionary Cache Hit Ratio - refers to the percentage of requests for data dictionary information that are satisfied by blocks in the dictionary cache. A high ratio indicates efficient access to dictionary data, while a low ratio may indicate contention or inadequate cache size.
Disk Sort Memory Ratio - refers to the ratio of memory used for disk sorting operations compared to the total memory available. A high ratio suggests excessive disk sorting, which impacts performance due to increased I/O operations.
Dispatcher Workload Busy Ratio - refers to the percentage of time dispatchers spend processing client requests. Monitoring this ratio helps assess dispatcher workload and resource utilization.
Latch Hit Ratio - refers to the percentage of latch requests that are satisfied without waiting. A high ratio indicates efficient latch usage, while a low ratio may suggest latch contention or resource contention.
Library Cache Hit Ratio - refers to the percentage of SQL statements found in the library cache, avoiding the need for hard parsing. A high ratio indicates efficient reuse of SQL statements and reduced parsing overhead.
Log Switches - refers to the number of times the database switches from one redo log file group to another. Monitoring log switches helps assess database activity and transaction throughput.
Rollback Segment Waits Ratio - refers to the ratio of transactions waiting for rollback segment resources compared to the total number of transactions. A high ratio may indicate contention for rollback segments, potentially impacting database performance.
Top Wait Time - refers to the wait event with the highest cumulative wait time, helping administrators prioritize performance tuning efforts and address critical bottlenecks.
Library Cache - refers to the shared area in the SGA of an Oracle database that stores parsed SQL statements, execution plans, and PL/SQL code.
PGA - stands for Program Global Area; refers to the memory region in an Oracle database that stores data and control information for individual server processes.
Resource Limits - refer to the constraints imposed on database users or profiles to control their usage of system resources such as CPU, memory, and I/O.
SGA - stands for System Global Area; refers to the shared memory region in an Oracle database that contains data and control information for the entire database instance.

Space

To view the space usage of a specific database, expand or click the Space node.

Used Space % - indicates the percentage of allocated space utilized by the database and its tablespaces.
Used Space Total - indicates the total amount of allocated space utilized by the database.
Schemas - refer to the logical containers for database objects, such as tables, views, and procedures.
Tablespaces - refer to the logical storage containers that hold data files associated with the database objects.

Health Explorer

To view a more detailed analysis of metrics and time-synchronized data of a specific database, click Health from the Oracle database node view.

You will be redirected to the Health Explorer page. From here, you can view one or more synchronized metrics over time, which is helpful when troubleshooting issues.

SQL Jobs

To view all SQL jobs, click SQL Jobs from the database node view, and you will be redirected to the SQL Jobs actions panel page. To create a new SQL job, click .

You will be redirected to the Create Synchronous SQL Script configuration page. Fill out all the necessary information, then click to save the script.

Name - refers to a descriptive name for the job.
Description - refers to any relevant information about the job being added.
Owner - refers to the user who's adding the new job.
Status - refers to the SQL job status. At this stage of the process, this is set to Initial.
Work Queue - specifies the queue or list where the job is managed and processed. This field contains the name of the Oracle Database.
Schedule - sets the frequency or timing for executing the job. You may choose from one of the schedules previously created in IT-Conductor. See How to Define Operating Schedules for more details.
Planned Start Time - refers to the scheduled start time for the SQL job.
Time Windows Begin - refers to the beginning of the time window during which the SQL job can be executed.
Time Windows End - refers to the end of the time window during which the SQL job can be executed.
Repeat - if checked, it specifies that the job should be repeated or executed only once.
Completion Monitoring - if checked, it enables tracking and reporting on the completion status of the SQL job.
Deliver Log - if checked, it determines if the log or output of the SQL job should be sent or delivered to specified recipients.
SQL - refers to the SQL query or script to be executed as part of the job.
Echo - if checked, it controls whether the executed SQL query or script is echoed back in the job output.
Preserve Formatting - if checked, it specifies whether it maintains the original formatting of the SQL query or script in the job output.
Wrapped - if checked, it indicates if long lines of SQL output should be wrapped or truncated to fit within specified constraints.
Query Timeout - refers to the maximum duration (in seconds) allowed for the execution of the SQL query before it is terminated. By default, this is set to 180 seconds.