Loop fails to convert local timestamp to SCN and terminates with ORA-08180 & ORA-06512

[Aegis]

Hello everyone, my loop seems to fail with:

Stream component 'st_0_SOURCE' terminated
Stream component failed at subtask 0, component st_0_SOURCE
Error executing command
Failed to set stream position on context '2022-03-15 14:59:07'
Failed to convert local timestamp 2022-03-15 16:59:07 to SCN
ORA-08180: no snapshot found based on specified time ORA-06512: at "SYS.TIMESTAMP_TO_SCN", line 1

Ive dug through traces as well, and cant seem to find solution on my own, what could be causing this and whats the possible fix? The stream is set to apply changes only with transformation to different target schema.

Thanks in advance!

[john_wang]

Hello @Aegis ,

It appears that there is an Oracle limitation that prevents Replicate from resuming replication from beyond a certain point. A detailed description SCN_TO_TIMESTAMP .

You may compare the 2 SQLs between

select timestamp_to_scn('2022-03-15 11:59:07') from dual;

&

select timestamp_to_scn('2022-03-28 11:59:07') from dual;

(choose the closer time to today if you hit the similar error).

Hope this helps.

Regards,

John.

[Heinvandenheuvel · Accepted solution]

Replicate needs (wants) an SCN number to know from which SCN number onwards to select changes from the (archived) logs. As @john_wang  wrote you have hit an Oracle restriction whereby the timestamp_to_scn function used by Replicate is only guaranteeing 5 days back and you appear to request more than 10 back.

You could possibly use an approximated  start time  by using an SCN perhaps obtained with:

select first_change#, first_time from v$archived_log where first_time > sysdate - 20;

Why does Replicate not do that? My guesses

1) too imprecise

2) if you have to go back that deep, it is likely going to take more time than to just re-load. Remember all of the Archived logs from the selected time onwards will need to be put back in place. Are you ready and able to provide all of those?

Hein.

[Steve_Nguyen]

@Aegis , did any of the reply help ?

[Aegis]

Indeed! all good, thanks!