Skip to Main Content

Breadcrumb

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

Question and Answer

Connor McDonald

Thanks for the question, Divya.

Asked: January 12, 2020 - 8:27 am UTC

Answered by: Connor McDonald - Last updated: January 23, 2020 - 2:58 am UTC

Category: Database Development - Version: 12c

Viewed 100+ times

Whilst you are here, check out some content from the AskTom team: Running external programs from the scheduler

You Asked

Hi All,
We are using Oracle Flashback Data Archive in our database to track Audit in most of the critical tables. We initially planned to store data for 6months but there are few errors / issues we face making it unstable.
So we have it as retention of one month now. Below are the issues

1) We are faced with ORA-01555: snapshot too old: rollback segment number with name “ ” too small. This error came right away when we query against few tables as below

select * from tblA versions between timestamp minvalue and maxvalue;

Our UNDO Tablspace is 100GB with retention of 24 hours and NO GURANTEE.

We did not want to make it as GURANTEE as we were concerned if it may impact the batch transactiopns against the DB. Do we need to change any of the UNDO settings ? IS there a way to determine the UNDO Tbsp we are supposed to have (based on no of transations per hour) for seamless FDA ?
As per forums, it says the data in UNDO is wiped before FDA can pick it up. IF so,


2) Though we created with retention of mone month, we are unable to query past 5 days or 10 days (intermittent). It looked like an Oracle issue and below is the reference info
We applied the patchw ith help of Oracle support from our ORG but the issue is still reoccurring

http://asktom.oracle.com/pls/apex/asktom.search?tag=oracle-flashback-error-not-a-valid-system-change-number

3) We use FDA as archival but also to run some statistical queries againse this data. eg. how many rows had col1 changed over past 24 hours. But its very slow when we do. Do we need to index these tables for VERSIONS_STARTTIME / VERSIONS_ENDTIME ? Is it advisable to
use FDA for these kind of analytical queries or does it make sense to extract and load to anopther instance and run it againse there? ITs more of a design decision but would like to get some suggestions

and we said...

For (1)

I would take a look at MOS note 2408008.1, and have a chat with Support to see if this is relevant to you.

Also, check out v$undostat which has a figure "tuned_undoretention" which should tell you what true undo retention you are getting.

In a non-flashback scenario, you would compare this to the "maxqueyrlen" figure to ensure that your undo is longer than than your longest query.

However, in the flashback case, then maxquerylen is in reality, how far back you are going to try go with as "AS OF" command. We will try mine undo first, and then head into the archive. It is because of this, that we typically recommend using "guarantee" mode if FDA is going to be a core part of your business/application functionality.

For (2), can you put some examples in a review, so we can see some context

For (3), you can see the underlying query plans for a flashback query, for example

SQL> select * from EMP 
  2   AS OF TIMESTAMP SYSDATE-3;

-----------------------------------------------------------------
| Id  | Operation                   | Name               | Rows  |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT            |                    |   446 |
|   1 |  VIEW                       |                    |   446 |
|   2 |   UNION-ALL                 |                    |       |
|*  3 |    FILTER                   |                    |       |
|   4 |     PARTITION RANGE ITERATOR|                    |   445 |
|*  5 |      TABLE ACCESS FULL      | SYS_FBA_HIST_69539 |   445 |
|*  6 |    FILTER                   |                    |       |
|*  7 |     HASH JOIN OUTER         |                    |     1 |
|*  8 |      TABLE ACCESS FULL      | EMP                |     1 |
|*  9 |      TABLE ACCESS FULL      | SYS_FBA_TCRV_69539 |    14 |
------------------------------------------------------------------


The structure of those underlying objects is really optimized for data retention and cleaning out the data once its gets past its flashback retention period. You are allowed however to create (non-unique) indexes on the history tables if this will help with certain queries. Reference: https://www.oracle.com/ocom/groups/public/@otn/documents/webcontent/4421812.pdf

But for totally adhoc queries, you might be better at looking at extracting the contents into a separate table and then letting people go wild querying against that.

There is also some internal tuning recommendations in MOS note 2370465.1 (but please consult with Support before doing anything in this document).

Hope this helps.

and you rated our response

  (2 ratings)

Reviews

Great Answer

January 22, 2020 - 12:32 pm UTC

Reviewer: A reader

Thanks for replying right away. The answer is detailed and precisely what i wanted to know.
Connor McDonald

Followup  

January 23, 2020 - 2:58 am UTC

glad we could help

Needed some more help

January 24, 2020 - 7:04 am UTC

Reviewer: Divya V from India

The answer was helpful and would like a follow up.

We were increasing UNDO Tbsp to mitigate 1555 error in FDA Query, but we could not get around the error yet. We have oracle 12.2 and "FBDA: ORA-1555 after installing the Flashback Data Archive megapatch patch 25533545 (Doc ID 2408008.1)" seems to be for 12.1 only and not 12.2.

Our tuned_undoretention shows 24 hours from v$undostat though the mode is NOT GURANTEE. The flashback query i write is versions between min and max. I assumed if not in UNDO it has to go to FDA and get the data for me. So getting this 1555 error means, the data is not archived in FDA as well? when the transaction is committed, it would have been there in UNDO and i wonder why it did not go to archive. When i issue FDA select query, will it always rely on UNDO ? I want to know why it must be on GURANTEE mode.

For 2nd point in my original Question, when we enabled FDA in a table with 30 days retention period, and when i query below i get data

select * from tblname versions between timestamp sysdate-1 and sysdate

but when i query below i do not and i get below error. If i disable and enable it works but i lose all flashback data

select * from tblname versions between timestamp sysdate-5 and sysdate

ORA-08181: specified number is not a valid system change number ORA-06512: at "SYS.SCN_TO_TIMESTAMP", line 1


We took help from Oracle support and applied a patch but the issue is reoccurring.


More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.