OCI error 'ORA-22288: file or LOB operation GETLENGTH failed No such file or directory' when task is resumed

[rp5]

If a new task is created and run for the first time it works, while when the task is stopped and resumed it  returns an error:

OCI error 'ORA-22288: file or LOB operation GETLENGTH failed No such file or directory'

The source is Oracle, while the target is Snowflake.

if anyone else came across similar issues and if so, did you manage to resolve them?

[PadmaPriya · Accepted solution]

Hello @rp5 

The replicate was trying to find files in the file system, but the files where already deleted by the DBA.

We advise our clients to use retention policy of 48h ( 48h is to cover the weekend).

As for the latency in the mentioned error from the visual we can say that this is a pick and to a real latency, in some cases there is a big transaction in the DB and the replicate waiting for the commit/ rollback , so you may see a pick while the wait time.

To conclude the case :

Please add retention policy of 48h and monitor the system, we think the changes will prevent the errors.

Please keep us posted if you get the same or any other errors.

1)The copy to the "temporary directory" solution is an old feature, and usually, we don't recommend using it.

In your case, it will not help (the "temporary directory" solution) because if someone deletes the redo log, then Replicate will not be able to copy the redo log files to a" temp folder."

2) Replicate best practice to keep the redo log files three days because if a problem happens during the weekend for example, Replicate can be resume and proceed with the migration.

3) If they have significant latency, we can check the latency reason.

But you must keep enough redo log files to allow Replicate to perform the migration.

Please refer to below link:

https://help.qlik.com/en-US/replicate/November2020/Content/Replicate/Main/Oracle/OralcleDB_sourcebin...

Thanks,
Padma Priya

Qlik Support

[gdoyle]

Having this same issue but changing the retention period doesn't seem to help here, any other ideas?

[Dana_Baldwin]

Hi @gdoyle 

Are you getting any other error message along with this? Something like "Failed to open Redo log", or ?

Thanks,

Dana

[gdoyle]

@Dana_Baldwin Yes I am getting the failed redo log as well. 

Thanks for the  quick response!

[Dana_Baldwin]

Hi @gdoyle 

For some reason the task can't locate the redo log it needs to continue. Has the task ever worked? Maybe the source DBA changed the destination ID's of the archived logs. I know you have increased retention on the archived logs - but how long are they kept? We generally advise 3 days in case something happens on a weekend and you need to start from a timestamp.

Does the archive log the task is looking for exist on disk, and show as available in v$archived_log, status column?

Thanks,

Dana

[gdoyle]

@Dana_Baldwin The task has worked well in the past. It seems to run fine during the day and have issues overnight. We have identical tasks setup in test and prd environments that are doing fine. We did recently upgrade all three environments and did have an issue with the data directory on dev(the environment im having this error in) but it seems to run fine throughout the day? I did a full reload target on the parent and child tasks(of log stream) to see if that helps. 

[Dana_Baldwin]

Hi @gdoyle 

Please double check the retention of the archived logs. If the task has no trouble reading the redo/archived logs during the day, it could be that there are large batches of transactions at night that take longer to process, risking an archived log being purged before it is read.

Yes, performing a reload is one of the ways to get past the missing archive log issue.

Thanks,

Dana

[gdoyle]

@Dana_Baldwin When you're talking about archived logs retention. Are you talking about the retention setting in my log stream task or on the source database side? Really appreciate your help, have a good holiday!

[Dana_Baldwin]

@gdoyle on the source database side. You're welcome! Hope you have a good holiday too!