Question : A datafile was wrongly added on file system instead of ASM diskgroup. How to move it to the ASM disk group again?
Answer :
1) Make sure the datafile to be moved is OFFLINE before proceeding :
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 ASMCMD to copy the file from filesystem to the diskgroup
ASMCMD> cp /u01/oracle/oradata/test1.dbf +DATA/LONDON/DATAFILE/test.dbf copying /u01/oracle/oradata/test1.dbf -> +DATA/LONDON/DATAFILE/test.dbf
Verify the movement :
ASMCMD> cd +DATA/ASM/DATAFILE ASMCMD> ls -lt Type Redund Striped Time Sys Name N test.dbf => +DATA/ASM/DATAFILE/test.dbf.286.833718815
3. Once the file 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 bring it ONLINE.
SQL> alter database recover datafile 6; Database altered.
SQL> alter database datafile 6 online; Database altered.
5. Confirm correct name and location:
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