Informatica MDM - How to fix an error - ORA-01555: snapshot too old?

While working on Informatica MDM jobs, I came across one issue. The issue is an ORA-01555: snapshot too old. This error message was reported while running the tokenization job. If you are also noticing a similar issue then this article will help. This article provides details about an error message and a solution to fix it.

Error Message:

The detailed error message is as below -

java.sql.BatchUpdateException: ORA-01555: snapshot too old: rollback segment number 11 with name "_SYSSMU11_2399779032$" too small

SIP-16084: Error occurred while verifying the need to tokenize records. Return code 12801, 

Error SQLException During VerifyNeedToStrip :ORA-12801: error signaled in parallel query server P000,
ORA-01555: snapshot too old: rollback segment number 30 with name "_SYSSMU30_2998435469$" too small.
 at com.siperian.common.SipRuntimeException.createNotExternalized(SipRuntimeException.java:74)
 at com.delos.cmx.server.interact.caller.InteractCleanseClient.executeGenerateMatchTokens(InteractCleanseClient.java:460)

Solution:

To fix the issue perform the steps below -

A. Database Issue

First, analyze if there is any database issue going on. If the database looks good then perform the steps below

Step 1: Stop any job running as 'INCOMPLETE'

Step 2: Stop the Application server

Step 3: Verify undo_retention value by running the query below on db side

           

             show parameter undo_retention;

Step 4: If the value is lower then increase the value to 4000 by executing the command below

            ALTER SYSTEM SET UNDO_RETENTION = 4000;

Step 5: Increase Undo Tablespace to Auto Extended on.

Step 6: Restart the database servers with the clear cache

Step 7: Drop T$ table if present any in database

Step 8: Start the MDM servers with a clear cache.

This will fix the issue. I hope this is helpful. You can learn more about job tuning here -