Oracle

Supported OS Linux Windows Mac OS

Integration version5.2.0

Oracle Dashboard

Overview

The Oracle integration provides health and performance metrics for your Oracle database in near real-time. Visualize these metrics with the provided dashboard and create monitors to alert your team on Oracle database states.

Enable Database Monitoring (DBM) for enhanced insights into query performance and database health. In addition to the standard integration features, Datadog DBM provides query-level metrics, live and historical query snapshots, wait event analysis, database load, query explain plans, and blocking query insights.

Setup

Installation

Prerequisite

To use the Oracle integration you can either use the native client (no additional install steps required), or the Oracle Instant Client.

Oracle Instant Client

Skip this step if you are not using Instant Client.

    Linux

    1. Follow the Oracle Instant Client installation for Linux.

    2. Verify that the Instant Client Basic package is installed. Find it on Oracle’s download page.

      After the Instant Client libraries are installed, ensure the runtime linker can find the libraries, for example:

      # Put the library location in the /etc/datadog-agent/environment file.

echo "LD_LIBRARY_PATH=/u01/app/oracle/product/instantclient_19" \
>> /etc/datadog-agent/environment
    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.

      • The Instant Client Basic package from Oracle’s download page is installed, and is available to all users on the given machine (for example, C:\oracle\instantclient_19).

      • The PATH environment variable contains the directory with the Instant Client (for example, C:\oracle\instantclient_19).

    Datadog user creation

      Multi-tenant
      Create user

      Create a read-only login to connect to your server and grant the required permissions:

      CREATE USER c##datadog IDENTIFIED BY &password CONTAINER = ALL ;

ALTER USER c##datadog SET CONTAINER_DATA=ALL CONTAINER=CURRENT;
      Grant permissions

      Log on as sysdba, and grant the following permissions:

      grant create session to c##datadog ;
grant select on v_$session to c##datadog ;
grant select on v_$database to c##datadog ;
grant select on v_$containers to c##datadog;
grant select on v_$sqlstats to c##datadog ;
grant select on v_$instance to c##datadog ;
grant select on dba_feature_usage_statistics to c##datadog ;
grant select on V_$SQL_PLAN_STATISTICS_ALL to c##datadog ;
grant select on V_$PROCESS to c##datadog ;
grant select on V_$SESSION to c##datadog ;
grant select on V_$CON_SYSMETRIC to c##datadog ;
grant select on CDB_TABLESPACE_USAGE_METRICS to c##datadog ;
grant select on CDB_TABLESPACES to c##datadog ;
grant select on V_$SQLCOMMAND to c##datadog ;
grant select on V_$DATAFILE to c##datadog ;
grant select on V_$SYSMETRIC to c##datadog ;
grant select on V_$SGAINFO to c##datadog ;
grant select on V_$PDBS to c##datadog ;
grant select on CDB_SERVICES to c##datadog ;
grant select on V_$OSSTAT to c##datadog ;
grant select on V_$PARAMETER to c##datadog ;
grant select on V_$SQLSTATS to c##datadog ;
grant select on V_$CONTAINERS to c##datadog ;
grant select on V_$SQL_PLAN_STATISTICS_ALL to c##datadog ;
grant select on V_$SQL to c##datadog ;
grant select on V_$PGASTAT to c##datadog ;
grant select on v_$asm_diskgroup to c##datadog ;
grant select on v_$rsrcmgrmetric to c##datadog ;
grant select on v_$dataguard_config to c##datadog ;
grant select on v_$dataguard_stats to c##datadog ;
grant select on v_$transaction to c##datadog;
grant select on v_$locked_object to c##datadog;
grant select on dba_objects to c##datadog;
grant select on cdb_data_files to c##datadog;
grant select on dba_data_files to c##datadog;

      If you configured custom queries that run on a pluggable database (PDB), you must grant the set container privilege to the C##DATADOG user:

      connect / as sysdba
alter session set container = your_pdb ;
grant set container to c##datadog ;
      Non-CDB
      Create user

      Create a read-only login to connect to your server and grant the required permissions:

      CREATE USER datadog IDENTIFIED BY &password ;
      Grant permissions

      Log on as sysdba, and grant the following permissions:

      grant create session to datadog ;
grant select on v_$session to datadog ;
grant select on v_$database to datadog ;
grant select on v_$containers to datadog;
grant select on v_$sqlstats to datadog ;
grant select on v_$instance to datadog ;
grant select on dba_feature_usage_statistics to datadog ;
grant select on V_$SQL_PLAN_STATISTICS_ALL to datadog ;
grant select on V_$PROCESS to datadog ;
grant select on V_$SESSION to datadog ;
grant select on V_$CON_SYSMETRIC to datadog ;
grant select on CDB_TABLESPACE_USAGE_METRICS to datadog ;
grant select on CDB_TABLESPACES to datadog ;
grant select on V_$SQLCOMMAND to datadog ;
grant select on V_$DATAFILE to datadog ;
grant select on V_$SYSMETRIC to datadog ;
grant select on V_$SGAINFO to datadog ;
grant select on V_$PDBS to datadog ;
grant select on CDB_SERVICES to datadog ;
grant select on V_$OSSTAT to datadog ;
grant select on V_$PARAMETER to datadog ;
grant select on V_$SQLSTATS to datadog ;
grant select on V_$CONTAINERS to datadog ;
grant select on V_$SQL_PLAN_STATISTICS_ALL to datadog ;
grant select on V_$SQL to datadog ;
grant select on V_$PGASTAT to datadog ;
grant select on v_$asm_diskgroup to datadog ;
grant select on v_$rsrcmgrmetric to datadog ;
grant select on v_$dataguard_config to datadog ;
grant select on v_$dataguard_stats to datadog ;
grant select on v_$transaction to datadog;
grant select on v_$locked_object to datadog;
grant select on dba_objects to datadog;
grant select on cdb_data_files to datadog;
grant select on dba_data_files to datadog;
      RDS
      Create user

      Create a read-only login to connect to your server and grant the required permissions:

      CREATE USER datadog IDENTIFIED BY your_password ;
      Grant permissions
      grant create session to datadog ;
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$SESSION','DATADOG','SELECT',p_grant_option => false); 
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$DATABASE','DATADOG','SELECT',p_grant_option => false); 
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$CONTAINERS','DATADOG','SELECT',p_grant_option => false); 
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$SQLSTATS','DATADOG','SELECT',p_grant_option => false); 
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$SQL','DATADOG','SELECT',p_grant_option => false); 
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$INSTANCE','DATADOG','SELECT',p_grant_option => false); 
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$SQL_PLAN_STATISTICS_ALL','DATADOG','SELECT',p_grant_option => false); 
exec rdsadmin.rdsadmin_util.grant_sys_object('DBA_FEATURE_USAGE_STATISTICS','DATADOG','SELECT',p_grant_option => false); 
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$PROCESS','DATADOG','SELECT',p_grant_option => false); 
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$SESSION','DATADOG','SELECT',p_grant_option => false); 
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$CON_SYSMETRIC','DATADOG','SELECT',p_grant_option => false); 
exec rdsadmin.rdsadmin_util.grant_sys_object('CDB_TABLESPACE_USAGE_METRICS','DATADOG','SELECT',p_grant_option => false); 
exec rdsadmin.rdsadmin_util.grant_sys_object('CDB_TABLESPACES','DATADOG','SELECT',p_grant_option => false); 
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$SQLCOMMAND','DATADOG','SELECT',p_grant_option => false);
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$DATAFILE','DATADOG','SELECT',p_grant_option => false);
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$SGAINFO','DATADOG','SELECT',p_grant_option => false);
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$SYSMETRIC','DATADOG','SELECT',p_grant_option => false);
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$PDBS','DATADOG','SELECT',p_grant_option => false);
exec rdsadmin.rdsadmin_util.grant_sys_object('CDB_SERVICES','DATADOG','SELECT',p_grant_option => false);
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$OSSTAT','DATADOG','SELECT',p_grant_option => false);
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$PARAMETER','DATADOG','SELECT',p_grant_option => false);
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$SQLSTATS','DATADOG','SELECT',p_grant_option => false);
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$CONTAINERS','DATADOG','SELECT',p_grant_option => false);
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$SQL_PLAN_STATISTICS_ALL','DATADOG','SELECT',p_grant_option => false);
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$SQL','DATADOG','SELECT',p_grant_option => false);
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$PGASTAT','DATADOG','SELECT',p_grant_option => false);
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$ASM_DISKGROUP','DATADOG','SELECT',p_grant_option => false);
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$RSRCMGRMETRIC','DATADOG','SELECT',p_grant_option => false);
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$DATAGUARD_CONFIG','DATADOG','SELECT',p_grant_option => false);
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$DATAGUARD_STATS','DATADOG','SELECT',p_grant_option => false);
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$TRANSACTION','DATADOG','SELECT',p_grant_option => false);
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$LOCKED_OBJECT','DATADOG','SELECT',p_grant_option => false);
exec rdsadmin.rdsadmin_util.grant_sys_object('DBA_OBJECTS','DATADOG','SELECT',p_grant_option => false);
exec rdsadmin.rdsadmin_util.grant_sys_object('CDB_DATA_FILES','DATADOG','SELECT',p_grant_option => false);
exec rdsadmin.rdsadmin_util.grant_sys_object('DBA_DATA_FILES','DATADOG','SELECT',p_grant_option => false);
      Oracle Autonomous Database
      Create user

      Create a read-only login to connect to your server and grant the required permissions:

      CREATE USER datadog IDENTIFIED BY your_password ;
      Grant permissions
      grant create session to datadog ;
grant select on v$session to datadog ;
grant select on v$database to datadog ;
grant select on v$containers to datadog;
grant select on v$sqlstats to datadog ;
grant select on v$instance to datadog ;
grant select on dba_feature_usage_statistics to datadog ;
grant select on V$SQL_PLAN_STATISTICS_ALL to datadog ;
grant select on V$PROCESS to datadog ;
grant select on V$SESSION to datadog ;
grant select on V$CON_SYSMETRIC to datadog ;
grant select on CDB_TABLESPACE_USAGE_METRICS to datadog ;
grant select on CDB_TABLESPACES to datadog ;
grant select on V$SQLCOMMAND to datadog ;
grant select on V$DATAFILE to datadog ;
grant select on V$SYSMETRIC to datadog ;
grant select on V$SGAINFO to datadog ;
grant select on V$PDBS to datadog ;
grant select on CDB_SERVICES to datadog ;
grant select on V$OSSTAT to datadog ;
grant select on V$PARAMETER to datadog ;
grant select on V$SQLSTATS to datadog ;
grant select on V$CONTAINERS to datadog ;
grant select on V$SQL_PLAN_STATISTICS_ALL to datadog ;
grant select on V$SQL to datadog ;
grant select on V$PGASTAT to datadog ;
grant select on v$asm_diskgroup to datadog ;
grant select on v$rsrcmgrmetric to datadog ;
grant select on v$dataguard_config to datadog ;
grant select on v$dataguard_stats to datadog ;
grant select on v$transaction to datadog;
grant select on v$locked_object to datadog;
grant select on dba_objects to datadog;
grant select on cdb_data_files to datadog;
grant select on dba_data_files to datadog;

      Configuration

      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>

      Note: For the Agent releases between 7.50.1 (inclusive) and 7.53.0 (exclusive), the configuration subdirectory is oracle-dbm.d. For all other Agent releases, the configuration directory is oracle.d.

      1. Restart the Agent.

      Connect to Oracle through TCPS

      To connect to Oracle through TCPS (TCP with SSL), uncomment the protocol configuration option and select TCPS. Update the server option to set the TCPS server to monitor.

      ```yaml
init_config:

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

    ## @param service_name - string - required
    ## The Oracle Database service name. To view the services available on your server,
    ## run the following query:
    #
    service_name: "<SERVICE_NAME>"

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

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

    ## @param protocol - string - optional - default: TCP
    ## The protocol to connect to the Oracle Database Server. Valid protocols include TCP and TCPS.
    ##
    #
    protocol: TCPS
```

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

      ParameterDescription
      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 used 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:

      - 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.active_sessions
      (gauge)      		number of active sessions
      oracle.buffer_cachehit_ratio
      (gauge)      		Ratio of buffer cache hits
      Shown as percent
      oracle.cache_blocks_corrupt
      (gauge)      		corrupt cache blocks
      Shown as block
      oracle.cache_blocks_lost
      (gauge)      		lost cache blocks
      Shown as block
      oracle.cursor_cachehit_ratio
      (gauge)      		Ratio of cursor cache hits
      Shown as percent
      oracle.database_wait_time_ratio
      (gauge)      		memory sorts per second
      Shown as percent
      oracle.disk_sorts
      (gauge)      		disk sorts per second
      Shown as operation
      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.library_cachehit_ratio
      (gauge)      		Ratio of library cache hits
      Shown as percent
      oracle.logons
      (gauge)      		number of logon attempts
      oracle.long_table_scans
      (gauge)      		number of long table scans per sec
      Shown as scan
      oracle.memory_sorts_ratio
      (gauge)      		memory sorts ratio
      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.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.process.pga_used_memory
      (gauge)      		PGA memory used by process
      Shown as byte
      oracle.rows_per_sort
      (gauge)      		rows per sort
      Shown as row
      oracle.service_response_time
      (gauge)      		service response time
      Shown as second
      oracle.session_count
      (gauge)      		session count
      oracle.session_limit_usage
      (gauge)      		session limit usage
      Shown as percent
      oracle.shared_pool_free
      (gauge)      		shared pool free memory %
      Shown as percent
      oracle.sorts_per_user_call
      (gauge)      		sorts per user call
      oracle.tablespace.in_use
      (gauge)      		tablespace in-use
      Shown as percent
      oracle.tablespace.offline
      (gauge)      		tablespace offline
      oracle.tablespace.size
      (gauge)      		tablespace size
      Shown as byte
      oracle.tablespace.used
      (gauge)      		tablespace used
      Shown as byte
      oracle.temp_space_used
      (gauge)      		temp space used
      Shown as byte
      oracle.user_rollbacks
      (gauge)      		number of user rollbacks
      Shown as operation

      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

      Need help? Contact Datadog support.