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:
- 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.
- Go to your task settings, go to Full Load > Full Load Tuning and set:
- Transaction consistency timeout (seconds): 30
- 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.