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.