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
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:
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:
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:
@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:
Step 3: Publish to OCI Monitoring
Implement the functionality to publish these metrics to OCI Monitoring:
@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:
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.
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
Previous Post
Next Post