11g Enhanced Information Lifecycle Management (ILM)

The concept of Information Lifecycle Management has been around for many decades.  In a nutshell, ILM is the intelligent archiving of historical Oracle data on cheaper tertiary media, like inexpensive disks, optimal jukeboxes and magnetic tape.

Figure 1:  ILM - It's all about the cost of storage

In a perfect world, with negligible-cost fast storage, ILM would be unnecessary because offloading and storing historical data would not be driven by economics.  However, even though disk and SSD is now cheaper than ever before, archiving historical data remains problematic, especially within the realm of Oracle auditing for regulatory compliance.

Government regulations such as SOX, GLB and HIPAA require auditing of updates (DML) and disclosure (SQL selects).  According to the law, organizations must provide complete audit trails for all DDL (i.e., schema changes), DML (e.g., updates, insert, deletes), and select confidential patient information.

Figure 2:  Time-Series Audit Chart

As highly active data ages out and becomes low-activity, ILM allows the DBA to easily move it away from the high-cost storage onto cheaper media. 

The basic idea of ILM is to develop a hierarchy of storage based on storage cost and data activity.  This being, ILM:

• Leaves highly active current data on high-speed solid-state disk (e.g. SSD which can be 300x faster than platter disks)

• Archives low-activity historical data to cheaper platter disk and tertiary media (tape)

Oracle introduced an ILM assistant in Oracle 9i, an Apex (HTML-DB) application, to offload and archive large volumes of historical database information.  Oracle notes that as long as Oracle Application Express is used in Databases 9i and up, Apex is useful because it:

• Manages ILM environments via a GUI interface

• Defines lifecycle definitions

• Manages security and compliance

• Advises when data needs to be moved, generates scripts

The Oracle 11g ILM Assistant

In Oracle 11g, ILM has been enhanced to incorporate Oracle partitioning to allow easy data movement at the partition level. This includes the all-important exchange partition syntax. As highly-active data ages out and becomes low-activity, ILM allows the DBA to easily move it away from high-cost storage onto cheaper media.  Oracle says that the basic ILM steps include:

• Defining the data classes

• Creating storage tiers for the data classes

• Creating data access and migration policies

• Defining and enforcing compliance policies

To allow for easy data movement within ILM, Oracle leverages their Automatic Storage Management (ASM) and partitioning features.  These features are offered within an 11g ILM assistant, a GUI designed to remove the tedium from managing many layers of storage.  Oracle 11g introduces these new additions within ILM:

• Oracle advanced data compression for tables, LOBs and partitions

• "Interval", "Ref" and "virtual column" partitioning methods

• 11g partitioning methods (list-list, list-range, list-hash and range-range)

In summary, ILM is more of a collection and integration of existing tools, such as ASM and partitioning, than a separate product.  Furthermore, the Apex front-end ILM assistant provides an easy interface to the management of multi-tiered data.