HOW TO MOVE DATAFILES TO ASM

Let's say you have datafiles on filesystem ; created ASM; and want to move these files to ASM storage , or you had a RAC and added datafiles on node1 local disks, but node2,node3..nodeX cannot access these newly created datafiles just like my customer has just did. Here is a solution using RMAN and copy datafile methods.

NOTE : some sqlstatement using double pipe " " are not visible in this blog(step 1, step2 ,step 4 step 6 ) ..!! I dont know why but pay attention ,the sql statement will fail if you run directly without adding double pipes.!!

UPDATE: Please see my other post "Recover Copy of Database" if the downtime or offline period is not in acceptable limits.

PLAN
1-determine the tablespaces which have datafiles on filesystem,take them offline,
2-determine datafiles which will be copied to ASM ; and backup using RMAN
3-Verifiy that all copy datafiles are avaliable.
4-Switch datafiles to copies.
5-Verifiy that switch operation is succesfull,
6- Online tablespace, which were taken offline in step1
7-Check datafiles , open other instances if using RAC and backup database.


1--
determine which tablespaces should be offlined ; containing datafiles from "/oradata" mount point.and offline tablespaces.
select TS# from v$datafile where NAME like '/oradata%' group by ts# order by ts#;
select 'alter tablespace ' name ' offline;' from v$tablespace where ts# in (7,8,9,12,13,14,15,16,17,18,24,25,26,27,28,29);


2--
determine which datafiles will be rman copied to ASM
select 'copy datafile ' file# ' to +DATA;' from v$datafile where NAME like '/oradata%';
EX: RMAN > copy datafile 38 to '+DATA';

3--
see copy is finished succesfully , you can check using rman
rman target /
list copy;

List of Datafile Copies
Key File S Completion Time Ckp SCN Ckp Time Name
------- ---- - --------------- ---------- --------------- ----
1 39 A 20-AUG-09 234419163 20-AUG-09 +DATA/abim/datafile/isveren.339.695416859
2 39 A 20-AUG-09 234419163 20-AUG-09 +DATA/abim/datafile/isveren.340.695417627
3 40 A 20-AUG-09 234419128 20-AUG-09 +DATA/abim/datafile/isci.341.695417761
4 41 A 20-AUG-09 234419286 20-AUG-09 +DATA/abim/datafile/uye.342.695417837
5 42 A 20-AUG-09 234419302 20-AUG-09 +DATA/abim/datafile/uyeana.343.695417913

4--
after rman copy datafile is finished; swith datafiles to copy,
!If multiple copies exist see SWITH reference
http://download.oracle.com/docs/cd/B19306_01/backup.102/b14194/rcmsynta061.htm
select 'SWITCH datafile ' file# ' to COPY;' from v$datafile where NAME like '/oradata%';
EX: RMAN> SWITCH DATAFILE 38 TO COPY ;

5--
see copy and current datafiles are excahnged. Use rman to verify
RMAN> list copy;
List of Datafile Copies
Key File S Completion Time Ckp SCN Ckp Time Name
------- ---- - --------------- ---------- --------------- ----
10 38 A 20-AUG-09 234419163 20-AUG-09 /oradata/ABIM/ISVEREN7.dbf
58 39 A 20-AUG-09 234419163 20-AUG-09 /oradata/ABIM/ISVEREN8.dbf
31 40 A 20-AUG-09 234419128 20-AUG-09 /oradata/ABIM/ISCIDATA08.dbf
32 41 A 20-AUG-09 234419286 20-AUG-09 /oradata/ABIM/UYE5.dbf
33 42 A 20-AUG-09 234419302 20-AUG-09 /oradata/ABIM/UYEANA6.dbf

6--
online tablespaces which you have offlined in step 1
EX: select 'alter tablespace ' name ' online;' from v$tablespace where ts# in (7,8,9,12,13,14,15,16,17,18,24,25,26,27,28,29);

7--
SQL>select file#,name from v$datafile;
RMAN>backup database;

REFERENCE :
Metalink Doc ID: 390274.1 How to move a datafile from a file system to ASM