PROBLEM:

One of the junior DBA mistakenly created a datafile in local filesystem instead of ASM disk group in RAC database 11g. But he is quick to realize his mistake.

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
--------------------------------------------------------------------------------
+NEWTST/TESTDB2/DATAFILE/system.260.934026715
+NEWTST/TESTDB2/DATAFILE/sysaux.261.934026717
+NEWTST/TESTDB2/DATAFILE/undotbs1.262.934026719
+NEWTST/TESTDB2/DATAFILE/undotbs2.264.934026727
+NEWTST/TESTDB2/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 '+NEWTST/TESTDB2/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=TESTDB21 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00007 name=/export/home/oracle/user02.dbf
output file name=+NEWTST/TESTDB2/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 '+NEWTST/TESTDB2/DATAFILE/user02.db';

Database altered.

4. Switch the data file to copy

RMAN> SWITCH DATAFILE '+NEWTST/TESTDB2/DATAFILE/user02.db' to copy;

using target database control file instead of recovery catalog
datafile 7 switched to datafile copy "+NEWTST/TESTDB2/DATAFILE/user02.db"

5. Recover the datafile

RMAN> recover datafile '+NEWTST/TESTDB2/DATAFILE/user02.db';

Starting recover at 12-FEB-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=94 instance=TESTDB21 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 '+NEWTST/TESTDB2/DATAFILE/user02.db' online;
Database altered.

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
+NEWTST/TESTDB2/DATAFILE/system.260.934026715
+NEWTST/TESTDB2/DATAFILE/sysaux.261.934026717
+NEWTST/TESTDB2/DATAFILE/undotbs1.262.934026719
+NEWTST/TESTDB2/DATAFILE/undotbs2.264.934026727
+NEWTST/TESTDB2/DATAFILE/users.265.934026729
+NEWTST/TESTDB2/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 '+NEWTST/TESTDB2/DATAFILE/user01.dbf';

Database altered.