Oracle Database

Stay organized with collections Save and categorize content based on your preferences.

The Oracle DB integration collects Oracle DB metrics and logs. The metrics are collected by querying relevant monitoring views. This integration writes structured trace logs.

For more information about Oracle DB, see the Oracle Database documentation.

Prerequisites

To collect Oracle DB telemetry, you must install the Ops Agent version 2.22.0 or higher.

This integration supports Oracle DB versions 12.2, 18c, 19c, and 21c.

Configure your Oracle DB instance

To collect metrics, a monitoring user requires SELECT access to the relevant views. The following sql script should create a monitoring user and give it the appropriate permissions if executed by a user with sufficient permissions connected to the Oracle DB instance as SYSDBA or SYSOPER.

-- Create the monitoring user "otel"
CREATE USER otel IDENTIFIED BY ;

-- Grant the "otel" user the required permissions
GRANT CONNECT TO otel;
GRANT SELECT ON SYS.GV_$DATABASE to otel;
GRANT SELECT ON SYS.GV_$INSTANCE to otel;
GRANT SELECT ON SYS.GV_$PROCESS to otel;
GRANT SELECT ON SYS.GV_$RESOURCE_LIMIT to otel;
GRANT SELECT ON SYS.GV_$SYSMETRIC to otel;
GRANT SELECT ON SYS.GV_$SYSSTAT to otel;
GRANT SELECT ON SYS.GV_$SYSTEM_EVENT to otel;
GRANT SELECT ON SYS.V_$RMAN_BACKUP_JOB_DETAILS to otel;
GRANT SELECT ON SYS.V_$SORT_SEGMENT to otel;
GRANT SELECT ON SYS.V_$TABLESPACE to otel;
GRANT SELECT ON SYS.V_$TEMPFILE to otel;
GRANT SELECT ON SYS.DBA_DATA_FILES to otel;
GRANT SELECT ON SYS.DBA_FREE_SPACE to otel;
GRANT SELECT ON SYS.DBA_TABLESPACE_USAGE_METRICS to otel;
GRANT SELECT ON SYS.DBA_TABLESPACES to otel;
GRANT SELECT ON SYS.GLOBAL_NAME to otel;

Configure the Ops Agent for Oracle DB

Following the guide for Configuring the Ops Agent, add the required elements to collect telemetry from Oracle DB instances, and restart the agent.

Example configuration

The following commands create the configuration to collect and ingest telemetry for Oracle DB and restarts the Ops Agent.

# Configures Ops Agent to collect telemetry from the app and restart Ops Agent.

set -e

# Create a back up of the existing file so existing configurations are not lost.
sudo cp /etc/google-cloud-ops-agent/config.yaml /etc/google-cloud-ops-agent/config.yaml.bak

# Configure the Ops Agent.
sudo tee /etc/google-cloud-ops-agent/config.yaml > /dev/null << EOF
logging:
  receivers:
    oracledb_audit:
      type: oracledb_audit
      oracle_home: /opt/oracle
    oracledb_alert:
      type: oracledb_alert
      oracle_home: /opt/oracle
  service:
    pipelines:
      oracledb:
        receivers:
          - oracledb_audit
          - oracledb_alert
metrics:
  receivers:
    oracledb:
      type: oracledb
      username: otel
      password: ora19c
      sid: ORCLCDB
      service_name: ORCLCDB
  service:
    log_level: debug
    pipelines:
      oracledb:
        receivers:
          - oracledb
EOF

sudo service google-cloud-ops-agent restart

Configure logs collection

To ingest logs from Oracle DB, you must create receivers for the logs that Oracle DB produces and then create a pipeline for the new receivers.

To configure a receiver for your oracledb_audit logs, specify the following fields:

Field Default Description
exclude_paths [] A list of filesystem path patterns to exclude from the set matched by include_paths.
include_paths A list of filesystem paths to read by tailing each file. A wild card (*) can be used in the paths. Cannot be provided with the oracle_home field.
oracle_home Location of the ORACLE_HOME for the environment; when provided it sets the include_paths to $ORACLE_HOME/admin/*/adump/*.aud. Cannot be provided with the include_paths field.
record_log_file_path false If set to true, then the path to the specific file from which the log record was obtained appears in the output log entry as the value of the agent.googleapis.com/log_file_path label. When using a wildcard, only the path of the file from which the record was obtained is recorded.
type The value must be oracledb_audit.
wildcard_refresh_interval 60s The interval at which wildcard file paths in include_paths are refreshed. Given as a time duration parsable by time.ParseDuration, for example 30s or 2m. This property might be useful under high logging throughputs where log files are rotated faster than the default interval.

To configure a receiver for your oracledb_alert logs, specify the following fields:

Field Default Description
exclude_paths [] A list of filesystem path patterns to exclude from the set matched by include_paths.
include_paths A list of filesystem paths to read by tailing each file. A wild card (*) can be used in the paths. Cannot be provided with the oracle_home field.
oracle_home Location of the ORACLE_HOME for the environment, when provided it sets the include_paths to $ORACLE_HOME/diag/rdbms/*/*/trace/alert_*.log. Cannot be provided with the include_paths field.
record_log_file_path false If set to true, then the path to the specific file from which the log record was obtained appears in the output log entry as the value of the agent.googleapis.com/log_file_path label. When using a wildcard, only the path of the file from which the record was obtained is recorded.
type The value must be oracledb_alert.
wildcard_refresh_interval 60s The interval at which wildcard file paths in include_paths are refreshed. Given as a time duration parsable by time.ParseDuration, for example 30s or 2m. This property might be useful under high logging throughputs where log files are rotated faster than the default interval.

What is logged

The logName is derived from the receiver IDs specified in the configuration. Detailed fields inside the LogEntry are as follows.

The oracledb_alert logs contain the following fields in the LogEntry:

Field Type Description
jsonPayload.message string Log message
severity string (LogSeverity) Log entry level (translated)

The oracledb_audit logs contain the following fields in the LogEntry:

Field Type Description
jsonPayload.action string Action being logged in the audit log
jsonPayload.action_number number Number identifying the type of action being logged
jsonPayload.client_terminal string Identifier of the client terminal where the action originated
jsonPayload.client_user string Client user for the audited action
jsonPayload.database_user string Database user for the audited action
jsonPayload.dbid number Database identifier
jsonPayload.length number Length of the string representing the action being logged
jsonPayload.privilege string Database privilege the action was executed under
jsonPayload.sessionid number Session identifier
jsonPayload.status string Status of the action
jsonPayload.user_host string Host where the audited action originated
severity string (LogSeverity) Log entry level (translated)

Configure metrics collection

To ingest metrics from Oracle DB, you must create receivers for the metrics that Oracle DB produces and then create a pipeline for the new receivers.

To configure a receiver for your oracledb metrics, specify the following fields:

Field Default Description
collection_interval 60s A time duration value, such as 30s or 5m.
endpoint localhost:1521 The endpoint used to connect to the oracle DB instance. This field supports either host:port or a unix socket path.
insecure true Sets whether or not to use a secure TLS connection. If set to false, then TLS is enabled.
insecure_skip_verify false Sets whether or not to skip verifying the certificate. If insecure is set to true, then the insecure_skip_verify value is not used.
password The password used to connect to the instance.
service_name The Service Name of the Oracle database being monitored. Use this field or the sid field as appropriate.
sid The SID of the Oracle database being monitored. Use this field or the service_name field as appropriate.
type This value must be oracledb.
username The username used to connect to the instance.
wallet Path to the directory containing the oracle wallet optionally used for authentication and securing connections.

What is monitored

The following table provides the list of metrics that the Ops Agent collects from the Oracle DB instance.

Metric type 
Kind, Type
Monitored resources
Labels
workload.googleapis.com/oracle.backup.latest
CUMULATIVEINT64
gce_instance
database_id
db_system
global_name
workload.googleapis.com/oracle.buffer.cache.ratio
GAUGEDOUBLE
gce_instance
database_id
db_system
global_name
instance_id
workload.googleapis.com/oracle.cursor.count
CUMULATIVEINT64
gce_instance
database_id
db_system
global_name
instance_id
workload.googleapis.com/oracle.cursor.current
GAUGEINT64
gce_instance
database_id
db_system
global_name
instance_id
workload.googleapis.com/oracle.disk.operation.count
CUMULATIVEINT64
gce_instance
database_id
db_system
direction
global_name
instance_id
workload.googleapis.com/oracle.disk.operation.size
CUMULATIVEINT64
gce_instance
database_id
db_system
direction
global_name
instance_id
workload.googleapis.com/oracle.logon.count
CUMULATIVEINT64
gce_instance
database_id
db_system
global_name
instance_id
workload.googleapis.com/oracle.logon.current
GAUGEINT64
gce_instance
database_id
db_system
global_name
instance_id
workload.googleapis.com/oracle.network.data
CUMULATIVEINT64
gce_instance
database_id
db_system
direction
global_name
instance_id
target
workload.googleapis.com/oracle.process.count
GAUGEINT64
gce_instance
database_id
db_system
global_name
instance_id
workload.googleapis.com/oracle.process.limit
GAUGEINT64
gce_instance
database_id
db_system
global_name
instance_id
workload.googleapis.com/oracle.process.pga_memory.size
GAUGEINT64
gce_instance
database_id
db_system
global_name
instance_id
program
state
workload.googleapis.com/oracle.row.cache.ratio
GAUGEDOUBLE
gce_instance
database_id
db_system
global_name
instance_id
workload.googleapis.com/oracle.service.response_time
GAUGEDOUBLE
gce_instance
database_id
db_system
global_name
instance_id
workload.googleapis.com/oracle.session.count
GAUGEINT64
gce_instance
database_id
db_system
global_name
instance_id
workload.googleapis.com/oracle.session.limit
GAUGEINT64
gce_instance
database_id
db_system
global_name
instance_id
workload.googleapis.com/oracle.sort.count
CUMULATIVEINT64
gce_instance
database_id
db_system
global_name
instance_id
type
workload.googleapis.com/oracle.sort.row.count
CUMULATIVEINT64
gce_instance
database_id
db_system
global_name
instance_id
workload.googleapis.com/oracle.tablespace.count
GAUGEINT64
gce_instance
contents
database_id
db_system
global_name
status
workload.googleapis.com/oracle.tablespace.size
GAUGEINT64
gce_instance
contents
database_id
db_system
global_name
state
tablespace_name
workload.googleapis.com/oracle.user.calls
CUMULATIVEINT64
gce_instance
database_id
db_system
global_name
instance_id
workload.googleapis.com/oracle.user.commits
CUMULATIVEINT64
gce_instance
database_id
db_system
global_name
instance_id
workload.googleapis.com/oracle.user.rollbacks
CUMULATIVEINT64
gce_instance
database_id
db_system
global_name
instance_id
workload.googleapis.com/oracle.wait.count
CUMULATIVEINT64
gce_instance
database_id
db_system
global_name
instance_id
type
wait_class
workload.googleapis.com/oracle.wait.time
CUMULATIVEINT64
gce_instance
database_id
db_system
global_name
instance_id
type
wait_class
workload.googleapis.com/oracle.wait.timeouts
CUMULATIVEINT64
gce_instance
database_id
db_system
global_name
instance_id
type
wait_class

Sample dashboard

To view your Oracle DB metrics, you must have a chart or dashboard configured. Cloud Monitoring provides a library of sample dashboards for integrations, which contain preconfigured charts. For information about installing these dashboards, see Installing sample dashboards.

Verify the configuration

This section describes how to verify that you correctly configured the Oracle DB receiver. It might take one or two minutes for the Ops Agent to begin collecting telemetry.

To verify that the logs are ingested, go to the Logs Explorer and run the following query to view the Oracle DB logs:

resource.type="gce_instance"
(log_id("oracledb_alert") OR log_id("oracledb_audit"))

To verify that the metrics are ingested, go to Metrics Explorer and run the following query in the MQL tab:

fetch gce_instance
| metric 'workload.googleapis.com/oracle.logon.count'
| every 1m

What's next

For a walkthrough on how to use Ansible to install the Ops Agent, configure a third-party application, and install a sample dashboard, see the Install the Ops Agent to troubleshoot third-party applications video.