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