Duplicate data files

In order to find duplicate data files in your environemnt use the following sql:
select tablespace_name,file_name from dba_data_files a where 1< (select count(*) from
dba_data_files b where substr(a.file_name,24,60)=substr (b.file_name,24,60)) ;

To fix the duplicate datafiles follow the below procedure:

1. If the database is up, shut it down.
> SHUTDOWN IMMEDIATE
2. Copy the datafile to the new name/location at operating system level.
> cp
3. Mount the database.
> STARTUP MOUNT
This command will read the control file but will not mount the datafiles.
4. Rename the file inside Oracle.
> ALTER DATABASE RENAME FILE
'/FULL_PATH_OF_OLD_LOCATION/AND_DATAFILE_NAME.DBF'
TO
'/FULL_PATH_OF_NEW_LOCATION/AND_DATAFILE_NAME.DBF';
Do this for all the datafiles that were renamed or moved at the operating system level.
5. Open the database.
> ALTER DATABASE OPEN;
6. Query v$dbfile to confirm that the changes made were correct.
> SELECT * FROM V$DBFILE;
7. Remove the datafile(s) from the old location at the operating system level.