Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW

ORA-01555: Snapshot too old: rollback segment number

No ratings
cancel
Showing results for 
Search instead for 
Did you mean: 
David_Fergen
Former Employee
Former Employee

ORA-01555: Snapshot too old: rollback segment number

Last Update:

May 11, 2023 10:22:45 AM

Updated By:

Sonja_Bauernfeind

Created date:

Jul 21, 2020 1:58:52 PM

The ORA-01555 is caused by Oracle read consistency mechanism.  If there are lots of updates, long running SQL and too small UNDO, the ORA-01555 error will appear.

There are some ways to help mitigate the error message:

  • Try to load the table during "quiet time":, eg the weekend or other non-peak time where you have a less frequency of changes
  • You can use parallel load: see UG get detailed usage (note not all source/target endpoints types support parallel load)
  • Change the table load priority: to start the big table load first
  • Add a filter: to load part of the history records to target side first
  • You can use other ways: Unload data from Oracle to file , it will take less time. then arrange the data load to target manually, then start AR task from the timestamp that customer did unload
  • Hardware improvement: Place the target DB to a faster device eg SSD, use faster network.
  • Software improvement: The error message "ORA-01555: Snapshot too old: rollback segment number" is a resource / setting / configuration issue with regard to Oracle undo tablespace.

 

Resolution

To fix getting the error message if in Automatic Undo Management mode, increase the setting of UNDO_RETENTION. Otherwise, use larger rollback segments.

Alternatively, we have observed that the following resolved the error:

  1. Add the internal parameter BulkArraySize to both source Oracle and target endpoints (except the SQL Server where this parameter is not available) and set it to 5000

    For information on how to set internal parameters, see Qlik Replicate: How to set Internal Parameters.

  2. Go to your task settings, go to Full Load > Full Load Tuning and set:

    1. Transaction consistency timeout (seconds): 30
    2. Commit rate during full load: 20000 (if no LOB columns else 5000)

 

Cause

The issue occurs as the rollback records needed by a reader for consistent read are overwritten by other writers.

Labels (1)
Version history
Last update:
‎2023-05-11 10:22 AM
Updated by: