Tuesday, September 18, 2012

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.