How to rename Duplicate datafile names in Oracle??

by 
11g database - Datafile created with extra blank space similar to existing datafile. Please note that oracle database will not allow exact datafile name, this happens when user put space character in file name, so it looks like duplicate filename but it will be different. So user wanted to rename such files, below is the steps for the same:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SQL> select banner from v$version;
 
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
 
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /refresh/home/app/11.2.0.4/oracle/product/11.2.0.4/dbhome_4/dbs/arch
Oldest online log sequence     28
Next log sequence to archive   30
Current log sequence           30
1
2
3
4
5
6
7
SQL> create tablespace test datafile '/refresh/home/app/11.2.0.4/oracle/oradata/orcl11204/test01.dbf' size 20M;
 
Tablespace created.
 
SQL> alter tablespace test add datafile '/refresh/home/app/11.2.0.4/oracle/oradata/orcl11204/test01.dbf ' size 10M;
 
Tablespace altered.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
SQL> !ls -rtl
total 2695068
-rw-r----- 1 oracle oracle   20979712 Aug 16 22:38 temp01.dbf
-rw-r----- 1 oracle oracle   52429312 Feb  9 08:46 redo02.log
-rw-r----- 1 oracle oracle   52429312 Feb  9 08:46 redo03.log
-rw-r----- 1 oracle oracle    5251072 Feb  9 08:46 users01.dbf
-rw-r----- 1 oracle oracle   31465472 Feb  9 09:03 undotbs01.dbf
-rw-r----- 1 oracle oracle  775954432 Feb  9 09:03 system01.dbf
-rw-r----- 1 oracle oracle  660611072 Feb  9 09:03 sysaux01.dbf
-rw-r----- 1 oracle oracle   20979712 Feb  9 09:03 test01.dbf
-rw-r----- 1 oracle oracle   10493952 Feb  9 09:03 test01.dbf  ===============>  Created with extra blank space
-rw-r----- 1 oracle oracle   52429312 Feb  9 09:03 redo01.log
-rw-r----- 1 oracle oracle    9748480 Feb  9 09:03 control01.ctl
-rw-r----- 1 oracle oracle    9748480 Feb  9 09:03 control02.ctl
1
2
3
4
SQL> select dump ( name ) from v$datafile where name like '%test%';
 
Typ=1 Len=36: 47,111,112,116,47,111,114,97,115,119,47,111,114,97,100,97,116,97,47,82,75,68,66,47,115,121,115,116,101,109,48,49,46,100,98,102
Typ=1 Len=39: 47,118,111,108,54,47,97,112,112,47,111,114,97,100,97,116,97,47,82,75,68,66,47,97,112,112,47,111,118,109,115,95,49,50,46,100,98,102,32  >>>>>> 32 confirms extra space
Use RMAN to rename the datafile:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
-bash-4.1$ rman target /
 
Recovery Manager: Release 11.2.0.4.0 - Production on Thu Feb 9 09:09:14 2023
 
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
 
connected to target database: ORCL1120 (DBID=3640541495)
 
RMAN> report schema;
 
using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name ORCL11204
 
List of Permanent Datafiles
====================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    740      SYSTEM               ***     /refresh/home/app/11.2.0.4/oracle/oradata/orcl11204/system01.dbf
2    630      SYSAUX               ***     /refresh/home/app/11.2.0.4/oracle/oradata/orcl11204/sysaux01.dbf
3    30       UNDOTBS1             ***     /refresh/home/app/11.2.0.4/oracle/oradata/orcl11204/undotbs01.dbf
4    5        USERS                ***     /refresh/home/app/11.2.0.4/oracle/oradata/orcl11204/users01.dbf
6    20       TEST                ***     /refresh/home/app/11.2.0.4/oracle/oradata/orcl11204/test01.dbf
7    10       TEST                ***     /refresh/home/app/11.2.0.4/oracle/oradata/orcl11204/test01.dbf
 
List of Temporary Files
=================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767       /refresh/home/app/11.2.0.4/oracle/oradata/orcl11204/temp01.dbf
1
2
3
4
5
6
7
8
9
10
RMAN> COPY DATAFILE 7 TO '/refresh/home/app/11.2.0.4/oracle/oradata/orcl11204/test02.dbf';
 
Starting backup at 09-FEB-23
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=33 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00007 name=/refresh/home/app/11.2.0.4/oracle/oradata/orcl11204/test01.dbf
output file name=/refresh/home/app/11.2.0.4/oracle/oradata/orcl11204/test02.dbf tag=TAG20230209T090933 RECID=1 STAMP=1128330574
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 09-FEB-23
1
2
3
RMAN>  SQL 'ALTER TABLESPACE TEST OFFLINE';
 
sql statement: ALTER TABLESPACE TEST OFFLINE
1
2
3
RMAN> SWITCH DATAFILE 7 TO COPY;
 
datafile 7 switched to datafile copy "/refresh/home/app/11.2.0.4/oracle/oradata/orcl11204/test02.dbf"
1
2
3
4
5
6
7
8
9
RMAN> recover tablespace 'TEST';
 
Starting recover at 09-FEB-23
using channel ORA_DISK_1
 
starting media recovery
media recovery complete, elapsed time: 00:00:00
 
Finished recover at 09-FEB-23
1
2
3
RMAN> SQL 'ALTER TABLESPACE TEST online';
 
sql statement: ALTER TABLESPACE TEST online
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
RMAN> REPORT SCHEMA;
 
Report of database schema for database with db_unique_name ORCL11204
 
List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    740      SYSTEM               ***     /refresh/home/app/11.2.0.4/oracle/oradata/orcl11204/system01.dbf
2    630      SYSAUX               ***     /refresh/home/app/11.2.0.4/oracle/oradata/orcl11204/sysaux01.dbf
3    30       UNDOTBS1             ***     /refresh/home/app/11.2.0.4/oracle/oradata/orcl11204/undotbs01.dbf
4    5        USERS                ***     /refresh/home/app/11.2.0.4/oracle/oradata/orcl11204/users01.dbf
6    20       TEST                ***     /refresh/home/app/11.2.0.4/oracle/oradata/orcl11204/test01.dbf
7    10       TEST                ***     /refresh/home/app/11.2.0.4/oracle/oradata/orcl11204/test02.dbf
 
List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767       /refresh/home/app/11.2.0.4/oracle/oradata/orcl11204/temp01.dbf
1
2
3
4
5
6
7
8
9
10
11
12
SQL> select file_name from dba_data_files;
 
FILE_NAME
--------------------------------------------------------------------------------
/refresh/home/app/11.2.0.4/oracle/oradata/orcl11204/users01.dbf
/refresh/home/app/11.2.0.4/oracle/oradata/orcl11204/undotbs01.dbf
/refresh/home/app/11.2.0.4/oracle/oradata/orcl11204/sysaux01.dbf
/refresh/home/app/11.2.0.4/oracle/oradata/orcl11204/system01.dbf
/refresh/home/app/11.2.0.4/oracle/oradata/orcl11204/test01.dbf
/refresh/home/app/11.2.0.4/oracle/oradata/orcl11204/test02.dbf  ============> Datafile name renamed successfully
 
7 rows selected.