News
Perth, Australia
+ (61) 417713124
prosolutions@gavinsoorma.com

Oracle 12c new feature In-Database Archiving

  • Posted by Gavin Soorma
  • On August 5, 2013
  • 0 Comments

Very often in our databases we have some very large tables which have a lot of historical and legacy data and the challenge is deciding what is old data and what is current data and even if we do identify the old data we do not need and have moved that data to tape storage, what happens if that data is suddenly required. Getting that data back in the database can be a very expensive and time consuming exercise.

Keeping large volumes of (unnecessary at most times) historical data in the production OLTP database can not only increase the database footprint for backup and recovery but can also have an adverse impact on database performance.

The new 12c Information Life Cycle Management (ILM) feature called In-Database Archiving enables us to overcome the issues stated above 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.

When we enable row archival for a table, a hidden column called ORA_ARCHIVE_STATE column is added to the table and this column is automatically assigned a value of 0 to denote current data and we can decide what data in the table is to be considered as candidates for row archiving and they are assigned the value 1

Once the older and more current data is distinguished, we can archive and compress the older data to reduce the size of the database or move that older data to a cheaper storage tier to reduce cost of storing data.

Let us have a look at an example of using this Oracle 12c new feature called In-Database Archiving

SQL> select count(*) from sales;

  COUNT(*)
----------
    918843

SQL> alter table sales row archival;

Table altered.

SQL> select distinct ora_archive_state from sales;

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

 

Note – the column ORA_ARCHIVE_STATE is now added to the table SALES and is a hidden column.

We now want to designate all rows in the sales table which belong to the years 1998 and 1999 as old and historical data.

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

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

426779 rows updated.

 

If we now issue a select * from sales 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 sales;

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

SQL> select count(*) from sales;

  COUNT(*)
----------
    492064

 

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 sales;

  COUNT(*)
----------
    918843

SQL> select distinct ora_archive_state from sales;

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

 

So what we can do now is partition the sales table on the ORA_ARCHIVE_STATE column and we can then compress the partitions containing the archive data. The current data will be left in an uncompressed state as it is frequently accessed 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.

 

0 Comments

Leave Reply

Your email address will not be published. Required fields are marked *