How to Use In-Database Archiving

In this posting, we are continuing with the theme of Information Lifecycle Management (ILM). Another option in Oracle 12c, is In-Database Archiving, also called Row Archival.

Why In-Database Archiving

The simple goal of in-database archiving is to assign values to the data based on business rules of whether the data is current or marked as non-active. As the value of the data lowers over time, the related data can be moved to less costly storage. The challenges are:

Databases grow; rarely do they get smaller

Data growth rates are accelerating

The old data may need to be instantly available in the database at a future time

In-database archiving is a possible solution.

Setting Up Row Archival

Use in-database archiving to distinguish active rows from non-active within the same table. It is also possible to compress the non-active rows as they are moved. Compression requires the Advanced Compression option.

The row archival attribute indicates whether a row is active or non-active. By default, when a row is inserted, it is set as active, and the ora_archive_state value is ‘0’. After a period of time, the row may be less frequently accessed and is rarely updated, but still considered as active. It needs to be retained for records retention and/or compliance purposes. It is, therefore, considered to be in a non-active state. The value ‘1’ – or any value other than ‘0’ – in the ora_archive_state column would reflect the non-active state. Update ora_archive_state to ‘1’ for row archiving. ACTIVE is the default value.

Using Row Archival

View ora_archive_state column:

When it is determined that the rows should not be active, set the rows to the archive state. This could be done with a batch job scheduled to run during the maintenance window.

UPDATE emp
SET ora_archive_state = 1
WHERE empno < 100;

Set rows back to active state if needed for active use:

UPDATE emp
SET ora_archive_state = 0;

Setting Session Visibility

To view active rows only:

View all rows:

ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ALL;

To return to active rows only:

ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ACTIVE;

Disabling Row Archival

When row archival is disabled, the ora_archive_state column is automatically removed. To disable row archival: