How to relocate/move oracle datafiles in Oracle 11g

We can move/relocate oracle datafile to different location, In this article, we will move datafile with the help of Recovery Manager.

Consider step by step approach to move oracle datafile as below:

Step I:

Get database datafile ID those you wanted to move with the help of REPORT SCHEMA rman command as below:

 RMAN> REPORT SCHEMA;
 Report of database schema for database with db_unique_name PROD
 List of Permanent Datafiles
 ===========================
 File Size(MB) Tablespace RB segs Datafile Name
 ---- -------- -------------------- ------- ------------------------
 1 8880 SYSTEM YES /DATA1/database/prod/system01.dbf
 2 2710 SYSAUX NO /DATA1/database/prod/sysaux01.dbf
 3 17255 UNDOTBS1 YES /DATA1/database/prod/undotbs01.dbf
 4 25 USERS NO /DATA1/database/prod/users01.dbf
 5 6444 PROD_DATA NO /DATA1/database/prod/prodapp_data.dbf
 6 5634 PROD_INDEX NO /DATA1/database/prod/prodapp_index.dbf
 7 32744 PROD_LOB NO /DATA1/database/prod/prodapp_lob.dbf
 List of Temporary Files
 =======================
 File Size(MB) Tablespace Maxsize(MB) Tempfile Name
 ---- -------- -------------------- ----------- --------------------
 1 500 TEMP_NEW 32767 /DATA/database/prod/temp_01.dbf

Note: To simulate scenario, I am going to move PROD_LOB tablespace( i.e. FILE ID:7 ) from ‘/DATA1/database/prod/’ to new location ‘/NewData/prodDB/df/’.

Step II:

Offline tablespace that you want to move.

 RMAN> SQL 'ALTER TABLESPACE PROD_LOB OFFLINE';

Step III:

Copy all the datafiles to destination, In our case PROD_LOB tablespace having only one datafile.

 RMAN> COPY DATAFILE 7 TO '/NewData/prodDB/df/prodapp_lob.dbf';

Step IV:

Switch back to the new datafile copy(s)

 RMAN> SWITCH DATAFILE 7 TO COPY;

Step V:

Bring offline Tablespace to online state with the help of SQL command: ‘ALTER TABLESPACE’.

 RMAN> SQL 'ALTER TABLESPACE PROD_LOB ONLINE';

Step VI:

Ensure above changes with the help of REPORT SCHEMA rman command as below:

 RMAN> REPORT SCHEMA;
 ...
 File Size(MB) Tablespace RB segs Datafile Name
 ---- -------- -------------------- ------- ------------------------
 ...
 7 32744 PROD_LOB NO /NewData/prodDB/df/prodapp_lob.dbf

Step VII:

If Tablespace get back to online state successfuly, than old datafile is useless. Remove it.

 RMAN> HOST 'rm /DATA1/database/prod/prodapp_lob.dbf';

Cheers!! Datafile relocated successfully.