Oracle - Move datafile from single-node into ASM using RMAN and recover (online mode)

If you need to move a datafile from single-node to ASM then it is necessary to use RMAN to copy the datafile before renaming the datafile using SQLPLUS.

1. Log into RMAN and copy the datafile to the required diskgroup:
   

   merlin:oracle@OASLIVE1 > rman
Recovery Manager: Release 10.2.0.4.0 - Production on Mon Nov 1 11:42:00 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
RMAN> connect target /
connected to target database: OASLIVE (DBID=3426612930)

RMAN> copy datafile 19 to '+LIVEDATA';
Starting backup at 01-NOV-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=122 instance=OASLIVE1 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00019 name=/u01/oracle/ora102/dbs/F:ORACLEORADATAOASLIVEOASLIVE_REPOS_01.DBF
output filename=+LIVEDATA/oaslive/datafile/repository.288.733923757 tag=TAG20101101T114237 recid=1 stamp=733923758
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 01-NOV-10

2. Take the datafile offline to prevent data from being written to it whilst the renaming is commencing
   

   merlin:oracle@OASLIVE1 > sqlplus '/as sysdba'
SQL*Plus: Release 10.2.0.4.0 - Production on Mon Nov 1 11:46:07 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> alter database datafile 19 offline;
Database altered.

3. Rename the datafile
   

   SQL> alter database rename file '/u01/oracle/ora102/dbs/F:ORACLEORADATAOASLIVEOASLIVE_REPOS_01.DBF' to '+LIVEDATA/oaslive/datafile/repository.288.733923757';
Database altered.

4. If we try to put the datafile back online we will get an error message saying that the datafile requires recovery. This is because the copy of the datafile was completed whilst the datafile was still online. The following error message will occur:
   

   SQL> alter database datafile 19 online;
alter database datafile 19 online
*
ERROR at line 1:
ORA-01113: file 19 needs media recovery
ORA-01110: data file 19: '+LIVEDATA/oaslive/datafile/repository.288.733923757'

5. Recover the datafile to restore
   

   SQL> recover datafile 19;
Media recovery complete.

6. Bring the datafile back online:
   

   SQL> alter database datafile 19 online;
Database altered.