How to monitor DBCS database FRA using custom metric in OCI

The purpose of this blog is to show you how to use custom metric, if you don't see the default metric available in OCI for any particular case or even if you see default metric but you need the metric output to work your way :)

 

I am creating this custom metric to monitor FRA free space but as I said you can use any query to create custom metric for any other purpose, if you know the process.

 

Pre-requisite:

Should have environment with Python, oci and oracledb driver installed. See my previous blog for this.

 

Connect Python with Oracle

 

A. Preparation Steps:

A-1. Create OCI config file to connect to OCI.
A-2. Prepare Python script

 

B. Post custom Metric data to OCI:

B-1. Run Python script to post custom metric data to OCI 
B-2. Check data is available on OCI
B-3. Schedule this to post data periodically to OCI
B-4. Create an Alarm to get notification

A-1. Create OCI config file to connect to OCI.

 

Login to OCI console and add API key to your user

Go to Profile -> my profile --> API keys -->  Add API key

Download private key and public key files to the server from where you want to connect and click Add.

 

We will see the configuration file preview like this

Click copy and save the text in a configuration file.

for Linux: /home/<user_name>/.oci/config

 

Make sure to add the private key file patch we download above.

 

A-2. Prepare Python script

 

#!/usr/lib/python3.6

import oci,subprocess,os,datetime
from pytz import timezone
import oracledb
import os

oracledb.init_oracle_client()

# using default configuration file (~/.oci/config)
from oci.config import from_file
config = from_file()

# initialize service client with default config file
monitoring_client = oci.monitoring.MonitoringClient(config,service_endpoint="https://telemetry-ingestion.ca-toronto-1.oraclecloud.com")

db_user = os.environ.get('DBAAS_USER_NAME', 'SYSTEM')
db_password = os.environ.get('DBAAS_USER_PASSWORD', '*******************')
db_connect = os.environ.get('DBAAS_DEFAULT_CONNECT_DESCRIPTOR', "ip address:1521/orcl_pdb1")

# Connect to database
connection = oracledb.connect(user=db_user, password=db_password, dsn=db_connect)
cursor = connection.cursor()

# get the FRA free space data

cursor.execute("""SELECT (CASE WHEN PERCENT_USED> 100 THEN 0 ELSE (100-PERCENT_USED) END) PERCENT_FREE
  FROM (SELECT (SUM(PERCENT_SPACE_USED)-SUM(PERCENT_SPACE_RECLAIMABLE)) PERCENT_USED
  FROM V$FLASH_RECOVERY_AREA_USAGE)""")

result = cursor.fetchall()

res = float(result[0][0])
print(res)

cursor.close()
connection.close()

times_stamp = datetime.datetime.now(timezone('UTC'))

# post custom metric to oci monitoring
# replace "compartment_ocid“ string with your compartmet ocid

post_metric_data_response = monitoring_client.post_metric_data(
    post_metric_data_details=oci.monitoring.models.PostMetricDataDetails(
        metric_data=[
            oci.monitoring.models.MetricDataDetails(
                namespace="custom_metrics",
                compartment_id="compartment ocid",
                name="FRA_free_space",
                dimensions={'DB_name': 'orcl_pdb1'},
                datapoints=[
                    oci.monitoring.models.Datapoint(
                        timestamp=datetime.datetime.strftime(
                            times_stamp,"%Y-%m-%dT%H:%M:%S.%fZ"),
                      value=res)]
                )]
    )
)

# Get the data from response
print(post_metric_data_response.data)

If  you see above script, first I import the modules, get the configuration file to authenticate with OCI, Initialize service client with default config file.. here replace "service_endpoint" string with your endpoint. Your should be different as per your region.

Service-endpoint

After I connect to database and execute query to get the data.

Then I post the metric to OCI monitoring service.

B-1. Run Python script to post custom metric data to OCI

You can see above FRA free space is 99.99% free. and failed_metrics_count is also 0.

This means we successfully posted data to OCI monitoring service.

B-2. Check data is available on OCI

Go to OCI --> observability & management --> Metric Explorer

Provide the correct compartment, metric namespace, metric name, dimension etc. that you provided 

in above python script and click update chart.

Now scroll up and enable show graph and you will see the values in OCI.

B-3. Schedule this to post data periodically to OCI

We can use crontab to schedule this python script to post data into OCI.

# Example of job definition:
# .---------------- minute (0 - 59)
# | .------------- hour (0 - 23)
# | | .---------- day of month (1 - 31)
# | | | .------- month (1 - 12) OR jan,feb,mar,apr ...
# | | | | .---- day of week (0 - 6) (Sunday=0 or 7) OR sun,mon,tue,wed,thu,fri,sat
# | | | | |
# * * * * * user-name command to be executed

B-4. Create an Alarm to get notification

Click on save alarm and now you can see the Alarm is setup and we will be notified as per threshold.

I hope you like the blog! Thanks for reading.