search cancel

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

book

Article ID: 257215

calendar_today

Updated On:

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.