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/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  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 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.

Thursday, October 11, 2012

How to run dbverify utility on datafiles which is in ASM storage.

Generally we will use dbvefiry utility to find any blocks has corrupted on datafile. but the files which is in ASM storage we need to include one more parameter named "userid" which accepts two arguments i.e username and password.
  
oracle:ortestdb@lnxrecov /oracle > dbv file=+DATA/ortestdb/datafile/users.411.795847253 blocksize=8192 userid=sys/*****

DBVERIFY: Release 10.2.0.4.0 - Production on Thu Oct 11 03:25:50 2012

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

DBVERIFY - Verification starting : FILE = +DATA/ortestdb/datafile/users.411.795847253

DBVERIFY - Verification complete

Total Pages Examined         : 25600
Total Pages Processed (Data) : 0
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 6
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 47
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 25547
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Highest block SCN            : 0 (0.0)
oracle:ortestdb@lnxrecov /oracle >
For ASM files if we don't use userid parameter then we will endup with error like below,
  
oracle:ortestdb@lnxrecov /oracle > dbv file=+DATA/ortestdb/datafile/users.411.795847253 blocksize=8192

DBVERIFY: Release 10.2.0.4.0 - Production on Thu Oct 11 03:36:46 2012

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

DBV-00008: USERID must be specified for OSM files