How to Use Temporal History in Oracle 12c to Track Changes Over the Lifetime of a Table

In this posting, we are continuing with the theme of Information Lifecycle Management (ILM). As mentioned in an earlier posting, there usually comes a point during the lifecycle of the data when it is no longer being regularly accessed:

• May be obsolete and is eligible for purging
• Might be needed in the future but could be moved to tape
• Still used for decision support analysis but could be moved to a data warehouse

Temporal History Overview

Oracle 11g introduced the Flashback Data Archive (FDA) which automatically tracks changes to a table over the lifetime of that table. In Oracle 12c, the name was changed from Flashback Data Archive to Temporal History. In Oracle 11g, this was an additional license option. No additional licensing required beginning in Oracle 12c, except when using OPTIMIZE DATA – discussed later.

Temporal History automatically tracks changes to a table over the lifetime of the table. This may assist in satisfying security and compliance requirements, archiving, auditing, reporting, etc. The archived data may also be useful for data analysis.

The history is secure. There is no possibility to modify the historical data. The data is automatically purged based on a user-specified retention policy. It is very efficient since there are special kernel optimizations to minimize performance overhead. Far greater efficiency than can be achieved utilizing triggers, which is how we achieved this process in the past. The archived data is stored in compressed form in tablespaces to minimize storage requirements. The process of archiving is completely transparent to applications and it is very easy to set up and use.

A Flashback Data Archive is a new database object type that is a logical container for storing historical information. It is stored in one or more tablespaces and tracks the history for one or more tables. A retention duration is specified for each Flashback Data Archive and the data is automatically deleted based on the retention specified. The historical data may be grouped by retention requirements in a Flashback Data Archive. Multiple tables can share the same retention and purge policies. However, each archive may have different retention periods.

Temporal History is an extension to existing flashback features, first introduced in Oracle 11g and extended in each version since. Prior to Flashback Data Archive, the flashback was restricted by the availability of UNDO and its retention period.

Temporal History Architecture

The Flashback Data Archive background process, FBDA, starts with the database.

1. FBDA operates first on the undo in the buffer cache. Capture interval defaults to 5 minutes and is automatically tuned.
2. In case the undo has already left the buffer cache, FBDA could also read the required values from the undo segments.
3. FBDA collects the modified rows of flashback archive enabled tables and writes them into the appropriate history tables, which make up the flashback data archive.

You can find the internally assigned names of the history tables by querying the *_FLASHBACK_ARCHIVE_TABLES view. History tables are compressed and internally partitioned.

The database automatically purges all historical information on the day after the retention period expires. It deletes the data but does not destroy the flashback data archive. For example, if the retention period is 10 days, then every day after the tenth day, the oldest information is deleted; thus, leaving only 10 days of information in the archive.

Preparing and Using the Flashback Data Archive

To set up archive administration:

1. Create one or more tablespaces to be used for data archives and grant QUOTA on the tablespaces. You may use existing tablespaces but it is not recommended.
2. Grant the FLASHBACK ARCHIVE ADMINISTER system privilege to create and maintain flashback archives to the individual that will be administering the archives.
3. Set up archive usage by granting the FLASHBACK ARCHIVE object privilege to enable history tracking for specific tables in the given flashback archives.
4. Grant FLASHBACK and SELECT privileges to those users that will be querying specific tables that are being archived.
5. Configure UNDO management by creating an UNDO tablespace and enabling Automatic Undo Management (default). Oracle recommends a fixed-size UNDO tablespace (no auto-extend) when using Temporal History.

To use the Flashback Data Archive, first create the Flashback Data Archive. Optionally, you can specify the default Flashback Data Archive for the database using the DEFAULT keyword:

CREATE FLASHBACK ARCHIVE DEFAULT two_year_archive
TABLESPACE archive_one QUOTA 2G RETENTION 2 YEAR;

Only one tablespace can be specified in the CREATE command. Add additional tablespaces to the archive using the ALTER FLASHBACK ARCHIVE command. Then, enable history tracking for a table, in this case, in two_year_archive:

ALTER TABLE employees FLASHBACK ARCHIVE two_year_archive;

You can now view the historical data:

SELECT employee_id, salary, commission
FROM employees AS OF TIMESTAMP TO_TIMESTAMP
(‘2015-01-01 14:35:42’, ‘YYYY-MM-DD HH24:MI:SS’);

The history table can be indexed to improve the performance of historical queries.

As an example of using the history data, assume that rows have been deleted and then you realize it should not have been. Recover data using the Flashback Data Archive:

INSERT INTO employees
SELECT * FROM employees AS OF TIMESTAMP
TO_TIMESTAMP(‘2014-03-12 11:30:00′
,’YYYY-MM-DD HH24:MI:SS’)
WHERE name = ‘ADAMS’;

When it is no longer necessary to track the history for a table, disable history tracking:

ALTER TABLE employees NO FLASHBACK ARCHIVE;

Caution: All the historical data for the table will be lost.

A best practice is to create a separate archive for each application module. Do not combine unrelated tables from Payroll and Accounts Payable just because the retention times are the same.

Next up in my series is Managing the Flashback Data Archive.