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.

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
   • Windows

   # Check if Oracle processes are running
   ps aux | grep ora_
   
   # Test database connectivity
   sqlplus sys/password@localhost:1521/ORCL as sysdba

   For Oracle installation, refer to the Oracle Database documentation.

2. Create Oracle Monitoring User

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

   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)
   • Linux (RPM)
   • Windows

   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

4. Set Environment Variables

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

   • Linux
   • Windows

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

5. Configure OpenTelemetry Collector

   • Linux
   • Windows

   Create /etc/otelcol-contrib/config.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]

6. Start and Enable the Service

   • Linux
   • Windows

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

Verification

1. Check Service Status

   • Linux
   • Windows

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

2. Test Oracle Connectivity

   -- 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.

   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

I/O and Buffer Cache

SQL Execution

Locks and Deadlocks

Storage

Troubleshooting

Connection Issues

ORA-12154: TNS could not resolve the connect identifier

Use the full service name, not the SID:

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

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:

# 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:

# 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:

-- 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 for real-time support
• Contact our support team at support@last9.io