Oracle Database

Supported OS Linux Windows Mac OS

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

Follow the Oracle Instant Client installation for Linux.
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

Follow the Oracle Windows installation guide to configure your Oracle Instant Client.
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:

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.

Note: Oracle Real Application Cluster (RAC) customers must configure the Agent for each RAC node, because the Agent collects information from every node separately by querying V$ views. The Agent doesn’t query any GV$ views to avoid generating interconnect traffic.

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:

Parameter	Description
`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 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.