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:
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.
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.
To set up archive administration:
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
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.