Skip to Main Content
  • Questions
  • Flashback Data Archive query returning duplicates

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, mikhail.

Asked: September 09, 2020 - 2:34 pm UTC

Last updated: September 14, 2020 - 2:53 am UTC

Version: 12.1.0.2.0

Viewed 1000+ times

You Asked

Hello!

I am using Oracle DB with "Flashback Data Archive" enabled.

I try to get one row as it was some time ago. For this I do "as of query"

But I got 2 rows instead of 1, although ID is Primary Key:
SQL> SELECT ID, STATE$DOC
  2  FROM   WB.CLAIMS AS OF TIMESTAMP TO_TIMESTAMP('2020-09-04 13:34:12', 'YYYY-MM-DD HH24:MI:SS')
  3  WHERE ID = 521228;

        ID STATE$DOC
---------- -------------------------------
    521228 Draft
    521228 Confirmed



query with AS OF SCN:

SQL> SELECT ID, STATE$DOC
  2  FROM   WB.CLAIMS AS OF  SCN timestamp_to_scn ( TO_TIMESTAMP('2020-09-03 13:34:12', 'YYYY-MM-DD HH24:MI:SS'))
  3  WHERE ID = 521228;

        ID STATE$DOC
---------- -------------------------------------------------------------------------------------------------------------------------
    521228 Draft
    521228 Confirmed



Is something wrong with database?
Or this is a normal behavior. If so how can I understand which row is right?

Thanks in advance.


and Connor said...

That looks like a bug to me.

Check out the following MOS note

Duplicate Data/Wrong Results with Flashback Data Archive Enabled (Doc ID 1998690.1)

There are several reparative actions to look at.



Rating

  (1 rating)

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

Comments

Thank you!

Mikhail, September 15, 2020 - 9:49 am UTC

Thank you for your time, I'll try to contact Oracle support.

More to Explore

Backup/Recovery

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