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/10.2.0.4.2/dbs/ +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/10.2.0.4.2/dbs/ +DATAFILE_ORATST(datafile) AVAILABLE RECOVERConnect 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 10.2.0.4.0 - 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/10.2.0.4.2/dbs/ +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;
NAME
--------------------------------------------------------
+DATAFILE_ORATST/ortst01/datafile/users.257.797314131
SQL> recover datafile 10;
Media recovery complete.
SQL>
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.
