1

My organization has chosen to use Oracle Flashback to replace manually created audit tables. However, the DBA set the TUNED_AUTORETENTION values and UNDO_RETENTION to only 14400 seconds. Unfortunately, we have to keep the audit values for 7 years on this data. Is there a way to query "expired" version data so specific changes can be audited by timespan, or does UNDO_RETENTION and the individual TUNED_AUTORETENTION values need to be set to 220800000? I looked into flashback_transaction_query but it only shows the transaction general data, not which column values were changed.

The database in question currently has its tablespace files set to automatically allocate more physical layer space on the logical drive.

C. Allen
  • 35
  • 4

1 Answers1

1

For this purpose you should be using Flashback Data Archive, not basic Flashback. The parameters you mentioned only apply to basic flashback.

A Flashback Data Archive provides the ability to track and store transactional changes to a table over its lifetime. A Flashback Data Archive is useful for compliance with record stage policies and audit reports.

See here: https://docs.oracle.com/en/database/oracle/oracle-database/19/adfns/flashback.html#GUID-06AA782A-3F79-4235-86D5-2D136485F093

pmdba
  • 6,457
  • 2
  • 6
  • 16
  • Flashback Data Archive is what my department intended to use. However, when executing a query, the XID is not preserved in the VERSIONS_XID column, and it appears that other Version and transaction data is also lost. We can see the table values, but not when they changed or who changed them. – C. Allen Jun 24 '21 at 12:36
  • what query are you using to view the archived versions? And what steps were taken to enable archiving on your table(s)? – pmdba Jun 24 '21 at 12:46
  • Unfortunately, the final scripts to enable were handled by the offsite host and the request to enable went through a couple of layers of management and approval, so I can't be 100% sure. The code we ran to enable was just things like ALTER TABLE Billing.INVOICE_PRICE FLASHBACK ARCHIVE FIN_AUDIT_7Y; . This is how we discovered there was no XID: SELECT billing.INVOICE_PRICE.* , NVL(VERSIONS_STARTTIME, '') CHANGE_DATE , NVL(VERSIONS_OPERATION, 'I') OPERATION , VERSIONS_XID FROM billing.INVOICE_PRICE VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE – C. Allen Jun 24 '21 at 14:41
  • hmmm. something doesn't sound right, but this might require the assistance of Oracle Support to sort out. VERSION_XID should be populated. – pmdba Jun 24 '21 at 15:17
  • Would those retention variables come into play if the tablespace datafiles were not set with AUTOEXTEND ON NEXT nG; ? – C. Allen Jun 24 '21 at 17:49
  • No. They only apply to basic flashback, not to flashback archive. The autoextend just allows the data files to grow as you accumulate more data. If you turned it off, it would freeze the files at their current size and potentially cause something to hang when you ran out of room. – pmdba Jun 24 '21 at 18:40