Oracle Database

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: Installation access on the monitoring server
• Network Access: Connectivity between collector and Oracle Database
• Last9 Account: With OpenTelemetry integration credentials

1. Verify Oracle Database Installation

   Ensure Oracle Database is running and accessible:

   # 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

   Create a dedicated user for OpenTelemetry monitoring with necessary permissions:

   -- Connect as SYSDBA
   sqlplus sys/password@localhost:1521/ORCL as sysdba
   
   -- Create monitoring user
   CREATE USER last9 IDENTIFIED BY secure_monitoring_password;
   
   -- Grant basic connection privileges
   GRANT CREATE SESSION TO last9;
   GRANT CONNECT TO last9;
   GRANT SELECT_CATALOG_ROLE TO last9;
   
   -- Grant access to performance views
   GRANT SELECT ON V_$SESSION TO last9;
   GRANT SELECT ON V_$SYSSTAT TO last9;
   GRANT SELECT ON V_$SYSTEM_EVENT TO last9;
   GRANT SELECT ON V_$PROCESS TO last9;
   GRANT SELECT ON V_$RESOURCE_LIMIT TO last9;
   GRANT SELECT ON V_$SQL TO last9;
   GRANT SELECT ON V_$SQLAREA TO last9;
   GRANT SELECT ON V_$SQLSTATS TO last9;
   GRANT SELECT ON V_$INSTANCE TO last9;
   GRANT SELECT ON V_$DATABASE TO last9;
   GRANT SELECT ON V_$PARAMETER TO last9;
   GRANT SELECT ON V_$PGASTAT TO last9;
   GRANT SELECT ON V_$SGASTAT TO last9;
   
   -- Grant access to tablespace information
   GRANT SELECT ON DBA_TABLESPACES TO last9;
   GRANT SELECT ON DBA_DATA_FILES TO last9;
   GRANT SELECT ON DBA_TABLESPACE_USAGE_METRICS TO last9;
   GRANT SELECT ON DBA_FREE_SPACE TO last9;
   
   -- Grant access to additional performance views
   GRANT SELECT ON V_$WAITSTAT TO last9;
   GRANT SELECT ON V_$LATCH TO last9;
   GRANT SELECT ON V_$LOCK TO last9;
   GRANT SELECT ON V_$LOCKED_OBJECT TO last9;
   
   -- Commit changes
   COMMIT;

   Permissions Explained:

   • CREATE SESSION, CONNECT: Basic connection privileges
   • SELECT_CATALOG_ROLE: Access to data dictionary views
   • V_$ views: Performance and session monitoring data
   • DBA_ views: Tablespace and storage information

3. Install OpenTelemetry Collector

   Install the OpenTelemetry Collector with Oracle Database receiver:

   • DEB Package
   • RPM Package

   For Debian/Ubuntu systems:

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

4. Install Oracle Instant Client (if needed)

   The Oracle receiver requires Oracle client libraries:

   • Oracle Linux/RHEL
   • Ubuntu/Debian

   # Download Oracle Instant Client RPMs from Oracle website
   # Basic and SDK packages are required
   sudo rpm -ivh oracle-instantclient-basic-21.1.0.0.0-1.x86_64.rpm
   sudo rpm -ivh oracle-instantclient-sdk-21.1.0.0.0-1.x86_64.rpm
   
   # Set environment variables
   echo 'export LD_LIBRARY_PATH=/usr/lib/oracle/21/client64/lib:$LD_LIBRARY_PATH' >> ~/.bashrc
   source ~/.bashrc

5. Configure OpenTelemetry Collector

   Create the collector configuration file:

   sudo mkdir -p /etc/otelcol-contrib
   sudo nano /etc/otelcol-contrib/config.yaml

   Add the following configuration to monitor Oracle Database:

   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 endpoint
       service: ORCL # Change to your Oracle service name (SID/Service)
       username: last9
       password: secure_monitoring_password # Use environment variable: ${env:ORACLE_PASSWORD}
       collection_interval: 60s
   
       # Configure comprehensive Oracle metrics
       metrics:
         # Session and Connection Metrics
         oracledb.sessions:
           enabled: true
         oracledb.processes:
           enabled: true
         oracledb.cpu_time:
           enabled: true
   
         # I/O Performance Metrics
         oracledb.physical_reads:
           enabled: true
         oracledb.physical_writes:
           enabled: true
         oracledb.physical_reads_direct:
           enabled: true
         oracledb.physical_writes_direct:
           enabled: true
         oracledb.physical_read_io_requests:
           enabled: true
         oracledb.physical_write_io_requests:
           enabled: true
   
         # Buffer Cache Metrics
         oracledb.consistent_gets:
           enabled: true
         oracledb.db_block_gets:
           enabled: true
         oracledb.db_block_changes:
           enabled: true
   
         # SQL Execution Metrics
         oracledb.sql_execute_count:
           enabled: true
         oracledb.sql_parse_calls:
           enabled: true
         oracledb.hard_parse_count:
           enabled: true
   
         # Parallel Processing Metrics
         oracledb.parallel_operations_not_downgraded:
           enabled: true
         oracledb.parallel_operations_downgraded_1_to_25_pct:
           enabled: true
         oracledb.parallel_operations_downgraded_25_to_50_pct:
           enabled: true
         oracledb.parallel_operations_downgraded_50_to_75_pct:
           enabled: true
         oracledb.parallel_operations_downgraded_75_to_99_pct:
           enabled: true
         oracledb.parallel_operations_downgraded_to_serial:
           enabled: true
   
         # Wait Events and Locks
         oracledb.lock_waits:
           enabled: true
         oracledb.enqueue_waits:
           enabled: true
         oracledb.latch_waits:
           enabled: true
   
         # Memory Metrics
         oracledb.pga_cache_hit_pct:
           enabled: true
         oracledb.buffer_cache_hit_ratio:
           enabled: true
   
   processors:
     batch:
       timeout: 30s
       send_batch_size: 10000
       send_batch_max_size: 10000
     resourcedetection/cloud:
       detectors: ["aws", "gcp", "azure"]
     resourcedetection/system:
       detectors: ["system"]
       system:
         hostname_sources: ["os"]
     transform/oracle:
       metric_statements:
         - context: metric
           statements:
             - set(resource.attributes["service.name"], "oracle-database")
             - set(resource.attributes["deployment.environment"], "production")
             - set(resource.attributes["database.system"], "oracle")
   
   exporters:
     otlp/last9:
       endpoint: "$last9_otlp_endpoint"
       headers:
         "Authorization": "$last9_otlp_auth_header"
     debug:
       verbosity: detailed
   
   service:
     pipelines:
       metrics:
         receivers: [oracledb, hostmetrics]
         processors:
           [
             batch,
             resourcedetection/system,
             resourcedetection/cloud,
             transform/oracle,
           ]
         exporters: [otlp/last9]

6. Secure Password Management

   Use environment variables for sensitive information:

   # Create environment file
   sudo nano /etc/otelcol-contrib/env
   
   # Add Oracle password
   ORACLE_PASSWORD=secure_monitoring_password

   Update the collector configuration to use environment variables:

   receivers:
     oracledb:
       endpoint: localhost:1521
       service: ORCL
       username: last9
       password: ${env:ORACLE_PASSWORD}

7. Create Systemd Service Configuration

   Create a systemd service file for the OpenTelemetry Collector:

   sudo nano /etc/systemd/system/otelcol-contrib.service

   Add the following service configuration:

   [Unit]
   Description=OpenTelemetry Collector for Oracle Database Monitoring
   After=network.target
   
   [Service]
   ExecStart=/usr/bin/otelcol-contrib --config /etc/otelcol-contrib/config.yaml
   Restart=always
   User=root
   Group=root
   
   # Load environment variables
   EnvironmentFile=-/etc/otelcol-contrib/env
   
   # Set Oracle environment
   Environment=LD_LIBRARY_PATH=/usr/lib/oracle/21/client64/lib
   Environment=ORACLE_HOME=/usr/lib/oracle/21/client64
   
   [Install]
   WantedBy=multi-user.target

8. Start and Enable the Service

   Start the OpenTelemetry Collector service and enable it to start automatically:

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

Understanding Oracle Metrics

The Oracle Database receiver collects comprehensive performance metrics:

Session and Connection Metrics

• Active Sessions: Currently active database sessions
• Process Count: Total database processes running
• CPU Time: Database CPU utilization
• Session Wait Events: Sessions waiting on various events

I/O Performance Metrics

• Physical Reads/Writes: Disk I/O operations
• Direct Path Operations: Bypass buffer cache I/O
• I/O Request Counts: Number of I/O operations
• Block Read/Write Operations: Database block-level I/O

Buffer Cache Performance

• Consistent Gets: Logical reads from buffer cache
• DB Block Gets: Current mode block gets
• Cache Hit Ratios: Effectiveness of buffer cache
• Block Changes: Modified blocks in buffer cache

SQL Execution Metrics

• SQL Execute Count: Total SQL statement executions
• Parse Calls: SQL statement parsing operations
• Hard Parse Count: Full SQL parsing operations
• Cursor Cache Effectiveness: SQL statement reuse

Memory Management

• PGA Statistics: Program Global Area utilization
• SGA Statistics: System Global Area utilization
• Sort Operations: Memory vs disk sorting
• Hash Area Usage: Hash join memory utilization

Advanced Configuration

Multi-Instance Monitoring

Monitor multiple Oracle instances from a single collector:

receivers:
  oracledb/prod:
    endpoint: prod-oracle:1521
    service: PROD
    username: last9
    password: ${env:ORACLE_PROD_PASSWORD}
    collection_interval: 60s

  oracledb/test:
    endpoint: test-oracle:1521
    service: TEST
    username: last9
    password: ${env:ORACLE_TEST_PASSWORD}
    collection_interval: 120s # Less frequent for test

service:
  pipelines:
    metrics/prod:
      receivers: [oracledb/prod]
      processors: [batch, transform/oracle]
      exporters: [otlp/last9]
    metrics/test:
      receivers: [oracledb/test]
      processors: [batch, transform/oracle]
      exporters: [otlp/last9]

Custom Metric Selection

Enable only specific metrics for targeted monitoring:

receivers:
  oracledb:
    endpoint: localhost:1521
    service: ORCL
    username: last9
    password: ${env:ORACLE_PASSWORD}

    # Focus on critical performance metrics
    metrics:
      oracledb.sessions:
        enabled: true
      oracledb.physical_reads:
        enabled: true
      oracledb.physical_writes:
        enabled: true
      oracledb.consistent_gets:
        enabled: true
      oracledb.db_block_gets:
        enabled: true
      oracledb.cpu_time:
        enabled: true
      # Disable less critical metrics
      oracledb.parallel_operations_not_downgraded:
        enabled: false

Performance Optimization

Optimize collector performance for high-load Oracle instances:

receivers:
  oracledb:
    collection_interval: 30s # More frequent collection

processors:
  batch:
    timeout: 10s
    send_batch_size: 5000
    send_batch_max_size: 8000

  memory_limiter:
    limit_mib: 512
    spike_limit_mib: 128

  transform/oracle:
    metric_statements:
      - context: metric
        statements:
          - set(resource.attributes["database.instance"], "primary")
          - set(resource.attributes["database.version"], "19c")

Verification

1. Check Service Status

   Verify the OpenTelemetry Collector service is running:

   sudo systemctl status otelcol-contrib

2. Monitor Service Logs

   Check for any configuration errors or connection issues:

   sudo journalctl -u otelcol-contrib -f

   Look for successful Oracle connection messages and metric collection activity.

3. Test Oracle Connectivity

   Verify the collector can connect to Oracle Database:

   # Test with monitoring user
   sqlplus last9/secure_monitoring_password@localhost:1521/ORCL
   
   # Check accessible views
   SQL> SELECT COUNT(*) FROM V_$SESSION;
   SQL> SELECT COUNT(*) FROM V_$SYSSTAT;
   SQL> SELECT COUNT(*) FROM DBA_TABLESPACES;

4. Verify Oracle Client Libraries

   Ensure Oracle libraries are properly installed:

   # Check library path
   echo $LD_LIBRARY_PATH
   
   # Test library loading
   ldd /usr/bin/otelcol-contrib | grep oracle
   
   # Test basic Oracle connectivity
   tnsping localhost:1521

5. Generate Database Activity

   Create some database activity to generate metrics:

   -- Connect to Oracle and run some queries
   SELECT COUNT(*) FROM ALL_TABLES;
   SELECT * FROM V_$VERSION;
   
   -- Create test load
   SELECT COUNT(*) FROM DBA_OBJECTS;
   SELECT COUNT(*) FROM DBA_TAB_COLUMNS;

6. 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
   • oracledb_physical_reads
   • oracledb_consistent_gets
   • oracledb_cpu_time

Key Metrics to Monitor

Critical Performance Indicators

Capacity Planning

Performance Tuning

Troubleshooting

Connection Issues

Oracle Connection Failed:

# Check Oracle listener status
lsnrctl status

# Test basic connectivity
telnet oracle-server 1521

# Check TNS configuration
cat $ORACLE_HOME/network/admin/tnsnames.ora

Authentication Errors:

-- Verify user exists and has privileges
SELECT USERNAME, ACCOUNT_STATUS FROM DBA_USERS WHERE USERNAME = 'LAST9';

-- Check granted privileges
SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE = 'LAST9';
SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = 'LAST9';

Oracle Client Library Issues

Library Not Found:

# Check if Oracle libraries are installed
ls -la /usr/lib/oracle/*/client64/lib/

# Update library path
export LD_LIBRARY_PATH=/usr/lib/oracle/21/client64/lib:$LD_LIBRARY_PATH

# Add to system configuration
echo '/usr/lib/oracle/21/client64/lib' | sudo tee -a /etc/ld.so.conf.d/oracle.conf
sudo ldconfig

Version Compatibility:

• Ensure Oracle client version is compatible with database version
• Oracle 21c client can connect to Oracle 11.2 and later
• Check Oracle documentation for version compatibility matrix

Missing Metrics

No Performance Metrics:

-- Check if performance views are accessible
SELECT COUNT(*) FROM V_$SYSSTAT;
SELECT COUNT(*) FROM V_$SESSION;

-- Verify statistics collection is enabled
SELECT VALUE FROM V_$PARAMETER WHERE NAME = 'statistics_level';

Permission Denied Errors:

-- Grant additional privileges if needed
GRANT SELECT ON V_$SESSTAT TO last9;
GRANT SELECT ON V_$MYSTAT TO last9;
GRANT SELECT ON V_$STATNAME TO last9;

High Resource Usage

Collector Memory Usage:

# Optimize collection settings
receivers:
  oracledb:
    collection_interval: 120s # Reduce collection frequency

processors:
  memory_limiter:
    limit_mib: 256 # Lower memory limit

  batch:
    timeout: 30s
    send_batch_size: 5000 # Smaller batches

Best Practices

Security

• Monitoring User: Create dedicated monitoring users with minimal required privileges
• Password Security: Store database passwords in environment variables or secret management systems
• Network Security: Use Oracle native network encryption (TCPS) for sensitive environments
• Audit Access: Monitor monitoring user access through Oracle audit trails

Performance

• Collection Intervals: Balance monitoring granularity with database performance impact
• Metric Selection: Enable only metrics relevant to your monitoring goals
• Connection Pooling: Use Oracle connection pooling for high-frequency collection
• Resource Limits: Set appropriate memory and CPU limits for the collector

Monitoring Strategy

• Baseline Metrics: Establish performance baselines for comparison and capacity planning
• Alert Thresholds: Set appropriate alerts for critical metrics like CPU, I/O, and locks
• Capacity Planning: Monitor resource utilization trends for database scaling decisions
• Performance Tuning: Use metrics to identify and resolve performance bottlenecks

Database Management

• Statistics Collection: Ensure Oracle statistics collection is enabled and up-to-date
• AWR Reports: Complement OpenTelemetry monitoring with Oracle AWR reports
• Index Monitoring: Monitor index usage and performance through custom queries
• Tablespace Management: Set up alerts for tablespace utilization and growth

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