Oracle Database

Dynatrace automatically detects all applications and microservices deployed in your system and how your system uses Oracle Database.

Dynatrace diagnoses anomalies in real time with AI and pinpoints the root cause in slow-performing or erroneous SQL statements. Deep code-level insights combined with cloud-native database server monitoring help you ensure a robust production environment.

The Oracle Database extension complements the client-side database usage insights by providing server-side observability starting with availability and performance statistics, down to individual SQL statements and log insights.

• Understand all database dependencies of your applications, which database statements are executed, and their performance.
• Improve the performance of your application by reducing or optimizing typical database patterns like the 1+N query problem
• Understand the resource impact that your applications have on your database.
• Understand the impact that resource shortages or other database issues have on your application by observing the database server itself.
• Manage and analyze DB audit logs to spot invalid login attempts
• Track the health and performance of Oracle Database servers (either standalone or multi-tenant)
• Track the most time consuming queries performance
• Speed up application-problem diagnosis when issues are caused by the database

Get started for Oracle Database clients:

If the application that connects to the Oracle Database server runs on a virtual machine or bare-metal, install OneAgent on it to get started.

If the application that connects to the Oracle Database server runs as a workload in Kubernetes or OpenShift, set up Dynatrace on Kubernetes or OpenShift.

Activate the following OneAgent features to get full tracing insight:

• Node.js Oracle DB
• PHP Oracle

Get started with Oracle Database servers:

If your Oracle Database server runs on a virtual machine or bare-metal, install OneAgent on it to get started with system performance metrics.

Activate the remote Oracle Database extension to get full insight into the Oracle Database server's health and performance combined with metrics and events.

The extension collects data by directly querying the database's performance views and thus requires a connection to the database. A dedicated user account is recommended for monitoring. For convenience, user creation scripts can be found here. These scripts will create a user with the CREATE_SESSION role and SELECT permissions on the following performance views:

• CDB_DATA_FILES
• CDB_TABLESPACE_USAGE_METRICS
• CDB_TABLESPACES
• CDB_TEMP_FILES
• GV_$ARCHIVE_DEST
• GV_$ARCHIVE_DEST_STATUS
• V_$ASM_DISK_STAT
• V_$ASM_DISKGROUP_STAT
• V_$BACKUP_SET_DETAILS
• GV_$CLUSTER_INTERCONNECTS
• GV_$CONTAINERS
• V_$CONTAINERS
• GV_$CON_SYS_TIME_MODEL
• GV_$CON_SYSSTAT
• GV_$DATABASE
• V_$DATABASE
• V_$DATABASE_BLOCK_CORRUPTION
• GV_$DATAFILE
• V_$DATAFILE
• GV_$DATAGUARD_STATUS
• GV_$INSTANCE
• GV_$INSTANCE_PING
• GV_$LIBRARYCACHE
• V_$LOG
• GV_$METRIC
• GV_$METRICGROUP
• GV_$PARAMETER
• GV_$PDBS
• GV_$PGASTAT
• V_$RECOVER_FILE
• V_$RECOVERY_FILE_DEST
• GV_$RESOURCE_LIMIT
• V_$RMAN_BACKUP_JOB_DETAILS
• GV_$SESSION
• V_$SESSION
• GV_$SGASTAT
• GV_$SQL
• V_$SQL
• GV_$SQLAREA
• V_$SQL_PLAN
• V_$SQL_PLAN_STATISTICS_ALL
• GV_$SYSSTAT
• GV_$SYSTEM_EVENT
• GV_$SYSTEM_WAIT_CLASS
• V_$TABLESPACE
• V_$TEMPFILE

Alternatively, the SELECT_CATALOG_ROLE can be assigned to the monitoring user.

While the extension supports connecting directly to a Pluggable Database (PDB), our recommended approach to monitor a multitenant Oracle DB setup is to point to extension the CDB. PDB discovery and monitoring will occur automatically. In this case, explicit permissions to access data across all containers may be needed:

alter user <your_username> set container_data=all container = current;

These privileges are already included in the user creation scripts.

Users of the Databases App who want to make use of Query Execution Plans, will also need to provide permission for the V$SQL_PLAN, V$SESSION, V$SQL_PLAN_STATISTICS_ALL and V$SQL views. These permissions are included in the user creation scripts. See the execution plan monitoring section below for more details.

Finally, activate log monitoring to get full log insight.

Note:
With the TopN feature set, the extension reports the most time consuming queries on the Oracle Instance entity page, in a card called "Logs". Enabling this feature may expose sensitive data in the reported queries.

The following Oracle versions are supported:

• Oracle DB 12.2+

The following architectures are supported:

• Oracle standalone servers
• Oracle Multitenant (CDB/PDB)
  • Configure monitoring for CDB to detect all PDBs
• Oracle AWS RDS
• Oracle RAC
  • Configure monitoring for the SCAN listener to detect all instances and databases.

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

For SaaS customers with access to the Databases app, the execution plan monitoring functionality is also available when analyzing statement performance.

For this feature to work, the DBMS_XPLAN package needs to be available, and the user configured in the Dynatrace extension needs to have permissions to call the DISPLAY_CURSOR function. As per the official documentation this means that SELECT or READ permissions are required on the following views:

• V$SQL_PLAN
• V$SESSION
• V$SQL_PLAN_STATISTICS_ALL
• V$SQL

In addition to this, the SELECT permission is also required for V$SQL_PLAN.

Please note that the execution plan monitoring feature may sometimes not work as expected for RAC deployments. This will be fixed as soon as possible.

Query performance tracking may expose sensitive data in reported statements. Consequently, Dynatrace provides an optional mechanism that allows masking selected attributes. Details are available in the official documentation.

The configurations below present and example of how the sensitive data can be masked within the statements tracked.

• create new processing rule under Settings -> Log Monitoring -> Processing
  • Processor definition: USING(INOUT content) | FIELDS_ADD(content: REPLACE_PATTERN(content, "(\"'\"):p1 (LD):p2 (\"'\"):p3", "${p1}${p2|sha1}${p3}"))
• or create/modify your custom extension to distribute such rules in your environment:

logProcessingRules:
  - ruleName: TopN statements masking
    query: event.group="query_performance"
    enabled: true
    ProcessorDefinition:
      rule: |
        USING(INOUT content) | FIELDS_ADD(content: REPLACE_PATTERN(content, "(\"'\"):p1 (LD):p2 (\"'\"):p3", "${p1}${p2|sha1}${p3}"))
    RuleTesting:
      sampleLog: |
        {
          "event.group": "query_performance",
          "content": "/*dt:ownQuery*/SELECT DECODE(name, 'sessions', value) AS sessions_limit, DECODE(name, 'processes', value) AS processes_limit FROM v$parameter WHERE name IN('sessions', 'processes')"
        }

Please adjust this rule to match your use case.

The metrics collected through this extension consume Dynatrace Davis Data Units (see DDUs for metrics).

A rough estimation of the amount of DDUs consumed by metric ingest can be obtained through the following formula:

(55 * number of instances * 525.6) + (7 * number of databases * 525.6) + (8 * number of backup jobs per year * 0.005) + (5 * number of ASM disks * 525.6) + (3 * number of ASM disk groups * 525.6)

For logs, regular DDU consumption for log monitoring applies. Depending on your licensing model, refer either to DDU consumption for Log Management and Analytics or DDUs for Log Monitoring Classic.

Why am I missing Data guard metrics?

For some of the Dataguard metrics, not seeing any data is the correct behavior and it means the Data guard deployment is working as expected. The following metrics will only show datapoints in case there are issues which need further investigation:

• NOLOGGING activity will record a metric datapoint whenever there are operations executed against the database which will not generate redo log records. This requires investigation because from a Data guard perspective the mechanism is completely bypassed when such activity occurs, leaving the standby vulnerable until these files are manually refreshed.
• Dataguard severe events will record a metric datapoint whenever Data guard reports a fatal or severe event. In such a situation, the metric dimensions will carry the event message/description which should be further investigated on the Database instance.
• Archive destination status will record a metric datapoint whenever the Database's archive destinations are reporting an Invalid or Error status. The metric dimensions will show a description of the status as well as point to the archive destination which reported the error.