Load Data Warehouse fails with ORA- 01452: cannot CREATE UNIQUE INDEX; duplicate keys found on the 1st of the month during the 1st run only
STEPS TO REPRODUCE:
Expected Results: The job to run well all the time
Actual Results: On the 1st of the month, Incremental fails once with error:
Executing ETL Job. NJS-0401: Execution of job failed. ETL Job Failed. Please see log bg-dwh.log for details. Load Facts Oracle - An error occurred executing this job entry : Couldn't execute SQL: BEGIN DWH_INV_TASK_PERIOD_FACTS_LOAD (P_ARRAY_SIZE => 50000, P_DBLINK => 'PPMDBLINK, P_CURRENT_DIM_LOAD_DATE => to_date('2022/02/01 04:43:10', 'yyyy/mm/dd HH24:MI:SS')); END; [CA Clarity][Oracle JDBC Driver][Oracle]ORA-20100: ENCOUNTERED EXCEPTION WHILE INSERTING INTO DWH_INV_TASK_PERIOD_FACTS_LOAD. SQLERRM : ORA-20100: ENCOUNTERED EXCEPTION WHILE INSERTING INTO DWH_INTERNAL_MD. SQLERRM : ORA- 01452: cannot CREATE UNIQUE INDEX; duplicate keys found ORA-06512: at "PPM_DWH.DWH_INV_TASK_PERIOD_FACTS_LOAD", line 76 ORA-06512: at line 2
Release : 16.0, 16.0.1, 16.0.2
This is DE64227, fixed in 16.0.3
Workaround: Run another job after that and it will go through successfully
Schedule to Run a Full Load every 1st of the month