Move Datafile between DikGroups in ASM

This is one of the common issues where users make mistakes while adding new data file or tablespace and creates in different disk group.

Follow the steps to move data file between disk groups

Here we are copying a Datafile from FLASH to DATA1 Disk Group.

1. Use RMAN to copy the data file from FLASH to DATA disk group.

RMAN> BACKUP AS COPY DATAFILE ‘+FLASH/datafile/usertbs7.588.475363994’ FORMAT ‘+DATA1’;

Or you can also specify the data file number instead of full path

RMAN> BACKUP AS COPY DATAFILE 5 FORMAT ‘+DATA1’;

2. Offline the datafile that you want to move to new disk group.

SQL> ALTER DATABASE DATAFILE ‘+FLASH/datafile/usertbs7.588.475363994’ OFFLINE;

3. Switch the data file to copy

RMAN> switch datafile ‘+FLASH/datafile/usertbs7.588.475363994’ to copy;

4. Recovery the datafile

RMAN> RECOVER DATAFILE ‘+DATA1/datafile/usertbs7.588.475363994’ ;

5. Bring the data file online.

SQL> ALTER DATABASE DATAFILE ‘+DATA1/datafile/usertbs7.588.475363994’ ONLINE;

6. Delete old datafile from ASM disk group.

RMAN> DELETE DATAFILECOPY ‘+FLASH/datafile/usertbs7.588.475363994’;

7. Check the Datafile Status

Check the status of data file by running the below query, you should be able to see the status as "AVAILABLE".

SQL> SELECT FILE_ID,FILE_NAME,STATUS FROM DBA_DATA_FILES;

Regards,
Satishbabu Gunukula, Oracle ACE
http://www.oracleracexpert.com