Skip to Main Content

Breadcrumb

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 31, 2020 - 10:14 am UTC

Category: Database Development - Version: 12c

Viewed 100+ times

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

  (6 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.


Connor McDonald

Followup  

January 29, 2020 - 9:12 am UTC

Can you check something for us:

1) get the object_id of the table name you have FDA on
2) run

select table_name from user_tables
where table_name like '%nnn%'

where nnn is that object id

January 29, 2020 - 2:32 pm UTC

Reviewer: Divya V from India

I got it for one of the table where FDA enabled and this is from all_Tables. Do we need this from user_Tables specifically?

select table_name from all_tables where table_name like '%457635%';

TABLE_NAME
--------------------------------------------------------------------------------------------------------------------------------
SYS_FBA_DDL_COLMAP_457635
SYS_FBA_HIST_457635
SYS_MFBA_NHIST_457635
SYS_FBA_TCRV_457635

Actually we ended up disabling FDA in many tables as we had issues with UNDO. We got 1555 errors before and 2 days back, most of DMLs in this DB had "unable to extend segment by 8 in undo tablespace 'UNDOTBS1'". We added 200GB more but the ACTIVE Undo was keep getting full and not released.
Though many DMLS were killed, the Active undo was not going down.I was suspecting if FDA causing the data to be retained in UNDO for longer time than it has to. Not sure if its a bug in FDA or we are not figuring the proper UNDO settings for seamless FDA implementation.
Connor McDonald

Followup  

January 30, 2020 - 9:00 am UTC

I think that's one you'll need to take up with Support - I've not seen or heard of such problems.

If you do, please drop us an email to asktom_us@oracle.com with the SR# - I'd like to keep up to date on how this progresses

January 30, 2020 - 7:45 am UTC

Reviewer: A reader

from user_Tables its just the list below

SYS_FBA_DDL_COLMAP_457635
SYS_FBA_TCRV_457635
SYS_FBA_HIST_457635
Connor McDonald

Followup  

January 30, 2020 - 9:01 am UTC

Thanks I was just ticking off the potential issue that can happen where flashback tables are (silently) not created, which makes a mess of things down the track

ORA-01555 caused by FDA

January 30, 2020 - 2:34 pm UTC

Reviewer: michal from Poland

Regarding the growth of UNDO - and consequently ORA-01555,

Not sure if it is Your issue here, but I've encountered similar symptoms when owner of table for which we are enabling FDA does not have a quota on FDA tablespace
(kind'a make sense, change to table X is still in UNDO, but it cannot be saved to SYS_FBA_* due to lack of quota - hence this UNDO have to be kept "forever")
Maybe this will help
Connor McDonald

Followup  

January 31, 2020 - 10:14 am UTC

Thanks for that input. Good stuff!

February 01, 2020 - 2:11 am UTC

Reviewer: A reader

Thanks, We do have unlimited Quota for the user on the tablespace where i created FDA

More to Explore

Design

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