datafile migration from filesystem to ASM

Introduction

Oracle has a standard procedure in the RMAN Backup and Recovery guide to migrate all the datafiles (full database) from a file system storage to ASM storage including redo logfiles too.  But sometimes a database may continue to have datafiles in the traditional file system storage as well as in ASM diskgroups.  The reason could be due to a lack of free space in the ASM disk groups and hence the need to store datafiles in filesytem.

This post explains how to clone or restore such a database (having datafiles both in a filesystem as well as ASM disks) into a destination host with datafiles only in ASM disks.

Steps

Let the filesystem location for the datafiles in source database be '/data/filesystem' with 10 datafiles in this directory and let the  destination ASM diskgroup be '+ASMDG' 

1)  On the source  database, prepare a simple select statement to generate output as below.

select 'set newname for datafile '||''''||file_name||''''||' to +'||''''||'ASMDG'||''''||';'
from dba_data_files
where file_name not like '%+%';

2) On the destination host, use the above generated output in the RMAN run block. For example, if the data to be restored is until Sep 06 2012, the below syntax can be used.

run
{
set until time "to_date('Sep 06 2012 08:00:00','Mon DD YYYY HH24:MI:SS')";
allocate channel d1 type disk;
allocate channel d2 type disk;
allocate channel d3 type disk;
allocate channel d4 type disk;
set newname for datafile '/data/filesystem/file1.dbf' to '+ASMDG';
set newname for datafile '/data/filesystem/file2.dbf' to '+ASMDG';
set newname for datafile '/data/filesystem/file3.dbf' to '+ASMDG';  
set newname for datafile '/data/filesystem/file4.dbf' to '+ASMDG';
set newname for datafile '/data/filesystem/file5.dbf' to '+ASMDG';
set newname for datafile '/data/filesystem/file6.dbf' to '+ASMDG'; 
set newname for datafile '/data/filesystem/file7.dbf' to '+ASMDG';
set newname for datafile '/data/filesystem/file8.dbf' to '+ASMDG';
set newname for datafile '/data/filesystem/file9.dbf' to '+ASMDG';  
set newname for datafile '/data/filesystem/file10.dbf' to '+ASMDG';
restore database;
SWITCH DATAFILE ALL;
}

Note 

The main goal of this post is to explain how to easily generate a "set name for datafile" command output from the source database and use the commands in the RMAN run block for the destination database.  Other RMAN commands for restore/cloning can be different based upon the actual situation.