topic Getting Error From Qlik Task in Qlik Replicate

Getting Error From Qlik Task

gseckin — Tue, 02 Apr 2024 11:12:00 GMT

Hi,

We're replicating very big table from oracle db to kafka. When I check logs, it says it finished with error but I also see success log. Should I ignore this error or it's just a wrong alarm?

Thanks

you can find the log below

03266399: 2024-04-02T03:11:17 [SOURCE_CAPTURE ]I: Source endpoint 'Oracle' is using provider syntax 'Oracle' (provider_syntax_manager.c:949) 01597276: 2024-04-02T03:11:17 [TASK_MANAGER ]I: Start loading table 'EPS'.'OPR_PAYMENTATTEMPTSTATUS' (Id = 1) by subtask 1. Start load timestamp 0006151475A8DD2B (replicationtask_util.c:754) 03266398: 2024-04-02T10:44:40 [SOURCE_UNLOAD ]E: ORA-01555: snapshot too old: rollback segment number 596 with name "_SYSSMU596_3430621215$" too small [1020436] (oracle_endpoint_unload.c:239) 03266398: 2024-04-02T10:44:40 [SOURCE_UNLOAD ]I: Unload finished for table '--------' (Id = 1). 545259000 rows sent. (streamcomponent.c:3784) 01597276: 2024-04-02T10:44:40 [TASK_MANAGER ]W: Table '------' (subtask 1 thread 0) is suspended. ORA-01555: snapshot too old: rollback segment number 596 with name "_SYSSMU596_3430621215$" too small (replicationtask.c:3150) 01597276: 2024-04-02T10:44:40 [TASK_MANAGER ]I: All tables are loaded. Full load only task is stopped (replicationtask.c:4262) 01597276: 2024-04-02T10:44:42 [TASK_MANAGER ]I: Subtask #1 ended (replicationtask_util.c:591) 01597276: 2024-04-02T10:44:47 [SERVER ]I: Stop server request received internally (server.c:2410) 01597276: 2024-04-02T10:44:47 [TASK_MANAGER ]I: Task management thread terminated (replicationtask.c:4446) 01597275: 2024-04-02T10:44:48 [SERVER ]I: Client session (ID 1994912877) closed (dispatcher.c:195) 01597275: 2024-04-02T10:44:48 [UTILITIES ]I: The last state is saved to file '/opt/attunity/replicate/data/tasks/ODS_OPR_OrderDetails_Backload/StateManager/ars_saved_state_000002.sts' at Tue, 02 Apr 2024 10:44:48 GMT (1712054688268922) (statemanager.c:641) 01597274: 2024-04-02T10:44:48 [SERVER ]I: The process stopped (server.c:2537) 01597274: 2024-04-02T10:44:48 [AT_GLOBAL ]I: Closing log file at Tue Apr 02 10:44:48 2024 (at_logger.c:2553)

Re: Getting Error From Qlik Task

Heinvandenheuvel — Tue, 02 Apr 2024 13:23:50 GMT

It was NOT successful. Partial succes: "545,259,000 rows sent. " - which may be 99.99% or 50% - you can check.

>> [TASK_MANAGER ]W: Table '------' (subtask 1 thread 0) is suspended

That's not good.

" ORA-01555: snapshot too old: rollback segment number 596 with name" - classic Oracle source issue - using Replicate or other tolls running a "Select * " from a very large table.

You may want to work with the DB to see if an increase in resources at the source is possible.

You may want/need to load in ranges. In the GUI double-click on the table in question and select 'parallel load' in the task settings check full-load tuning max-tasks. Default 5 is often fine but with active parallel loads you may need to increase to 10 or 20 or some such.

Hein

Re: Getting Error From Qlik Task

DesmondWOO — Tue, 02 Apr 2024 15:31:34 GMT

Hi @gseckin ,

In addition to Hein's response, you may also check this article: ORA-01555: Snapshot too old: rollback segment number .

Regards,
Desmond

Re: Getting Error From Qlik Task

john_wang — Thu, 04 Apr 2024 04:18:01 GMT

Hello @gseckin ,

Add some comments besides @Heinvandenheuvel & @DesmondWOO :

There are many discussion about the Oracle famous error ORA-01555, it's hard to tell the best approach however there are many options.

In Qlik Replicate, it needs to get a snapshot for FL, and then caches all changes which made after the snapshot but before full load finishes, then apply them to target side as well, it is used to guarantee the data consistency.

1. Try to load the table during "quiet time", eg the weekend or other non-peak time where you have a less frequency of changes

2. Utilize the parallel load, see UG get detailed usage

3. Uses multiple tasks to transfer the big table in parallel

4. Change the table load priority to start the big table load first

5. Add filter to load part of the history records to target side first by Full Load ONLY tasks

6. Improve the target DB performance to a faster device eg SSD, use faster network, or other hardware improvement

7. 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.

Cause: Rollback records needed by a reader for consistent read are overwritten by other writers.

Action: If in Automatic Undo Management mode, increase the setting of UNDO_RETENTION. Otherwise, use larger rollback segments.

Here's a reference for the Oracle Error -

An Oracle DBA should be able to make the changes on the database side
http://www.dba-oracle.com/t_ora_01555_snapshot_old.htm

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:9532055800346374418

Hope this helps.
John.