search cancel

How to create a custom checkpoint for the oracle probe

book

Article ID: 14959

calendar_today

Updated On:

Products

DX Unified Infrastructure Management (Nimsoft / UIM) Unified Infrastructure Management for Mainframe CA Unified Infrastructure Management SaaS (Nimsoft / UIM)

Issue/Introduction

This document explains how to create and configure custom (new) checkpoints using the oracle probe.

Custom checkpoints allow you to use our own preferred queries for database monitoring when the default checkpoints provided do not satisfy your specific database monitoring requirements. This guide walks you through configuring and testing a custom checkpoint, configuring data collection (QoS), configuring alarms and alarm thresholds.

Cause

  • oracle probe custom checkpoint configuration
  • guidance

Environment

  • UIM: 8.4 or later
  • oracle probe 5.x

Resolution

1. Open oracle probe GUI in Infrastructure Manager or Admin Console.

2. Go to “Templates” tab and right click on Open space and then click on “Create new” option.                             

3. Add the required name for the checkpoint to monitor

 

4. Query template editor will open as below.

 

5. Select “Active” and “Send Quality of Service” options. You can give the Check interval and other required fields as per the requirement.



6. Go to “Query” tab.

  • Select the connection.
  • Add the query.
  • Add the “Checked value” and “Condition”
  • Give any query file name.

                            

7. You can add the thresholds as per the requirement and values in the “Thresholds/Schedules” section.

 

8. Checkpoints gets added to the oracle probe.

9. Click on Apply and OK and then restart the probe.

Additional Information

Reference: 

https://techdocs.broadcom.com/content/broadcom/techdocs/us/en/ca-enterprise-software/it-operations-management/ca-unified-infrastructure-management-probes/GA/alphabetical-probe-articles/oracle-oracle-database-monitoring/oracle-im-configuration.html#concept.dita_c3a5979fe43f13fdc5d575693acf4d91b878a95e_OptionalCreateCustomCheckpoints

How to change the threshold for specific checkpoint in a oracle probe 

https://ca-broadcom.wolkenservicedesk.com/external/article?articleId=12017

The oracle probe alarm message text can be customized as well, for example, "tablespace crossed threshold" and any message variables such as $instance can be configured and used in the custom alarm message.
 
oracle probe custom checkpoint tips:
If you get an error such as oracle Query returns error Query test connection to <instance> failed
 
Check the query first.
 
a) In one case the customer configured a query with set statements/defaults but it would not run.
 
set pagesize 500
set linesize 500
break on report
compute sum of free_mb on report
compute sum of act_size on report
compute sum of used_mb on report
col %used format 999.99
col %Free format 999.99
col act_size format 99,99,999,9999.99
col free_mb format 99,99,999.99
col used_mb format 99,99,999,9999.99
col largest_extent_MB format 99,99,999.99
col tablespace for a30
 
If the accuracy of the query is not affected, just use the SELECT statement or create a stored procedure and run that instead.
 
b) Alarm message variable expansion was not working due to the query which used special characters in a few of the column names
 
select SUBSTR(a.tablespace_name,1,40) "TABLESPACE",act_size,free_mb,
free_mb*100/act_size "%FREE",
act_size-free_mb used_MB,((act_size-free_mb)*100)/act_size "%USED",largest_extent_MB
from (SELECT TABLESPACE_NAME,SUM(BYTES)/1024/1024 FREE_MB,
max(bytes)/1024/1024/1024 largest_extent_MB
FROM DBA_FREE_SPACE group by tablespace_name) a,
(SELECT TABLESPACE_NAME,SUM(BYTES)/1024/1024 ACT_SIZE FROM
DBA_XXXX_FILES group by tablespace_name) b
where a.tablespace_name(+)=b.tablespace_name
 
We had to modify the original query (special character '%' removed from column headers):
 
select SUBSTR(a.tablespace_name,1,40) "TABLESPACE",act_size,free_mb,
free_mb*100/act_size "FREE_PCT",
act_size-free_mb used_MB,((act_size-free_mb)*100)/act_size "USED_PCT",largest_extent_MB
from (SELECT TABLESPACE_NAME,SUM(BYTES)/1024/1024 FREE_MB,
max(bytes)/1024/1024/1024 largest_extent_MB
FROM DBA_FREE_SPACE group by tablespace_name) a,
(SELECT TABLESPACE_NAME,SUM(BYTES)/1024/1024 ACT_SIZE FROM
DBA_XXXX_FILES group by tablespace_name) b
where a.tablespace_name(+)=b.tablespace_name
 
...since the special character % was preventing the expansion/replacement of the message variable with the samplevalue.
 
Tested using USED_PCT and FREE_PCT instead of %USED and %FREE yielded successful results.
 
- You cannot use column headers which contain special chars such as %FREE or %FREE in the alarm message

- To workaround this you must use FREE_PCT or any other relevant string without a special character in the front or the back of the column header name in the query.
 
Then the resultant alarm message variable will be expanded and the samplevalue will be shown/populated, otherwise it will only display the variable name and not the collected sample value.
 
Also, note that you can adjust the threshold->alarm message and add a <space> and % to the value in the alarm message to customize it to show the symbol/units, e.g., Test1 Free 4.79 % on Instance <instance_name> Used %
 
Lastly, please note that the custom query being used MUST yield numeric values so thresholds can be configured for those values. Strings will not work for thresholds/threshold breaches and alarms. In another case/scenario where configuration of the custom profile checkpoint did not yield the expected Data types or column use values, it was discovered that the query was converting all the values to string:

The query being used contained a TO_CHAR. The Oracle TO_CHAR() function converts a DATE or INTERVAL value to a string in a specified date format.

So that is why only character data type was available in the drop down window for selection. Once the query was changed to stop the conversion to string, the numeric Data type displayed and so did 'value.' Then we were able to create the proper thresholds and alarm messages, including a custom clear message.

To support the column names and data being collected in the query, in this particular case we changed the $NAME to $TSPACE and $USED_PCT to $USED_PCT1 respectively and then the oracle probe GUI Status window showed the proper tablespace names and percentage values. Row key identifier also has to be unique so we used the column $TSPACE (for the tablespace name) instead of USED_PCT1.

The customer also created a new unique clear message using the new variables and applied it.

Attachments