Re: Oracle 11g, ASM & ILM

From: Tim Gorman <tim_at_evdbt.com>
Date: Tue, 28 Sep 2010 16:55:49 -0600
Message-ID: <4CA27275.4070609_at_evdbt.com>

Don,

The "ILM Assistant" plug-in for APEX 3.2+ (installation guide here, users guide here, downloadable here) is supposed to allow you to designate different ASM diskgroups as the different "logical storage tiers".  I have never used ILMA (nor APEX for that matter), so I'm not sure, but you might want to look at it?

Otherwise, I've done a lot of roll-yer-own ILM and the way we've implemented it is by designating different ASM diskgroups as different tiers using a naming convention.  For example, a diskgroup named "+DWP_D1001" is the first diskgroup of tier-1 storage (i.e. "D1001") for database "DWP".  Additional tier1 diskgroups might be "+DWP_D1002", "+DWP_D1003", added as needed.  Disk groups named "+DWP_D2001" would be the first diskgroup of tier-2 storage, then "+DWP_D3001" would be the first diskgroup of tier-3 storage.  And so on.  The nice thing is that such a naming convention can look a lot like a file-system naming convention (i.e. "/dwp/d1001", etc).

The big issue here is -- who does the actual migrating or movement of data?  Both the ILMA (which generates SQL*Plus scripts to perform migrations) mentioned above and the home-rolled ILM efforts I've been involved with for the past several 10+ years have the RDBMS (via Perl or PL/SQL or whatnot) performing the actual migration from one tier of storage to the other, so the diskgroups can be named and organized as described above, and then tablespaces with time-based naming created or moved within them.

I'm working on a new project where the storage array wants to "auto-magically" perform the migration when activity levels to the LUNs fall below a specified level, so the ASM diskgroup naming needs to change to something more similar to tablespace naming.  Whereas we've long been accustomed to naming tablespaces by time period (i.e. "DATA_201011" for Nov2010 data, etc), when the storage array is performing the migration you need to create the ASM diskgroups from a single LUN and then name (and use!) the disk group like it was a tablespace (i.e. +DWP_DATA_201011 for Nov2010 data).  The problem here is that Oracle permits many more tablespaces than diskgroups -- the maximum for 11gR2 ASM is 63 disk groups, so the number is *really* low -- be *very* careful if you decide to use storage-level automated migration under ASM, whatever naming convention you choose.  Personally, I don't like this option one bit...

Hope this helps...

Tim Gorman
consultant -> Evergreen Database Technologies, Inc.
postal     => P.O. Box 630791, Highlands Ranch CO  80163-0791
website    => http://www.EvDBT.com/
email      => Tim_at_EvDBT.com
mobile     => +1-303-885-4526
fax        => +1-303-484-3608
Lost Data? => http://www.ora600.be/ for info about DUDE...

On 9/28/2010 1:27 PM, Don Granaman wrote:

I've just started looking at the possibilities for hierarchical storage management in Oracle 11.2 (RAC) with ASM.  Chapter 5 of the Oracle Database VLDB and Partitioning Guide seems to be the only useful technical discussion of it I can find.  That chapter talks about it, but rather vaguely.  For example:

 

REM These tablespaces would be placed on a Low Cost Tier

CREATE SMALLFILE TABLESPACE "2006_ORDERS" DATAFILE '2006_orders'

SIZE 5M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED LOGGING

EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;

 

So... how is this assigned to a low cost tier - in ASM?  

 

There seems to be no indication of how this datafile is created on the “Low Cost Tier”.  The most obvious would seem to be to create distinct ASM diskgroups for the distinct storage tiers and then (manually) create tablespaces/datafiles in appropriate diskgroups.  I was expecting to find something in the ASM guide (or elsewhere) that elaborates on this, but didn’t.

 

Does anyone know of a better reference, whitepaper, etc.?

 

Thanks!

Don Granaman

 

-- http://www.freelists.org/webpage/oracle-l Received on Tue Sep 28 2010 - 17:55:49 CDT