Below steps need to be followed to move the datafile to ASM disk group.
SOLUTION:
1. Make the datafile offline;
SQL> select file_name from dba_data_files; FILE_NAME -------------------------------------------------------------------------------- +DATA/RAC2/DATAFILE/system.260.934026715 +DATA/RAC2/DATAFILE/sysaux.261.934026717 +DATA/RAC2/DATAFILE/undotbs1.262.934026719 +DATA/RAC2/DATAFILE/undotbs2.264.934026727 +DATA/RAC2/DATAFILE/users.265.934026729 /export/home/oracle/user02.dbf alter database datafile '/export/home/oracle/user01.dbf' offline;
2. Connect to RMAN and copy datafile to ASM DISKGROUP
RMAN> copy datafile '/export/home/oracle/user02.dbf' to '+DATA/RAC2/DATAFILE/user02.db'; Starting backup at 12-FEB-17 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=94 instance=RAC2 device type=DISK channel ORA_DISK_1: starting datafile copy input datafile file number=00007 name=/export/home/oracle/user02.dbf output file name=+DATA/RAC2/DATAFILE/user02.db tag=TAG20170212T182216 RECID=1 STAMP=935778137 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03 Finished backup at 12-FEB-17
3. Rename the datafile in control file
SQL> alter database rename file '/export/home/oracle/user02.dbf' to '+DATA/RAC2/DATAFILE/user02.db'; Database altered.
4. Switch the data file to copy
RMAN> SWITCH DATAFILE '+DATA/RAC2/DATAFILE/user02.db' to copy; using target database control file instead of recovery catalog datafile 7 switched to datafile copy "+DATA/RAC2/DATAFILE/user02.db"
5. Recover the datafile
RMAN> recover datafile '+DATA/RAC2/DATAFILE/user02.db'; Starting recover at 12-FEB-17 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=94 instance=RAC2 device type=DISK starting media recovery media recovery complete, elapsed time: 00:00:00 Finished recover at 12-FEB-17
6. Make the datafile online:
SQL> alter database datafile '+DATA/RAC2/DATAFILE/user02.db' online; Database altered. SQL> select file_name from dba_data_files; FILE_NAME -------------------------------------------------------------------------------- +DATA/RAC2/DATAFILE/system.260.934026715 +DATA/RAC2/DATAFILE/sysaux.261.934026717 +DATA/RAC2/DATAFILE/undotbs1.262.934026719 +DATA/RAC2/DATAFILE/undotbs2.264.934026727 +DATA/RAC2/DATAFILE/users.265.934026729 +DATA/RAC2/DATAFILE/user02.db 7 rows selected.
IF YOU ARE IN ORACLE 12C:
In oracle 12c, we can move the datafile online with one line.
SQL> alter database move datafile '/export/home/oracle/user01.dbf' to '+DATA/RAC2/DATAFILE/user01.dbf'; Database altered.
