If you use Oracle’s Grid Control (Regardless of the version), you have been probably asked or wondered what thresholds are used for the targets you are monitoring. We all have tried to standardize and make the thresholds and metrics the same for all customers, but as much we try, each customer and database will have different characteristics that will require threshold modifications to the standard. I sure at some point, documentation would be asked for and or required for the thresholds set and used for the targets you monitor. You have a couple of choices one is to use the Grid Control OMS console and either perform a screen print of the metrics, or you can utilize the tables inside of grid control.
BTW, you can export the templates, but will have to convert them from XML to a readable format and cant guarantee all targets are using the values from the “Standard” templates.
If you have more than 1 or target to monitor, you need a better way than screen prints every target. I have started to utilize the Grid Control tables more as the source of these reports. I have found a few tables that will help generate these reports into CSV files or can be used by APEX.
Todays blog will show the table and query (Sqlplus) that will get information from your target’s templates used (Default or otherwise). Part 2 will show the tables for the targets actually used metrics.
As a note all tables are owned by the user sysman and are views and can be found in the following URL:
https://docs.oracle.com/cd/E24628_01/doc.121/e57277/toc.htm – Grid Control Extensibility Guide.
To find out the metrics and thresholds used by the templates you can query the view – mgmt$template_metric_settings – this view contains the template settings and information. The columns in the table are defined and described in the following document: mgmt$template_metric_settings
To compile the template metric values, you can execute the following query Note – This doesn’t include corrective actions, so the query doesn’t include those columns. If you use the correction actions, please add the corrective action columns to the query:
<select t.template_name,
t.target_type,
t.metric_name,
t.metric_column,
t.collection_name,
t.column_label,
decode (t.warning_operator,0,'GT',
1,'EQ',
2,'LT',
3,'LE',
4,'GE',
5,'CONTAINS',
6,'NE',
7,'MATCH : Regular expression',
'Unknown') "Warning Operator",
warning_threshold,
decode(critical_operator,0,'GT',
,'EQ',
2,'LT',
3,'LE',
4,'GE',
5,'CONTAINS',
6,'NE',
7,'MATCH : Regular expression',
'Unknown') "Critical Operator",
t.critical_threshold,
t.occurrence_count,
t.warning_action_type,
t.critical_action_type
from sysman.mgmt$template_metric_settings t
where (t.template_name, t.target_type, t.metric_name not in (
select template_name, target_type, metric_name
from sysman.mgmt$template_metric_settings i
where t.template_name = i.template_name
and t.target_type = i.target_type
and a.metric_name = b.metric_name
and (ltrim(rtrim(i.critical_threshold)) is null) and (ltrim(rtrim(i.warning_threshold)) is null))
order by t.template_name, t.target_type, t.column_label >
Note – when copying the script – the single quotes may not copy correctly.
You can also spool this out to a CSV file using the “colsep ,” or use the information from https://chartio.com/resources/tutorials/how-to-write-to-acsv-file-using-oracle-sql-plus/
Part II will include all the target thresholds query
Mike Gangler's Musings 