Shows the steps to view the Autonomous Database metrics.

1. On the Details page, under Resources, click Metrics.
2. There is a chart for each metric. In each chart you can select the Interval and Statistic or use the default values.

• View Logs and Audit Trails
  Shows the steps to view the Autonomous Database logs and audit trails.

Shows the steps to view metrics for Autonomous Databases in a compartment.

1. On the Service Metrics page, under Compartment select your compartment.
2. On the Service Metrics page, under Metric Namespace select oci_autonomous_database.
3. If there are multiple Autonomous Databases in the compartment you can show metrics aggregated across the Autonomous Databases by selecting Aggregate Metric Streams.
4. If you want to limit the metrics you see, next to Dimensions click Add (click Edit if you have already added dimensions).
   1. In the Dimension Name field select a dimension.
   2. In the Dimension Value field select a value.
   3. Click Done.

   In the Edit dimensions dialog click +Additional Dimension to add an additional dimension. Click x to remove a dimension.

You can limit the instances where you see metrics with dimensions. The available dimensions include: workload type, instance display name, region, and the instance OCID.

Describes how to create and publish custom metrics on Autonomous Database.

Prerequisites

1. Obtain Oracle Cloud Infrastructure access through Oracle Cloud Free Tier or a paid Cloud Account.

   See Get an Oracle Cloud Account for more information.

2. Create an Autonomous Database instance or have access to an existing Autonomous Database instance.

   See Provision an Autonomous Database Instance for more information.

3. Obtain ADMIN credentials for your Autonomous Database instance.

4. Use Database Actions or any of the Oracle Database clients, such as SQL Developer or SQL*Plus to connect to the database.

   See Connect to Autonomous Database for more information.

5. You need access to the OCI Monitoring service and OCI Identity and Access Management.

   See Publishing Custom Metrics Using the API for more information.

Overview of Custom Metrics with OCI Monitoring Service

The following figure shows the steps to create and publish custom metrics on Autonomous Database. This shows that you collect metric data with a script running on your Autonomous Database instance and you publish this data to the OCI Monitoring service, and how you can create alarms and notifications for metric values.

Description of the illustration autonomous-database-custom-metrics.eps

• Create and deploy a PL/SQL script on your Autonomous Database instance. This script runs periodically to compute, collect, and publish custom metrics to the OCI Monitoring service.

• The Autonomous Database instance can be on a public or on a private endpoint. The communication between the Autonomous Database instance and the OCI Monitoring service takes place on the Oracle Cloud network. This means, to publish metrics on the OCI Monitoring service you do not need to create a service gateway.

Create and Publish Custom Metrics

To create and publish custom metrics:

1. In OCI Identity and Access Management create a dynamic group for your Autonomous Database instance and create a policy to authorize the dynamic group to post metrics to the OCI Monitoring service.

   For example:

   1. In the Oracle Cloud Infrastructure console click Identity & Security.
   2. Under Identity click Domains and select an identity domain (or create a new identity domain).
   3. Under Identity domain, click Dynamic groups.
   4. Click Create dynamic group and enter a Name, a Description, and a rule.

      For example, create a dynamic group named adb_dg for your Autonomous Database instance and create a rule:

      ALL {resource.type = 'autonomousdatabase', resource.compartment.id = 'OCID_of_compartment'}

      Alternatively, you can select a single Autonomous Database instance instead of all the instances in the compartment:

      ALL {resource.type = 'autonomousdatabase', resource.id = 'OCID_of_autonomousdatabase_instance'}

      
      

      
      Description of the illustration adb_custom_metrics_dynamic_group.png

      
      
   5. Click Create.
   6. Create an OCI Identity and Access Management (IAM) policy to authorize the dynamic group.

      For example, create a policy to authorize the dynamic group adb_dg to post metrics to the OCI Monitoring service with the policy adb_dg_policy:

      Allow dynamic-group adb_dg to use metrics in compartment OCID_of_compartment

      
      

      
      Description of the illustration adb_custom_metrics_policy.png

      
      

   After you define the policy, the dynamic group with the definition adb_dg is authorized to post metrics in the compartment.

   At this point, the PL/SQL routine running on Autonomous Database can’t post any metrics to the OCI Monitoring service because there are no Autonomous Database users authorized to publish metrics to the OCI Monitoring service. You provide this capability when you enable the resource principal in the following step (step 2c).

2. Create a new database user or schema with the requisite privileges in your Autonomous Database instance or update an existing database user or schema with the requisite privileges.

   For example:

   1. Create a database user or use an existing user to publish metrics. .

      For example, create a new user ECOMMERCE_USER on the Autonomous Database instance:

      CREATE USER ECOMMERCE_USER IDENTIFIED BY "password";

      Now you have the new user ECOMMERCE_USER. The following steps are the same with this user or with the user you specify. However if you create a user with a different name you need to replace ECOMMERCE_USER with the alternative user name.

   2. Grant requisite Oracle Database related privileges to the user.

      GRANT CREATE TABLE, ALTER ANY INDEX, CREATE PROCEDURE, 
             CREATE JOB, SELECT ANY TABLE, EXECUTE ANY PROCEDURE, UPDATE ANY TABLE, 
             CREATE SESSION, UNLIMITED TABLESPACE, CONNECT, RESOURCE 
             TO ECOMMERCE_USER;
      GRANT  SELECT ON "SYS"."V_$PDBS" TO ECOMMERCE_USER;
      GRANT  EXECUTE ON "C##CLOUD$SERVICE"."DBMS_CLOUD" to ECOMMERCE_USER;
      GRANT  SELECT ON SYS.DBA_JOBS_RUNNING TO ECOMMERCE_USER;

   3. Enable Oracle Database credentials for the Oracle Cloud Resource Principal and give access to the user.

      This step connects the dynamic group adb_dg you created in step 1 to the database user ECOMMERCE_USER, giving the user authorization to post metrics to the OCI Monitoring service.

      For example:

      EXEC DBMS_CLOUD_ADMIN.ENABLE_RESOURCE_PRINCIPAL(username => 'ECOMMERCE_USER');

      See Use Resource Principal to Access Oracle Cloud Infrastructure Resources for more information.

      As an alternative to Resource Principal authentication, you can use OCI Native credentials for authentication. See CREATE_CREDENTIAL Procedure for more information.

   4. (Optional) Verify the operations you completed in the previous step.

      For example:

      SELECT OWNER, CREDENTIAL_NAME FROM DBA_CREDENTIALS WHERE CREDENTIAL_NAME = 'OCI$RESOURCE_PRINCIPAL'  AND OWNER =  'ADMIN';

      To check if a user other than ADMIN has access to OCI Resource Principal, for example to check if the ECOMMERCE_USER has access to the database credential, use the DBA_TAB_PRIVS view:

      SELECT * FROM DBA_TAB_PRIVS WHERE DBA_TAB_PRIVS.GRANTEE='ECOMMERCE_USER';

3. Create a PL/SQL script that publishes metric data and schedule the script to run on your Autonomous Database instance.
   1. For example, create a PL/SQL script with a metric that counts of the rows of the Star Schema Benchmark sample data and publishes to the OCI Monitoring service.

      CREATE OR REPLACE PROCEDURE publish_lineorder_metric (p_sql_statement IN VARCHAR2)
      IS
      l_result NUMBER;
      l_compartment_ocid VARCHAR2(255);
      l_db_name VARCHAR2(255);
      l_region VARCHAR2(255);
      l_json_payload CLOB;
      l_cloud_identity CLOB;
      BEGIN
      
      -- 1. Execute the SQL statement and get the result
      EXECUTE IMMEDIATE p_sql_statement INTO l_result;
       
       -- 2. Get compartment OCID, DB name, and region from cloud identity
       SELECT cloud_identity INTO l_cloud_identity FROM v$pdbs;
        
       l_compartment_ocid := JSON_VALUE(l_cloud_identity, '$.COMPARTMENT_OCID');
       l_db_name := JSON_VALUE(l_cloud_identity, '$.DATABASE_NAME');
       l_region := JSON_VALUE(l_cloud_identity, '$.REGION');
       
      DBMS_OUTPUT.PUT_LINE(l_cloud_identity);
       
       -- 3. Construct the JSON payload for the metric
      
       l_json_payload :=
       '{' ||
       ' "metricData": [' ||
       ' {' ||
       ' "namespace": "custom_metrics",' ||
       ' "compartmentId": "' || l_compartment_ocid || '",' ||
       ' "name": "lineorder_metric",' ||
       ' "dimensions": {' ||
       ' "dbName": "' || l_db_name || '"' ||
       ' },' ||
       ' "datapoints": [' ||
       ' {' ||
       ' "timestamp": "' || TO_CHAR(SYSTIMESTAMP AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS.FF3"Z"') || '",' ||
       ' "value": ' || l_result ||
       ' }' ||
       ' ]' ||
       ' }' ||
       ' ]' ||
       '}';
       
       -- 4. Publish the metric using DBMS_CLOUD.SEND_REQUEST
      
       DECLARE
       l_response DBMS_CLOUD_TYPES.resp;
       BEGIN
       l_response := DBMS_CLOUD.SEND_REQUEST(
       credential_name => 'OCI$RESOURCE_PRINCIPAL',
       uri => 'https://telemetry-ingestion.' || l_region || '.oraclecloud.com/20180401/metrics',
       method => 'POST',
       body => UTL_RAW.CAST_TO_RAW(l_json_payload)
       );
       
       –- 5. Check response status
      
       IF DBMS_CLOUD.GET_RESPONSE_STATUS_CODE(l_response) = 200 THEN
       DBMS_OUTPUT.PUT_LINE('Metric published successfully!');
       ELSE
       DBMS_OUTPUT.PUT_LINE('Error publishing metric: ' || DBMS_CLOUD.GET_RESPONSE_STATUS_CODE(l_response));
       DBMS_OUTPUT.PUT_LINE(DBMS_CLOUD.GET_RESPONSE_TEXT(l_response));
       END IF;
       
       EXCEPTION
       WHEN OTHERS THEN
       DBMS_OUTPUT.PUT_LINE('Error during SEND_REQUEST: ' || SQLERRM);
       END;
      EXCEPTION
       WHEN OTHERS THEN
       DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
      END;
      /

      See PostMetricData for more information.

   2. Schedule a job to call and publish the custom metric data point once per minute. In production the schedule would be configured to run according to your needs.

      BEGIN
         DBMS_SCHEDULER.CREATE_JOB (
            job_name => 'publish_lineorder_count_job',
            job_type => 'STORED_PROCEDURE',
            job_action => 'ECOMMERCE_USER.publish_lineorder_metric',
            number_of_arguments => 1,
            start_date => SYSTIMESTAMP,
            repeat_interval => 'FREQ=MINUTELY;INTERVAL=1',
            enabled => FALSE,
            AUTO_DROP => FALSE,
            comments => 'Publishes the count of rows in SSB.LINEORDER every 1 minute'
      );
       
        DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (
             job_name => 'publish_lineorder_count_job',
             argument_position => 1,
             argument_value => 'SELECT COUNT(*) FROM SSB.LINEORDER'
       );
       
         DBMS_SCHEDULER.ENABLE('ECOMMERCE_USER.PUBLISH_LINEORDER_COUNT_JOB');
      END;

4. Explore the published custom metrics on the Metrics Explorer.
   1. From the Oracle Cloud Infrastructure Console navigation menu, click Observability & Management.
   2. Under Observability & Management, click Metrics Explorer.
      
      

      
      Description of the illustration adb_custom_metrics_explore.png

      
      
   3. In Metrics Explorer, select the namespace as custom_metrics_from_adb, resourceGroup as ecommerece_adb, and metric name as lineorder_metric (the name of the metric you created in the PL/SQL script).

      All the metadata and dimensions you set for custom metrics are available. You can construct Metrics Query Language (MQL) queries to analyze these metrics according to your needs and use case. You may also set up Oracle Cloud Alarms on the metric stream to alert your operational team. This automates the observability loop for the Autonomous Database metrics of your choice. Note that in the Metrics Explorer the metric screen by default shows graph view and you have the option to enable list view to highlight data points.