Publishing Custom Autonomous Container Database Metrics on Oracle Cloud Infrastructure Monitoring Service

October 14, 2024 | 5 minute read
German Viscuso
Director of Community - Autonomous Database / Dedicated
Text Size 100%:

Oracle Autonomous Database - Dedicated (ADB-D) in Oracle Cloud Infrastructure (OCI) provides a comprehensive suite of built-in metrics for database instances. In its latest update, Oracle has expanded these capabilities to include Autonomous Container Database (ACD) level metrics in ADB-D, accessible via new views prefixed with 'ACD_'.

This blog post guides you through setting up a service to fetch these metrics and publish them to OCI Monitoring, enhancing your ability to monitor, analyze, and react to database performance metrics efficiently.

ACD Metrics Overview

The introduction of new views such as ACD_V$EVENTMETRIC, ACD_V$RESOURCE_LIMIT, and several others, enables deep telemetry at levels previously unavailable in ADB, providing enhanced observability for Autonomous Container Databases (ACD).

These views enable telemetry at levels previously unavailable in ADB, providing enhanced observability for Autonomous Container Databases (ACD).

For a full list of available ACD views please see this page.

Setup and Configuration

For demonstration, let's focus on publishing custom metrics using a small, efficient service deployed on a VM in OCI. This approach is straightforward and can be easily integrated into existing infrastructure.

Prerequisites

  • Oracle Cloud Account with access to ADB-D and OCI Monitoring
  • JDK and OCI Java SDK setup on your development machine
  • Basic knowledge of SQL and Oracle Database

Fetching Metrics Using SQL

Here's an SQL snippet to fetch data from the ACD_V$SYSMETRIC view, which provides real-time performance metrics such as CPU and Wait time ratios:

Copied to Clipboard
Error: Could not Copy
Copied to Clipboard
Error: Could not Copy
SELECT METRIC_NAME, VALUE, METRIC_UNIT
FROM ACD_V$SYSMETRIC
WHERE METRIC_NAME IN ('Database CPU Time Ratio', 'Database Wait Time Ratio');

The output of this SQL query is what we’re going to send to the OCI Monitoring Service as custom metric.

Implementing the Metrics Fetching Service

We will use Java with the Micronaut framework to create a simple scheduled service. This service will fetch metrics from ADB-D and publish them to OCI Monitoring.

Step 1: Set Up the Project

Create a new Micronaut project using your preferred method and add the OCI Java SDK dependency to your build.gradle:

Copied to Clipboard
Error: Could not Copy
Copied to Clipboard
Error: Could not Copy
implementation 'com.oracle.oci.sdk:oci-java-sdk-full:1.36.0'

Step 2: Service Implementation

Implement a service that queries the database and retrieves the metrics:

Copied to Clipboard
Error: Could not Copy
Copied to Clipboard
Error: Could not Copy
@Singleton
public class DatabaseMetricsService {
    private final OracleDataSource dataSource;

    public DatabaseMetricsService() throws SQLException {
        this.dataSource = new OracleDataSource();
        dataSource.setURL("jdbc:oracle:thin:@your_db_connection_string");
        dataSource.setUser("admin");
        dataSource.setPassword("your_password");
    }

    public List<databasemetric> fetchMetrics() throws SQLException {
        List<databasemetric> metrics = new ArrayList<>();
        try (Connection connection = dataSource.getConnection();
             PreparedStatement stmt = connection.prepareStatement(
                 "SELECT METRIC_NAME, VALUE, METRIC_UNIT FROM ACD_V$SYSMETRIC WHERE METRIC_NAME IN ('Database CPU Time Ratio', 'Database Wait Time Ratio')")) {
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                metrics.add(new DatabaseMetric(rs.getString("METRIC_NAME"), rs.getDouble("VALUE"), rs.getString("METRIC_UNIT")));
            }
        }
        return metrics;
    }
}
</databasemetric></databasemetric>

Here we define a DatabaseMetricsService class that retrieves specific performance metrics from an Oracle database:

  1. It initializes a singleton OracleDataSource with a connection URL, user, and password.
  2. It queries metrics like 'Database CPU Time Ratio' and 'Database Wait Time Ratio' from the ACD_V$SYSMETRIC view, constructs a list of DatabaseMetric objects containing these metrics, and returns this list.
  3. It uses a try-with-resources statement to ensure the database connection and prepared statement are properly closed after execution.

Step 3: Publish to OCI Monitoring

Implement the functionality to publish these metrics to OCI Monitoring:

Copied to Clipboard
Error: Could not Copy
Copied to Clipboard
Error: Could not Copy
@Scheduled(fixedRate = "1m")
public void publishMetrics() throws SQLException {
    List<databasemetric> metrics = databaseMetricsService.fetchMetrics();
    for (DatabaseMetric metric : metrics) {
        PostMetricDataDetails details = PostMetricDataDetails.builder()
            .namespace("custom_namespace")
            .compartmentId("your_compartment_ocid")
            .name(metric.getName())
            .datapoints(List.of(
                Datapoint.builder()
                    .value(metric.getValue())
                    .timestamp(Date.from(Instant.now()))
                    .build()))
            .build();
        monitoringClient.postMetricData(PostMetricDataRequest.builder().postMetricDataDetails(details).build());
    }
}
</databasemetric>

The custom metrics will go to the defined custom namespace and compartment id defined above.

Note that the @Scheduled(fixedRate = "1m") annotation configures the publishMetrics method to execute at a fixed rate of one minute. This means that regardless of how long the method execution takes, the next execution will occur one minute after the start of the last execution. This scheduling ensures that your method sends the custom metrics to OCI Monitoring every minute, providing a consistent flow of metric data for monitoring and analysis purposes.

Also note that the timestamp for each data point is the moment where the datapoint is created in the code and not the moment when the metric was collected (if you want that you’ll have to fetch the timestamp from the metric data itself in the SQL output).

Deployment

Deploy this service to an OCI Compute instance and ensure it has the necessary IAM policies to interact with OCI Monitoring. Using an Instance Principal provider, for example, we need to first create a dynamic group where the rule applies to all instances in a given compartment. Now it’s a matter of applying the proper policy statements to give the dynamic group permissions:

Copied to Clipboard
Error: Could not Copy
Copied to Clipboard
Error: Could not Copy
allow dynamic-group my_group to use metrics in tenancy

Visualization and Alarms

Once you have successfully deployed your service and started publishing custom metrics to OCI Monitoring, you can leverage OCI's robust visualization tools to track and analyze these metrics (such as dashboards, alarms and notifications).

Conclusion

With this setup, you can now monitor key performance indicators for your Oracle Autonomous Database at a granular level, directly from within the Oracle Cloud Infrastructure. This integration not only enhances observability but also allows you to proactively manage your database resources more effectively.

This method ensures that you leverage the full capabilities of Oracle Cloud, maintaining a high level of performance monitoring and management for your Autonomous Databases.

For further details on sending custom metrics to the OCI Monitoring service please see this blog.

German Viscuso

Director of Community - Autonomous Database / Dedicated

I have experience fostering developer communities at scale. I have a software development background and previous experience with database systems (mostly OODBs and NoSQL). I'm a techie and believe that rather sooner than later we'll be surpassed by AI. You can follow me via Twitter: @germanviscuso

Show more

Previous Post

Enhance your AI/ML applications with flexible Bring Your Own Model options

Mark Hornick | 16 min read

Next Post


Protect your data with Oracle Data Redaction in Oracle Database 23ai

Richard Evans | 4 min read