Join us at the Dash conference! July 16-17, NYC

Oracle

Agent Check Agent Check

Supported OS: Linux Mac OS Windows

Oracle Dashboard

Overview

Get metrics from Oracle Database servers in real time to visualize and monitor availability and performance.

Setup

Installation

Prerequisite

To use the Oracle integration, either install the Oracle Instant Client libraries, or download the Oracle JDBC Driver. Due to licensing restrictions, these libraries are not included in the Datadog Agent, but can be downloaded directly from Oracle.

JDBC Driver
Oracle Instant Client

The Oracle check requires either access to the cx_Oracle Python module, or the Oracle JDBC Driver:

  1. Go to the download page and install both the Instant Client Basic and SDK packages.

    If you are using Linux, after the Instant Client libraries are installed ensure the runtime linker can find the libraries. For example, using ldconfig:

    # Put the library location in an ld configuration file.
    
    sudo sh -c "echo /usr/lib/oracle/12.2/client64/lib > \
        /etc/ld.so.conf.d/oracle-instantclient.conf"
    
    # Update the bindings.
    
    sudo ldconfig
    
  2. Decompress those libraries in a given directory available to all users on the given machine (i.e. /opt/oracle):

    mkdir -p /opt/oracle/ && cd /opt/oracle/
    unzip /opt/oracle/instantclient-basic-linux.x64-12.1.0.2.0.zip
    unzip /opt/oracle/instantclient-sdk-linux.x64-12.1.0.2.0.zip
    
  3. Update your LD_LIBRARY_PATH to include the location of the Instant Client libraries when starting/restarting the agent:

  export LD_LIBRARY_PATH=/opt/oracle/instantclient/lib:$LD_LIBRARY_PATH

Note: Agent 6 uses Upstart or systemd to orchestrate the datadog-agent service. Environment variables may need to be added to the service configuration files at the default locations of /etc/init/datadog-agent.conf (Upstart) or /lib/systemd/system/datadog-agent.service (systemd). See documentation on Upstart or systemd for more information on how to configure these settings.

The following is an example of adding LD_LIBRARY_PATH to the Datadog Agent service configuration files (/int/init/datadog-agent.conf) on a system using Upstart.

description "Datadog Agent"

start on started networking
stop on runlevel [!2345]

respawn
respawn limit 10 5
normal exit 0

# Logging to console from the Agent is disabled since the Agent already logs using file or
# syslog depending on its configuration. We make Upstart log what the process still outputs in order
# to log panics/crashes to /var/log/upstart/datadog-agent.log
console log
env DD_LOG_TO_CONSOLE=false
env LD_LIBRARY_PATH=/usr/lib/oracle/11.2/client64/lib/

setuid dd-agent

script
  exec /opt/datadog-agent/bin/agent/agent start -p /opt/datadog-agent/run/agent.pid
end script

post-stop script
  rm -f /opt/datadog-agent/run/agent.pid
end script

Datadog User creation

Create a read-only datadog user with proper access to your Oracle Database Server. Connect to your Oracle database with an administrative user (e.g. SYSDBA or SYSOPER) and run:

-- Enable Oracle Script.
ALTER SESSION SET "_ORACLE_SCRIPT"=true;

-- Create the datadog user. Replace the password placeholder with a secure password.
CREATE USER datadog IDENTIFIED BY <PASSWORD>;

-- Grant access to the datadog user.
GRANT CONNECT TO datadog;
GRANT SELECT ON GV_$PROCESS TO datadog;
GRANT SELECT ON gv_$sysmetric TO datadog;
GRANT SELECT ON sys.dba_data_files TO datadog;
GRANT SELECT ON sys.dba_tablespaces TO datadog;
GRANT SELECT ON sys.dba_tablespace_usage_metrics TO datadog;

Note: If you’re using Oracle 11g, there’s no need to run the following line:

ALTER SESSION SET "_ORACLE_SCRIPT"=true;

Configuration

Edit the oracle.d/conf.yaml file, in the conf.d/ folder at the root of your Agent’s configuration directory to point to your server and port, set the masters to monitor. See the sample oracle.d/conf.yaml for all available configuration options.

Validation

Run the Agent’s status subcommand and look for oracle under the Checks section.

Custom Query

Providing custom queries is also supported. Each query must have 3 parameters:

Parameter Description
metric_prefix   This is what each metric starts with. 
query   This is the SQL to execute. It can be a simple statement or a multi-line script. All rows of the result are evaluated.
columns This is a list representing each column, ordered sequentially from left to right. There are 2 required pieces of data:
a. type - This is the submission method (gauge, count, etc.).
b. name - This is the suffix to append to the metric_prefix in order to form the full metric name. If type is tag, this column is instead considered as a tag which is applied to every metric collected by this particular query.

Optionally use the tags parameter to apply a list of tags to each metric collected.

The following:

self.gauge('oracle.custom_query.metric1', value, tags=['tester:oracle', 'tag1:value'])
self.count('oracle.custom_query.metric2', value, tags=['tester:oracle', 'tag1:value'])

is what the following example configuration would become:

- metric_prefix: oracle.custom_query
  query: |  # Use the pipe if you require a multi-line script.
   SELECT columns
   FROM tester.test_table
   WHERE conditions
  columns:
  # Put this for any column you wish to skip:
  - {}
  - name: metric1
    type: gauge
  - name: tag1
    type: tag
  - name: metric2
    type: count
  tags:
  - tester:oracle

See the sample oracle.d/conf.yaml for all available configuration options.

Data Collected

Metrics

oracle.buffer_cachehit_ratio
(gauge)
Ratio of buffer cache hits
shown as fraction
oracle.cursor_cachehit_ratio
(gauge)
Ratio of cursor cache hits
shown as fraction
oracle.library_cachehit_ratio
(gauge)
Ratio of library cache hits
shown as fraction
oracle.shared_pool_free
(gauge)
shared pool free memory %
shown as percent
oracle.physical_reads
(gauge)
physical reads per sec
shown as read
oracle.physical_writes
(gauge)
physical writes per sec
shown as write
oracle.enqueue_timeouts
(gauge)
enqueue timeouts per sec
shown as timeout
oracle.gc_cr_block_received
(gauge)
GC CR block received
shown as block
oracle.cache_blocks_corrupt
(gauge)
corrupt cache blocks
shown as block
oracle.cache_blocks_lost
(gauge)
lost cache blocks
shown as block
oracle.logons
(gauge)
number of logon attempts
oracle.active_sessions
(gauge)
number of active sessions
oracle.long_table_scans
(gauge)
number of long table scans per sec
shown as scan
oracle.service_response_time
(gauge)
service response time
shown as second
oracle.user_rollbacks
(gauge)
number of user rollbacks
shown as operation
oracle.sorts_per_user_call
(gauge)
sorts per user call
oracle.rows_per_sort
(gauge)
rows per sort
shown as row
oracle.disk_sorts
(gauge)
disk sorts per second
shown as operation
oracle.memory_sorts_ratio
(gauge)
memory sorts ratio
shown as fraction
oracle.database_wait_time_ratio
(gauge)
memory sorts per second
shown as fraction
oracle.session_limit_usage
(gauge)
session limit usage
shown as percent
oracle.session_count
(gauge)
session count
oracle.process.pga_used_memory
(gauge)
PGA memory used by process
shown as byte
oracle.process.pga_allocated_memory
(gauge)
PGA memory allocated by process
shown as byte
oracle.process.pga_freeable_memory
(gauge)
PGA memory freeable by process
shown as byte
oracle.process.pga_maximum_memory
(gauge)
PGA maximum memory ever allocated by process
shown as byte
oracle.temp_space_used
(gauge)
temp space used
shown as byte
oracle.tablespace.used
(gauge)
tablespace used
shown as byte
oracle.tablespace.size
(gauge)
tablespace size
shown as byte
oracle.tablespace.in_use
(gauge)
tablespace in-use
shown as fraction
oracle.tablespace.offline
(gauge)
tablespace offline

Events

The Oracle Database check does not include any events.

Service Checks

oracle.can_connect Verifies the database is available and accepting connections.

Troubleshooting

Need help? Contact Datadog support.


Mistake in the docs? Feel free to contribute!