Renaming datafiles using RMAN

Most of us, when we create database make some assumptions where the datafiles should be and later decides to move somewhere else. To me, this was not the reason for moving datafiles. Some error while providing path to datafiles in OUI made the datafiles land to wrong directory with wrong name.

SQL>  select file_id, file_name, TABLESPACE_NAME from dba_data_files order by tablespace_name;

FILE_ID FILE_NAME                                        TABLESPACE_NAME 
------- ------------------------------------------------ ------------------------- 
      2 /u02/oracle/oradata/orcl/systemsysaux01.dbf      SYSAUX 
      1 /u01/oracle/oradata/orcl/systemsystem01.dbf      SYSTEM 
      3 /u03/oracle/oradata/orcl/dataundotbs01.dbf       UNDOTBS1 
      4 /u01/oracle/oradata/orcl/datausers01.dbf         USERS 
RMAN> copy datafile 1 to '/u01/oracle/oradata/orcl/system/system01.dbf'; 
 
Starting backup at 12-SEP-14 
using target database control file instead of recovery catalog 
allocated channel: ORA_DISK_1 
channel ORA_DISK_1: SID=601 device type=DISK 
channel ORA_DISK_1: starting datafile copy 
input datafile file number=00001 name=/u01/oracle/oradata/orcl/systemsystem01.dbf 
output file name=/u01/oracle/oradata/orcl/system/system01.dbf tag=TAG20140912T045027 RECID=1 STAMP=858055834 
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07 
Finished backup at 12-SEP-14
RMAN> copy datafile 2 to '/u02/oracle/oradata/orcl/system/sysaux01.dbf'; 
 
Starting backup at 12-SEP-14 
using channel ORA_DISK_1 
channel ORA_DISK_1: starting datafile copy 
input datafile file number=00002 name=/u02/oracle/oradata/orcl/systemsysaux01.dbf 
output file name=/u02/oracle/oradata/orcl/system/sysaux01.dbf tag=TAG20140912T045146 RECID=2 STAMP=858055910 
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07 
Finished backup at 12-SEP-14
RMAN> copy datafile 3 to '/u03/oracle/oradata/orcl/data/undotbs01.dbf'; 
 
Starting backup at 12-SEP-14 
using channel ORA_DISK_1 
channel ORA_DISK_1: starting datafile copy 
input datafile file number=00003 name=/u03/oracle/oradata/orcl/dataundotbs01.dbf 
output file name=/u03/oracle/oradata/orcl/data/undotbs01.dbf tag=TAG20140912T045235 RECID=3 STAMP=858055957 
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03 
Finished backup at 12-SEP-14
SQL> alter database rename file '/u01/oracle/oradata/orcl/systemsystem01.dbf' to '/u01/oracle/oradata/orcl/system/system01.dbf';
SQL> alter database rename file '/u02/oracle/oradata/orcl/systemsysaux01.dbf' to '/u02/oracle/oradata/orcl/system/sysaux01.dbf';
SQL> alter database rename file '/u03/oracle/oradata/orcl/dataundotbs01.dbf' to '/u03/oracle/oradata/orcl/data/undotbs01.dbf';
SQL> alter database rename file '/u01/oracle/oradata/orcl/datausers01.dbf' to '/u01/oracle/oradata/orcl/data/users01.dbf';
SQL>  ALTER DATABASE OPEN;

Database altered.
ALERT LOG FILE
Completed: ALTER DATABASE   MOUNT 
Fri Sep 12 04:55:38 2014 
alter database rename file '/u01/oracle/oradata/orcl/systemsystem01.dbf' to '/u01/oracle/oradata/orcl/system/system01.dbf' 
Completed: alter database rename file '/u01/oracle/oradata/orcl/systemsystem01.dbf' to '/u01/oracle/oradata/orcl/system/system01.dbf' 
alter database rename file '/u02/oracle/oradata/orcl/systemsysaux01.dbf' to '/u02/oracle/oradata/orcl/system/sysaux01.dbf' 
Completed: alter database rename file '/u02/oracle/oradata/orcl/systemsysaux01.dbf' to '/u02/oracle/oradata/orcl/system/sysaux01.dbf' 
Fri Sep 12 04:55:50 2014 
alter database rename file '/u03/oracle/oradata/orcl/dataundotbs01.dbf' to '/u03/oracle/oradata/orcl/data/undotbs01.dbf' 
Completed: alter database rename file '/u03/oracle/oradata/orcl/dataundotbs01.dbf' to '/u03/oracle/oradata/orcl/data/undotbs01.dbf' 
alter database rename file '/u01/oracle/oradata/orcl/datausers01.dbf' to '/u01/oracle/oradata/orcl/data/users01.dbf' 
Completed: alter database rename file '/u01/oracle/oradata/orcl/datausers01.dbf' to '/u01/oracle/oradata/orcl/data/users01.dbf'
SQL>  select file_id, file_name, TABLESPACE_NAME from dba_data_files order by tablespace_name; 
 
FILE_ID FILE_NAME                                        TABLESPACE_NAME 
------- ------------------------------------------------ ------------------- 
      2 /u02/oracle/oradata/orcl/system/sysaux01.dbf     SYSAUX 
      1 /u01/oracle/oradata/orcl/system/system01.dbf     SYSTEM 
      3 /u03/oracle/oradata/orcl/data/undotbs01.dbf      UNDOTBS1 
      4 /u01/oracle/oradata/orcl/data/users01.dbf        USERS 
 
SQL>  ALTER TABLESPACE TEMP ADD TEMPFILE '/u02/oracle/oradata/orcl/data/temp01.dbf' SIZE 20m AUTOEXTEND ON MAXSIZE 2G; 
 
Tablespace altered. 
 
SQL>  ALTER TABLESPACE TEMP ADD TEMPFILE '/u02/oracle/oradata/orcl/datatemp01.dbf' SIZE 20480K REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED; 

Tablespace altered. 
 
SQL>  ALTER TABLESPACE TEMP DROP TEMPFILE '/u02/oracle/oradata/orcl/datatemp01.dbf'; 
 
Tablespace altered. 
 
SQL>  SELECT FILE_NAME, TABLESPACE_NAME, STATUS FROM DBA_TEMP_FILES; 
           
FILE_NAME                                       TABLESPACE_NAME    STATUS 
----------------------------------------------- ------------------ --------------- 
/u02/oracle/oradata/orcl/datatemp01.dbf         TEMP               OFFLINE 
/u02/oracle/oradata/orcl/data/temp01.dbf        TEMP               ONLINE
But the original file still exist, which need to be removed :
$ file /u02/oracle/oradata/orcl/systemsysaux01.dbf 
/u02/oracle/oradata/orcl/systemsysaux01.dbf: data 
 
$ file /u01/oracle/oradata/orcl/systemsystem01.dbf 
/u01/oracle/oradata/orcl/systemsystem01.dbf: data 
 
$ file /u03/oracle/oradata/orcl/dataundotbs01.dbf 
/u03/oracle/oradata/orcl/dataundotbs01.dbf: data 
 
$ file /u01/oracle/oradata/orcl/datausers01.dbf 
/u01/oracle/oradata/orcl/datausers01.dbf: data 
Comments