Case:
Let’s suppose you created a new datafile and missed the .dbf extension or had a typo in the name but you want to fix it.

Let’s suppose the bad datafile name is /data01/STFBKUAT01/datafile/users012 and it should be renamed to /data01/STFBKUAT01/datafile/users02.dbf

Prerequisite: – The database should be in ARCHIVELOG mode.
– It’s always recommended to take a full backup before and after changing the
database physical structure.

Solution:
If you are on 12c, lucky you are, you can do the job with a single command with NO DOWNTIME but with more overhead:

SQL> alter database move datafile ‘/data01/STFBKUAT01/datafile/users012‘ to ‘/data01/STFBKUAT01/datafile/users02.dbf‘;

Database altered.

This command will copy the datafile using the new name, then will switch to the new datafile, then will delete the old datafile with the old name. as it’s engaging a physical copy of the datafile this will generate an overhead based on the datafile size and system speed.

If you are on 11g backward you can rename the datafile with minimal downtime, but with less overhead compared to 12c method:

— Bring the datafile back offline: [Downtime start]
SQL> alter database datafile ‘/data01/STFBKUAT01/datafile/users012‘ offline;

Database altered.

— Rename the datafile on the OS side:
SQL> host mv /data01/STFBKUAT01/datafile/users012  /data01/STFBKUAT01/datafile/users02.dbf

— Rename the datafile on the DB side:
SQL> alter database rename file ‘/data01/STFBKUAT01/datafile/users012‘ to ‘/data01/STFBKUAT01/datafile/users02.dbf‘;

Database altered.

— Recover the datafile:
SQL> recover datafile ‘/data01/STFBKUAT01/datafile/users02.dbf‘;
Media recovery complete.

— Bring the datafile back online: [Downtime end]
SQL> alter database datafile ‘/data01/STFBKUAT01/datafile/users02.dbf‘ online;

Database altered.