Saturday, October 27, 2012

Migrate datafile from filesystem into ASM

In this post, I am going to demonstrate how to move datafile which was wrongly created on filesystem instead of ASM storage.

You may get a question here! what's the necessary to create a datafile in Filesystem instead of ASM diskgroup. well, while adding any datafile into ASM diskgroup there is a possibility that datafile might get create on Filesystem instead of ASM storage eventhough you specified appropripate Diskgroup name!

Still not clear!!! Read the below post.

Say for example, I have a tablesepace named USERS which has one datafile created on +DATAFILE_ORATST diskgroup.
FILE_ID FILE_NAME                                                 Size_MB
------- ------------------------------------------------------ ----------
      5 +DATAFILE_ORATST/ortst01/datafile/users.290.791711667    50.0625

Now I am going to add another 100MB datafile into USERS tablespace.
SQL> alter tablespace users add datafile ' +DATAFILE_ORATST(datafile)' size 100m;

Tablespace altered.
If you notice the above statement carefully you could see there is WHITE space before diskgroup name! if you give SPACE between singlequote and + symbol then Oracle will not recognize the diskgroup name which you specified in alter statement, eventhough you specified exact diskgroup name. then the new datafile will automatically get create in $ORACLE_HOME/dbs directory.
SQL>select file_id,file_name,bytes/1024/1024 "Size_MB" from dba_data_files where tablespace_name='USERS';
 FILE_ID FILE_NAME                                                     Size_MB
------- ---------------------------------------------------------- ----------
      5 +DATAFILE_ORATST/ortst01/datafile/users.290.791711667        50.0625
     10 /oracle/product/ +DATAFILE_ORATST(datafile)   100
Here the actual conversion happens. Make that datafile offline.
SQL> alter database datafile 10 offline;

Database altered.

SQL>select file_name,status,online_status from dba_data_files where tablespace_name='USERS';

FILE_NAME                                                   STATUS    ONLINE_
----------------------------------------------------------  --------- -------
+DATAFILE_ORATST/ortst01/datafile/users.290.791711667      AVAILABLE  ONLINE
/oracle/product/ +DATAFILE_ORATST(datafile) AVAILABLE  RECOVER
Connect to target database using RMAN , (Here catalog connection not required) Then take a copy of that datafile into new diskgroup format i.e +DATAFILE_ORATST followed by switch that datafile to copy to update the new datafile location in controlfile.
oracle:ortst01@orahst /oracle> rman target /

Recovery Manager: Release - Production on Mon Oct 22 04:07:55 2012

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

connected to target database: ORATST01 (DBID=85768739)

RMAN>backup as copy datafile 10 format '+DATAFILE_ORATST';

Starting backup at 22-OCT-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=269 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=274 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00010 name=/oracle/product/ +DATAFILE_ORATST(datafile)
output filename=+DATAFILE_ORATST/ORATST01/datafile/users.257.797314131 tag=TAG20121022T040850 recid=21 stamp=797314133
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 22-OCT-12

RMAN> switch datafile 10 to copy;

datafile 10 switched to datafile copy "+DATAFILE_ORATST/ORATST01/datafile/users.257.797314131"
Now wrongly created datafile got plugged into ASM storage, then do recovery on that datafile to bring it to consistent state and then open it.
SQL> select name from v$datafile where file#=10;


SQL> recover datafile 10;
Media recovery complete.
SQL> alter database datafile 10 online;

Database altered.

FILE_ID FILE_NAME                                                      Size_MB
------- ----------------------------------------------------------- ----------
      5 +DATAFILE_CTORATEST36/ortst01/datafile/users.290.791711667      50.0625
     10 +DATAFILE_CTORATEST36/ortst01/datafile/users.257.797314131        100

Now we can manually remove that old datafile located in $ORACLE_HOME/dbs directory.

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