Oracle Database

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 a receiver for the metrics that Oracle DB produces and then create a pipeline for the new receiver.

This receiver does not support the use of multiple instances in the configuration, for example, to monitor multiple endpoints. All such instances write to the same time series, and Cloud Monitoring has no way to distinguish among them.

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
CUMULATIVE, INT64
gce_instance 		database_id
db_system
global_name
workload.googleapis.com/oracle.buffer.cache.ratio
GAUGE, DOUBLE
gce_instance 		database_id
db_system
global_name
instance_id
workload.googleapis.com/oracle.cursor.count
CUMULATIVE, INT64
gce_instance 		database_id
db_system
global_name
instance_id
workload.googleapis.com/oracle.cursor.current
GAUGE, INT64
gce_instance 		database_id
db_system
global_name
instance_id
workload.googleapis.com/oracle.disk.operation.count
CUMULATIVE, INT64
gce_instance 		database_id
db_system
direction
global_name
instance_id
workload.googleapis.com/oracle.disk.operation.size
CUMULATIVE, INT64
gce_instance 		database_id
db_system
direction
global_name
instance_id
workload.googleapis.com/oracle.logon.count
CUMULATIVE, INT64
gce_instance 		database_id
db_system
global_name
instance_id
workload.googleapis.com/oracle.logon.current
GAUGE, INT64
gce_instance 		database_id
db_system
global_name
instance_id
workload.googleapis.com/oracle.network.data
CUMULATIVE, INT64
gce_instance 		database_id
db_system
direction
global_name
instance_id
target
workload.googleapis.com/oracle.process.count
GAUGE, INT64
gce_instance 		database_id
db_system
global_name
instance_id
workload.googleapis.com/oracle.process.limit
GAUGE, INT64
gce_instance 		database_id
db_system
global_name
instance_id
workload.googleapis.com/oracle.process.pga_memory.size
GAUGE, INT64
gce_instance 		database_id
db_system
global_name
instance_id
program
state
workload.googleapis.com/oracle.row.cache.ratio
GAUGE, DOUBLE
gce_instance 		database_id
db_system
global_name
instance_id
workload.googleapis.com/oracle.service.response_time
GAUGE, DOUBLE
gce_instance 		database_id
db_system
global_name
instance_id
workload.googleapis.com/oracle.session.count
GAUGE, INT64
gce_instance 		database_id
db_system
global_name
instance_id
workload.googleapis.com/oracle.session.limit
GAUGE, INT64
gce_instance 		database_id
db_system
global_name
instance_id
workload.googleapis.com/oracle.sort.count
CUMULATIVE, INT64
gce_instance 		database_id
db_system
global_name
instance_id
type
workload.googleapis.com/oracle.sort.row.count
CUMULATIVE, INT64
gce_instance 		database_id
db_system
global_name
instance_id
workload.googleapis.com/oracle.tablespace.count
GAUGE, INT64
gce_instance 		contents
database_id
db_system
global_name
status
workload.googleapis.com/oracle.tablespace.size
GAUGE, INT64
gce_instance 		contents
database_id
db_system
global_name
state
tablespace_name
workload.googleapis.com/oracle.user.calls
CUMULATIVE, INT64
gce_instance 		database_id
db_system
global_name
instance_id
workload.googleapis.com/oracle.user.commits
CUMULATIVE, INT64
gce_instance 		database_id
db_system
global_name
instance_id
workload.googleapis.com/oracle.user.rollbacks
CUMULATIVE, INT64
gce_instance 		database_id
db_system
global_name
instance_id
workload.googleapis.com/oracle.wait.count
CUMULATIVE, INT64
gce_instance 		database_id
db_system
global_name
instance_id
type
wait_class
workload.googleapis.com/oracle.wait.time
CUMULATIVE, INT64
gce_instance 		database_id
db_system
global_name
instance_id
type
wait_class
workload.googleapis.com/oracle.wait.timeouts
CUMULATIVE, INT64
gce_instance 		database_id
db_system
global_name
instance_id
type
wait_class

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 Oracle DB logs are being sent to Cloud Logging, do the following:

  1. In the navigation panel of the Google Cloud console, select Logging, and then select Logs Explorer:

    Go to Logs Explorer

  2. Enter the following query in the editor, and then click Run query: 
    resource.type="gce_instance"
(log_id("oracledb_alert") OR log_id("oracledb_audit"))

To verify that Oracle DB metrics are being sent to Cloud Monitoring, do the following:

  1. In the navigation panel of the Google Cloud console, select Monitoring, and then select  Metrics explorer:

    Go to Metrics explorer

  2. In the toolbar of the query-builder pane, select the button whose name is either  MQL or  PromQL.
  3. Verify that MQL is selected in the Language toggle. The language toggle is in the same toolbar that lets you format your query.
  4. Enter the following query in the editor, and then click Run query: 
    fetch gce_instance
| metric 'workload.googleapis.com/oracle.logon.count'
| every 1m

View dashboard

To view your Oracle DB metrics, you must have a chart or dashboard configured. The Oracle DB integration includes one or more dashboards for you. Any dashboards are automatically installed after you configure the integration and the Ops Agent has begun collecting metric data.

You can also view static previews of dashboards without installing the integration.

To view an installed dashboard, do the following:

  1. In the navigation panel of the Google Cloud console, select Monitoring, and then select  Dashboards:

    Go to Dashboards

  2. Select the Dashboard List tab, and then choose the Integrations category.
  3. Click the name of the dashboard you want to view.

If you have configured an integration but the dashboard has not been installed, then check that the Ops Agent is running. When there is no metric data for a chart in the dashboard, installation of the dashboard fails. After the Ops Agent begins collecting metrics, the dashboard is installed for you.

To view a static preview of the dashboard, do the following:

  1. In the navigation panel of the Google Cloud console, select Monitoring, and then select  Integrations:

    Go to Integrations

  2. Click the Compute Engine deployment-platform filter.
  3. Locate the entry for Oracle DB and click View Details.
  4. Select the Dashboards tab to see a static preview. If the dashboard is installed, then you can navigate to it by clicking View dashboard.

For more information about dashboards in Cloud Monitoring, see Dashboards and charts.

For more information about using the Integrations page, see Manage integrations.

Install alerting policies

Alerting policies instruct Cloud Monitoring to notify you when specified conditions occur. The Oracle DB integration includes one or more alerting policies for you to use. You can view and install these alerting policies from the Integrations page in Monitoring.

To view the descriptions of available alerting policies and install them, do the following:

  1. In the navigation panel of the Google Cloud console, select Monitoring, and then select  Integrations:

    Go to Integrations

  2. Locate the entry for Oracle DB and click View Details.
  3. Select the Alerts tab. This tab provides descriptions of available alerting policies and provides an interface for installing them.
  4. Install alerting policies. Alerting policies need to know where to send notifications that the alert has been triggered, so they require information from you for installation. To install alerting policies, do the following:
    1. From the list of available alerting policies, select those that you want to install.

    2. In the Configure notifications section, select one or more notification channels. You have the option to disable the use of notification channels, but if you do, then your alerting policies fire silently. You can check their status in Monitoring, but you receive no notifications.

      For more information about notification channels, see Manage notification channels.

    3. Click Create Policies.

For more information about alerting policies in Cloud Monitoring, see Introduction to alerting.

For more information about using the Integrations page, see Manage integrations.

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.