--- title: Configuring the Oracle Integration on Agent Versions Lower than 7.50.1 description: Datadog, the leading service for cloud-scale monitoring. breadcrumbs: >- Docs > Integrations > Integration Guides > Configuring the Oracle Integration on Agent Versions Lower than 7.50.1 --- # Configuring the Oracle Integration on Agent Versions Lower than 7.50.1 ## Overview{% #overview %} This guide describes setting up the Oracle integration on versions of the Datadog Agent lower than 7.50.1. For more information on the Oracle integration, including setting it up on newer Agent versions, see the [Oracle integration documentation](https://docs.datadoghq.com/integrations/oracle/?tab=linux). ## Setup{% #setup %} ### Installation{% #installation %} #### Prerequisite{% #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{% #oracle-instant-client %} {% tab title="Linux" %} 1. Follow the [Oracle Instant Client installation for Linux](https://docs.oracle.com/en/database/oracle/oracle-database/21/lacli/install-instant-client-using-zip.html). 1. Verify the following: - Both the *Instant Client Basic* and *SDK* packages are installed. Find them on Oracle's [download page](https://www.oracle.com/technetwork/database/features/instant-client/index.htm). After the Instant Client libraries are installed, ensure the runtime linker can find the libraries. For example, using `ldconfig`: ```shell # 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`): ```shell 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 ``` {% /tab %} {% tab title="Windows" %} 1. Follow the [Oracle Windows installation guide](https://www.oracle.com/database/technologies/instant-client/winx64-64-downloads.html#ic_winx64_inst) to configure your Oracle Instant Client. 1. Verify the following: - The [Microsoft Visual Studio 2017 Redistributable](https://support.microsoft.com/en-us/topic/the-latest-supported-visual-c-downloads-2647da03-1eea-4433-9aff-95f26a218cc0) or the appropriate version is installed for the Oracle Instant Client. - Both the *Instant Client Basic* and *SDK* packages from Oracle's [download page][18] are installed. - Both packages are extracted into a single directory that is available to all users on the given machine (for example, `C:\oracle`). {% /tab %} ##### JDBC driver{% #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](https://www.oracle.com/technetwork/database/application-development/jdbc/downloads/index.html) JAR file. 1. Add the path to the downloaded file in your `$CLASSPATH` or the check configuration file under `jdbc_driver_path` (see the [sample oracle.yaml](https://github.com/DataDog/integrations-core/blob/master/oracle/datadog_checks/oracle/data/conf.yaml.example)). #### Datadog user creation{% #datadog-user-creation %} {% tab title="Standalone" %} Create a read-only `datadog` user with proper access to your Oracle Database Server. Connect to your Oracle database with an administrative user, such as `SYSDBA` or `SYSOPER`, and run: ```text -- 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 ; -- 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: ```text ALTER SESSION SET "_ORACLE_SCRIPT"=true; ``` {% /tab %} {% tab title="Multi-tenant" %} ##### Oracle 12c or 19c{% #oracle-12c-or-19c %} Log in to the root database as an Administrator to create a `datadog` user and grant permissions: ```text 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; ``` {% /tab %} ### Configuration{% #configuration %} {% tab title="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](https://docs.datadoghq.com/agent/guide/agent-configuration-files/#agent-configuration-directory). Update the `server` and `port` to set the masters to monitor. See the [sample oracle.d/conf.yaml](https://github.com/DataDog/integrations-core/blob/master/oracle/datadog_checks/oracle/data/conf.yaml.example) for all available configuration options. ```yaml 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: ## @param username - string - required ## The username for the Datadog user account. # username: ## @param password - string - required ## The password for the Datadog user account. # password: ``` 1. [Restart the Agent](https://docs.datadoghq.com/agent/guide/agent-commands/#start-stop-and-restart-the-agent). #### Only custom queries{% #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 prevent 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 must have those table permissions to run a custom query. ```yaml 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: "" ## @param username - string - required ## The username for the user account. # username: ## @param password - string - required ## The password for the user account. # 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 ``` #### Connect to Oracle through TCPS{% #connect-to-oracle-through-tcps %} 1. 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: ## `SELECT value FROM v$parameter WHERE name='service_names'` # service_name: "" ## @param username - string - required ## The username for the user account. # username: ## @param password - string - required ## The password for the user account. # password: "" ## @param protocol - string - optional - default: TCP ## The protocol to connect to the Oracle Database Server. Valid protocols include TCP and TCPS. ## ## When connecting to Oracle Database via JDBC, `jdbc_truststore` and `jdbc_truststore_type` are required. ## More information can be found from Oracle Database's whitepaper: ## ## https://www.oracle.com/technetwork/topics/wp-oracle-jdbc-thin-ssl-130128.pdf # protocol: TCPS ``` 1. Update the `sqlnet.ora`, `listener.ora`, and `tnsnames.ora` to allow TCPS connections on your Oracle Database. ##### TCPS through Oracle without JDBC{% #tcps-through-oracle-without-jdbc %} If you are not using JDBC, verify that the Datadog Agent is able to connect to your database. Use the `sqlplus` command line tool with the information inputted in your configuration options: ```shell sqlplus /@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCPS)(HOST=)(PORT=))(SERVICE_NAME=))) ``` When using the [Oracle Instant Client](https://python-oracledb.readthedocs.io/en/latest/user_guide/connection_handling.html#install-the-wallet-and-network-configuration-files) connection, move three files to the `network/admin` directory of the client libraries used by your application: - `tnsnames.ora`: Maps net service names used for application connection strings to your database services. - `sqlnet.ora`: Configures Oracle Network settings. - `cwallet.sso`: Enables SSL or TLS connections. Keep this file secure. ##### TCPS through JDBC{% #tcps-through-jdbc %} If you are connecting to Oracle Database using JDBC, you also need to specify `jdbc_truststore_path`, `jdbc_truststore_type`, and `jdbc_truststore_password` (optional) if there is a password on the truststore. **Note**: `SSO` truststores don't require passwords. ```yaml # In the `instances:` section ... ## @param jdbc_truststore_path - string - optional ## The JDBC truststore file path. # jdbc_truststore_path: /path/to/truststore ## @param jdbc_truststore_type - string - optional ## The JDBC truststore file type. Supported truststore types include JKS, SSO, and PKCS12. # jdbc_truststore_type: SSO ## @param jdbc_truststore_password - string - optional ## The password for the truststore when connecting via JDBC. # # jdbc_truststore_password: ``` For more information about connecting to the Oracle Database through TCPS on JDBC, see the official [Oracle whitepaper](https://www.oracle.com/technetwork/topics/wp-oracle-jdbc-thin-ssl-130128.pdf). {% /tab %} {% tab title="Containerized" %} For containerized environments, see the [Autodiscovery Integration Templates](https://docs.datadoghq.com/agent/kubernetes/integrations/) for guidance on applying the parameters below. | Parameter | Value | | -------------------- | ------------------------------------------------------------------------------------------------------------- | | `` | `oracle` | | `` | blank or `{}` | | `` | `{"server": "%%host%%:1521", "service_name":"", "username":"datadog", "password":""}` | {% /tab %} ### Validation{% #validation %} [Run the Agent's status subcommand](https://docs.datadoghq.com/agent/guide/agent-commands/#agent-status-and-information) and look for `oracle` under the Checks section. ## Custom query{% #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: ```python 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: ```yaml - 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](https://github.com/DataDog/integrations-core/blob/master/oracle/datadog_checks/oracle/data/conf.yaml.example) for all available configuration options. ### Example{% #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. ```yaml init_config: instances: - server: localhost:1521 service_name: orcl11g.us.oracle.com username: datadog password: xxxxxxx jdbc_driver_path: /u01/app/oracle/product/11.2/dbhome_1/jdbc/lib/ojdbc6.jar tags: - db:oracle custom_queries: - 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 ``` To access `v_$session`, give permission to `DATADOG` and test the permissions. ```text 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; ``` Once configured, you can create a [monitor](https://docs.datadoghq.com/monitors/monitor_types/metric/?tab=threshold) based on `oracle.custom_query.locks` metrics. ## Troubleshooting{% #troubleshooting %} ### Common problems{% #common-problems %} #### Oracle Native Client{% #oracle-native-client %} - If you encounter a `DPY-6000: cannot connect to database`: ```text Failed to connect to Oracle DB, error: DPY-6000: cannot connect to database. Listener refused connection. (Similar to ORA-12660) ``` - Ensure Native Network Encryption or Checksumming are not enabled. If they are enabled, you must use the Instant Client method by setting `use_instant_client: true`. For more information about setting up the Oracle Instant Client, see the [Oracle integration documentation](https://github.com/DataDog/integrations-core/tree/7.41.x/oracle#oracle-instant-client). #### Oracle Instant Client{% #oracle-instant-client-1 %} - Verify that both the Oracle Instant Client and SDK files are located in the same directory. The structure of the directory should look similar: ```text |___ BASIC_LITE_LICENSE |___ BASIC_LITE_README |___ adrci |___ genezi |___ libclntsh.so -> libclntsh.so.19.1 |___ libclntsh.so.10.1 -> libclntsh.so.19.1 |___ libclntsh.so.11.1 -> libclntsh.so.19.1 |___ libclntsh.so.12.1 -> libclntsh.so.19.1 |___ libclntsh.so.18.1 -> libclntsh.so.19.1 |___ libclntsh.so.19.1 |___ libclntshcore.so.19.1 |___ libipc1.so |___ libmql1.so |___ libnnz19.so |___ libocci.so -> libocci.so.19.1 |___ libocci.so.10.1 -> libocci.so.19.1 |___ libocci.so.11.1 -> libocci.so.19.1 |___ libocci.so.12.1 -> libocci.so.19.1 |___ libocci.so.18.1 -> libocci.so.19.1 |___ libocci.so.19.1 |___ libociicus.so |___ libocijdbc19.so |___ liboramysql19.so |___ listener.ora |___ network | `___ admin | |___ README | |___ cwallet.sso | |___ sqlnet.ora | `___ tnsnames.ora |___ ojdbc8.jar |___ ucp.jar |___ uidrvci `___ xstreams.jar ``` #### JDBC driver (Linux only){% #jdbc-driver-linux-only %} - If you encounter a `JVMNotFoundException`: ```text 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`: ```text JAVA_HOME=/path/to/java ``` - Then restart the Agent. - If you encounter this error `Unsupported major.minor version 52.0` it means 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. ```shell 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](https://docs.datadoghq.com/help/).