Oracle Information Lifecycle Management Setup With OEM

Introduction

Organizations consider data stores as one of the most valuable assets they own. From another angle, storing data for longer periods of time, costs organizations dearly. This is a mandate by corporate compliance rules and regulations. For example, some compliance rules mandate to store data more than 30 years! Failing to prove compliance may result in heavy fines paid.
Hence, IT managers have to satisfy both requirements intelligently. Oracle Information Lifecycle Management (OILM), is a feature of Advanced Compression Option that is available since Oracle Database 11g.

Oracle ILM offers the following:

Application Transparency

Application transparency is very important in ILM because it means that there is no need to customize applications and it also enables various changes to be made to the data without any effect on the applications that are using that data. Data can easily be moved at the different stages of its lifecycle and access to the data can be optimized with the database. Another important benefit is that application transparency offers the flexibility required to quickly adapt to any new regulatory requirements, again without any impact on the existing applications.

Fine-Grained Data

Oracle can view data at a fine-grained level and group related data.

Low-Cost Storage Management

With so much data to retain, using a low cost storage is a key factor for implementing ILM. Because Oracle can take advantage of many types of storage devices: the maximum amount of data can be held for the lowest possible cost.

Within Oracle Database, it is possible to define security and audit policies, which enforces access data methods as well as auditing all activities.

The Oracle Database Advantage

It is a well-know fact that Oracle Database is designed to store all types of data: Relational, Documents and Images (through SecureFiles type), Spatial (through SDO type), Vector (through Vector type), JSON (through JSON type), … This means that when organizations store all of their data in an Oracle Database, then the ILM policy implemented is 100% applicable on all the data owned and there’s no need to consider anything else in this regard.

The following section shows the various capabilities of Oracle Database that participate a strong ILM strategy.

Advanced (OLTP) Compression Option

Advanced Compression, also known as OLTP compression, is the least CPU intensive compression method provided by Oracle Database. It is designed to work with OLTP applications. An important fact about ACO is that it works with encrytped data in an Oracle Database. Advanced Compression comes with a lot of other features. Among these features that apply to ILM, is Automatic Data Optimization. ADO is described below.

The Heat Map

At the heart of it, the Heat Map is the feature that enables automated ILM policy on an Oracle Database. It works by collecting stats about database usage, hence it becomes possible to take actions based on data age and usage. The Heat Map is part of Advanced Compression Option.

Automatic Data Optimization

A set of PL/SQL Oracle Database packages that facilitate configuring ILM policies. ADO is part of Advanced Compression Option.

Partitioning

Since Oracle 8.0, Oracle Database included Partitioning as a means of storing data in smaller segments. Although these segments are physically stored separately, but the Oracle Database Optimizer is aware about the physical locations of these partitions. With partioning in-place, queries can achieve thousands of times performance gain. Also, Partitioning aid in table availability. This is because when a partition is inaccessible due to any unanticipated reason, the rest of the partitions will remain accessible. Finally when backing-up and restoring, only the partitions in-question are addressed, hence slashing the backup/recovery time. 
Partitioning plays a major and significant role when considering an ILM strategy. This is because database partitions can be managed without downtime.

Hybrid Columnar Compression

Another database feature that plays an important role in the ILM strategy is Hybrid Columnar Compression (HCC). HCC is another database compression technique that has been available since Oracle 10.0. It is available on the following hardware:

• Exadata Database Machine
• Oracle Database Appliance
• ZFS Storage Appliance – or any storage running ZFS Filesytem.

Planning for ILM

When considering an ILM Policy, keep in mind the relationship between data activity and data age:

From the above chart, as data gets older, it becomes less active. Hence older data volume gets larger as it is aged.

To achieve a good ILM strategy, please consider the following:

• What data is important, where is it stored, and what must be retained?
• How this data flows within the organization?
• What happens to this data over time and whether it is still required?
• The degree of data availability and protection that is needed?
• Data retention for legal and business requirements?

Based on the above, you can consider the following storage tiers:

Here we assume that the data age is governed by a date column in the table.
Please note that all of the below storage tiers are optional. You may select any or all of them according to the need.

Note: Although database compression offers a better query performance, but each compression method has its own CPU utilization. Row Store compression (OLTP compression) consumes the least CPU. While HCC consumes higher CPU depending on which level is used.

Note: Compression Level varies according to the data stored in tables. The figures indicate the maximum compression estimate associated with the compression method.

Configuring ILM

In this blog, we are considering 2 configuration methods: the first is using Oracle Enterprise Manager (OEM), and the other using CLI. Each method is included in each section.

Enabling the Heat Map

The first step is to let the database populate data usage statistics. This is achieved by enabling the following init.ora parameter:

Now the database will consider collecting data statistics.
However, we need to enforce this on the tables in question using OEM:

The alternate DDL follows:

Begin
    DBMS_STATS.GATHER_TABLE_STATS
    (
        OwnName => 'PART',
        TabName => 'ILM_DEMO',
        Granularity => 'ALL'
     );
End;
/

Accessing Information Lifecycle Management Homepage

It is important to confirm if the Heat Map gathers the needed statistics for the table(s) in question.
From OEM, you can access the ILM Homepage from the database homepage -> Administration -> Storage -> Information Lifecycle Management
There are 2 tabs over there: Heat Map page & Policy page.

Heatmap Homepage

From this page, you have a full view about the database Heat Map:

By default, the heat map diagram on OEM shows the tablespace heat map. However, if a certain tablespace is clicked, then the underlying tables / partitions are displayed.
From the above page, it is possible to view by Last Access Date, Last Lookup Scan Date, Last Write Date, Last Full Table Scan Date.

Alternatively, SQL retrieves all Heat Map information:

Below is a sample output from SQL*Developer:

Any of the columns: Written Since (Days), Read Since (Days), Last Full Scan (Days), Created Since (Days) are > 0, then the database Heat Map is collecting the stats.
Once the tables / partitions statistics are confirmed to be collected, then we can proceed with the next steps.

Policy Page

Below is the Policy Page screenshot:

Before setting ILM Policies, we must enable Row Movement for the segment(s) in question. Enabling row movement is essential for ILM because we will be compressing and moving data around. From OEM:

Alternatively, by SQL:

ILM Policies have a common default values that affect the way they work. The values can be viewed / updated by clicking on Default Execution Settings on the ILM homepage:

By clicking on any parameter value, you get a quick help.

You also get the above result using SQL:

SQL> Select * From DBA_ILMParameters;

And below is a sample output:

To check the meaning of each parameter, please click here.

ILM Policies are defined on the Tablespace, Table or Partition level, depedning on which level we are intersted in applying the policy on.

To access the ILM Policy screen, click on the following:

• Tablespace: Administration -> Storage -> Tablespaces -> Click on the TS in question -> Edit -> Automatic Data Optimiziation
• Table: Schema -> Database Objects -> Tables -> select the schema / table in question -> Edit -> Automatic Data Optimiziation
• Partition -> Schema -> Database Objects -> Tables -> select the schema / table in question -> Edit -> Partition -> Advanced Options -> Automatic Data Optimization

The below screenshot is retrieved by any of the above methods:

Note: If an ILM Policy is applied on a superset, then it will automatically be inherited by the subset(s). For example, applying the policy on a Tablespace Level, it will be inherited  on all segments stored in this tablespace. And applying the policy on the table level, it will be inherited on all underlying partitions / sub partitions. It is therefore recommended to apply policies on the superset as much as possible to facilitate the whole process. Throughout this blog, we are setting an ILM Policy on a table level. 

To add a new policy, click on Add button:

ILM Scope

Scope can be Segment, Group or Row.
When Row is selected as the ILM scope, only OLTP Compression is applicable, and no Storage Tiering applies then.
Group instructs the database to migrate the table and its dependent objects, such as indexes and SecureFiles LOBs. When Group is selected, all compression methods are applicable and Storage Tiering is also applicable. However, Set In-Memory does not apply then.

ILM Types

There are 3 ILM Policy types that can be deployed on database segments. Once an ILM Policy is in-place, it will be automatically executed and perform the function assigned. The initiation time will be according to the Condition selected. Below are the available types with Oracle ILM:

• Compression: compress segments with any selected compression method.
• Storage Tiering: moves segments to a different tablespace.
• In-Memory: Set In-Memory, Modify In-Memory and No In-Memory can be used.

Note: In-Memory means that the segment is going to be populated, modified, or evicted from the In-Memory Columnar Store.

Compression ILM Type

Compression will compress the segment in question according to Compression Level: with any of the following values: All Operations, OLTP, Query Low, Query High, Archive Low or Archive High.
     Note: All Operations compression level is a synonym for OLTP / Advanced Compression. It is kept for backward compatibility.

Condition: the condition that the policy will be initiated. This can be either of the 2 conditions below:

1. Tracking Statistics (via the Heat Map) which will be used to decide when to execute the policy, After: (Days / Months / Years), of Activity of: No Access, No Modification or Creation.
2. Custom Function: a stored PL/SQL function that returns Boolean for the ILM Policy to be executed.

Storage Tiering ILM Type

Moves the segment in question to a specified Tablespace. The intention is to move to a tablespace with its datafiles sitting on a lower cost storage.
You can set Tablespace is marked Read Only after the object is moved to automatically make the tablespace Read Only.

This ILM type is initiated by any of the following conditions:

1. Tracking Statistics: see above. 
   Automatic tablespace fullness detection will also be considered by ILM in this case. 
2. There are 2 ILM parameters that govern the amount of tablespace fullness: TBS PERCENT USED and TBS PERCENT USED.
   See the Default Execution Settings above for details.
3. Custom Function: a stored PL/SQL function that returns Boolean for the ILM Policy to be executed.

Note: If you want the Tiering Policy to be time-related, then marking the tablespace to be read-only is a must.

Set In-Memory ILM Type

This type will automatically the segment in-question to the In-Memory Columnar Store.

The same conditions that govern the previous 2 ILM types, also work here. The exception is the following:

• Priority: which is the priority of loading the segment into the Columnar Store (None, Low, Medium, High, Critical).
• Compression: In-Memory Compression (No Compression, Query Low, Query High, Capacity Low, Capacity High).

Note: After setting or deleting a new Policy from the above page, you must click on Ok, and on the following page, click on Apply button:

The below screenshot is for a table that has ILM Policies set in-place:

The above example applies what is shown in the table ILM Tiers above. 

Below are the equivalent DDL statements:

Usage

Once the ILM Policies are in place, they’ll get initiated when the Condition Days are met. However, you can always experiment to confirm if the Policies are going to do what they’re expected to do and don’t have to wait this much time to confirm.
This can be done from OEM ILM homepage, Policy page by clicking on the Evaluate button:

Notice the Completed counter has incremented by 1. Click on the number to get the following pop-up screen:

From this screen, select the latest Task ID – or the Task ID that you know is related to your evaluation, and click on Evaluation Details:

Note the following:

• Although the ILM Policies are created on the table level, but they take effect on each partition.
  This saves a lot of work as the policies are inherited by each partition in that table.
• The column Selected for Execution shows the state of the policy on each partition.
  In this case, it is PRECONDITION NOT SATISFIED.
  This is normal, because we did not meet the execution conditions yet.
• If the policy is due for execution, the state would be SELECTED FOR EXECUTION, and the policy will be executed on-time automatically.

Only You can also force the policy to be executed by clicking the Execute Policy button:

Here we see that the table ROPT2 has its ILM Policy SELECTED FOR EXECUTION. Which means that it is due for execution and we can force it to execute as shown in the screenshot below:

After that, pressing the Execution History button, shows the ILM Job status:

Any ILM policy after execution it automatically becomes Disabled:

You may as well use the following SQL statement to execute ILM policies on a given table:

References

• VLDB and Partitioning Guide
• Automatic Data Optimization