Load DWH job fails with error ORA-01555: snapshot too old

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

The Load Data Warehouse job fails with following with an example table being DWH_TME_ENTRY:

ERROR 2018-05-29 01:10:55,589 [Thread-329555] dwh.event ClarityDB - isOracle? - An error occurred executing this job entry :
Couldn't execute SQL: DECLARE
.
.
.
        INSERT INTO DWH_CMN_ERROR_MESSAGE VALUES('DWH_TME_ENTRY',V_SQLERRM,V_SQLCODE,REPLACE(V_SQL_TEXT,'**',''''));
        COMMIT;
        RAISE_APPLICATION_ERROR(-20100,'ENCOUNTERED EXCEPTION IN DWH_DIM_LOAD (DWH_TME_ENTRY). SQLERRM : ' || V_SQLERRM);
    END;

END;

[CA Clarity][Oracle JDBC Driver][Oracle]ORA-20100: ENCOUNTERED EXCEPTION IN DWH_DIM_LOAD (DWH_TME_ENTRY). SQLERRM : ORA-01555: snapshot too old: rollback segment number 89 with name "_SYSSMU89_214320579$" too small
ORA-02063: preceding line from [SCHEMADWH]_[SCHEMAPPM]
ORA-06512: at line 31

This can happen to any Data Warehouse table, and the error message may vary for segment number and name. 

Cause

Usually an issue with the Oracle database configuration. Sometimes, an issue with performance

Resolution

We recommend involving your DBA Team. Below are some possible causes and solutions to consider 

1. Old Clarity version - the latest Clarity versions contain the latest performance fixes. Ask support what can be done on your version specifically
2. UNDO size - has to have plenty of available space
3. We recommend Automatic UNDO management to be enabled
4. Increase the value for undo_retention if it's low (don't go higher than 1h)
5. Check with your DBA for any blocking sessions occupying the space
6. If slice data appears to be the cause - make sure you do not include too many periods for the slice
7. AWR report can be used to look further 

Additional Information

Reference also:

• Load DWH job failures - troubleshooting
• Data Warehouse (DWH) known issues
• Load DWH Full - When to run to prevent failures