We had a requirement to track how, when, and who made changes to the data in some of our sensitive tables. In the past, I would accomplish this by creating a trigger that would create before-and-after images into an audit table along with some other information such as the time of the change, the current user making the change, the IP address of the user making the change, etc. With 11g, Oracle introduced “Total Recall” that was subsequently renamed to Flashback Data Archive (FDA or FBA) in 12c. This new feature allows for long-term storage of “undo” data in a separate area known as the flashback archive.
Since my target database was on Oracle 12c, I decided to try out this new technology as opposed to a trigger solution.
Setup steps
Create a TABLESPACE and associated data file
CREATE TABLESPACE "FDA" DATAFILE '+DG_ORA1/datafile/fda_01.dbf' SIZE 1G NOLOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
Create the flashback archive pointing to the above TABLESPACE. Additionally, set the quota and retention
CREATE FLASHBACK ARCHIVE DEFAULT OA_FDA TABLESPACE FDA QUOTA 1G RETENTION 30 day;
If you add more space to the TABLESPACE associated with the flashback archive, remember to increase the quota assigned above to the flashback archive. It is important to allocate enough space to this TABLESPACE as running out of space here will also impact the operations on the base tables.
In order to capture additional information about the user account, terminal, program, etc. that made the change, set the context level as shown below. There are other options to the level, however, I found “ALL” to be a good choice.
exec dbms_flashback_archive.set_context_level(level=> 'ALL');
Grant the schema owner access to the flashback archive
ALTER USER ACCT_OWNER QUOTA UNLIMITED ON FDA;
add the tables that you want to monitor to the flashback archive
ALTER TABLE "ACCT_OWNER"."ACCT_MASTER" FLASHBACK ARCHIVE OA_FDA;
For every table that is added to the flashback archive, Oracle creates three additional tables named
SYS_FBA_DDL_COLMAP_91245 SYS_FBA_HIST_91245 SYS_FBA_TCRV_91245
I am not exactly sure how the number is assigned for each table added to the flashback archive but it will get a number and these three tables. These tables are created in the flashback data archive TABLESPACE created at the beginning of this process.
You can list the tables that are currently in the flashback data archive with the following SQL statement
SELECT * FROM dba_flashback_archive_tables ORDER BY owner_name, table_name ;
Viewing the information if you have the key columns
The following SQL statement will display the change to a given row of table. Note that the SQL statement is run on the actual table and the optimizer uses the indexes on the actual table hence code the predicates appropriately
SELECT
versions_startscn
,versions_starttime
,versions_endtime
,versions_xid
,case versions_operation
when 'D' then 'Delete'
when 'I' then 'Insert'
when 'U' then 'Update'
else versions_operation
end as Action
,DBMS_FLASHBACK_ARCHIVE.get_sys_context
(versions_xid, 'USERENV','SESSION_USER') AS session_user
,DBMS_FLASHBACK_ARCHIVE.get_sys_context
(versions_xid, 'USERENV','HOST') AS host
,DBMS_FLASHBACK_ARCHIVE.get_sys_context
(versions_xid, 'USERENV','module') AS module
,DBMS_FLASHBACK_ARCHIVE.get_sys_context
(versions_xid, 'USERENV','os_user') AS os_user
,DBMS_FLASHBACK_ARCHIVE.get_sys_context
(versions_xid, 'USERENV','ip_address') AS ip_address
,DBMS_FLASHBACK_ARCHIVE.get_sys_context
(versions_xid, 'USERENV','AUTHENTICATED_IDENTITY')
as AUTHENTICATED_IDENTITY
,DBMS_FLASHBACK_ARCHIVE.get_sys_context
(versions_xid, 'USERENV','DATABASE_ROLE') as test
,RO.*
FROM "ACCT_OWNER"."ACCT_MASTER" versions between
timestamp (systimestamp - interval '1' day) and
systimestamp RO
WHERE key_value_col = 1622310
and versions_xid is not null
order by
versions_starttime desc
;
Note:
a. that the table is aliased to RO at the end of the FROM clause (i.e. after the versions between part of the SQL). This allows me to see all the columns with RO.*.
b. in some cases I saw rows where the versions_xid was not populated and this causes the DBMS_FLASHBACK_ARCHIVE.get_sys_context to fail.
c. if you attempt to read data before any changes have been made and written to the flashback archive, you get a null error. Adjust the time period specified in the versions above.
d. there is a slight delay between modifying the table and seeing it in the flashback data archive. Changes to the flashback archive are written by a new process called the “fbda”. This is not a significant issue and I assume I was only able to notice it because I was testing on a quiet system where I was the only user.
Viewing the information if you DO NOT have the key columns
The above SQL will answer the question of who modified the data in a particular row. If you are attempting to identify all the changes that were made to a given table (i.e. you do not have key column values), the above SQL will not return in any reasonable amount of time. The issue is that each row in the result set initiates three calls to DBMS_FLASHBACK_ARCHIVE.get_sys_context for every column that uses the function. For example, if there are 100 rows that are in the flashback archive, and I am using the get_sys_context column 7 times in the above SQL, there will be (1000 rows * 7 columns * 3 calls) = 21,000 calls to DBMS_FLASHBACK_ARCHIVE.get_sys_context.
Also, note that the versions clause will list all of the rows that exist in the specified timeframe and not the rows that were modified in that timeframe. This will be discussed later below.
As a result of these gotchas, the below SQL performs better if you’re searching for all the rows that are modified a a given time
SELECT
versions_startscn
,versions_starttime
,versions_endtime
,versions_xid
,case versions_operation
when 'D' then 'Delete'
when 'I' then 'Insert'
when 'U' then 'Update'
else versions_operation
end as Action
,RO.*
FROM "ACCT_OWNER"."ACCT_MASTER" versions between
timestamp (systimestamp - interval '1' day) and
systimestamp RO
order by
versions_starttime desc
;
The above query assumes that you want to see the changes in chronological order descending. If you want to see the information by a key column, then order by that column. After you have identified the particular row that you’re interested in, use the key column value and run the previous SQLs with the DBMS_FLASHBACK_ARCHIVE.get_sys_context columns.
Altering a table – Adding a column
If you add a column, the rows that existed in the flashback archive for the have values of now with the new column which is pretty much expected behavior for any table.
Altering a table – Modifying a column
If you modify an existing column of the table that is currently in the flashback archive, you will see a new column named something like M_50389_old_column_name together with the previous column.
Dropping a table that is part of the flashback archive
Before dropping a table that is part of the flashback archive, you need to remove it from the flashback data archive otherwise you will get the error
drop table "ACCT_OWNER"."ACCT_MASTER_DEAN"
Error report -
ORA-55610: Invalid DDL statement on history-tracked table
55610. 00000 - "Invalid DDL statement on history-tracked table"
*Cause: An attempt was made to perform certain DDL statement that is
disallowed on tables that are enabled for Flashback Archive.
*Action: No action required.
Remove the table from the flashback archive as follows
ALTER TABLE "ACCT_OWNER"."ACCT_MASTER" NO FLASHBACK ARCHIVE;
and then it can be dropped.
Any attempt to read the flashback data for a table that has been drop results in the error
ORA-01466: unable to read data - table definition has changed
01466. 00000 - "unable to read data - table definition has changed"
*Cause: Query parsed after tbl (or index) change, and executed
w/old snapshot
*Action: commit (or rollback) transaction, and re-execute
Rows that exist vs. rows that were modified in the versions interval
When I started this exercise, I assumed that a query based on versions between timestamp “A” and timestamp “B” would only show me the rows that were modified between these two timestamps. However, my queries would return rows that had been modified outside of this range. This is documented at Oracle support in Doc id 2686158.1 which states “the VERSIONS BETWEEN clause is used to qualify rows which “existed” in the time range, and it is not something used to qualify rows which were “inserted” in the time range”. The column versions_starttime indicates the time of the actual change.
Performance implications
In a quiet system, I ran a workload of 10,000 inserts, 10,000 updates followed by 10,000 deletes. Each SQL statement was followed by a commit in an attempt to simulate an OLTP workload. After the AWR snapshot had rolled over to a new snapshot interval, I turned on the flashback archive and ran the same workload. An AWR difference report did not show any noticeable difference in resource consumption. However, this could have been due to the small size of my workload. Users testing in a somewhat busy nonproduction environment were also unable to identify any perceptible increase in elapsed time.
Locking
I naively assumed that since the rows were being written to the flashback archive via the FBDA process there would be no locking. However, we did see instances of locking. A colleague found the below documents on Oracle support
2423880.1 - FBDA: Massive Performance Problem After
enabling FDA (Doc ID 2423880.1)
Request patch for bug 22239699
22239699 - Bug - FBDA TCRV delete maintenance colliding with
archiving delete causing TX contention
2344671.1 - Flashback Data Archive: Error ORA-1 and ORA-60 In
SQL Merge Into SYS_FBA_TCRV DEADLOCK ERRORS
2370465.1 - FDA - Flashback Data Archive Usage and Best
Practices (a.k.a. Total Recall)
Summary
In summary, it was relatively easy to set up the flashback archive and add or remove tables. Volume testing in a nonproduction environment has not shown any increase in resource consumption. Ensuring that there is enough space in the flashback archive table space is very important as running out of space impacts the application.
