Migrating to Oracle ASM using Oracle Enterprise Manager 10g Database Control

Author: Fairlie Rego, Senior Oracle Consultant, SingTel Optus Pty Ltd
Publication Date: July 2005

Oracle Automatic Storage Management (ASM), introduced in Oracle Database 10g Release 1, is a special-purpose cluster filesystem embedded into the Oracle kernel; it removes the need for a third-party volume manager and filesystem. Many more details are available, but suffice to say here that Oracle ASM stripes and mirrors data across disk and performs online disk configuration and dynamic rebalancing (among other things). Essentially, it solves many storage management challenges faced by a DBA.

In Oracle Database 10g Release 1, the Recovery Manager (RMAN) was required to convert an existing database to Oracle ASM. In Release 2, this process is greatly simplified with the introduction of an option in Oracle Enterprise Manager 10g Database Control (dbconsole on the command line) to do the same. In this Technical Note, I will describe how to use this approach to convert an existing database to Oracle ASM.

For demonstration purposes, assume that the existing database has the following layout on an ext3 filesystem.

SQL> select name from v$datafile;

NAME
------------------------------------------------------------
/u01/app/oracle/oradata/test/system01.dbf
/u01/app/oracle/oradata/test/undotbs01.dbf
/u01/app/oracle/oradata/test/sysaux01.dbf
/u01/app/oracle/oradata/test/users01.dbf

Under the Change Database Section, click on "Migrate to ASM."

Next, enter the password for the SYS user to connect to the ASM instance.

You will then choose the location of the existing files as well as a precreated ASM diskgroup to which the existing files will be added.

A job is created to perform the task. Now enter a suitable description for the same.

In the next screen, click on the "Tablespaces and Files to be Migrated" section...

and confirm which files will be migrated to ASM. If you're satisfied, click on "Submit."

If the submissiion was successful, you will receive a verification screen.

The job performs the following tasks:

• Shuts down the database
• Mounts the database
• Copies the files to the ASM disk group and renames them in the controlfile
• Drops the tempfiles
• Opens the database
• Adds the tempfiles in the ASM diskgroup
• Drops the logfiles and creates them in the ASM diskgroup with two members one at a time

Next, review the alert.log of the database and confirm that the files have been moved to ASM.

   
SQL>  select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+DATA_TEST/test/datafile/system.259.563293767
+DATA_TEST/test/datafile/undotbs1.260.563293767
+DATA_TEST/test/datafile/sysaux.262.563293767
+DATA_TEST/test/datafile/users.261.563293767

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
+DATA_TEST/test/controlfile/backup.256.563293701
+DATA_TEST/test/controlfile/backup.257.563293703

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
+DATA_TEST/test/onlinelog/group_1.268.563293929
+DATA_TEST/test/onlinelog/group_2.270.563293969
+DATA_TEST/test/onlinelog/group_4.266.563293915
+DATA_TEST/test/onlinelog/group_4.267.563293921
+DATA_TEST/test/onlinelog/group_1.269.563293935
+DATA_TEST/test/onlinelog/group_2.271.563293973

6 rows selected.