How to rename Duplicate datafile names in Oracle??
3 min read
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:
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
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.
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
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:
-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
RMAN> SQL 'ALTER TABLESPACE TEST OFFLINE';
sql statement: ALTER TABLESPACE TEST OFFLINE
RMAN> SWITCH DATAFILE 7 TO COPY;
datafile 7 switched to datafile copy "/refresh/home/app/11.2.0.4/oracle/oradata/orcl11204/test02.dbf"
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
RMAN> SQL 'ALTER TABLESPACE TEST online';
sql statement: ALTER TABLESPACE TEST online
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
