This is a followup post to my earlier Post on how to install Apex and the ILM assistant with 11g.
I know that the images are kind of shoddy because they have been resized. If you are a firefox user and want to see the image , just right click on it and choose “view image”. IE users do whatever it is you do to view images.
Configure ILM Assistant
The Ilm assistant can be accessed by pointing the browser to
http://yourhostname:8080/apex/f?p=737677
Here the install guide says that you have to use the admin login.
This is incorrect (http://forums.oracle.com/forums/thread.jspa?threadID=623966&tstart=0)
Best is to create a separate database login to be used for the ILM assistant.
cd <directorywhereilmappwasunzipped>
sqlplus system/systempassword
create user ILM_DB_ADMIN identified by Admin
default tablespace users temporary tablespace temp
/
grant create session, advisor, alter any table,alter tablespace, drop any table,manage tablespace to ILM_DB_ADMIN
/
sqlplus sys/password as sysdba
@grant_privs ILM_DB_ADMIN
Now you can login to the ilm assistant using the username ILM_DB_ADMIN and password Admin.
If you have not install the ilma_demo do that now (The following example uses the tablespaces created by this script)
I also use the sales history sample schema in the following example.
Login as the SH schema and create a table sales 2 (create table sales2 as select * from sales). We will use this table in our example.
Delete all the rows in this table which has a time_id > 01-JUL-2008. (Delete from sales2 where time_id >= to_date(’01-JUL-2008′,’RR’))
Update all Time_Id’s in this table to increment them by 7 years (update sales2 set time_id = add_months(time_id,84))
Using the ILM Assistant
Here is a small example on how the ILM assistant can be used.
There are 3 main steps in configuring Information Lifecycle management for data stored in Oracle database tables.
1) Define Logical Storage Tiers
2) Define A Lifecycle and its stages
3) Associate a table to a Lifecycle
In the first step we are associating tablespaces created on different type of storage to a logical storage tier.
In the second step we provide the date range and other properties like compression and read-only to each stage in the lifecycle of the data.
In the thrid step we are associating lifecycles to each of the tables.
Then you can run simulations after which ILMA can provide you scripts to partition the tables
Defining Logical Storage Tiers
Login to ILMA as ILMA_DB_ADMIN
Click on “Logical Storage Tier”
 |
Create a storage tier “High Performance” with the properties shown above. Choose ILMDEMO_5 as the read-write tablespace for this.
 |
Create a second storage tier “Low cost” with the properties shown above. Choose ILMDEMO_9 as the read-write tablespace for this.
 |
Create a storage tier “Online Archive” with the properties shown above. Choose ILMDEMO_7 as the read-only tablespace for this.
Define the Lifecycle
 |
Create a new lifecycle “SH lifecycle” , Retaining details for 3 months, and using the “High Performance” storage
 |
Create a lifecycle stage “Sh Last 2 Qrtrs”, Retaining details for 6 months and using “Low Cost” storage
 |
Create a lifecycle End stage “Sh Older Data”, with the “Online Archive” storage tier.
Associate Lifecycles to Tables
Click on “Lifecycle Tables”
Show all tables
 |
Click on “Candidate” in the “Lifecycle status” column for the tables “SALES2”
 |
Choose “SH Lifecycle” as the lifecycle, Compression factor 1.2, Number of rows 800000000, average row length of 100 (Alternatively you can click on load table statistics so that oracle picks up this info from the table statistics). You can leave the date range as blank and oracle looks at the table and picks up the appropriate min and max values for the date range.
Once the simulation is complete you can click on “pReview Simulation” and see the results.
 |
You can click on “Create a migration script” and ILM will generate you a sql script which can be used to partition the table “SALES2”.
Click to View Partitioning Script
Test Lifecycle Management
Shutdown the database
Change the system clock to move it ahead by about 4 months.
Then log back into the Ilm assistant.
Click on the “Scan for events” button.
 |
The events are broken down into “Overdue Events”, “Todays Events” and “Upcoming Events”.
Since we moved the clock ahead there are a few “Overdue events”.
 |
Under the “Overdue Events” -> “Move data” click on “14 Managed” (Your screen might be different).
You have a few Events for the SALES2 table, some to merge partitions and some to move partitions.
 |
You can accept those changes and choose to “Implement accepted recommendations”.
ILMA will prompt you asking if you want a sql script or want to submit a job.
You can choose one of those options to implement your change.
Click to View Data Movement Script
So now you get the general Idea about how to use the ILM assistant