How do I properly use Oracle Flashback to audit table changes with user information?

Question

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.

pmdba · Accepted Answer · 2021-06-23 23:23:09Z

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

answered Jun 23, 2021 at 23:23

pmdba

7,0832 gold badges10 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

C. Allen Over a year ago

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.

pmdba Over a year ago

what query are you using to view the archived versions? And what steps were taken to enable archiving on your table(s)?

C. Allen Over a year ago

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

pmdba Over a year ago

hmmm. something doesn't sound right, but this might require the assistance of Oracle Support to sort out. VERSION_XID should be populated.

C. Allen Over a year ago

Would those retention variables come into play if the tablespace datafiles were not set with AUTOEXTEND ON NEXT nG; ?

|

Collectives™ on Stack Overflow

How do I properly use Oracle Flashback to audit table changes with user information?

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related