Moving a datafile from File System to ASM

Moving datafiles from file system to ASM can be done in different ways, one of them using the RMAN copy command.

Scenario: Database is up & running. Only objects related to moving datafile will be affected. i.e., No down time.

Following steps can be used to compete the scenario:

1. Check where to build a new file system based tablespace:

$ cd /u01/oradata/racdb
$ df -k .
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/DataVG-datavg--lv1
                      394G  316G   58G  85% /u01

2. Connect to sqlplus and create a new tablespace

$ sqlplus / as sysdba

SQL> create tablespace sample datafile '/u01/oradata/racdb/sample01.dbf' size 150M;

Tablespace created.

SQL> select file_name from dba_data_files where tablespace_name ='sample';

FILE_NAME
--------------------------------------------------------------------------------
/u01/oradata/racdb/sample01.dbf


3. take the tablespace offline, you may take offline a single datafile from a multifile tablespace if required.

SQL> ALTER TABLESPACE sample OFFLINE;

Tablespace altered.


4. Check where are your datafiles located on ASM

SQL> SELECT FILE_NAME FROM DBA_DATA_FILES;

FILE_NAME
--------------------------------------------------------------------------------
+DATA/racdb/datafile/users.259.623629857
+DATA/racdb/datafile/sysaux.257.623629849
+DATA/racdb/datafile/undotbs1.258.623629855
+DATA/racdb/datafile/system.256.623629845
+DATA/racdb/datafile/undotbs2.261.623630209
/u01/oradata/racdb/sample01.dbf

6 rows selected.

5. Log out from sqlplus, start an RMAN session and execute the copy command

Note: that when giving the destination inside ASM you just need to pass the disk group name when using omf (Oracle Managed Files) that is the best practice in ASM.

SQL> EXIT
$ rman target /
RMAN>
RMAN> copy datafile '/u01/oradata/racdb/sample01.dbf' to '+DATA';

Starting backup at 05-FEB-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile fno=00006 name=/u01/oradata/racdb/sample01.dbf
output filename=+DATA/racdb/datafile/sample.263.625644857 tag=TAG20070619T061416 recid=1 stamp=625644858
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:04
Finished backup at 05-FEB-15

RMAN> exit


Recovery Manager complete.

6. Start an sqlplus session, rename the old file to the new ASM file to update the control file  with new location of the datafile.

$ sqlplus / as sysdba

SQL> alter database rename file '/u01/oradata/racdb/sample01.dbf' to '+DATA/racdb/datafile/sample.263.625644857';

Database altered.

SQL> alter tablespace sample online;

Tablespace altered.

7. remove the old file

$ cd /u01/oradata/racdb
$ rm sample01.dbf

8. Create a test object to verify the status:

SQL> create table test_table tablespace sample as select * from dba_source;

Table created.

It is working fine....