Oracle Database Monitoring

Analyze your database performance, usage and availability at scale to enhance resource efficiency and optimize end-user experience problems.

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.

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

Ensure the following modules are installed to connect to the Oracle database:
  • oracle-instantclient-basic-linux
  • oracle-instantclient-sdk-linux
  • cx_Oracle - Python Interface for Oracle Database

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.
  • Download the plugin files from our GitHub repository
    • For monitoring your entire database, download the oracle.py" file
      wget https://raw.githubusercontent.com/site24x7/plugins/master/oracle/oracle.py
    • To know about your Oracle tablespaces, download the oracle_tablespace_details.py file
      wget https://raw.githubusercontent.com/site24x7/plugins/master/oracle_tablespace_details/oracle_tablespace_details.py
    • If you wish to monitor only the usage of your tablespaces, download the oracle_tablespace_usage.py file
      wget https://raw.githubusercontent.com/site24x7/plugins/master/oracle_tablespace_usage/oracle_tablespace_usage.py
  • To enable monitoring, enter details on your ORACLE_HOST, ORACLE_PORT, ORACLE_USERNAME, ORACLE_PASSWORD and ORACLE_SID found under the Config Section of the plugin script.
    For the plugins that monitor your tablespace details and its usage, enter the name of the tablespace that you wish to monitor in the TABLESPACE_NAME
  • 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

  1. Login and go to Server > Plugins > Name of Plugin Monitor
  2. You will be able to view the performance charts on the various metrics for your 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.

Related Articles

  • Out-of-the-box plugins - Monitor your entire app stack with our extensive list of integrations
  • Server Monitoring - Stay on top of server outages and performance issues
  • 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
Trusted

World's leading companies

Client

Don't have a Site24x7 account? Sign up now!