topic Re: Oracle Database Extension - Useful Metrics to Monitor in Extensions

Oracle Database Extension - Useful Metrics to Monitor

badgerfifteen — Mon, 13 Jan 2025 13:58:22 GMT

Hi all,

Just wondering on what metrics are useful to monitor/alert? Curious how everyone has set it up in their environment.

Re: Oracle Database Extension - Useful Metrics to Monitor

PacoPorro — Mon, 13 Jan 2025 15:23:59 GMT

My suggestion as minimum:

Related to performance

CPU Usage: total amount of CPU time consumed by the Oracle database. High CPU usage could indicate inefficient queries or resource contention.
Database's memory utilization to prevent memory-related issues
Duration of SQL queries to identify if there's a problem with slow-running/problematic statements
Logical Reads: number of data blocks read from the buffer cache during query execution. High numbers of logical reads may indicate inefficient SQL queries or missing indexes.
SQL parse time: High parse times can impact database responsiveness.

Related to resource

Disk I/O Statistics: read and write operations to evaluate storage subsystem performance
Average Active Sessions (AAS) and Blocked Sessions compared with Total sessions. This insights into database activity and helps identify bottlenecks related to CPU usage

Database Health

Space utilization of tablespaces
Redo log wait time: High wait times can signify that the log buffer is too small or that the LGWR process isn't writing to disk fast enough
Wait Events: to identify resource contention and performance bottlenecks

Others:

Process availability
Error Rates

Re: Oracle Database Extension - Useful Metrics to Monitor

badgerfifteen — Tue, 14 Jan 2025 11:15:45 GMT

Thanks. This is a good starter 🙂

I noticed in the extension that it mentioned that you could pull up login calls through the logs. I've tried logging in several times but I've not been able to find an entry relating to logins at all within the logs. Just wondering if you know how to get the most out of the logs.

Re: Oracle Database Extension - Useful Metrics to Monitor

PacoPorro — Tue, 14 Jan 2025 11:24:25 GMT

Check the extension information you can find it under
Log Management and Analytics

Audit log files tracking

Check log file's location

SELECT name, value FROM v$parameter WHERE name = 'audit_trail';

OS means that the audit logs are stored locally in the file.

SELECT value FROM v$parameter WHERE name = 'audit_file_dest';

specifies the log file's location

Add LogAgent security rules

Configure LogAgent's security rules to allow access to local files, by adding the oracle.json file under :

on Linux/UNIX: /var/lib/dynatrace/oneagent/agent/config/logmodule
on Windows: %PROGRAMDATA%\dynatrace\oneagent\agent\config\logmodule

{
    "@version": "1.0.0",
    "allowed-log-paths-configuration": [
        {
            "directory-pattern": "/u01/app/oracle/admin/oracle_standalone/adump/",
            "file-pattern": "*.aud",
            "action": "INCLUDE"
        }
    ]
}

Agent restart is not required, this config will be applied within 1 minute.

Configure custom log source and log ingest rules

Custom log source

Use the log file location fetched via the query executed in the first step, e.g. /u01/app/oracle/admin/oracle_standalone/adump/*.

Log ingest rules

An example configuration that includes only ORA-01017 that reports invalid username or password logon attempts could be configured using: Log content is any of: (.*)RETURNCODE:\[(\d+)\] "1017 AND Log source is any of: /u01/app/oracle/admin/oracle_standalone/adump/*

Accessing logs

Logs collected the way described above can be accessed using following DQL query: fetch logs | filter matchesValue(log.source, "/u01/app/oracle/admin/oracle_standalone/adump/*").

Metrics extraction

Log processing rule

Configure log processing rule to extract log attributes out of the log content:

Rule name: preferred name
Matcher: matchesValue(log.source, "/u01/app/oracle/admin/oracle_standalone/adump/*")
Processor definition:

PARSE(content, "
  DATA ' RETURNCODE:['INT']' SPACE '\"' INT:ora.returncode LD") |
PARSE(content, "
  DATA ' USERID:['INT']'SPACE CSVDQS:ora.userid LD") | 
PARSE(content, "
  DATA ' USERHOST:['INT']'SPACE CSVDQS:ora.userhost LD")

Metric extraction

Extract metrics from log entries to enable alerting:

Metric key: e.g.log.oracle.invalid_credentials
Matcher: matchesValue(log.source, "/u01/app/oracle/admin/oracle_standalone/adump/*")
Metric measurement: Occurence of logs records
Dimensions: specify the log attributes to be used as metric dimensions

Re: Oracle Database Extension - Useful Metrics to Monitor

badgerfifteen — Tue, 14 Jan 2025 11:40:10 GMT

Does this require a OneAgent on the actual database server itself?

Re: Oracle Database Extension - Useful Metrics to Monitor

PacoPorro — Tue, 14 Jan 2025 11:53:52 GMT

yes