Oracle

Oracle

Agent Check Agent Check

Supported OS Linux Mac OS Windows

Integrationv3.5.0

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 (Linux only). Due to licensing restrictions, these libraries are not included in the Datadog Agent, but can be downloaded directly from Oracle.

Oracle Instant Client
Linux
  1. Follow the Oracle Instant Client installation for Linux.

  2. Verify the following:

    • Both the Instant Client Basic and SDK packages are installed. Find them on Oracle’s download page.

      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
      
    • Both packages are decompressed into a single directory that is available to all users on the given machine (for example, /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
      
Windows
  1. Follow the Oracle Windows installation guide to configure your Oracle Instant Client.

  2. Verify the following:

    • The Microsoft Visual Studio 2017 Redistributable or the appropriate version is installed for the Oracle Instant Client.

    • Both the Instant Client Basic and SDK packages from Oracle’s download page are installed.

    • Both packages are extracted into a single directory that is available to all users on the given machine (for example, C:\oracle).

JDBC driver

NOTE: This method only works on Linux.

Java 8 or higher is required on your system for JPype, one of the libraries used by the Agent when using JDBC driver.

Once it is installed, complete the following steps:

  1. Download the JDBC Driver JAR file.
  2. Add the path to the downloaded file in your $CLASSPATH or the check configuration file under jdbc_driver_path (see the sample oracle.yaml).

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;
Oracle 12c or 19c

Log in to the root database as an Administrator to create a datadog user and grant permissions:

alter session set container = cdb$root;
CREATE USER c##datadog IDENTIFIED BY password CONTAINER=ALL;
GRANT CREATE SESSION TO c##datadog CONTAINER=ALL;
Grant select any dictionary to c##datadog container=all;
GRANT SELECT ON GV_$PROCESS TO c##datadog CONTAINER=ALL;
GRANT SELECT ON gv_$sysmetric TO c##datadog CONTAINER=ALL;

Configuration

Host

To configure this check for an Agent running on a host:

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

    init_config:
    
    instances:
      ## @param server - string - required
      ## The IP address or hostname of the Oracle Database Server.
      #
      - server: localhost:1521
    
       ## @param service_name - string - required
       ## The Oracle Database service name. To view the services available on your server,
       ## run the following query: `SELECT value FROM v$parameter WHERE name='service_names'`
       #
       service_name: <SERVICE_NAME>
    
       ## @param username - string - required
       ## The username for the Datadog user account.
       #
       username: <USERNAME>
    
       ## @param password - string - required
       ## The password for the Datadog user account.
       #
       password: <PASSWORD>
    
  2. Restart the Agent.

Only custom queries

To skip default metric checks for an instance and only run custom queries with an existing metrics gathering user, insert the tag only_custom_queries with a value of true. This allows a configured instance of the Oracle integration to skip the system, process, and tablespace metrics from running, and allows custom queries to be run without having the permissions described in the Datadog user creation section. If this configuration entry is omitted, the user you specify is required to have those table permissions to run a custom query.

init_config:

instances:
  ## @param server - string - required
  ## The IP address or hostname of the Oracle Database Server.
  #
  - server: localhost:1521

    ## @param service_name - string - required
    ## The Oracle Database service name. To view the services available on your server,
    ## run the following query:
    ## `SELECT value FROM v$parameter WHERE name='service_names'`
    #
    service_name: "<SERVICE_NAME>"

    ## @param user - string - required
    ## The username for the user account.
    #
    user: <USER>

    ## @param password - string - required
    ## The password for the user account.
    #
    password: "<PASSWORD>"

    ## @param only_custom_queries - string - optional
    ## Set this parameter to any value if you want to only run custom
    ## queries for this instance.
    #
    only_custom_queries: true

Containerized

For containerized environments, see the Autodiscovery Integration Templates for guidance on applying the parameters below.

ParameterValue
<INTEGRATION_NAME>oracle
<INIT_CONFIG>blank or {}
<INSTANCE_CONFIG>{"server": "%%host%%:1521", "service_name":"<SERVICE_NAME>", "username":"datadog", "password":"<PASSWORD>"}

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 three parameters:

ParameterDescription
metric_prefixThis is what each metric starts with.
queryThis is the SQL to execute. It can be a simple statement or a multi-line script. All rows of the result are evaluated.
columnsThis is a list representing each column, ordered sequentially from left to right. There are two 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.

Example

Create a query configuration to help identify database locks:

  1. To include a custom query, modify conf.d\oracle.d\conf.yaml. Uncomment the custom_queries block, add the required queries and columns, and restart the Agent.
  init_config:
  instances:
      - server: localhost:1521
        service_name: orcl11g.us.oracle.com
        user: datadog
        password: xxxxxxx
        jdbc_driver_path: /u01/app/oracle/product/11.2/dbhome_1/jdbc/lib/ojdbc6.jar
        tags:
          - db:oracle
        custom_queries:
          - metric_prefix: oracle.custom_query.locks
            query: |
              select blocking_session, username, osuser, sid, serial# as serial, wait_class, seconds_in_wait
              from v_$session
              where blocking_session is not NULL order by blocking_session              
            columns:
              - name: blocking_session
                type: gauge
              - name: username
                type: tag
              - name: osuser
                type: tag
              - name: sid
                type: tag
              - name: serial
                type: tag
              - name: wait_class
                type: tag
              - name: seconds_in_wait
                type: tag
  1. To access v_$session, give permission to DATADOG and test the permissions.
SQL> grant select on sys.v_$session to datadog;

##connecting with the DD user to validate the access:


SQL> show user
USER is "DATADOG"


##creating a synonym to make the view visible
SQL> create synonym datadog.v_$session for sys.v_$session;


Synonym created.


SQL> select blocking_session,username,osuser, sid, serial#, wait_class, seconds_in_wait from v_$session
where blocking_session is not NULL order by blocking_session;
  1. Once configured, you can create a monitor based on oracle.custom_query.locks metrics.

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 percent
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 percent
oracle.tablespace.offline
(gauge)
tablespace offline

Events

The Oracle Database check does not include any events.

Service Checks

oracle.can_connect
Returns OK if the integration can connect to the oracle database, CRITICAL otherwise
Statuses: ok, critical

oracle.can_query
Returns OK if the integration can run all the queries, CRITICAL otherwise
Statuses: ok, critical

Troubleshooting

Common problems

Oracle Instant Client

  • Verify that both the Oracle Instant Client and SDK files are located in the same directory. The structure of the directory should look similar:
|____sdk/
|____network/
|____libociei.dylib
|____libocci.dylib
|____libocci.dylib.10.1
|____adrci
|____uidrvci
|____libclntsh.dylib.19.1
|____ojdbc8.jar
|____BASIC_README
|____liboramysql19.dylib
|____libocijdbc19.dylib
|____libocci.dylib.19.1
|____libclntsh.dylib
|____xstreams.jar
|____libclntsh.dylib.10.1
|____libnnz19.dylib
|____libclntshcore.dylib.19.1
|____libocci.dylib.12.1
|____libocci.dylib.18.1
|____libclntsh.dylib.11.1
|____BASIC_LICENSE
|____SDK_LICENSE
|____libocci.dylib.11.1
|____libclntsh.dylib.12.1
|____libclntsh.dylib.18.1
|____ucp.jar
|____genezi
|____SDK_README

Linux
  • See further Linux installation documentation on Oracle.
Windows
  • Verify the Microsoft Visual Studio Redistributable requirement is met for your version. See the Windows downloads page for more details.
  • See further Windows installation documentation on Oracle.

JDBC driver (Linux only)

  • If you encounter a JVMNotFoundException:

    JVMNotFoundException("No JVM shared library file ({jpype._jvmfinder.JVMNotFoundException: No JVM shared library file (libjvm.so) found. Try setting up the JAVA_HOME environment variable properly.})"
    
    • Ensure that the JAVA_HOME environment variable is set and pointing to the correct directory.
    • Add the environment variable to /etc/environment:
      JAVA_HOME=/path/to/java
      
    • Then restart the Agent.
  • If you encounter this error Unsupported major.minor version 52.0 it means that you’re running a Java version that is too old. You need to either update your system Java or additionally install a newer version and point your JAVA_HOME variable to the new install as explained above.

  • Verify your environment variables are set correctly by running the following command from the Agent. Ensure the displayed output matches the correct value.

      sudo -u dd-agent -- /opt/datadog-agent/embedded/bin/python -c "import os; print("JAVA_HOME:{}".format(os.environ.get("JAVA_HOME")))"
    

Need help? Contact Datadog support.