How to move datafile from Non ASM (file system) to ASM
Solution: Verify the Name and location of datafile and in which tablespace the datafile resides
SQL> select tablespace_name "Tablespace",file_id "ID",file_name "File", bytes/1024/1024 "Size" from dba_data_files where TABLESPACE_NAME='TEST_TBS';
Tablespace ID File Size
---------- --- -------------------------------------- ---------
TEST_TBS 8 /u01/oradata/orcl/test_tbs01.dbf 2016
Verify if you have sufficient space available on the ASM Disk Group
SQL> select name,total_mb,free_mb, state from v$asm_diskgroup;
NAME TOTAL_MB FREE_MB STATE
----- -------- ---------- ---------
DATA 101355 59017 MOUNTED
Take the tablespace offline
SQL> alter tablespace TEST_TBS offline;
Tablespace altered.
SQL> Select tablespace_name,status from dba_tablespaces where tablespace_name='TEST_TBS';
TABLESPACE_NAME STATUS
--------------- -------
TEST_TBS OFFLINE
Use the RMAN command to copy the datafile from the file system to asm disk
[oracle@linux01 ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Tue Feb 18 06:15:45 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=2195477165)
RMAN> copy datafile 8 to '+DATA';
Starting backup at 18-FEB-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=212 instance=ORCL2 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00008 name=/u01/oradata/orcl/test_tbs01.dbf
output file name=+DATA/orcl/datafile/test_tbs01.259.123 tag=TAG20140218T061630 RECID=1 STAMP=839831287
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:11:45
Finished backup at 18-FEB-14
RMAN> switch datafile 8 to copy;
datafile 8 switched to datafile copy "+DATA/orcl/datafile/test_tbs01.259.123"
Bring back the tablespace online
[oracle@linux01 ~]$ sqlplus sys as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Feb 18 06:41:36 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> alter tablespace TEST_TBS online;
Tablespace altered.
Check the new location of the data file and the status
SQL> Select tablespace_name,status from dba_tablespaces where tablespace_name='TEST_TBS';
TABLESPACE_NAME STATUS
--------------- -------
TEST_TBS ONLINE
SQL> select tablespace_name "Tablespace",file_id "ID",file_name "File", bytes/1024/1024 "Size" from dba_data_files where TABLESPACE_NAME='TEST_TBS';
Tablespace ID File Size
---------- --- -------------------------------------- ---------
TEST_TBS 8 +DATA/orcl/datafile/test_tbs01.259.123 2016
Remove the file from the OS file system:
rm /u01/oradata/orcl/test_tbs01.dbf
