Convert datafile from one diskgroup into another.

Few days back, one of my colleague made mistake while adding new datafile into ASM diskgroup. New datafile was wrongly added into +DATAFILE diskgroup instead of +DATAFILE_PRODDB. Then I followed below steps to change that datafile into +DATAFILE_PRODDB diskgroup.

Get the datafile info which was wrongly added.

SQL> select file_id,file_name,online_status from dba_Data_Files where tablespace_name='TOOLS';

   FILE_ID FILE_NAME                                          ONLINE_
---------- -------------------------------------------------- -------
         4 +DATAFILE/orreco42/datafile/tools.532.793507573     ONLINE

Make that datafile offline.

SQL> alter database datafile 4 offline;

Database altered.

Now connect to RMAN and make image copy of that datafile into new diskgroup.

oracle:orreco42@lnxora01 /oracle > rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Tue Sep 18 07:10:27 2012

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: ORRECO42 (DBID=4078254412)

RMAN> backup as copy datafile 4 format '+DATAFILE_PRODDB';

Starting backup at 18-SEP-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=64 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=+DATAFILE/orreco42/datafile/tools.532.793507573
output filename=+DATAFILE_PRODDB/orreco42/datafile/tools.456.794301047 tag=TAG20120918T071045 recid=2 stamp=794301047
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:02
Finished backup at 18-SEP-12

RMAN> exit

Recovery Manager complete.

Then we need to update the controlfile with the new datafile location. the new file name of datafile 4 can be obtained from above rman command, see the output filename .

while doing this change controlfile will be updated at the same time old file name which was created in +DATAFILE diskgroup will also be removed automatically.

SQL> alter database rename file '+DATAFILE/orreco42/datafile/tools.532.793507573' to '+DATAFILE_PRODDB/orreco42/datafile/tools.456.794301047';
Database altered.

Now connect to RMAN and switch datafile 4 to copy (i.e image copy)

RMAN> switch datafile 4 to copy;

using target database control file instead of recovery catalog
datafile 4 switched to datafile copy "+DATAFILE_PRODDB/orreco42/datafile/tools.456.794301047"

RMAN>

RMAN> recover datafile 4;

Starting recover at 18-SEP-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=54 devtype=DISK

starting media recovery
media recovery complete, elapsed time: 00:00:09

Finished recover at 18-SEP-12

RMAN>

RMAN> sql 'alter database datafile 4 online';

sql statement: alter database datafile 4 online

Before to make the datafile online we need to recover. This is because we brought the datafile 4 offline so if we open the datafile without recover it will get fail saying, datafile 4 needs media recovery. (At this stage datafile 4 will be in recvoer mode so we need to RECOVER it to make the datafile 4 into consistant state.

Now you can see datafile 4 is renamed into appropriate diskgroup.

SQL> select file_id,file_name,online_status from dba_Data_Files where tablespace_name='TOOLS';

   FILE_ID FILE_NAME                                     ONLINE_
---------- --------------------------------------------- -------
         4 +DATAFILE_PRODDB/orreco42/datafile/tools.456.794301047   ONLINE

I Hope this article helped to you. I am expecting your suggestions/feedback.