Monitoring UIM Oracle Database Partitions via DX UIM Oracle Probe

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)

Issue/Introduction

This document provides step-by-step instructions for configuring a custom query checkpoint within the DX UIM oracle probe to monitor Oracle database table partitions (RN_QOS_DATA_%). This probe alerts administrators if any required database table lacks future partitions.

This allows for proactive alerting when tables are not partitioned correctly, when insufficient future partitions exist, or when data maintenance jobs are failing, preventing potential Quality of Service (QoS) data loss.

Environment

DX UIM 23.4.3

Oracle database 19.x

Oracle probe 

Resolution

1. Create the Custom Query Checkpoint

1. Open the Oracle Probe GUI and select the target database connection.

2. Navigate to the Template tab and select New.

3. Go to the Query tab, select custom query, and input the following SQL query:

SQL

 

SELECT CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END AS tables_no_future 
FROM (
    SELECT table_name, 
           MAX(CASE WHEN partition_name NOT LIKE '%\$MAXVALUE' 
                    AND REGEXP_LIKE(SUBSTR(partition_name, INSTR(partition_name,'\$')+1), '^[0-9]+\$') 
               THEN DATE '1970-01-01' + TO_NUMBER(SUBSTR(partition_name, INSTR(partition_name,'\$')+1))/86400 
               END) AS max_boundary_date 
    FROM all_tab_partitions 
    WHERE table_name LIKE 'RN\_QOS\_DATA\_%' ESCAPE '\' 
      AND table_owner = '<YOUR_UIM_SCHEMA_USER>' 
    GROUP BY table_name
) t 
WHERE max_boundary_date IS NULL OR max_boundary_date < TRUNC(SYSDATE);

Note: Replace <YOUR_UIM_SCHEMA_USER> with your actual UIM Database Schema User (e.g., CA_UIM_2).

4. Click Test to verify that the query executes successfully.
   

---

2. Configure Checkpoint Variables

1. Set Checked value to TABLES_NO_FUTURE.

2. Set Condition to !=.

3. Under Message variables, click Edit and ensure the following variables are enabled:

Variable Name	Data Type	Column Use
TABLES_NO_FUTURE	Numeric	value int
profile	Character	information
check	Character	information
instance	Character	information

 

---

3. Configure Thresholds and Alarms

1. Navigate to General tab -> Thresholds -> click New.

2. Define the threshold properties:

   • Threshold Object Name: default

   • Threshold Value: 0

   • Severity: Warning

   • Message: partition_check

   • Message Text: UIM Database: one or more tables not partitioned

3. Check the Check Active and Send Alarm boxes.
   
   
   
   

---

4. Execution Interval & Activation

1. Set the Check Interval to 24 hrs (or 12 hrs depending on operational requirements).

2. Save changes and Activate the template.

---

Verification & Alarm Behavior

If any monitored table fails to maintain a future partition (max_boundary_date < TRUNC(SYSDATE) or NULL), TABLES_NO_FUTURE evaluates to 1, generating a Warning alarm with the text:

UIM Database: one or more tables not partitioned