How to migrate Oracle datafiles to ASM storage via RMAN

$ sqlplus / as sysdba

SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;
ALTER DATABASE DISABLE BLOCK CHANGE TRACKING
*
ERROR at line 1:
ORA-19759: block change tracking is not enabled

/*
   If you are using block change tracking, then disable it.
   In my case I am not using it.
*/

SQL> alter system set db_create_file_dest='+DATA' scope=spfile;

System altered.

/*
  This parameter define the default location for data files,
  control_files etc, if no location for these files is specified
  at the time of their creation. +DATA is the diskgroup in ASM.
*/

SQL> alter system set db_create_online_log_dest_1='+DATA' scope=spfile;

System altered.

/*
  This parameter defines the default location number 1 for online logs.
*/

SQL> alter system reset control_files scope=spfile sid='*';

System altered.

/*
   Here we remove the control_files parameter from spfile.
   So next time we restore the control file it will automatically go
   to +DATA diskgroup since it is defined in db_create_file_dest,
   and the new path will be automatically updated in spfile.
*/

SQL> exit

Once all initialization parameters are set properly, start RMAN to migrate the files to ASM diskgroups.

$ rman target /

RMAN> startup nomount

Oracle instance started

Total System Global Area     608174080 bytes

Fixed Size                     1220820 bytes
Variable Size                171970348 bytes
Database Buffers             427819008 bytes
Redo Buffers                   7163904 bytes

RMAN> restore controlfile from
      '/u01/apps/oracle/oradata/ora10g/control01.ctl';

output trimmed......

channel ORA_DISK_1: copied control file copy
output filename=+DATA/ora10g/controlfile/backup.256.716296513
Finished restore at 28-AUG-09

/*
   Control file restored to +DATA from its old location.
   It will be restored to +DATA because we set db_create_file_dest to
   +DATA.
*/

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> backup as copy database format '+DATA';

.......
input datafile fno=00001 name=/u01/apps/oracle/oradata/ora10g
/system01.dbf
output filename=+DATA/ora10g/datafile/system.257.716296627
 tag=TAG20090828T111707 recid=2 stamp=716296715
.......
input datafile fno=00003 name=/u01/apps/oracle/oradata/ora10g
/sysaux01.dbf
output filename=+DATA/ora10g/datafile/sysaux.258.716296723
 tag=TAG20090828T111707 recid=3 stamp=716296785
.......
input datafile fno=00005 name=/u01/apps/oracle/oradata/ora10g
/example01.dbf
output filename=+DATA/ora10g/datafile/example.259.716296789
 tag=TAG20090828T111707 recid=4 stamp=716296809
.......
input datafile fno=00002 name=/u01/apps/oracle/oradata/ora10g
/undotbs01.dbf
output filename=+DATA/ora10g/datafile/undotbs1.260.716296815
 tag=TAG20090828T111707 recid=5 stamp=716296822
.......
input datafile fno=00004 name=/u01/apps/oracle/oradata/ora10g
/users01.dbf
output filename=+DATA/ora10g/datafile/users.261.716296823
tag=TAG20090828T111707 recid=6 stamp=716296823
.......
Finished backup at 28-AUG-09

/* make a copy of all datafiles on +DATA diskgroup. */

RMAN> switch database to copy;

datafile 1 switched to datafile copy "+DATA/ora10g/datafile
/system.257.716296627"
datafile 2 switched to datafile copy "+DATA/ora10g/datafile
/undotbs1.260.716296815"
datafile 3 switched to datafile copy "+DATA/ora10g/datafile
/sysaux.258.716296723"
datafile 4 switched to datafile copy "+DATA/ora10g/datafile
/users.261.716296823"
datafile 5 switched to datafile copy "+DATA/ora10g/datafile
/example.259.716296789"

/*
   Switch database to the datafile copies. After this switch the
copies on +DATA
   have become the database datafiles and the original files have
 become copies.
*/

RMAN> alter database open;

database opened

RMAN> exit


Recovery Manager complete.

After you open the database, go ahead and enable the block change tracking file if you disabled it in the start of this process.

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;

Lets now verify where our control file and data files are sitting:

$ sqlplus / as sysdba

SQL> select name from v$datafile;

NAME
-------------------------------------------------
+DATA/ora10g/datafile/system.257.716296627
+DATA/ora10g/datafile/undotbs1.260.716296815
+DATA/ora10g/datafile/sysaux.258.716296723
+DATA/ora10g/datafile/users.261.716296823
+DATA/ora10g/datafile/example.259.716296789

SQL> show parameter control_files

NAME                TYPE        VALUE
------------------- ----------- ------------------------------
control_files       string      +DATA/ora10g/controlfile/backu
                                p.256.716296513
SQL>

They are on the +DATA diskgroup in ASM storage.

Migrate tempfile to ASM storage:

SQL> select file_name from dba_temp_files;

FILE_NAME
----------------------------------------------
/u01/app/oracle/oradata/ora10g/temp01.dbf

SQL> alter tablespace temp add tempfile size 20m;

Tablespace altered.

SQL> select file_name from dba_temp_files;

FILE_NAME
----------------------------------------------
/u01/app/oracle/oradata/ora10g/temp01.dbf
+DATA/ora10g/tempfile/temp.264.718706509

SQL> alter tablespace temp drop tempfile
  2  '/u01/app/oracle/oradata/ora10g/temp01.dbf';

Tablespace altered.

SQL> select file_name from dba_temp_files;

FILE_NAME
----------------------------------------------
+DATA/ora10g/tempfile/temp.264.718706509

SQL>