UIM Oracle Database is failing the automated process for partitioning tables.

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)

Issue/Introduction

The automatic process is failing to repartition the qos tables. The following keys are in the data_engine.cfg file:

data_management_partition = yes
data_management_active = yes

The data_engine logs show the following:

Nov 28 19:30:39:561 [140687239481088] 0 de: data_engine [QoS] [QoSData] - commitDataHasMax [QoSData] data_engine [QoS] status: -1
 OCI_ERROR - ORA-14400: inserted partition key does not map to any partition

 OCIEnv: 0x0x7ff448004db0 OCIAuthInfo: 0x0x7ff448012a30 OCISvcCtx: 0x0x7ff448045c58 
 - Error: commitDataHasMax [QoSData] data_engine [QoS] status: -1
 OCI_ERROR - ORA-14400: inserted partition key does not map to any partition

Retention processes are able to remove old partitions, but new partitions are not created.

 

************Warning Use at your own Risk************

Please review this KB Article and all queries with your DBA before following the steps.

We also highly recommend that these queries and their results be tested in a Test/DEV environment before running in Production.

Environment

Release : UIM 20.3.3
Oracle 19c

Cause

Corruption in the database

Resolution

************Warning Use at your own Risk************

Please review this KB Article and all queries with your DBA before following the steps.

We also highly recommend that these queries and their results be tested in a Test/DEV environment before running in Production.

 

The following steps can be taken to repartition the qos_tables:

1. Open the data_engine probe using Raw Configure mode (select the probe then hold down the SHIFT key and rt-click to open in Raw Configure mode)
2. Set data_management_active to "no" and restart data_engine again. From this point on, it’s permissible to leave data_engine active – there is no need to deactivate it.
3. Run the following commands to add/update the below procedures.  (These must be run as Schema User)
   

   CREATE OR REPLACE PROCEDURE rtable_maint(ltabletype varchar2)

   IS
   lqos_def_id NUMBER;
   lr_table varchar2(30);
   lRetentionTime number;
   lExtraPartitions number;
   ltablenumber varchar2(30);
   lCurrentTime timestamp;
   lloglevel number default 5;
   cursor q is select qos_def_id, r_table from s_qos_definition;
   BEGIN
   lCurrentTime := trunc(nvl(lCurrentTime, systimestamp));
   lRetentionTime := 35;
   lExtraPartitions := 30;
       OPEN q;
           LOOP
           FETCH q into lqos_def_id,lr_table;
           EXIT WHEN q%NOTFOUND;
               BEGIN
               spn_de_UnpartitionTable(lr_table,ltabletype,lqos_def_id);
               dbms_output.put_line(lr_table);
               spn_de_PartitionTable(lr_table,lTableType,lRetentionTime,lExtraPartitions,lCurrentTime,lloglevel);        
               
               END;
           END LOOP;
           commit;
       CLOSE q;
   END;
   /

   CREATE OR REPLACE PROCEDURE btable_maint(ltabletype varchar2)
   IS
   lqos_def_id NUMBER;
   lb_table varchar2(30);
   lRetentionTime number;
   lExtraPartitions number;
   ltablenumber varchar2(30);
   lCurrentTime timestamp;
   lloglevel number default 5;
   cursor q is select qos_def_id, b_table from s_qos_definition;
   BEGIN
   lCurrentTime := trunc(nvl(lCurrentTime, systimestamp));
   lRetentionTime := 30;
   lExtraPartitions := 30;
       OPEN q;
           LOOP
           FETCH q into lqos_def_id,lb_table;
           EXIT WHEN q%NOTFOUND;
               BEGIN
               spn_de_UnpartitionTable(lb_table,ltabletype,lqos_def_id);
               dbms_output.put_line(lb_table);
               spn_de_PartitionTable(lb_table,lTableType,lRetentionTime,lExtraPartitions,lCurrentTime,lloglevel);        
               
               END;
           END LOOP;
           commit;
       CLOSE q;
   END;
   /

   CREATE OR REPLACE PROCEDURE htable_maint(ltabletype varchar2)
   IS
   lqos_def_id NUMBER;
   lh_table varchar2(30);
   lRetentionTime number;
   lExtraPartitions number;
   ltablenumber varchar2(30);
   lCurrentTime timestamp;
   lloglevel number default 5;
   cursor q is select qos_def_id, h_table from s_qos_definition;
   BEGIN
   lCurrentTime := trunc(nvl(lCurrentTime, systimestamp));
   lRetentionTime := 30;
   lExtraPartitions := 30;
       OPEN q;
           LOOP
           FETCH q into lqos_def_id,lh_table;
           EXIT WHEN q%NOTFOUND;
               BEGIN
               spn_de_UnpartitionTable(lh_table,ltabletype,lqos_def_id);
               dbms_output.put_line(lh_table);
               spn_de_PartitionTable(lh_table,lTableType,lRetentionTime,lExtraPartitions,lCurrentTime,lloglevel);        
               
               END;
           END LOOP;
           commit;
       CLOSE q;
   END;
   /

   CREATE OR REPLACE PROCEDURE dtable_maint(ltabletype varchar2)
   IS
   lqos_def_id NUMBER;
   ld_table varchar2(30);
   lRetentionTime number;
   lExtraPartitions number;
   ltablenumber varchar2(30);
   lCurrentTime timestamp;
   lloglevel number default 5;
   cursor q is select qos_def_id, d_table from s_qos_definition;
   BEGIN
   lCurrentTime := trunc(nvl(lCurrentTime, systimestamp));
   lRetentionTime := 360;
   lExtraPartitions := 30;
       OPEN q;
           LOOP
           FETCH q into lqos_def_id,ld_table;
           EXIT WHEN q%NOTFOUND;
               BEGIN
               spn_de_UnpartitionTable(ld_table,ltabletype,lqos_def_id);
               dbms_output.put_line(ld_table);
               spn_de_PartitionTable(ld_table,lTableType,lRetentionTime,lExtraPartitions,lCurrentTime,lloglevel);        
               
               END;
           END LOOP;
           commit;
       CLOSE q;
   END;
   /

4. Run the following commands one at a time to execute the stored procedures.  They can take a lot of time depending upon the number and size of the tables:
   exec rtable_maint('RN')
   exec btable_maint('BN');
   exec htable_maint('HN');
   exec dtable_maint('DN');
5. Set data_management_active to "yes" and restart data_engine again.