UIM - How to verify data_engine maintenance for Oracle
Article ID: 128474
NIMSOFT PROBESDX Infrastructure Management
How can we verify that the data_engine is deleting old raw and historic data properly from the database?
Release: Component: UIMDEG
Below are the 2 stored procedures require to be created under CA_UIM Schema to verify data purge: 1. spn_verify_datapurge (Main Stored Procedure) 2. spn_validate_data 1.) create or replace procedure spn_verify_datapurge AS v_raw_days number; v_hist_days number; v_daily_days number; v_tbl_num number; v_index number; v_count number; v_rows_found number; BEGIN v_index :=1; v_rows_found :=0; select rawage,historyage,dailyavgage into v_raw_days,v_hist_days,v_daily_days from tbn_de_datamaintconfig; select count(*) into v_tbl_num from s_qos_definition; spn_validate_data('RN_QOS_DATA_',v_tbl_num,v_raw_days); spn_validate_data('HN_QOS_DATA_',v_tbl_num,v_hist_days); spn_validate_data('DN_QOS_DATA_',v_tbl_num,v_daily_days); END spn_verify_datapurge; 2.) create or replace procedure spn_validate_data(p_table_name IN varchar2,p_tbl_num IN number,p_retention_days IN number) AS v_index number; v_table_name varchar2(20); v_count number; v_rows_count number; v_rows_found number; BEGIN v_index :=1; v_rows_found := 0; while(v_index <= p_tbl_num) LOOP v_table_name := p_table_name ||substr(('0000'||v_index),-4); select count(*) into v_count from tab where tname=v_table_name; if(v_count > 0) then execute immediate 'select count(*) from '||v_table_name||' where sampletime < sysdate - '||p_retention_days into v_rows_count; if(v_rows_count > 0) then DBMS_OUTPUT.put_line(v_table_name || ' contains '||' samples which are older than '|| p_retention_days||' days'); v_rows_found := 1; end if; end if; v_index := v_index + 1; v_rows_count := 0; v_count := 0; END LOOP; if(v_rows_found = 0) then DBMS_OUTPUT.PUT_LINE('No unmaintained data found in ' || SUBSTR(p_table_name,0,2)||' table'); end if; END spn_validate_data;
Below are the 2 ways we can execute stored procedure:
Or in some native clients below command will be used to execute stored procedure.
If the output from the script shows as follows then data_engine is honoring the retention policies configured in data_engine No unmaintained data found in RN tables No unmaintained data found in HN tables No unmaintained data found in DN tables If, however you receive output that looks like the following, then a support case may need to be opened to fix the issue RN_QOS_DATA_0001 contains 12 samples which are older than 30 days . RN_QOS_DATA_0007 contains 25 samples which are older than 30 days. RN_QOS_DATA_0008 contains 415 samples which are older than 30 days.