In Database Archiving

• In Database Archiving is a new feature of Oracle 12c meant to solve management of historical data inside the database.
• Like its name says, this functionnality leaves data into the database, so it remains accessible if you need it.
• With In Database Archiving, historical data remains in the database but is invisible to the application.
• Hybrid Columnar Compression (HCC) is used to compress the historical data into the database.

Temporal Validity

• Temporal Validity works  in concomitence with In Database Archiving
• It adds the “valid time” dimension to the data by adding columns to table indicating if the row is still valid or can be archived.
• This helps performance by reducing the quantity of data accessible to the application.
• The Valid-Time of data is defined at table creation :

SQL> CREATE TABLE ORDER_HISTORY (ORDER_ID NUMBER, ORDER_DATE DATE, COMMENTS VARCHARC2(100), USER_TIME_START DATE, USER_TIME_END DATE, PERIOD FOR user_time (USER_TIME_START , USER_TIME_END));

•  When you insert a row in the table you must insert the validity period too :

INSERT INTO ORDER_HISTORY (ORDER_ID , ORDER_DATE, COMMENTS, USER_TIME_START, USER_TIME_END) VALUES (1,SYSDATE,'ORDER 1',SYSDATE, SYSDATE+30);

 Hybrid Columnar Compression

• Oracle Hybrid Columnar Compression is made for In Database Archiving, it helps to reduce the storage size for inactive/historical data
• It offers 15x to 50x compression and is currently supported by :
  • Exadata
  • ZFS
  • Pillar Axiom 600
• It supports 2 types of compression, Warehouse Compression and Archive Compression.
  • Warehouse Compression can compress data up to 10x, it will improve query by reducing the size of the data queried, it is meant for active data
  • Archive Compression can achieve a 15x to 50x compression ratio. It uses a column based compression instead of a row based compression suppressing duplicates inside the same column, this is meant for historical data.

Enable/Disable In Database Archiving

• In Database Archiving is enable at table creation by specifying the ROW ARCHIVAL keywords:

SQL> CREATE TABLE ORDER_HISTORY (ORDER_ID NUMBER, ORDER_DATE DATE, COMMENTS VARCHARC2(100)) ROW ARCHIVAL;

• To disable In Database Archiving you can use

SQL> ALTER TABLE ORDER_HISTORY NO ROW ARCHIVAL;

•  When you activate In Database Archiving on a table, the hidden column ORA_ARCHIVE_STATE is added to the table.
• The ORA_ARCHIVE_STATE is not visible to users but you can see it by specifying explicitly the column in the SELECT clause.
• This Column can either contain 1 or 0, 0 for active and 1 for inactive. When you insert new rows to a table, they are by default active until they are marked as inactive.
• The row inactivity is managed by the temporal validity, when a row is rarely updated or accessed it is marked as inactive and then archived.

Manually archive or unarchive rows

• You can manually update the ORA_ARCHIVE_STATE column to define which rows are active and which rows are inactive:

SQL> UPDATE ORDER_HISTORY SET ORA_ARCHIVE_STATE = DBMS_ILM.ARCHIVESTATENAME(1) WHERE ORDER_DATE > SYSDATE - INTERVAL '30' DAY;

•  By default when you query a table where the In Database Archiving feature is enable, Oracle returns only the active rows, if you want to see all rows then you have to activate it explicitly in your session :

ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ALL;