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.