Oracle 12c - In Database Archiving

In-Database Archiving enables us to archive rows within a table/database by marking them as inactive/disable (mark for delete).

By enabling the database to distinguish from active data and ‘older’ in-active data while at the same time storing everything in the same database.

These inactive/disabled rows are in the database and can be optimized using compression, but are not visible to an application. The data in these rows is available for compliance purposes if needed by setting a session parameter.

Below example of using In-Database Archiving

SQL> select count(*) from Audit;

  COUNT(*)
----------
    80001

SQL> alter table Audit row archival;

Table altered.

SQL> select distinct ora_archive_state from Audit;

ORA_ARCHIVE_STATE
--------------------------------------------------------------------------------
0

Please Note – New column ORA_ARCHIVE_STATE is now added to the table Audit and is a hidden column.

We now want to designate all rows in the Audit table which belong to the years 2001 and 2002 as old and historical data.

All data after 01-JAN-2003 should be treated as current and active data.

SQL> update Audit
  2  set ORA_ARCHIVE_STATE=DBMS_ILM.ARCHIVESTATENAME(1)
  3  where time_id < '01-JAN-03';

40000 rows updated.

SQL> Commit;

If we now issue a select * from Audit command, we see that only about half the actual number of rows are being returned by the query as Oracle is not returning the rows where the value is 1 for the column ORA_ARCHIVE_STATE

SQL> select distinct ora_archive_state from Audit;

ORA_ARCHIVE_STATE
--------------------------------------------------
0

SQL> select count(*) from Audit;

  COUNT(*)
----------
    40001

Now let as assume there is a requirement to view the historical and inactive data as well.
At the session level we can set the value for the parameter ROW ARCHIVAL VISIBILITY to ALL

SQL> alter session set row archival visibility=ALL;

Session altered.

SQL> select count(*) from Audit;

  COUNT(*)
----------
    80001

SQL> select distinct ora_archive_state from Audit;

ORA_ARCHIVE_STATE
--------------------------------------------------------------------------------
1
0

Partition the Audit table on the ORA_ARCHIVE_STATE column is a good option, so that we can then compress the partitions containing the archive(old) data. 
The current/Active data will be left in an uncompressed state as it is frequently accessed by application and we do not want to impact performance.


We can also make those partitions containing the older data read only and exclude them from our regular daily database backups.