> ## Documentation Index
> Fetch the complete documentation index at: https://docs-xcor.paloaltonetworks.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Oracle Database

> Oracle Database health and workload metrics, including sessions, wait and response time, cache hit ratios, I/O, and tablespace usage per pluggable database.

The Oracle Database integration requires CXDOT Collector 1.11.0 or greater.

[Oracle Database](https://www.oracle.com/database/) is a relational database management
system. Use the Oracle Database integration to collect session, wait time, cache, I/O,
and tablespace metrics from Oracle Database instances running in your environment.

The Oracle Database integration supports Oracle Database 19c or greater.

## Supported telemetry types

The Oracle Database integration supports these telemetry types:

| Type | Supported |
| :-: | :-: |
| Logs | No |
| Metrics | Yes |
| Traces | No |
| Events | No |

## Prerequisites

The Oracle Database integration has the following prerequisites:

* Determine whether your database is a container database. Connect to the database
  with a SQL client and run the following query:

  ```sql theme={null}
  SELECT cdb FROM v$database;
  ```

  A value of `YES` means the database is a container database. Connect the integration
  to the root container. From there, it reports instance-wide metrics and per-container
  metrics for the root and each pluggable database, identified by the `oracle.db.pdb`
  attribute. If the integration connects to a pluggable database, per-container metrics
  cover that database only.

* Create a database user for the CXDOT Collector. For a container database, connect to
  the root container as `SYSDBA` and run the following statements to create a common user
  that can read every container's statistics. For more information about `CONTAINER_DATA`,
  see
  [`ALTER USER`](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/ALTER-USER.html)
  in the Oracle Database documentation.

  ```sql expandable theme={null}
  CREATE USER c##cxdot IDENTIFIED BY "PASSWORD" CONTAINER = ALL;
  ALTER USER c##cxdot SET CONTAINER_DATA = ALL CONTAINER = CURRENT;
  GRANT CREATE SESSION TO c##cxdot CONTAINER = ALL;
  GRANT SELECT ON v_$asm_diskgroup_stat TO c##cxdot CONTAINER = ALL;
  GRANT SELECT ON v_$con_sysmetric TO c##cxdot CONTAINER = ALL;
  GRANT SELECT ON v_$con_sysstat TO c##cxdot CONTAINER = ALL;
  GRANT SELECT ON v_$containers TO c##cxdot CONTAINER = ALL;
  GRANT SELECT ON v_$database TO c##cxdot CONTAINER = ALL;
  GRANT SELECT ON v_$datafile TO c##cxdot CONTAINER = ALL;
  GRANT SELECT ON v_$instance TO c##cxdot CONTAINER = ALL;
  GRANT SELECT ON v_$lock TO c##cxdot CONTAINER = ALL;
  GRANT SELECT ON v_$osstat TO c##cxdot CONTAINER = ALL;
  GRANT SELECT ON v_$parameter TO c##cxdot CONTAINER = ALL;
  GRANT SELECT ON v_$pdbs TO c##cxdot CONTAINER = ALL;
  GRANT SELECT ON v_$pgastat TO c##cxdot CONTAINER = ALL;
  GRANT SELECT ON v_$process TO c##cxdot CONTAINER = ALL;
  GRANT SELECT ON v_$resource_limit TO c##cxdot CONTAINER = ALL;
  GRANT SELECT ON v_$rsrcmgrmetric TO c##cxdot CONTAINER = ALL;
  GRANT SELECT ON v_$session TO c##cxdot CONTAINER = ALL;
  GRANT SELECT ON v_$sgainfo TO c##cxdot CONTAINER = ALL;
  GRANT SELECT ON v_$sysmetric TO c##cxdot CONTAINER = ALL;
  GRANT SELECT ON v_$sysstat TO c##cxdot CONTAINER = ALL;
  GRANT SELECT ON v_$transaction TO c##cxdot CONTAINER = ALL;
  GRANT SELECT ON cdb_data_files TO c##cxdot CONTAINER = ALL;
  GRANT SELECT ON cdb_services TO c##cxdot CONTAINER = ALL;
  GRANT SELECT ON cdb_tablespace_usage_metrics TO c##cxdot CONTAINER = ALL;
  GRANT SELECT ON cdb_tablespaces TO c##cxdot CONTAINER = ALL;
  ```

  For a database that isn't a container database, connect as `SYSDBA` and run the
  following statements instead:

  ```sql expandable theme={null}
  CREATE USER cxdot IDENTIFIED BY "PASSWORD";
  GRANT CREATE SESSION TO cxdot;
  GRANT SELECT ON v_$asm_diskgroup_stat TO cxdot;
  GRANT SELECT ON v_$con_sysmetric TO cxdot;
  GRANT SELECT ON v_$containers TO cxdot;
  GRANT SELECT ON v_$database TO cxdot;
  GRANT SELECT ON v_$datafile TO cxdot;
  GRANT SELECT ON v_$instance TO cxdot;
  GRANT SELECT ON v_$lock TO cxdot;
  GRANT SELECT ON v_$osstat TO cxdot;
  GRANT SELECT ON v_$parameter TO cxdot;
  GRANT SELECT ON v_$pgastat TO cxdot;
  GRANT SELECT ON v_$process TO cxdot;
  GRANT SELECT ON v_$resource_limit TO cxdot;
  GRANT SELECT ON v_$rsrcmgrmetric TO cxdot;
  GRANT SELECT ON v_$session TO cxdot;
  GRANT SELECT ON v_$sgainfo TO cxdot;
  GRANT SELECT ON v_$sysmetric TO cxdot;
  GRANT SELECT ON v_$sysstat TO cxdot;
  GRANT SELECT ON v_$transaction TO cxdot;
  GRANT SELECT ON cdb_data_files TO cxdot;
  GRANT SELECT ON cdb_tablespace_usage_metrics TO cxdot;
  GRANT SELECT ON cdb_tablespaces TO cxdot;
  ```

  Replace `PASSWORD` with a password for the user. To reuse an existing monitoring user,
  run every statement except `CREATE USER` for that user.

* Make the Oracle Database listener, typically on port `1521`, reachable from the CXDOT
  Collector over TCP. The integration uses a user name and password. It doesn't support
  the `TCPS` protocol, Oracle wallets, or net service names from a `tnsnames.ora` file.

## Configure

The integration is enabled by default. You can configure targets either by annotating
pods and Services in your Kubernetes cluster or by listing static endpoints in your Helm
values. To configure the Oracle Database integration, follow these steps:

1. To collect from Oracle Database pods in Kubernetes, add a CXDOT integration
   annotation to the pod template. The annotation key must end with the container name,
   and its value specifies the database service name and references credentials stored in
   a Kubernetes Secret in the same namespace. For example, add the following annotation
   to your Oracle Database workload:

   ```yaml theme={null}
   spec:
     template:
       metadata:
         annotations:
           integrations.cxdot.paloaltonetworks.com/oracle: |
             schema_version: v1.14
             oracle:
               instances:
                 - endpoint: ${target.host}:${target.port.listener}
                   service: ORCLCDB
               username: "c##cxdot"
               password: ${k8s_secret:oracle-credentials/password}
       spec:
         containers:
           - name: oracle
             image: container-registry.oracle.com/database/enterprise:21.3.0.0
             ports:
               - name: listener
                 containerPort: 1521
   ```

   The Collector replaces `${target.host}` with the pod IP address,
   `${target.port.listener}` with the container port named `listener`, and
   `${k8s_secret:oracle-credentials/password}` with the `password` key of the
   `oracle-credentials` Secret. To allow the Collector to read the Secret, add the pod's
   namespace to `apiServer.metadataServer.secretAllowlist` in the Helm values for your
   CXDOT Collector.

2. Optional: To collect from Oracle Database instances outside your Kubernetes cluster,
   configure static targets instead of annotated targets. Supply the password through your
   deployment's secret management and expose it on the cluster-tier CXDOT Collector
   through `clusterCollector.extraEnv`. For example, add the following to the
   `values.yaml` file for the CXDOT Collector Helm chart:

   ```yaml theme={null}
   clusterCollector:
     extraEnv:
       - name: ORACLE_PASSWORD
         valueFrom:
           secretKeyRef:
             name: oracle-credentials
             key: password

   collection:
     cxdot:
       integrations:
         oracle:
           username: "c##cxdot"
           password: "${env:ORACLE_PASSWORD}"
           instances:
             - endpoint: oracle.example.com:1521
               service: ORCLCDB
   ```

   This integration instance collects only the listed instances and disables
   autodiscovery. Every instance uses the same `username` and `password`. Use a
   separate integration instance for different credentials. One elected cluster-tier
   CXDOT Collector replica collects static targets. For more information, see
   [architecture](https://docs-xcor.paloaltonetworks.com/ingest/xcor/collector/install/kubernetes/architecture.md).

3. Optional: Report SQL query results as metrics. Add `custom_queries` to a static
   instance or to an instance in a CXDOT integration annotation. The following query
   reports free space for each Automatic Storage Management (ASM) disk group as
   `oracle.asm_diskgroup.free_mb`. The `asm_diskgroup_name` attribute identifies
   the disk group:

   ```yaml theme={null}
   collection:
     cxdot:
       integrations:
         oracle:
           username: "c##cxdot"
           password: "${env:ORACLE_PASSWORD}"
           instances:
             - endpoint: oracle.example.com:1521
               service: ORCLCDB
               custom_queries:
                 - metric_prefix: oracle.asm_diskgroup
                   query: SELECT name, free_mb FROM v$asm_diskgroup
                   columns:
                     - name: asm_diskgroup_name
                       type: tag
                     - name: free_mb
                       type: gauge
   ```

   Grant the database user `SELECT` on each view or table that a query reads. This
   example requires `SELECT` on `v_$asm_diskgroup`. For a query in a pluggable
   database, also grant `SET CONTAINER` in that database.

4. Optional: Configure separate integration instances to collect from both annotated
   and static targets. For example, add the following to the `values.yaml` file for the
   CXDOT Collector Helm chart:

   ```yaml theme={null}
   collection:
     cxdot:
       integrations:
         oracle: {}
         oracle/external:
           username: "c##cxdot"
           password: "${env:ORACLE_PASSWORD}"
           instances:
             - endpoint: oracle.example.com:1521
               service: ORCLCDB
   ```

   The `oracle/external` key holds the static targets. A named instance replaces the
   implicit default, so the empty `oracle: {}` entry keeps autodiscovery enabled.

5. Optional: Disable the integration. For example, add the following to the
   `values.yaml` file for the CXDOT Collector Helm chart:

   ```yaml theme={null}
   collection:
     cxdot:
       integrations:
         oracle:
           enabled: false
   ```

Metrics from each discovered pod include that pod's Kubernetes metadata. For more
information, see
[autodiscovery](https://docs-xcor.paloaltonetworks.com/ingest/xcor/collector/discover/kubernetes.md).

### Validate

To validate the Oracle Database integration, follow these steps:

1. In the
   [Live Telemetry Analyzer](https://docs-xcor.paloaltonetworks.com/investigate/analyze/telemetry-analyzer.md),
   add the following filters:

   * `__name__=cxdot.integration.target.health`
   * `cxdot.integration.name=oracle`

   Confirm that each target reports `1`, identified by its `server.address` and
   `server.port` attributes. The `cxdot.integration.check` attribute separates the
   connection status, `oracle`, from the query status, `oracle_query`.

2. In
   [Metrics Explorer](https://docs-xcor.paloaltonetworks.com/investigate/querying/metrics/explorer.md),
   run the following query:

   ```text theme={null}
   sum by ("server.address", "server.port", "oracle.db.pdb") ({"oracledb.session.count"})
   ```

   Confirm that the query returns time series for each target. For a target connected to
   a root container, confirm that the query returns a time series for `CDB$ROOT` and for
   each open pluggable database.

### Troubleshooting

* Metrics report only `CDB$ROOT` in the `oracle.db.pdb` attribute: the database user
  can't read the statistics of other containers. Run the `ALTER USER` statement from the
  Prerequisites section, which sets the user's `CONTAINER_DATA` to `ALL`.
* Some metrics are missing, such as execution and commit counts or resource limits: the
  database user lacks a Prerequisites grant. The CXDOT Collector logs `Error scraping
  metrics` with `ORA-00942`. The `oracle_query` health check can report `1` while these
  metrics are missing because it doesn't cover every query. If you reuse a monitoring
  user from another tool, confirm grants on `v_$sysstat`, `v_$con_sysstat`, and
  `v_$resource_limit`, and for a pluggable database also on `v_$parameter`,
  `v_$transaction`, and `v_$lock`.
* Average active sessions, CPU usage, physical reads and writes, session counts by type
  and status, and total Program Global Area (PGA) memory carry no `oracle.db.pdb`
  attribute while other per-container metrics, such as tablespace metrics, do: the
  integration reports instance-wide totals until it confirms it can
  read each container's statistics. When the database isn't open yet, for example right
  after its pod restarted, the Collector logs `failed to detect Oracle version`,
  `failed to detect CDB status`, or `per-PDB grants probe failed` and retries each
  collection until the database answers. Those first two messages also mean the user
  lacks `SELECT` on `v_$instance` or `v_$database`. Grant access, and the next collection
  detects the database without a restart. When the user lacks `SELECT` on
  `v_$con_sysstat`, the Collector logs `per-PDB grants not available` and stops
  retrying. Grant access and restart the CXDOT Collector.
* Custom query metrics are missing and the `oracle_query` health check reports `0`: a
  custom query failed. The CXDOT Collector logs `Error scraping metrics` with
  `custom query`. A query fails if its result has a different number of columns than
  its `columns` list, or if a metric column contains a `NULL` or a value that isn't
  numeric.
  Use `NVL` or `COALESCE` for columns that can contain `NULL`.
* Tablespace metrics are missing while other metrics arrive: the tablespace queries
  timed out, or the user lacks a grant on the `cdb_` views. A timed-out tablespace query
  retries only after a full tablespace collection interval. Raise `timeout` in your Helm
  values or in a CXDOT integration annotation.

## Configuration reference

Configure one Oracle Database integration instance with the following settings. In Helm values,
place these settings under `collection.cxdot.integrations.oracle`. In a Collector configuration
file, place them under `cxdot.integrations.oracle`.

### Optional settings

* **`enabled`**
  Type: `boolean`. Optional. Default: `true`.
  If true, the Collector collects Oracle Database metrics from the instances listed in
  `instances`, or from targets discovered through annotations when the list is empty. If false,
  the Collector doesn't collect Oracle Database metrics.

* **`username`**
  Type: `string`. Optional.
  Oracle Database user for the configured instances. The Collector requires it when `instances`
  contains entries. For a container database, use a common user that can read every container's
  statistics.

* **`password`**
  Type: `string`. Optional.
  Password for `username`. The Collector requires it to start collecting from `instances`
  entries, and masks the value in diagnostic output, logs, and errors.

* **`collection_interval`**
  Type: `duration`. Optional. Default: `60s`.
  How often to collect metrics from each configured Oracle Database instance. Oracle Database
  recomputes system metrics, such as hit ratios and rates, every 15 or 60 seconds. Discovered
  targets use the collection interval in their annotation instead.

* **`timeout`**
  Type: `duration`. Optional. Default: `20s`.
  Maximum time to collect metrics from an Oracle Database instance during one interval. Each
  tablespace query gets this limit. All custom queries share one additional limit. An interval
  that runs both tablespace queries and custom queries can take up to four times this value. A
  value of `0s` disables all time limits. Discovered targets use the query timeout in their
  annotation, or this default when no timeout is set, limited to their collection interval.

* **`tablespace_collection_interval`**
  Type: `duration`. Optional. Default: `600s`.
  How often the integration collects tablespace metrics from each configured instance. The
  tablespace queries read every container's data files, so they run less often than the other
  queries. Between those queries, the integration reports the most recent tablespace values
  every `collection_interval`. Targets discovered through annotations use the tablespace
  interval in their annotation instead.

* **`collect_system_metrics`**
  Type: `boolean`. Optional. Default: `true`.
  Whether to collect the core system metrics Oracle Database computes in `V$SYSMETRIC`: cache
  hit ratios, session counts and utilization, the wait time ratio, logon, transaction, scan, and
  sort rates, SQL service latency, and temporary space usage. If false, the Collector reports
  none of them. Applies to the configured targets. Annotation-discovered targets use the
  `sysmetrics` setting of their own annotation instance instead.

* **`collect_extended_system_metrics`**
  Type: `boolean`. Optional. Default: `true`.
  Whether to collect the extended system metrics from `V$SYSMETRIC`: CPU usage and host load,
  logical and physical I/O rates, data file and network I/O, parse rates and ratios, redo
  generation, checkpoint and block change rates, cache hit ratios, and process utilization. If
  false, the Collector reports none of them. Applies to the configured targets.
  Annotation-discovered targets collect them only when their annotation instance enables
  Database Monitoring with `dbm: true` and leaves `sysmetrics` on.

* **`collect_process_memory`**
  Type: `boolean`. Optional. Default: `true`.
  Whether to collect the Program Global Area (PGA) memory each Oracle Database process has
  allocated, is using, and can free, with its maximum. The Collector reports one time series per
  process, so this family grows with the number of processes on the instance. If false, the
  Collector doesn't read `V$PROCESS` memory. Applies to the configured targets.
  Annotation-discovered targets use the `process_memory` setting of their own annotation
  instance instead.

* **`collect_inactive_sessions`**
  Type: `boolean`. Optional. Default: `true`.
  Whether to collect the time since each inactive session's last call, one time series per
  session. If false, the Collector doesn't report `oracledb.session.last_call.age`. Applies to
  the configured targets. Annotation-discovered targets use the `inactive_sessions` setting of
  their own annotation instance instead.

* **`collect_session_counts`**
  Type: `boolean`. Optional. Default: `true`.
  Whether to collect `oracledb.sessions.usage`, the number of sessions by session type and
  status. If false, the Collector doesn't report it. Applies to the configured targets.
  Annotation-discovered targets count sessions when their annotation instance leaves
  `user_sessions_count` on and at least one of `process_memory` and `inactive_sessions` on.

* **`collect_tablespaces`**
  Type: `boolean`. Optional. Default: `true`.
  Whether to collect tablespace metrics: the size, limit, utilization, and status of every
  tablespace, read every `tablespace_collection_interval`. If false, the Collector doesn't run
  the tablespace queries. Applies to the configured targets. Annotation-discovered targets use
  the `tablespaces` setting of their own annotation instance instead.

* **`collect_shared_memory`**
  Type: `boolean`. Optional. Default: `true`.
  Whether to collect `oracledb.sga.size`, the size of each System Global Area component. If
  false, the Collector doesn't read `V$SGAINFO`. Applies to the configured targets.
  Annotation-discovered targets collect it only when their annotation instance enables Database
  Monitoring with `dbm: true` and leaves `shared_memory` on.

* **`collect_resource_manager`**
  Type: `boolean`. Optional. Default: `true`.
  Whether to collect the CPU time each Resource Manager consumer group consumed and waited for.
  If false, the Collector doesn't read `V$RSRCMGRMETRIC`. Applies to the configured targets.
  Annotation-discovered targets collect it only when their annotation instance enables Database
  Monitoring with `dbm: true` and leaves `resource_manager` on.

* **`collect_asm`**
  Type: `boolean`. Optional. Default: `true`.
  Whether to collect the space and offline disks of each Automatic Storage Management disk
  group. If false, the Collector doesn't read `V$ASM_DISKGROUP_STAT`. A database without
  Automatic Storage Management reports none. Applies to the configured targets.
  Annotation-discovered targets use the `asm` setting of their own annotation instance instead.

* **`instances`**
  Type: `array of object`. Optional. Default: `[]`.
  Oracle Database instances to monitor. Each discovered target uses the credentials in its
  annotation.

* **`instances[].endpoint`**
  Type: `string`. Required.
  `host:port` of the database listener, such as `oracle.default.svc:1521`.

* **`instances[].service`**
  Type: `string`. Required. Minimum length: `1`.
  Oracle Database service name. For a container database, use the root container's service to
  collect from every pluggable database.

* **`instances[].tags`**
  Type: `array of string`. Optional. Default: `[]`.
  Resource attributes in `key:value` format to add to every time series from this instance. For
  example, `dbname:billing` adds `dbname` with the value `billing`. Values can contain colons.
  The integration ignores entries without a colon and keeps the last value for repeated keys. A
  tag can't replace an attribute set by the integration or by the collector-wide
  `resource_attributes`. Discovered targets apply `tags` from their annotation instance in the
  same way.

* **`instances[].custom_queries`**
  Type: `array of object`. Optional. Default: `[]`.
  SQL queries that report results as metrics for this instance. Each query runs every collection
  interval unless it sets `collection_interval`. A query failure reports no metrics for that
  collection and sets the `oracle_query` health check to `0`. Other metrics and queries
  continue. Grant the database user `SELECT` on every view and table that the queries read.

* **`instances[].custom_queries[].query`**
  Type: `string`. Required. Minimum length: `1`.
  SQL statement to run. Each result row produces one data point for each metric column. Tag
  columns become attributes on those data points.

* **`instances[].custom_queries[].metric_prefix`**
  Type: `string`. Optional. Default: `oracle`.
  Prefix of every metric name the query reports. A metric name is the prefix, a period, and the
  column's `name`, such as `oracle.asm_diskgroup.free_mb`.

* **`instances[].custom_queries[].columns`**
  Type: `array of object`. Required. Minimum items: `1`.
  One entry for each column the query returns, in the order the query returns them. The query
  fails when it returns a different number of columns. An entry without a `name`, such as `{}`,
  skips its column.

* **`instances[].custom_queries[].columns[].name`**
  Type: `string`. Optional.
  Metric name suffix for a metric column, or attribute name for a `tag` column.

* **`instances[].custom_queries[].columns[].type`**
  Type: `string`. Optional.
  How the integration reports the column. `tag` adds the value as an attribute on each of the
  row's data points and leaves it off when the value is `NULL`. `gauge` reports the value as a
  gauge. `count` adds up a non-negative column across rows with the same attribute values and
  adds that total to a cumulative counter on every run. `monotonic_count` and `rate` report the
  value as a cumulative counter, for a column that only increases until the database restarts.
  Use the `increase` or `rate` function to query a counter's change. Allowed values: `tag`,
  `gauge`, `count`, `monotonic_count`, `rate`.

* **`instances[].custom_queries[].tags`**
  Type: `array of string`. Optional. Default: `[]`.
  Attributes to add to every data point of the query, each in the form `key:value`. The value
  can contain colons. The integration ignores an entry without a colon. A tag here replaces a
  `tag` column of the same name.

* **`instances[].custom_queries[].collection_interval`**
  Type: `number`. Optional. Minimum: `1`.
  Seconds between query runs, truncated to whole seconds. The query runs during the first
  collection that falls no earlier than half a collection interval before the interval passes,
  so collection timing jitter doesn't skip runs. Between runs, each collection reports the
  metrics from the last run, or none if that run failed. When unset, the query runs every
  collection interval. The Datadog Agent's Oracle check accepts this setting but runs every
  query during each collection. A query carried over from the Agent with this setting runs less
  often here.

* **`instances[].custom_queries[].pdb`**
  Type: `string`. Optional. Default: `CDB$ROOT`.
  Oracle Database container in which to run the query. This setting applies when `service` names
  a container database's root container. The integration adds the container name to every data
  point as the `oracle.db.pdb` attribute. A connection to a pluggable database or a database
  without containers ignores this setting and runs the query where it connects. A query in a
  pluggable database requires the database user to have the `SET CONTAINER` privilege in that
  pluggable database.


## Related topics

- [CXDOT Collector integrations](https://docs-xcor.paloaltonetworks.com/ingest/xcor/integrations/collector.md)
- [SQL DB Input source plugin](https://docs-xcor.paloaltonetworks.com/ingest/pipeline/plugins/source-plugins/sqldb.md)
- [Oracle Log Analytics destination plugin](https://docs-xcor.paloaltonetworks.com/ingest/pipeline/plugins/destination-plugins/oracle.md)
- [Ingest Google Cloud metrics](https://docs-xcor.paloaltonetworks.com/ingest/xcor/integrations/server/gcp/metrics.md)
- [Azure metrics available in Cortex XCOR](https://docs-xcor.paloaltonetworks.com/ingest/xcor/integrations/server/azure/azure-metrics.md)


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.