|
|
Inside Oracle 11g ILM
Oracle Database Tips by Donald Burleson
|
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 disk,
optimal jukeboxes and magnetic tape.
ILM - It's all
about the cost of storage
In a perfect world with
negligible-cost fast storage, ILM would be unnecessary, as
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 audits of confidential patient information.
The basic idea of ILM is to
develop a hierarchy of storage, based on storage cost and data
activity:
-
Leave highly active "current" data on
high-speed solid-state disk (e.g.
SSD which can be 300x faster than platter disks)
-
Archive low-activity historical data to
cheaper platter disk and tertiary media (tape).
Oracle ILM Assistant in Oracle9i
Oracle introduced an ILM
assistant in Oracle 9i, an Apex (HTML-DB) application to assist in the
offloading and archiving of large volumes of historical database information.
Oracle notes:
-
Manages your ILM
environment via a GUI interface
-
Define lifecycle
definitions
-
Manage security &
compliance
-
Advises when data needs
to be moved, generates scripts
-
Requires Oracle
Application Express
-
Supports Oracle Database
9i and up
-
Tool downloaded from OTN
(available now!)
The Oracle 11g ILM assistant
In Oracle 11g, ILM has been
enhanced to incorporate Oracle partitioning (and the all-important "exchange
partition" syntax), to allow easy data movement at the partition level. 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.
Oracle says that the basic ILM steps include:
-
Define the Data Classes
-
Create Storage Tiers for
the Data Classes
-
Create Data Access and
Migration Policies
-
Define and Enforce
Compliance Policies
To allow for easy data
movement within ILM, Oracle leverages their Automatic Storage Management (ASM)
and partitioning features, all within a 11g ILM assistant, a GUI designed to
remove the tedium from managing many "layers" of storage. Oracle 11g
introduces there features within ILM:
-
New Oracle advanced data
compression for tables, LOB's and partitions
-
New "Interval", "Ref" and
"virtual column" partitioning methods
-
New 11g partitioning
methods (list-list, list-range, list-hash and range-range)
In sum, ILM is more of a
collection and integration of existing tools (ASM, partitioning) than a separate
product, and the Apex front-end (the ILM assistant) provides an easy
interface to the management of multi-tiered data.