Move datafile from File system to ASM (or) Move datafile from NON-ASM to ASM

One of our application team has deployed new version of application, and we gave privilege to create tabelspace for their deployment.

As usual application team created the datafile in File System but our database is using ASM. So We need to Move datafile from File system to ASM.

##########################

#  Task 
##########################

Move Datafile From File system to ASM Diskgroup.

##########################
#  Pre checks 
##########################

1. File Name and Tablespace Name where Datafiles are Created.
2. Check Whether Diskgroup is having Sufficient Space
3. Intimate Application team that data's will not be available during this maintenance.

##########################

## Solution
##########################

File Name and Tablespace Name where Datafiles are Created.

set lines 200
col file_name for a60
col "size MB" for 9,99,999
col tablespace_name for a20

Select tablespace_name,file_id,file_name,ceil(bytes/1024/1024/1024) "Size in GB",autoextensible,status,ceil(maxbytes/1024/1024/1024) "MAx in GB" from dba_data_files where file_id=69;

TABLESPACE_NAME         FILE_ID FILE_NAME                                                    Size in GB AUT STATUS     MAx in GB
-------------------- ---------- ------------------------------------------------------------ ---------- --- --------- ----------
TBS_NEW                   69 /u01/app/oracle/product/11.2/db_5/dbs/TBS_NEW.DBF          1 YES AVAILABLE         32

Select tablespace_name,file_id,file_name,ceil(bytes/1024/1024/1024) "Size in GB",autoextensible,status,ceil(maxbytes/1024/1024/1024) "MAx in GB" from dba_data_files where tablespace_name='TBS_NEW';

TABLESPACE_NAME         FILE_ID FILE_NAME                                                    Size in GB AUT STATUS     MAx in GB
-------------------- ---------- ------------------------------------------------------------ ---------- --- --------- ----------
TBS_NEW                   69 /u01/app/oracle/product/11.2/db_5/dbs/TBS_NEW.DBF          1 YES AVAILABLE         32

Check Whether Diskgroup is having Sufficient Space

Select name,total_mb,free_mb from v$asm_diskgroup where state='CONNECTED';

NAME                             TOTAL_MB    FREE_MB

------------------------------ ---------- ----------

ORALIN_DATA                      2294348     495983

ORALIN_RECO                       184335     161864

Now Place the Tablespace OFFLINE.

SQL> alter tablespace TBS_NEW offline;

Tablespace altered.

Check Tablespace Status  

SQL> Select tablespace_name,status from dba_tablespaces where tablespace_name='TBS_NEW';

TABLESPACE_NAME      STATUS
-------------------- ---------
TBS_NEW           OFFLINE


Copy the Datafile from File System to ASM using RMAN command.

Copy Datafile 69 to '+ORALIN_DATA';

ORALIN_1 @ host01:/u01/app/oracle/diag/rdbms/ORALIN/ORALIN_1/trace

> rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Mon Feb 4 00:03:30 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORALIN (DBID=4269551271)

RMAN> copy datafile 69 to '+ORALIN_DATA';

Starting backup at 04-FEB-13

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=4848 instance=ORALIN_1 device type=DISK

channel ORA_DISK_1: starting datafile copy

input datafile file number=00069 name=/u01/app/oracle/product/11.2/db_5/dbs/TBS_NEW.DBF

output file name=+ORALIN_DATA/ORALIN/datafile/TBS_NEW.352.806457841 tag=TAG20130204T000400 RECID=68 STAMP=806457842

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03

Finished backup at 04-FEB-13

Starting Control File and SPFILE Autobackup at 04-FEB-13

piece handle=+ORALIN_RECO/ORALIN_gvl/autobackup/2013_02_04/s_806457844.487.806457845 comment=NONE

Finished Control File and SPFILE Autobackup at 04-FEB-13

RMAN>

Now Switch the Datafile to Update this new location in controlfile.

switch datafile 69 to copy;

RMAN> switch datafile 69 to copy;
datafile 69 switched to datafile copy "+ORALIN_DATA/ORALIN_gvl/datafile/TBS_NEW.352.806457841"

Place Tablespace ONLINE.

SQL> alter tablespace TBS_NEW online;

Tablespace altered.

Check Tablespace Status  & Datafile Location :

SQL>  Select tablespace_name,status from dba_tablespaces where tablespace_name='TBS_NEW';

TABLESPACE_NAME      STATUS

-------------------- ---------

TBS_NEW           ONLINE

SQL> Select file_id,file_name,status from dba_data_files where tablespace_name='TBS_NEW';

   FILE_ID FILE_NAME                                                    STATUS
---------- ------------------------------------------------------------ ---------
        69 +ORALIN_DATA/ORALIN_gvl/datafile/TBS_NEW.352.806457841  AVAILABLE