Use the Asmcmd CP command to move the datafile from the file system (move) to the ASM Disk group-for 11GR2
Reference text:
How to Move a datafile from Filesystem to ASM Using asmcmd CP Command. (Doc ID 1610615.1)
Suitable for:
Oracle database-enterprise edition-version 11.2.0.1 to 11.2.0.4 [Release 11.2]
Information in this document applies to any platform.
Symptoms:
The datafile was incorrectly added to the file system and should be placed in the ASM Disk group.
Reason:
This is not a datafile of the system table space
Solution:
1 Let the datafile offline
sql> alter system switch logfile;
System altered.
Sql> Select file_name, file_id from Dba_data_files;
file_name file_id
---------------------
/U01/ORACLE/ORADATA/TEST1.DBF 6
Sql> ALTER DATABASE datafile 6 offline;
Database altered.
Sql> select file_name, file_id, online_status from Dba_data_files where file_id=6;
file_name file_id Online_status
---------- -------
/U01/ORACLE/ORADATA/TEST1.DBF 6 RECOVER
2. Use the Asmcmd command to copy this file from the file system to the ASM Disk group.
Asmcmd> cp/u01/oracle/oradata/test1.dbf +data/london/datafile/test.dbf
COPYING/U01/ORACLE/ORADATA/TEST1.DBF-+DATA/LONDON/DATAFILE/TEST.DBF
Asmcmd> Ls-lt
Type redund striped Time Sys Name
N test.dbf = +data/asm/datafile/test.dbf.286.833718815
asmcmd> pwd
+data/asm/datafile
3. Once the datafile is copied, rename the datafile
sql> ALTER DATABASE rename file '/u01/oracle/oradata/test1.dbf ' to ' +data/london/datafile/test.dbf ';
Database altered.
4. Recover the datafile, and onlime the datafile
sql> ALTER DATABASE recover datafile 6;
Database altered.
Sql> ALTER DATABASE datafile 6 online;
Database altered.
5. Confirm the correct file name and path
Sql> select file_name, file_id, online_status from Dba_data_files where file_id=6;
file_name file_id Online_status
---------- -------
+DATA/LONDON/DATAFILE/TEST.DBF 6 ONLINE