Set up the Agent for Oracle

You can set up Google Cloud's Agent for Compute Workloads on the host alongside your Oracle Database to collect metrics and to monitor your Oracle Database workloads.

This document shows how to install, configure, and verify Agent for Compute Workloads on Compute Engine instances that run Oracle Database workloads.

The agent collects various Oracle metrics by querying Oracle Database performance views (such as V$DATABASE, V$INSTANCE, and V$DATAGUARD_STATS) and data dictionary views (such as DBA_DATA_FILES and DBA_FREE_SPACE). These metrics are then sent to Cloud Monitoring where they can be visualized and analyzed. See Supported metrics.

System requirements

Operating Systems	Oracle versions	Oracle editions
Red Hat Enterprise Linux versions 7, 8, and 9 Oracle Linux versions 7, 8, and 9	Oracle Database 19c Oracle Database 23ai* Note: The agent hasn't been tested on Oracle Database 23ai running on Red Hat Enterprise Linux 7 or Oracle Linux 7.	Enterprise Edition Standard Edition Express (Free) Edition

Prerequisites

Before you install Agent for Compute Workloads to monitor your Oracle Database workloads, make sure that the following prerequisites are met:

Create a Compute Engine instance if you don't already have one. Configure access scopes to cloud-platform.
Set up root access on the Compute Engine instance.
Enable connectivity to Google Cloud API endpoints, either through public network access or private network access.
If you want to enable metric collection, create a secret in Secret Manager to store the database password for the database user that is to be created later in this guide.

For the examples in this guide, we assume that it's wlmagent-password.
Grant Identity and Access Management (IAM) roles to the service account.

Google Cloud's Agent for Compute Workloads uses the Identity and Access Management (IAM) service account attached to the VM for authentication with Google Cloud and for permission to access Google Cloud resources. Use a new service account that includes the following IAM roles:

Caution: Using the Compute Engine default service account for the Agent for Compute Workloads and giving the account access to Secret Manager significantly increases the security risk. Instead, we recommend that you create a single-purpose service account and limit user's shell access to VMs.
- Compute Viewer (roles/compute.viewer)
- Monitoring Metric Writer (roles/monitoring.metricWriter)
- Secret Manager Secret Accessor (roles/secretmanager.secretAccessor)
  
  To follow the principle of least privilege, we recommend that you grant the Secret Manager Secret Accessor role at the secret level rather than the project level. This limits access only to the specific secrets that the service account needs.
To learn how to grant a role, see Manage access to service accounts.
Enable access to Google Cloud APIs.

To enable access to Google Cloud APIs, see Create a VM and attach the service account.

We recommend that you configure your VM instances to access Google Cloud APIs and manage access control using IAM permissions assigned to the service account. See User-managed service account and Authorization.

If you are running Oracle Database on a VM instance that does not have an external IP address, then enable Private Google Access on the VM's subnet so that the Agent for Compute Workloads can access Google Cloud APIs and services. To enable Private Google Access on a subnet, see Configure Private Google Access.

Install the agent

To install the Agent for Compute Workloads, do the following:

Create a repository configuration file.

sudo tee /etc/yum.repos.d/google-cloud-workload-agent.repo << EOM
[google-cloud-workload-agent]
name=Google Cloud Workload Agent
baseurl=https://packages.cloud.google.com/yum/repos/google-cloud-workload-agent-\$basearch
enabled=1
gpgcheck=0
repo_gpgcheck=1
gpgkey=https://packages.cloud.google.com/yum/doc/yum-key.gpg https://packages.cloud.google.com/yum/doc/rpm-package-key.gpg
EOM

Update the metadata of the package manager.
```
sudo yum makecache
```

Install google-cloud-workload-agent rpm package.

sudo yum install google-cloud-workload-agent

After you install the agent for Oracle, verify the installation of the agent.

Verify agent installation

To verify that the agent is running, select your operating system, and then follow the steps:

Use SSH to connect to your VM instance.

Run the following command:

systemctl status google-cloud-workload-agent

If the agent is functioning properly, then the output contains active (running). For example:

google-cloud-workload-agent.service - Google Cloud Workload Agent
Loaded: loaded (/usr/lib/systemd/system/google-cloud-workload-agent.service; enabled; preset: disabled)
Active: active (running) since Tue 2024-09-03 22:29:57 UTC; 3s ago
Main PID: 274972 (google_cloud_wo)
 Tasks: 10 (limit: 100440)
Memory: 51.2M (max: 1.0G limit: 1.0G available: 972.7M)
   CPU: 625ms
CGroup: /system.slice/google-cloud-workload-agent.service
       └─274972 /usr/bin/google_cloud_workload_agent startdaemon

Sep 03 22:29:57 my_gce_instance systemd[1]: Started Google Cloud Workload Agent.

If the agent isn't running, try restarting the agent.

Configure the agent

After installing the Agent for Compute Workloads, optionally, you can enable other features of the agent by updating the agent's configuration file. For a list of parameters that you can configure for Oracle Database workloads, see Configuration parameters.

After configuring the agent, you can once again verify the installation to make sure that the agent is properly configured.

Configuration parameters

Agent for Compute Workloads supports the following configuration parameters for Oracle:

Parameter		Description
`log_level`	`String` To set the logging level of the agent, add the log levels. The available log levels are as follows: `DEBUG` `INFO` `WARNING` `ERROR` Default is `INFO`.
`log_to_cloud`	`Boolean` To redirect the agent's logs to Cloud Logging, specify `true`. Default is `true`.
`oracle_configuration.enabled`	`Boolean` Optional. Determines whether the Oracle service is active. When set to `true`, or if it's unset and the workload is present, you can configure discovery and metric collection. If set to `false`, the Oracle service and its related functionalities are disabled.
The following child parameters are applicable only when you specify `oracle_configuration.enabled: true`.
`oracle_configuration.oracle_discovery.enabled`	`Boolean` Optional. Determines whether the Oracle Discovery is active. The default value is `true`.
`oracle_configuration.oracle_discovery.update_frequency`	`Int` Optional. Specify the sample interval, in seconds, which determines the frequency at which Agent for Compute Workloads runs the discovery process. The default value is 3600 seconds (1 hour). Make sure the value ends with a lowercase "s" to indicate seconds. For example: `30s`.
`oracle_configuration.oracle_metrics.enabled`	`Boolean` Optional. To enable Agent for Compute Workloads to collect the Oracle monitoring metrics, specify `true`. The default value is `false`.
The following child parameters are applicable only when you specify `oracle_metrics.enabled: true`.
`oracle_configuration.oracle_metrics.collection_frequency`	`Int` Optional. Specify the sample interval, in seconds, which determines the frequency at which Agent for Compute Workloads queries your Oracle Database instances to collect the Oracle monitoring metrics. The default value is 60 seconds. Make sure the value ends with a lowercase "s" to indicate seconds. For example: `30s`.
`oracle_configuration.oracle_metrics.query_timeout`	`String` Optional. Specify the timeout for each query made to the Oracle Database instances. The default value is 10 seconds. Make sure the value ends with a lowercase "s" to indicate seconds. For example: `30s`.
`oracle_configuration.oracle_metrics.connection_parameters.username`	`String` Specifies the user account that is used to query the Oracle Database instance. Make sure that this user has the required premissins to read the performance views in your Oracle Database.
`oracle_configuration.oracle_metrics.connection_parameters.host`	`String` Specifies the identifier of the local machine hosting your database instance.
`oracle_configuration.oracle_metrics.connection_parameters.port`	`Int` Specifies the port on which your Oracle Database instance accepts queries.
`oracle_configuration.oracle_metrics.connection_parameters.service_name`	`String` Specifies the service name for your Oracle Database instance that you want the agent to monitor.
`oracle_configuration.oracle_metrics.connection_parameters.secret.project_id`	`String` Specifies the project ID of the Secret Manager to retrieve the password for the user making queries to the database.
`oracle_configuration.oracle_metrics.connection_parameters.secret.secret_name`	`String` Specifies the name of the secret in Secret Manager that stores the user account's password.

Collect and view Oracle metrics

You can enable collection of metrics for Oracle Database workloads. See Supported metrics.

Enable metric collection

To enable the collection of Oracle metrics using the Agent for Compute Workloads, do the following:

Use SSH to connect to your VM instance.
As the Oracle OS user, run the oraenv tool to set the environment variables. We assume that you've already defined the oracle user and the ORACLE_SID in the /etc/oratab file.
```
sudo su - oracle
export PATH=$PATH:/usr/local/bin
. oraenv
sqlplus / as sysdba
```
As SYSDBA or SYSOPER user, in Oracle Database, create a user for monitoring with a password matching the secret that you created in the Prerequisites.

Note: If you have an Oracle Multitenant container database, prefix the username with the common_user_prefix, usually c##, to create a common user. For example, wlmagent becomes c##wlmagent.
```
CREATE USER wlmagent IDENTIFIED BY password;
```
As SYSDBA or SYSOPER user, grant the following permissions to the monitoring user to query performance views:
- SESSION
- SELECT_CATALOG_ROLE
- SYSDG
```
GRANT CREATE SESSION,SELECT_CATALOG_ROLE,SYSDG TO wlmagent;
```
Exit out of sqlplus and the oracle user.
As the root user, edit the agent's configuration file using your preferred editor.
```
sudo nano /etc/google-cloud-workload-agent/configuration.json
```
In the configuration file, edit the oracle_metrics section to make the following updates:
1. Set the parameter enabled to true.
2. Set the service_name parameter to specify the service name of the Oracle instance that you want the agent to monitor.
3. Specify the Oracle username for the agent to connect to your database.
4. Set the host and port parameters for the agent to connect to your database.
5. Set the secret parameter for the agent to retrieve a password for the username:
  - project_id: The ID of the project which contains the Secret Manager data.
  - secret_name: The name of the secret in Secret Manager.
Following is an example configuration file:
```
{
  "log_level": "INFO",
  "common_discovery": {"collection_frequency": "3600s" },
  "oracle_configuration": {
    "enabled": true,
    "oracle_metrics": {
      "enabled": true,
      "collection_frequency": "30s",
      "connection_parameters": [
        {
          "host": "localhost",
          "port": 1521,
          "service_name": "orcl",
          "username": "wlmagent",
          "secret": {
            "project_id": "my-project",
            "secret_name": "wlmagent_password"
          }
        }
      ]
    }
  }
}
```
Save the configuration file.
Restart the agent for the new settings to take effect.

View the metrics

You can view the collected metrics and monitor the Oracle performance and status either using the Metrics Explorer or by importing a custom dashboard.

View Metrics in Metrics Explorer

To view the Oracle metrics in Metrics Explorer, do the following:

In the Google Cloud console, go to Monitoring.
Click Metrics Explorer.
Under Find resource type and metric, do the following:
1. For Resource type, select VM Instance.
2. For Metric, select the Oracle metrics that you want to view.

You can view real-time and historical data for the selected Oracle metrics, using filters and aggregation as needed.

Import a Custom Dashboard

To view the Oracle metrics collected by the agent, you can create custom Cloud Monitoring dashboards by following the instructions at Create and manage custom dashboards.

We provide an example dashboard oracle-status-overview.json and instructions for how to import it. The oracle-status-overview.json dashboard shows the following Oracle metrics charts:

Database time breakdown
Database memory usage
Average Active Sessions by Wait Class
Disk I/O
CPU utilization
Network Traffic

To import the oracle-status-overview.json dashboard, do the following:

Verify that the gcloud CLI is installed and up to date. For instructions, see Install the gcloud CLI.

From the Agent for Compute Workloads GitHub repository, download the oracle-status-overview.json dashboard:

$ curl -H "Accept: application/vnd.github.v3.raw" -o oracle-status-overview.json https://api.github.com/repos/GoogleCloudPlatform/workloadagent/contents/observability/dashboards/oracle-status-overview.json

Run the following command to import the dashboard:
```
gcloud alpha monitoring dashboards create --config-from-file=oracle-status-overview.json
```
After the command is run, the custom dashboard is created in Cloud Monitoring. To learn how to view a dashboard, see Find and view a dashboard.

Supported metrics

All Oracle metrics collected by the Agent for Compute Workloads are available under the workload.googleapis.com/oracle path.

Following is a list of supported Oracle metrics and their corresponding Cloud Monitoring paths:

Process memory
- Process (PGA) memory in use, in bytes.
  
  process/pga_memory/total_used_size
- Process (PGA) memory allocated, in bytes
  
  process/pga_memory/total_allocated_size
Shared memory
- Size of shared memory (SGA) item, in bytes
  
  process/sga_memory/size
System wait events
- Total number of waits for a registered wait class
  
  sys_wait/count
- Aggregate time spent in this wait class, in seconds
  
  sys_wait/time
- Total foreground waits in this wait class
  
  sys_wait/foreground/count
- Aggregate foreground wait time for this wait class, in seconds
  
  sys_wait/foreground/time
System elapsed time
- Elapsed time spent performing database user-level calls, in seconds
  
  sys_time/db_time
- CPU time spent on database user-level calls, in seconds
  
  sys_time/db_cpu
- Amount of elapsed time SQL statements are executing
  
  sys_time/sql_execute_elapsed_time
- Amount of elapsed time spent parsing SQL statements
  
  sys_time/parse_time_elapsed
- Amount of elapsed time spent running the PL/SQL interpreter
  
  sys_time/pl_sql_execution_elapsed_time
- Elapsed time consumed by database background processes, in seconds
  
  sys_time/background_elapsed_time
I/O statistics
- Total count of read operations (small + large)
  
  iostat/read_ops_count
- Total count of write operations (small + large)
  
  iostat/write_ops_count
- Total number of bytes read
  
  iostat/read_bytes_count
- Total number of bytes written
  
  iostat/write_bytes_count
- Average latency per I/O operation
  
  iostat/average_latency_seconds
Data files
- Allocated size of the data file, in bytes
  
  data_files/total_bytes
- Data file space in use, in bytes
  
  data_files/bytes_used
- Data file free space, in bytes
  
  data_files/available_bytes
- Data file auto-extension limit, in bytes
  
  data_files/max_bytes
- Percentage of the data file used
  
  data_files/percent_used
Database instance
- Instance uptime, in seconds
  
  instance/uptime
- Instance status
  
  instance/status
  
  This metric can have the following values:
  
  UNKNOWN 0
  
  STARTED 1
  
  MOUNTED 2
  
  OPEN 3
  
  OPEN MIGRATE 4
- Open mode
  
  instance/db_open_mode
  
  This metric can have the following values:
  
  UNKNOWN 0
  
  MOUNTED 1
  
  READ WRITE 2
  
  READ ONLY 3
  
  READ ONLY WITH APPLY 4
Oracle Data Guard
- Redo apply delay, in seconds
  
  dataguard/apply_lag
- Redo transport delay, in seconds
  
  dataguard/transport_lag

Manage the agent

Restart the agent

If Agent for Compute Workloads stops working or you update its configuration, then restart the agent.

Use SSH to connect to the VM instance.

Run the following command:

sudo systemctl restart google-cloud-workload-agent

Check the version of the agent

To check the version of your agent, complete the following steps:

Use SSH to connect to the VM instance.
Run the following command:
```
yum info google-cloud-workload-agent
```

Check for updates

Use SSH to connect to the VM instance.

Run the following command:

sudo yum check-update google-cloud-workload-agent

Update the agent

To make sure that you have the latest version of the agent, check for updates periodically and update the agent:

Use SSH to connect to the VM instance.

Run the following command:

sudo yum --nogpgcheck update google-cloud-workload-agent

View agent logs

The Agent for Compute Workloads logs are available at /var/log/google-cloud-workload-agent.log.

Log verbosity is controlled by the log_level parameter. Setting DEBUG log level includes additional information for troubleshooting specific issues, but produces significantly larger logs.

By default, the logs for Agent for Compute Workloads are redirected from your VM instances to Cloud Logging.

To view the agent's logs in Logging, follow these steps:

In the Google Cloud console, go to the Logs Explorer page.

Go to Logs explorer
Go the Query pane.
From the Resources drop-down menu, select Global, and then click Apply.
In the query editor, enter google-cloud-workload-agent.
Click Run query.

You can view the logs generated by the agent running on all your VM instances. You can filter the logs for a specific instance.

Configure agent logs in Cloud Logging

To disable the default redirection of agent's logs to Cloud Logging, follow these steps:

Use SSH to connect to your VM instance.

Open the agent's configuration file:

/etc/google-cloud-workload-agent/configuration.json

For the property log_to_cloud, update the value to false.
Save the configuration file.
Restart the agent for this change to take effect.

Troubleshooting

The following sections provide information about common issues related to using the agent for Oracle Database workloads, their causes and resolution.

Insufficient IAM permissions

Issue: The Agent for Compute Workloads logs show insufficient IAM permissions error.

googleapi: Error 403: The client is not authorized to make this request.

Cause: The service account used by the agent does not have the required IAM permissions to access the Google Cloud APIs or a password for the Oracle user in the Secret Manager.

Resolution: To resolve this issue, make sure that the VM service account has the IAM roles and permissions mentioned in Prerequisites.

Insufficient authentication scopes

Issue: The Agent for Compute Workloads logs show insufficient authentication scopes.

googleapi: Error 403: Request had insufficient authentication scopes.

Cause: The service account used by the agent does not have the required access scope.

Resolution: To resolve this issue, configure the access scopes of the VM to cloud-platform.

Metrics not showing up in Cloud Monitoring

Issue: Metrics from the Agent for Compute Workloads are not visible in Cloud Monitoring.

Cause:

Following are the possible causes of this issue:

The service account used by the Agent for Compute Workloads lacks necessary IAM permissions.
The Oracle user used by the agent does not have sufficient privileges to query performance views.
There are errors in the configuration of the agent.

Resolution:

To resolve the issue with the insufficient permissions for the service account, do the following:
1. In the Google Cloud console, go to the VM instance details page and note the service account being used by the instance running the agent.
2. Go to the IAM & admin page and make sure that the service account has the all the required roles and permissions mentioned in the Prerequisites. Grant any of the required missing roles to the service account.
To resolve the issue with the insufficient permissions for the Oracle user, do the following:
1. Verify that the Oracle user has the following required permissions to query performance views:
  - SESSION
  - SELECT_CATALOG_ROLE
  - SYSDG
2. Grant any of the missing permissions by running the following SQL command:
```
-- Grant the "wlmagent" user the required permissions
GRANT CREATE SESSION,SELECT_CATALOG_ROLE,SYSDG TO USERNAME;
```
To resolve the issue with the misconfiguration of the agent, do the following:
1. Use SSH to connect to your VM instance.
2. Review the agent's logs to identify any errors or issues preventing metric collection. You can find the logs at /var/log/google-cloud-workload-agent.log.
  
  Look for permission errors, misconfigurations, or connectivity issues.
3. Resolve any errors.
4. Restart the agent and check if the metric collection starts.

Failed to load configuration file

Issue: If the configuration file has invalid values, you see the following error.

"Failed to load configuration","pid":3524,"error":"proto: (line 19:42): unknown
field "{field_name}"

Resolution: To resolve this issue, update the configuration file using the details in Configuration parameters.

Failed to initialize data collection

Issue: After installing the agent if the configuration file is not updated, you see the following error:

"Failed to initialize guest collection","pid":2112,"error":"invalid value for "user_name" "secret_name"

Resolution: To resolve this issue, initialize the credential configuration using Configuration parameters.

`UNKNOWN`	`0`
`STARTED`	`1`
`MOUNTED`	`2`
`OPEN`	`3`
`OPEN MIGRATE`	`4`

`UNKNOWN`	`0`
`MOUNTED`	`1`
`READ WRITE`	`2`
`READ ONLY`	`3`
`READ ONLY WITH APPLY`	`4`

Set up the Agent for Oracle Stay organized with collections Save and categorize content based on your preferences.

System requirements

Prerequisites

Install the agent

Verify agent installation

Configure the agent

Configuration parameters

Collect and view Oracle metrics

Enable metric collection

View the metrics

View Metrics in Metrics Explorer

Import a Custom Dashboard

Supported metrics

Manage the agent

Restart the agent

Check the version of the agent

Check for updates

Update the agent

View agent logs

Configure agent logs in Cloud Logging

Troubleshooting

Insufficient IAM permissions

Insufficient authentication scopes

Metrics not showing up in Cloud Monitoring

Failed to load configuration file

Failed to initialize data collection

Set up the Agent for Oracle