How to create a custom checkpoint for the oracle probe

Products

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

Issue/Introduction

This help 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.

Environment

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

Cause

• oracle probe custom checkpoint configuration
• guidance

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.

Once the custom checkpoint has been successfully added, you can add the checkpoint to a profile.

To add the checkpoint to a profile:

1. Click the Options icon next to <connection name> node and click 'Add profile checkpoints.'
2. Select the required checkpoint to add from the Available list and click 'Submit.'

 

Additional Information

References:

Oracle AC Configuration

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

oracle probe alarm messages

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 that contain special characters 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 %
• IMPORTANT!!! 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 the 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.