How to Manage the Flashback Data Archive

Flashback data archive

In this post, we will continue with the theme of Information Lifecycle Management (ILM). Through ILM, I will be discussing how to successfully manage the Flashback Data Archive.

Maintaining the Flashback Data Archives

To add space to an existing archive:

ALTER FLASHBACK ARCHIVE two_year_archive
ADD TABLESPACE archive_two QUOTA 2G;

To change the retention time:

ALTER FLASHBACK ARCHIVE two_year_archive
MODIFY RETENTION 2 YEAR;

It is possible to purge data from an archive. However, remember that the data is automatically purged based on your setting for the retention period.

ALTER FLASHBACK ARCHIVE two_year_archive
PURGE BEFORE TIMESTAMP (SYSTIMESTAMP – INTERVAL ‘1’ DAY);

Finally, a Flashback Data Archive can be dropped:

DROP FLASHBACK ARCHIVE two_year_archive;

The archived data for all tables in that specific archive will be lost.

DDL Restrictions

Prior to Oracle 11g Release 2, using any of the following DDL statements on a table that is enabled for Flashback Data Archive causes the error ORA-55610:

ALTER TABLE statement that does any of the following:
Drops, renames, or modifies a column
Performs partition or subpartition operations
Converts a LONG column to a LOB column
DROP TABLE statement
TRUNCATE TABLE statement
RENAME TABLE statement

Beginning with Oracle 11g Release 2, additional DDL is supported:

DROP, RENAME, and MODIFY column
DROP and TRUNCATE partition
RENAME and TRUNCATE table

When a schema has evolved in any of these ways, the Flashback Data Archive automatically keeps track of the changes. Flashback queries work across the DDL changes, and output is presented accordingly. All other DDL is not automatically supported.

However, in Oracle 11g Release 2, Oracle introduced Full Schema Evolution. DDL changes not automatically supported by the Flashback Data Archive may be accommodated through the DBMS_FLASHBACK_ARCHIVE PL/SQL package. The DISASSOCIATE_FBA procedure is used to disassociate the archive from the table (note the chart below). Changes and then made but must be made to both the base table and the corresponding archive. This disables Flashback Data Archive on specified tables, allowing more complex DDL (upgrades, split tables). Once the changes are completed, use the REASSOCIATE_FBA procedure to again associate the table with the archive. The schemas must be the same for the base table and the history table. The Flashback Data Archive will validate that the schemas are the same upon association.

This operation should be used with the understanding that the archive can no longer be guaranteed to track all changes to the base table. The base table could have been modified during the disassociation. Use of these packages requires the FLASHBACK ARCHIVE ADMINISTER privilege.

dec3-1

Temporal History Optimization

Prior to Oracle 12c, the history tables are compressed and deduplicated. By default, in Oracle 12c, they are not compressed nor deduplicated. When a table is created with the OPTIMIZE DATA clause, table and LOB compression and LOB deduplication is automatically turned on.

CREATE FLASHBACK ARCHIVE two_year_archive TABLESPACE tbs1
OPTIMIZE DATA RETENTION 5 YEAR;

OPTIMIZE DATA requires the Advanced Compression Option, an additional license option. NO OPTIMIZE DATA is the default. FDA history tables already compressed and deduplicated in 11g or before the upgrade to 12c will continue to get compression and deduplication.

To stop optimization on FDA history tables, execute the statement:

ALTER FLASHBACK ARCHIVE two_year_archive NO OPTIMIZE DATA;

Viewing Flashback Data Archive Information

Several data dictionary views are available for viewing Flashback Data Archive information:

dba_flashback_archive — Information about Flashback Data Archives

dba_flashback_archive_ts — Information on all tablespaces that contain Flashback Data Archives

dba_flashback_archive_tables — Information about tables that are enabled for flashback archiving and the archive to which the table is assigned

Next up in the Information Lifecycle Management(ILM) series I will be discussing Temporal Validity.

image sources

  • dec3-1: Kent Hinckley

Type to search blog.learningtree.com

Do you mean "" ?

Sorry, no results were found for your query.

Please check your spelling and try your search again.