Renaming and Relocating Oracle datafiles.

To rename a datafile.

SQL> alter tablespace xyz offline;  
                     or database would be in mount stage.
SQL> startup force mount;

If not  taken offline, datafile would be relocated but needed media recovery before it is to be accessed.
to perform media recovery first take the tablespace offline or have a hold on the database in mount stage.
SQL> alter tablespace xyz offline;
SQL> recover datafile 'C:\app\admin\oradata\xyz.dbf';
SQL> alter tablespace xyz online;

 To rename, relocate or both of a datafile in the same tablespace.

Take the tablespace oflfine or hold the database in mount stage. If we failed to hold the database in mount stage or the datafile in offline, the relocated datafile needs media recovery.
SQL> alter tablespace xyz offline;
             or
SQL> startup force mount;
copy and paste the datafile in desired location. if it is to be renamed change the name.
SQL> alter tablespace xyz rename datafile 'C:\app\admin\oradata\old\xyz.dbf', to
                                                         'C:\app\admin\oradata\old\data\abc.dbf';
SQL> alter tablespace xyz online;
              or
SQL> alter database open;   # if the database is in mount mode.


To rename, relocate or both of a datafile of different tablespace.
 Hold the database in mount stage but be sure the database should not be open, if the database is open, hold the datafiles or tablespaces  in offline mode then fire the below mentioned command.
 1. Mount the database.
SQL> startup force mount;
          or
Place both the tablespaces in offline mode.
SQL> alter tablespace xyz offline;
SQL> alter tablespace abc offline;
  2. copy and paste the datafiles in desired location. if it is to be renamed change the name of that datafile
SQL> alter database rename file
                  'C:\app\admin\oradata\old\xyz.dbf','C:\app\admin\oradata\old\ac.dbf'       to                         'C:\app\admin\oradata\new\xyz.dbf','C:\app\admin\oradata\old\abc.dbf';
SQL> alter tablespace xyz online;
SQL> alter tablespace abc online;
           or
SQL> alter database open;         # if the database is in mount mode.

Q. Why alter database rename file here ..?

To update the controlfiles regarding these multiple changes to take effect.

**  for media recovery the tablespace or datafile should be offline or database should be in mount stage.