metrics - alfredokriegdba.com

I want to share how to use Enterprise Manager’s (EM) REST APIs to query metric data and integrate with 3rd party tools.

First of all, always follow the documentation. Below is the link to the EM’s REST API functionality.

Using the EM’s REST API functionality we can query the EM repository using a web service. Let’s follow below steps.

a) Create a new EM user who is going to have the privileges to query the EM’s repository. Navigate to Setup -> Security -> Administrators.

Administrators navigation

b) Create a new Repository User.

Administrator creation

c) I’m going to create a new Administrator named ABC.

Administrator creation

d) Create a new Named Credential. Navigate to Setup -> Security -> Named Credentials.

Named Credential creation

e) Click on Create.

Named Credential creation

f) I’m going to create NC_ABC Named Credential.

Named Credential creation

g) Enable the repository REST APIs in EM. For that execute below emctl command.

emctl set property -name oracle.sysman.db.restfulapi.executesql.repository.query.enable -value true -sysman_pwd "sysman"

Now is time to test our REST APIs.

I’m going to test them out using 2 different mechanisms. For the first one, let’s use CURL.

$ curl -k -u ABC -H "Content-Type: application/json" -d '{"sqlStatement": "SELECT * FROM sysman.MGMT$TARGET_METRIC_SETTINGS", "credential": {"DBCredsMonitoring":"NC_ABC"}, "maxRowLimit": 10, "maxColumnLimit": 2}' https://<EM_Host>:<EM Port>/em/websvcs/restful/emws/oracle.sysman.db/executesql/repository/query/v1

Here are the results based on my demo environment:

REST API results from CURL

The second test is by using Ansible. If you are not familiar on how to configure Ansible in order to work with EM, please follow the Oracle Live Labs that we created about this.

CLICK HERE…

I’m going to create a new YAML file (test.yml) in order to test the REST API.

---
- name: Using a REST API
  become: false
  hosts: emserver
  gather_facts: false
  tasks:

    - name: Query Metric Settings
      uri:
        url: https://emcc.marketplace.com:7803/em/websvcs/restful/emws/oracle.sysman.db/executesql/repository/query/v1
        method: POST
        return_content: yes
        force_basic_auth: yes
        validate_certs: no
        body_format: json
        url_username: ABC
        url_password: *******
        headers:
           Content-Type: application/json
        body:
          {
            "sqlStatement": "SELECT * FROM sysman.MGMT$TARGET_METRIC_SETTINGS", "maxRowLimit": 2, "maxColumnLimit": 29
          }
      register: results

    - name: Print returned json dictionary
      debug:
        var: results.json

Let’s execute this using Ansible.

ansible-playbook /home/oracle/ansible/yml/test.yml -u oracle --private-key=~/.ssh/dtkey.ssh

Here the results:

REST API results from Ansible

With this you can get the desired results from the EM’s repository using this REST API.

Thanks,
Alfredo

Enterprise Manager 12c is a great monitoring tool, with it you can monitor a wide range of target types from databases to middleware; although out-of-the-box metrics can suit your monitoring requirements they can generate a considerable amount of white noise. In order to reduce this noise first you have to identify which are the top alerts in your system; Cloud Control comes with several predefined reports that help you to dig into multiple areas of your system, there’s a report “20 Most Common Alerts” which shows you the incidence of common alerts.

In the picture above, you can clearly see that metric “Database Time Spent Waiting (%)” appears twice in my Top 3, let’s find out our metric setting for my DB targets; in order to do this we must go to a DB home page then Oracle Database -> Monitoring -> Metrics and Collection Settings.

Wait a minute! Why I’m receiving alerts if there are no thresholds setup for any of those metrics?, this behavior is clearly explained in MOS note 1500074.1 about a default warning threshold of 30% inside the database configuration. Let’s take a look to dba_threshold to confirm.

set lines 300

column METRICS_NAME format a30

column WARNING_OPERATOR format a30

column WARNING_VALUE format a30

column CRITICAL_OPERATOR format a30

column CRITICAL_VALUE format a30

SELECT METRICS_NAME,WARNING_OPERATOR ,WARNING_VALUE,CRITICAL_OPERATOR ,CRITICAL_VALUE FROM DBA_THRESHOLDS;

METRICS_NAME WARNING_OPERATOR WARNING_VALUE CRITICAL_OPERATOR CRITICAL_VALUE

———————————– —————————— —————————— —————————— ——————————

Average Users Waiting Counts GT 10 NONE

Average Users Waiting Counts GT 30 NONE

Blocked User Session Count GT 0 NONE

Current Open Cursors Count GT 1200 NONE

Database Time Spent Waiting (%) GT 30 NONE

Database Time Spent Waiting (%) GT 50 NONE

Logons Per Sec GE 100 NONE

Session Limit % GT 90 GT 97

Tablespace Bytes Space Usage DO NOT CHECK 0 DO_NOT_CHECK 0

Tablespace Space Usage GE 85 GE 97

22 rows selected.

There you go!, all metrics for “Database Time Spent Waiting (%)” are set to 30% or 50% values, now the trick to disable these metrics is to set them to a different value like 99%; this will override the default value as follows:

Let’s look at the database setting again:

set lines 300

column METRICS_NAME format a30

column WARNING_OPERATOR format a30

column WARNING_VALUE format a30

column CRITICAL_OPERATOR format a30

column CRITICAL_VALUE format a30

METRICS_NAME WARNING_OPERATOR WARNING_VALUE CRITICAL_OPERATOR CRITICAL_VALUE

———————————– —————————— —————————— —————————— ——————————

Average Users Waiting Counts GT 10 NONE

Average Users Waiting Counts GT 30 NONE

Blocked User Session Count GT 0 NONE

Current Open Cursors Count GT 1200 NONE

Database Time Spent Waiting (%) GT 99 NONE

Logons Per Sec GE 100 NONE

Session Limit % GT 90 GT 97

Tablespace Bytes Space Usage DO NOT CHECK 0 DO_NOT_CHECK 0

Tablespace Space Usage GE 85 GE 97

25 rows selected.

We successfully modified these metrics to a very high value; at this point you can decide to stay at 99% or you can remove that threshold in order to completely disable them.

Now let’s confirm those settings in the database:

set lines 300

column METRICS_NAME format a30

column WARNING_OPERATOR format a30

column WARNING_VALUE format a30

column CRITICAL_OPERATOR format a30

column CRITICAL_VALUE format a30

METRICS_NAME WARNING_OPERATOR WARNING_VALUE CRITICAL_OPERATOR CRITICAL_VALUE

———————————– —————————— —————————— —————————— ——————————

Average Users Waiting Counts GT 10 NONE

Average Users Waiting Counts GT 30 NONE

Blocked User Session Count GT 0 NONE

Current Open Cursors Count GT 1200 NONE

Logons Per Sec GE 100 NONE

Session Limit % GT 90 GT 97

Tablespace Bytes Space Usage DO NOT CHECK 0 DO_NOT_CHECK 0

Tablespace Space Usage GE 85 GE 97

14 rows selected.

The metrics are not there anymore and hopefully the alerts neither. This behavior is also noted for “Average Users Waiting Counts” metric, if you are receiving considerable white noise for this metric you can disable as well following the same procedure. A good practice is to create a Monitoring template to help you modify these thresholds for multiple targets at once.

Stay tuned for my next post about reducing OEM 12c noise.

Thanks,

Alfredo

Tag: metrics

Integrate EM Metrics With Third-Party Tools Using REST APIs

Oracle Enterprise Manager – Reducing the noise, Part 1