Monday 1 February 2010

Migrate your Oracle 10g database to ASM

This blog entry is about migrating your Oracle 10g database to ASM using OEM Database Control on ubuntu 9.10 Karmic Koala. To be able to do this you must have:
  • CSS service running on your ubuntu box
  • ASM instance running
  • OEM Database control running
To resolve your problems of starting the Oracle CSS daemon and ASM instance running on Ubuntu Karmic Koala, follow the resources I give below.

You will have to do some hocus-pocus with some parameters to adjust memory, improvise disks with dd (cause I only had one) and CSS script startup hick-ups. Insist with the resources excession.org.uk and pythian.com and oraclebase kindly provide and you will be running ASM on single instance on ubuntu 9.10 Karmic Koala in no time.

You should have the above three ready before you attempt to migrate your databases to ASM. You must go through the trouble and create an ASM instance with DBCA as per the resource below. No ASM instance no migration!

Once you are ready there only 4 steps to follow on the OEM and they are easy!

About ASM

For years DBAs and SAs have been looking over spindles and trying to decide where to put what. Which files shall I put on which disk? RAID this RAID that and so on...

What is the best way to tune I/O for Oracle?

The answer is, easily enough, Let Oracle tune I/O for you with their SAME (Stripe and Mirror Everything) approach. More, the mirroring and striping is done at the extent level and file level and not disk level.

That is exactly what ASM does, it balances I/O activity. It is an Oracle instance which runs alongside your database instances, in memory, and balances the I/O of database data files. It balances and tunes I/O whenever you add disks or you drop them, even online with no intrusion!

All your Oracle datafiles now are controlled by ASM and the way it does it is that ASM makes sure that a file is evenly spread across all disks when the file is allocated, so rebalancing is not required. 


All this sounds very good, if we don't care about I/O tuning what will happen to SQL Tuning? Are we going to do structural and logical tuning only?


Further, with emergence of "Oracle TimesTen In memory" database technology where the whole database can be loaded easily in solid state memory (Flash Disks), I wonder how long will ASM last?


Resources: 


http://www.excession.org.uk/blog/installing-oracle-on-ubuntu-karmic-64-bit.html

http://www.pythian.com/news/810/howto-set-up-oracle-asm-on-ubuntu-gutsy-gibbon/


http://www.oracle-base.com/articles/10g/AutomaticStorageManagement10g.php