ILMA Installation Guide

ILMA Users Guide

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

Automatic shared memory management and Automatic PGA memory management were features in 10g that enabled users to automate the memory allocation for various sub areas in the SGA and PGA.

Oracle introduced Automatic Memory Management in 11g. This now enables to manage total memory allocated to oracle (SGA+PGA) in an automated fashion. You allocate the total memory allocated to Oracle and the Oracle database dynamically exchanges memory between the SGA and the instance PGA as needed (Based on the workload). Oracle also tunes the sizes of individual SGA components and of the individual PGA’s.

Automatic memory management is supported on Linux,Solaris, Windows, HP-UX and AIX.

You can set memory_target to a value that will be a sum of the memory that you want to allocate fot the SGA and the PGA.
You can set memory_max_target to a value higher than memory_target so that you can increase the memory allocated for oracle if you desire.

You should then set the sga_target and pga_aggregate_target to 0, so that Automatic memory management can tune the space allocation,up or down, for PGA and SGA without any restrictions.

If you set a value for sga_target and pga_aggregate_target then they will be considered as minimum values for the SGA and PGA sizes. If you set a value for sga_target and not for pga_aggregate_target you will auto-tune both parameters. Pga_Aggregate_target is automatically initialized to be memory_target – sga_target.

If neither are set then they are auto tuned by allocating 60% to the SGA and 40% to the PGA.

I just installed the oracle Information lifecycle management assistant so that I could test it with 11g.

You first need to install Oracle Application Express (Apex) and then the Oracle ILM assistant.

When you create a database in 11g you can choose to install application express. In the process below, we skip that and install it manually after the database creation.
You have two options to use application express.

• Use the oracle http server with mod_plsql
• Use the Embedded Pl/Sql gateway. (From the 11g database)

In this example we use the Embedded Pl/Sql gateway. (This is an http server built into the oracle database)

Create a general purpose database with the 11g Dbca. (We assume that a database listener has been already created and is started up).

Download the apex 3.1.1 software from http://www.oracle.com/technology/products/database/application_express/download.html

Install Oracle application express 3.1.1

cd /apex/
sqlplus / as sysdba
@apex_epg_config
alter user anonymous account unlock;

Setup The port for the Embedded pl/sql gateway

Check if the port is already setup

SQL> SELECT DBMS_XDB.GETHTTPPORT FROM DUAL;

GETHTTPPORT
———–
0

So xmlhttp is disabled.

Set the port for the http listener

SQL> exec dbms_xdb.sethttpport(8080);

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> SELECT DBMS_XDB.GETHTTPPORT FROM DUAL;

GETHTTPPORT
———–
8080

Install oracle application express

sqlplus / as sysdba
SQL> @apexins users users TEMP /i/

Set the password for the apex user “Admin”

@apxxepwd.sql admin

You can now login to application express as the admin user by accessing “use http://yourhostname:8080/apex/apex_admin” and start using Oracle Application Express.

Install the Oracle ILM assistant

Download the ilm assistant from http://www.oracle.com/technology/software/deploy/ilm/index.html
Download the ilm demo from http://www.oracle.com/technology/obe/11gr1_db/manage/ilm/files/ilm.zip

sqlplus / as sysdba
SQL> alter user flows_030100 identified by flows_030100 account unlock;
SQL> @ilma_install flows_030100 users RK01
SQL> alter user flows_030100 identified by flows_030100 account lock;

You can now access Oracle ILM assistant using the following URL.

http://yourhostname:8080/apex/f?p=737677

If you want to install the ILM demos then
first create the sh schema using sh_main from $ORACLE_HOME/demo/schemas/sales_history
cd to the directory where you unzipped the files from ilma_demo_012.zip
Then

sqlplus / as sysdba
@ilm_data

You can then try out the examples at http://www.oracle.com/technology/obe/11gr1_db/manage/ilm/ilm.htm