Skip to Main Content
  • Questions
  • Oracle flashback error not a valid system change number

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question.

Asked: September 27, 2019 - 5:17 am UTC

Last updated: March 10, 2021 - 1:41 am UTC

Version: 12.2

Viewed 1000+ times

You Asked

Hi I created Oracle flashback Archive with retention of 1 month and enabled this archive on few of the tables. But when i execute a versions query like below, i get Error "ORA-08181: specified number is not a valid system change number. ORA-06512: at "SYS.TIMESTAMP_TO_SCN". " And i am not getting this consistently, Sometimes i can query way back 10 days and for some tables i cannot query past 2 days.

select versions_starttime from tbl1 versions between timestamp minvalue and maxvalue
or
select versions_starttime from tbl1 versions between timestamp sysdate-2 and sysdate

We do have AUTO undo management and undo retention is 24 hours and retention guarantee is set. As per many forums, its mentioned we get this Error when we try to look far back and as per the below link, it should be max( auto-tuned undo retention period, retention times of all flashback archives in the database).

https://docs.oracle.com/database/121/SQLRF/functions175.htm#SQLRF06325

Can someone help why we get this Error irrespective of FDA retention being one month?

and Connor said...

You may be hitting this bug

Bug 27092508 - Fashback Queries Fails with ORA-08181

I think you'll need to chat to Support so they can have a look at your SMON_SCN_TIME table to make sure its working OK

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, September 29, 2019 - 5:19 am UTC


ora-08181 not fixed yet

Marlon, March 09, 2021 - 4:22 pm UTC

Hi,

We also implemented FDA but start getting this error 8181 very soon. Oracle is not able to fix this bug yet on today 9th of march 2021. Should using the SMON_SCN_TIME table for retrieving timestamp be appropriate knowing that this table gets cleaned up and so always missing old scn values? Is there another way to recreate SMON_SCN_TIME in order to perform scn to time conversion on historical scn values retrieved from FDA? Or a smart query to interpolate these values? Thanks in advance

More to Explore

Backup/Recovery

Check out the complete guide to all of the Backup & Recovery techniques in the Oracle Database.