Oracle Database Monitoring

Oracle is an object-relational database management system primarily designed for enterprise grid computing. It manages information, applications and provides logical data storage using tablespaces.

Monitoring its performance is essential to oversee database health and quickly identify and fix problematic servers and resource areas.

Know the memory and disk space metrics to make sure your Oracle DB processes have enough resources available.
Monitor response time and find out if there's any service suffering from bad database response times.
Know if the tablespaces are full and whether they require appropriate action to increase their capacity.
Track the number of Oracle sessions to know how busy a server is and ensure their continued functioning.

This document details the monitoring metrics to ensure continued performance and the steps to install the plugin.

Performance Monitoring Metrics

To monitor your entire Oracle database `(oracle.py)`

Buffer Cache Hit Ratio

It is the rate at which Oracle finds the data blocks it needs in memory over the lifetime of an instance. buffer_cache_hit_ratio is critical for databases with an undersized buffer pool.

Disk and Memory Sort Ratio

Use the metric disk_memory_sort_ratio to know the percentage of times a session had to perform a disk or a memory sort. Excessive sort activity can degrade the overall database performance.

Oracle Status

oracle_status helps to notify whether the database is running or not. This helps to monitor business critical metrics, generate alarms and also execute actions automatically when your database goes down.

Response Time

response_time (ms) is the time spent in database operations per transaction. This helps to understand the database’s capacity to process any change in the workload because of resource shortage or contention.

Process Usage Percentage

Use the metric processes_usage to study the maximum number of Operating System (OS) user processes that can simultaneously connect to the Oracle database at the same time. This includes the background processes as well.

Sessions Usage

The sessions_usage in percentage specifies the maximum number of sessions used by the database. As every login creates a session, this metric helps to determine the maximum concurrent users in the system.

Failed Jobs

Use the metric failed_jobs to know the number of jobs failed, either by throwing an error or by abnormally terminating.

Active and Inactive Sessions

The metrics active_sessions and inactive_sessions denotes the number of active and inactive sessions respectively.

Failed Login Count

Use the metric failed_login_count to know the number of failed logon attempts.

Invalid Objects Count

The invalid_objects_count metric shows the invalid objects by owner, which will help reduce subsequent latencies.

PGA Cache Hit Percentage

The metric pga_cache_hit_percentage gives the cache hits of Program Global Area (PGA) in percentage used by the process.

RMAN Failed Backup Count

The rman_failed_backup_count provides the number of failed backups in the RMAN repository.

SQL Response Time

The sql_response_time monitors the response time of the SQL service.

To monitor your tablespaces

Note: If you wish to monitor only the status and usage of a tablespace, use the oracle_tablespace_usage.py plugin.

Tablespace Status and its Usage (in %)

Know your tablespace_status to be either Read Write, Read Only or Offline. tablespace_usage_percent helps to track how data grows in the database and to make sure appropriate provisioning is given at the right time.

Number of Reads & Writes in a Tablespace

The reads and writes represents the number of physical reads and writes respectively. A total of the reads and writes gives the I/O activity for a specific disk.

Free Blocks

The free_blocks metric gives the number of blocks on Oracle’s freelist groups for a table, index, or cluster segment

Used and Free Space (in MB)

The metrics used_space and free_space describes used and free sequential data blocks in a tablespace.

Logging

LOGGING/NOLOGGING helps to control direct path writes to reduce generation of REDO and UNDO. It is one of the varied methods to control the balance between recoverability and performance.

Prerequisites

oracle-instantclient-basic-linux
oracle-instantclient-sdk-linux
cx_Oracle - Python Interface for Oracle Database

Install the following packages: apt-get install python-dev build-essential libaio1
Download instant client for Linux x86-64 from the Oracle website instantclient-basic-linux.x64-12.2.0.1.0.zip instantclient-sdk-linux.x64-12.2.0.1.0.zip
Unzip and extract the downloaded zip files into a folder. For example, mkdir -p /opt/oracle_client
Add environment variables:
- Create a file in /etc/profile.d/oracle.sh and add the following lines: export ORACLE_HOME=/opt/oracle_client/instantclient_12_2 export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME
Create a file in /etc/ld.so.conf.d/oracle.conf and add the following lines /opt/oracle_client/instantclient_12_2
Execute the command sudo ldconfig
Note: Ensure you log out of your server and relogin for the changes to be reflected.
Create a symlink cd $ORACLE_HOME ln -s libclntsh.so.12.2 libclntsh.so
Install cx_Oracle python using pip


              
               pip install cx_Oracle

Note: If you are using Centos/RHEL OS, follow the steps given in this article to install the above modules.

Plugin Installation

Download and install the latest version of the Site24x7 Linux agent in the server where you plan to run the plugin. If it is installed successfully, you will see a Linux server monitor in the Site24x7 Control Panel. This confirms that the agent is able to communicate with our data center.
For monitoring your entire database, download the oracle.py file; to monitor the tablespaces, download the oracle_tablespace_details.py file; to monitor only the usage of your tablespaces, download the oracle_tablespace_usage.py file
Enter the ORACLE_HOST, ORACLE_PORT, ORACLE_USERNAME, ORACLE_PASSWORD and ORACLE_SID under the #Config Section of the plugin script.
Note: If you are monitoring the tablespaces, make sure to enter the name of the tablespace that you wish to monitor in the TABLESPACE_NAME
Note
Ensure the following permissions are given to the user mentioned in the above configuration:
- CONNECT
- SELECT_CATALOG_ROLE (or) grant SELECT permission for all the tables below:
  For 'oracle.py': gv$session, v$pgastat, dba_scheduler_job_log, v$rman_status, dba_audit_trail, dba_objects, v$resource_limit, v$sysmetric, v$sysstat
  
  For 'oracle_tablespace_usage.py' & 'oracle_tablespace_details.py': sys.dba_tablespaces, sys.dba_free_space, sys.dba_data_f, dba_extents
Create distinct folders according to the plugins you have downloaded, under the Site24x7 Linux agent plugin directory '/opt/site24x7/monagent/plugins/' and place the respective plugin file under '/opt/site24x7/monagent/plugins/<plugin folder>/'
- oracle.py plugin under the folder oracle
- oracle_tablespace_details.py under the folder oracle_tablespace_details
- oracle_tablespace_usage.py under the folder oracle_tablespace_usage

The agent will automatically execute the plugin within five minutes and send performance data to the Site24x7 data center.

View Data in the Site24x7 Web Client

Log in to Site24x7 and go to Server> Plugins > Name of Plugin Monitor.
You will be able to view the performance charts on the various metrics for your Oracle server.

Error Handling

S.No	Error Message	Solution
1	cx_Oracle module is not installed	Check in the Prerequisites section and install the Python module
2	Exception while connecting to host	Make sure you have entered valid configuration details (Hostname, Port, SID, Username, Password)
3	Please check the Tablespace Name in the configuration section	Ensure the Tablespace name given in the Configuration Section of the plugin script is valid

Plugin Contribution

Feel free to contribute to our existing plugin and come up with suggestions or feedback on our Community.

Other Plugin Integrations

Out-of-the-box plugins - Monitor your entire app stack with our extensive list of integrations
NGINX and NGINX Plus - Stay ahead of issues occuring within NGINX as well as anywhere else in your web infrastructure
MySQL - Monitor performance metrics of your MySQL databases
Postgres - Proactively monitor the availability and performance of business-crtical Postgres database server
Nagios - Execute thousands of Nagios plugins in Site24x7 without the need of running a Nagios server
Custom Script Monitoring - Create custom Linux and Windows plugins and monitor attributes that you need
GitHub Repository - Check out our 50+ plugin integrations