# Oracle

> Monitor Oracle Database performance, sessions, I/O operations, and system metrics with OpenTelemetry for comprehensive database observability

Source: https://last9.io/docs/integrations/databases/oracle/

Use OpenTelemetry to monitor Oracle Database performance and send telemetry data to Last9. This integration provides comprehensive monitoring of Oracle database performance, including SQL execution, I/O operations, session management, and system resource utilization.

:::note
The `oracledb` receiver uses [`sijms/go-ora`](https://github.com/sijms/go-ora) — a pure Go Oracle driver. **No Oracle Instant Client installation is required** on the collector host.
:::

## Prerequisites

Before setting up Oracle Database monitoring, ensure you have:

- **Oracle Database**: Oracle Database 11g or higher installed and running
- **Administrative Access**: Database administrator privileges to create monitoring users
- **OpenTelemetry Collector**: v0.147.0 installed on the monitoring server
- **Network Access**: Connectivity between collector host and Oracle Database on port 1521
- **Last9 Account**: With OpenTelemetry integration credentials

1.  **Verify Oracle Database Installation**

    Ensure Oracle Database is running and accessible:

**Linux**

    ```bash
    # Check if Oracle processes are running
    ps aux | grep ora_

    # Test database connectivity
    sqlplus sys/password@localhost:1521/ORCL as sysdba
    ```

**Windows**

    ```powershell
    # Check Oracle services
    Get-Service | Where-Object { $_.Name -like "*Oracle*" }

    # Test connectivity
    sqlplus sys/password@localhost:1521/ORCL as sysdba
    ```

    For Oracle installation, refer to the [Oracle Database documentation](https://docs.oracle.com/en/database/).

2.  **Create Oracle Monitoring User**

    Connect as SYSDBA and create a dedicated read-only monitoring user:

    ```sql
    CREATE USER last9_monitor IDENTIFIED BY "YourSecurePassword123!";

    GRANT CREATE SESSION TO last9_monitor;
    GRANT SELECT_CATALOG_ROLE TO last9_monitor;
    GRANT SELECT ON V_$SESSION TO last9_monitor;
    GRANT SELECT ON V_$SYSSTAT TO last9_monitor;
    GRANT SELECT ON V_$SYSTEM_EVENT TO last9_monitor;
    GRANT SELECT ON V_$TABLESPACE TO last9_monitor;
    GRANT SELECT ON DBA_DATA_FILES TO last9_monitor;
    GRANT SELECT ON DBA_FREE_SPACE TO last9_monitor;

    -- Verify
    SELECT username, account_status FROM dba_users WHERE username = 'LAST9_MONITOR';
    ```

3.  **Install OpenTelemetry Collector**

**Linux (DEB)**

    ```bash
    wget https://github.com/open-telemetry/opentelemetry-collector-releases/releases/download/v0.147.0/otelcol-contrib_0.147.0_linux_amd64.deb
    sudo dpkg -i otelcol-contrib_0.147.0_linux_amd64.deb
    ```

**Linux (RPM)**

    ```bash
    wget https://github.com/open-telemetry/opentelemetry-collector-releases/releases/download/v0.147.0/otelcol-contrib_0.147.0_linux_amd64.rpm
    sudo rpm -ivh otelcol-contrib_0.147.0_linux_amd64.rpm
    ```

**Windows**

    ```powershell
    $url = "https://github.com/open-telemetry/opentelemetry-collector-releases/releases/download/v0.147.0/otelcol-contrib_0.147.0_windows_x64.msi"
    Invoke-WebRequest -Uri $url -OutFile "otelcol-contrib.msi" -UseBasicParsing
    Start-Process msiexec.exe -ArgumentList "/i otelcol-contrib.msi /quiet" -Wait -NoNewWindow
    ```

4.  **Set Environment Variables**

    Store the Oracle password as an environment variable rather than hardcoding it in the config.

**Linux**

    ```bash
    # For systemd service — add to the service's EnvironmentFile
    echo 'ORACLE_MONITOR_PASSWORD=YourSecurePassword123!' | sudo tee -a /etc/otelcol-contrib/env
    ```

**Windows**

    ```powershell
    # Machine-level — persists across reboots and service restarts
    [Environment]::SetEnvironmentVariable("ORACLE_MONITOR_PASSWORD", "YourSecurePassword123!", "Machine")

    # Restart the service to pick up the new variable
    Restart-Service otelcol-contrib -ErrorAction SilentlyContinue
    ```

5.  **Configure OpenTelemetry Collector**

**Linux**

    Create `/etc/otelcol-contrib/config.yaml`:

    ```yaml
    extensions:
      health_check:
      pprof:
        endpoint: localhost:1777

    receivers:
      hostmetrics:
        collection_interval: 60s
        scrapers:
          cpu:
            metrics:
              system.cpu.logical.count:
                enabled: true
          memory:
            metrics:
              system.memory.utilization:
                enabled: true
              system.memory.limit:
                enabled: true
          load:
          disk:
          filesystem:
            metrics:
              system.filesystem.utilization:
                enabled: true
          network:
          paging:

      oracledb:
        endpoint: localhost:1521 # Change to your Oracle host:port
        service: ORCL # Use service name (not SID) — query: SELECT value FROM v$parameter WHERE name = 'service_names'
        username: last9_monitor
        password: ${env:ORACLE_MONITOR_PASSWORD}
        collection_interval: 60s
        metrics:
          oracledb.cpu_time:
            enabled: true
          oracledb.executions:
            enabled: true
          oracledb.logical_reads:
            enabled: true
          oracledb.hard_parses:
            enabled: true
          oracledb.parse_calls:
            enabled: true
          oracledb.pga_memory:
            enabled: true
          oracledb.physical_reads:
            enabled: true
          oracledb.consistent_gets:
            enabled: true
          oracledb.db_block_gets:
            enabled: true
          oracledb.user_commits:
            enabled: true
          oracledb.user_rollbacks:
            enabled: true
          oracledb.enqueue_deadlocks:
            enabled: true
          oracledb.exchange_deadlocks:
            enabled: true
          oracledb.enqueue_locks.usage:
            enabled: true
          oracledb.enqueue_locks.limit:
            enabled: true
          oracledb.dml_locks.usage:
            enabled: true
          oracledb.dml_locks.limit:
            enabled: true
          oracledb.enqueue_resources.usage:
            enabled: true
          oracledb.enqueue_resources.limit:
            enabled: true
          oracledb.sessions.usage:
            enabled: true
          oracledb.sessions.limit:
            enabled: true
          oracledb.processes.usage:
            enabled: true
          oracledb.processes.limit:
            enabled: true
          oracledb.transactions.usage:
            enabled: true
          oracledb.transactions.limit:
            enabled: true
          oracledb.tablespace_size.usage:
            enabled: true
          oracledb.tablespace_size.limit:
            enabled: true

    processors:
      batch:
        timeout: 10s
        send_batch_size: 10000
        send_batch_max_size: 10000
      resourcedetection/system:
        detectors: ["system"]
        system:
          hostname_sources: ["os"]
          resource_attributes:
            host.ip:
              enabled: true
            host.cpu.model.name:
              enabled: true

    exporters:
      otlphttp/last9:
        endpoint: "${env:LAST9_OTLP_ENDPOINT}"
        headers:
          "Authorization": "${env:LAST9_OTLP_AUTH}"

    service:
      extensions: [health_check, pprof]
      pipelines:
        metrics:
          receivers: [oracledb, hostmetrics]
          processors: [batch, resourcedetection/system]
          exporters: [otlphttp/last9]
    ```

**Windows**

    Create `C:\Program Files\OpenTelemetry Collector\config.yaml`:

    ```yaml
    extensions:
      health_check:
      pprof:
        endpoint: localhost:1777
      zpages:
        endpoint: 0.0.0.0:55679

    receivers:
      # Collector self-logs from Windows Application Event Log
      windowseventlog/collector:
        query: |
          <QueryList><Query Id="0" Path="Application"><Select Path="Application">*[System[Provider[@Name="OpenTelemetry Collector Contrib"]]]</Select></Query></QueryList>

      hostmetrics:
        collection_interval: 60s
        scrapers:
          cpu:
            metrics:
              system.cpu.logical.count:
                enabled: true
          memory:
            metrics:
              system.memory.utilization:
                enabled: true
              system.memory.limit:
                enabled: true
          load:
          disk:
          filesystem:
            metrics:
              system.filesystem.utilization:
                enabled: true
          network:
          paging:
          process:
            mute_process_user_error: true
            mute_process_io_error: true
            mute_process_exe_error: true
            include:
              names: ["otelcol-contrib"]
              match_type: regexp
            metrics:
              process.cpu.utilization:
                enabled: true
              process.memory.utilization:
                enabled: true
              process.threads:
                enabled: true

      oracledb:
        endpoint: localhost:1521 # Change to your Oracle host:port
        service: ORCL # Use service name (not SID) — query: SELECT value FROM v$parameter WHERE name = 'service_names'
        username: last9_monitor
        password: ${env:ORACLE_MONITOR_PASSWORD}
        collection_interval: 60s
        metrics:
          oracledb.cpu_time:
            enabled: true
          oracledb.executions:
            enabled: true
          oracledb.logical_reads:
            enabled: true
          oracledb.hard_parses:
            enabled: true
          oracledb.parse_calls:
            enabled: true
          oracledb.pga_memory:
            enabled: true
          oracledb.physical_reads:
            enabled: true
          oracledb.consistent_gets:
            enabled: true
          oracledb.db_block_gets:
            enabled: true
          oracledb.user_commits:
            enabled: true
          oracledb.user_rollbacks:
            enabled: true
          oracledb.enqueue_deadlocks:
            enabled: true
          oracledb.exchange_deadlocks:
            enabled: true
          oracledb.enqueue_locks.usage:
            enabled: true
          oracledb.enqueue_locks.limit:
            enabled: true
          oracledb.dml_locks.usage:
            enabled: true
          oracledb.dml_locks.limit:
            enabled: true
          oracledb.enqueue_resources.usage:
            enabled: true
          oracledb.enqueue_resources.limit:
            enabled: true
          oracledb.sessions.usage:
            enabled: true
          oracledb.sessions.limit:
            enabled: true
          oracledb.processes.usage:
            enabled: true
          oracledb.processes.limit:
            enabled: true
          oracledb.transactions.usage:
            enabled: true
          oracledb.transactions.limit:
            enabled: true
          oracledb.tablespace_size.usage:
            enabled: true
          oracledb.tablespace_size.limit:
            enabled: true

    processors:
      batch:
        timeout: 10s
        send_batch_size: 10000
        send_batch_max_size: 10000

      resourcedetection/system:
        detectors: ["system"]
        system:
          hostname_sources: ["os"]
          resource_attributes:
            host.ip:
              enabled: true
            host.cpu.model.name:
              enabled: true

      resource/collector_logs:
        attributes:
          - key: service.name
            value: otel-collector
            action: upsert
          - key: source
            value: windows-event-log
            action: upsert

    exporters:
      otlphttp/last9:
        endpoint: "${env:LAST9_OTLP_ENDPOINT}"
        headers:
          "Authorization": "${env:LAST9_OTLP_AUTH}"

    service:
      extensions: [health_check, pprof, zpages]
      pipelines:
        metrics:
          receivers: [oracledb, hostmetrics]
          processors: [batch, resourcedetection/system]
          exporters: [otlphttp/last9]
        logs:
          receivers: [windowseventlog/collector]
          processors: [batch, resourcedetection/system, resource/collector_logs]
          exporters: [otlphttp/last9]
    ```

6.  **Start and Enable the Service**

**Linux**

    ```bash
    sudo systemctl daemon-reload
    sudo systemctl enable otelcol-contrib
    sudo systemctl start otelcol-contrib
    sudo systemctl status otelcol-contrib
    ```

**Windows**

    ```powershell
    Set-Service -Name otelcol-contrib -StartupType Automatic
    Start-Service otelcol-contrib
    Get-Service otelcol-contrib
    ```

## Verification

1. **Check Service Status**

**Linux**

   ```bash
   sudo systemctl status otelcol-contrib
   sudo journalctl -u otelcol-contrib -f
   ```

**Windows**

   ```powershell
   Get-Service otelcol-contrib
   # View recent logs from Windows Event Log
   Get-EventLog -LogName Application -Source "OpenTelemetry Collector Contrib" -Newest 20
   ```

2. **Test Oracle Connectivity**

   ```sql
   -- Connect with monitoring user and verify view access
   sqlplus last9_monitor/password@localhost:1521/ORCL

   SELECT COUNT(*) FROM V$SESSION;
   SELECT COUNT(*) FROM V$SYSSTAT;
   SELECT COUNT(*) FROM DBA_TABLESPACES;
   ```

3. **Verify Metrics in Last9**

   Log into your Last9 account and check that Oracle metrics are being received in [Grafana](https://app.last9.io/grafana).

   Look for metrics like:

   - `oracledb_sessions_usage`
   - `oracledb_physical_reads`
   - `oracledb_consistent_gets`
   - `oracledb_cpu_time`
   - `oracledb_tablespace_size_usage`

## Key Metrics to Monitor

### Sessions and Connections

| Metric                        | Description              | Alert Threshold               |
| ----------------------------- | ------------------------ | ----------------------------- |
| `oracledb.sessions.usage`     | Active database sessions | > 80% of `sessions.limit`     |
| `oracledb.sessions.limit`     | Maximum allowed sessions | Reference value               |
| `oracledb.processes.usage`    | Current Oracle processes | > 80% of `processes.limit`    |
| `oracledb.transactions.usage` | Active transactions      | > 80% of `transactions.limit` |

### I/O and Buffer Cache

| Metric                     | Description                  | Alert Threshold               |
| -------------------------- | ---------------------------- | ----------------------------- |
| `oracledb.physical_reads`  | Physical disk reads          | High growth rate              |
| `oracledb.consistent_gets` | Logical reads (buffer cache) | Track ratio vs physical_reads |
| `oracledb.db_block_gets`   | Current mode block reads     | Baseline monitoring           |
| `oracledb.logical_reads`   | Total logical reads          | Baseline monitoring           |

### SQL Execution

| Metric                 | Description                        | Alert Threshold       |
| ---------------------- | ---------------------------------- | --------------------- |
| `oracledb.executions`  | SQL executions per interval        | Baseline monitoring   |
| `oracledb.hard_parses` | Hard parses (full SQL compilation) | Rising trend          |
| `oracledb.parse_calls` | Total parse calls                  | Track hard/soft ratio |
| `oracledb.cpu_time`    | Database CPU time                  | > 80% sustained       |

### Locks and Deadlocks

| Metric                         | Description          | Alert Threshold |
| ------------------------------ | -------------------- | --------------- |
| `oracledb.enqueue_deadlocks`   | Deadlock events      | > 0             |
| `oracledb.exchange_deadlocks`  | Exchange deadlocks   | > 0             |
| `oracledb.enqueue_locks.usage` | Enqueue locks in use | > 80% of limit  |
| `oracledb.dml_locks.usage`     | DML locks in use     | > 80% of limit  |

### Storage

| Metric                           | Description           | Alert Threshold |
| -------------------------------- | --------------------- | --------------- |
| `oracledb.tablespace_size.usage` | Tablespace bytes used | > 80% of limit  |
| `oracledb.tablespace_size.limit` | Tablespace max size   | Reference value |
| `oracledb.pga_memory`            | PGA memory in use     | > 80% of target |

## Troubleshooting

### Connection Issues

**ORA-12154: TNS could not resolve the connect identifier**

Use the full service name, not the SID:

```sql
-- Find the correct service name
SELECT value FROM v$parameter WHERE name = 'service_names';
```

Set `service:` in the config to that value (e.g., `ORCL.domain.com`).

**Authentication errors:**

```sql
SELECT username, account_status FROM dba_users WHERE username = 'LAST9_MONITOR';
SELECT * FROM dba_sys_privs WHERE grantee = 'LAST9_MONITOR';
```

### Windows-Specific

**Service not starting:**

```powershell
# Check Windows Event Log for errors
Get-EventLog -LogName Application -Source "OpenTelemetry Collector Contrib" -Newest 10 | Format-List

# Validate config before starting
& "C:\Program Files\OpenTelemetry Collector\otelcol-contrib.exe" validate --config "C:\Program Files\OpenTelemetry Collector\config.yaml"
```

**Environment variable not picked up:**

```powershell
# Verify it's set at Machine level (not just current session)
[Environment]::GetEnvironmentVariable("ORACLE_MONITOR_PASSWORD", "Machine")

# Restart service after setting to reload env
Restart-Service otelcol-contrib
```

### Missing Metrics

**No metrics appearing:**

```sql
-- Verify statistics_level allows V$ view access
SELECT VALUE FROM V$PARAMETER WHERE NAME = 'statistics_level';

-- Should return TYPICAL or ALL (not BASIC)
```

**Invalid keys error on startup:** The `metrics:` block is a strict allowlist — any key not in the receiver's `metadata.yaml` at your version causes a hard startup failure. Use only the metric names listed in the configuration above.

## Need Help?

If you encounter any issues or have questions:

- Join our [Discord community](https://discord.com/channels/652153247672729619/652153247672729621) for real-time support
- Contact our support team at [support@last9.io](mailto:support@last9.io)