My customer wants to get some specific information from each database on regular basis. I was sure to be able doing that with Enterprise Manager, so the information would be stored in the repository.
Here how it is done:
On OEM (version 13.5 here), as a high privileged user go to Enterprise Menu – Monitoring – Metric Extensions.
Click Create – Metric Extension.
Target Type can be “Database Instance” if you want to query on CDB$ROOT level only, or “Pluggable Database” to query on PDB level. For some reason the “Pluggable Database” did not work at a later step, so the example here will be with “Database Instance”.
On “Select Advanced Properties” you can normally choose which categories this metric should be applied: if should run on “DB Instance only”, “RAC Instance only”, “Exclude CDB”, or based on Data Guard Status. Also here, when I chose “Primary”, later in the configuration I was getting errors. So at the end I did left all unselected (mean apply on all).
Then set the Metric Extension name, which will be the value of column “metric_name” in MGMT$METRIC_CURRENT table. The Adapter for my case is “SQL”
The collection schedule should be Enable, with Data Upload, using Metric for “Alerting and Historical Trending”, which allows to have some history. As my query is light and I wanted to have results gathered quite soon, I set the “Upload Interval” to 24 and collection frequency every 30 minutes. So I could have the first results in 30 minutes, next will come after 12 hours.
On the next Screen – Adapter – I wrote the SQL Query. In my case it was:
select con_id_to_con_name(con_id) pdb_name, owner, count(*) n_obj, count(distinct tablespace_name) n_tbs, round(sum(bytes)/1024/1024) mb_used
from cdb_segments
group by con_id, owner;
This makes the workaround to query on cdb$root level (Database Instance) only and get the results for all PDBs.
Then one has to define the output columns. In my case I’ve two “Key Columns”, which identify the row – pdb_name and owner, both of type String. Then I’ve three “Data Column” – n_obj, n_tbs and mb_used of type Number.
The following screen defines which database credentials to be used. In my case the “Default Monitoring Credentials” which are from dbsnmp user are enough.
And before finishing, one can test the collection on a few targets, to make sure all works as expected. Then we can click “Finish”.
Now that the Metric Extension is created there we have perform several Actions (select metric and use the “Actions” button):
- Save as Deployable Draft
- Public Metric Extension
- Deploy to Targets
I don’t go through it as it is quite easy to understand how it works.
At my customer they use “Monitoring Templates”, in order to have a set of metrics automatic deployed to new targets of a certain type. One needs to add the Metric Extension to the Monitorig Template. For that, we go to Enterprise Menu – Monitoring – Monitoring Templates. We select the template concerning Database Instances, click “Edit”, go to “Other Collected Items” tab and click “Add Metrics to Template”. Select “Metric Extensions/Configuration Extensions” and our created metric should appear. We select and click Continue and then OK.
Now is just matter of waiting up to 30 minutes and the result from each database will be uploaded to the OEM Repository and can be queried using the following SQL – for the example above:
select target_name, key_value as pdb_name, key_value2 as owner,
max(case when metric_column='n_obj' then value end) as n_obj,
max(case when metric_column='n_tbs' then value end) as n_tbs,
max(case when metric_column='mb_used' then value end) as mb_used
from MGMT$METRIC_CURRENT
where metric_name ='ME$<metric_name>'
group by target_name,key_value,key_value2
order by 1,2,3,4;
Historical details should come in MGMT$METRIC_DETAILS, using the column collection_timestamp.