Rename Oracle Managed File (OMF) datafiles in ASM

(Version edited after comments -> rman backup as copy)
(Version edited to include delete leftover datafile from rman)

Recently I was asked to rename a tablespace. The environment was Oracle version 11.2.0.3 (both database and clusterware/ASM).

This is the test case I build to understand how that works:
(I couldn’t find a clean, straightforward description how to do that, which is why I blog it here)

I created an empty tablespace ‘test1′ for test purposes:

SYS@v11203 AS SYSDBA> create bigfile tablespace test1 datafile size 10m;

(I use bigfile tablespaces only with ASM. Adding datafiles is such a labour intensive work, bigfile tablespaces elimenate that, when auto extent is correctly set)

A tablespace can be easily renamed with the alter tablespace rename command:

SYS@v11203 AS SYSDBA> alter tablespace test1 rename to test2;

This changes the Oracle data dictionary to reflect a new name. This doesn’t touch the underlying datafile:

SYS@v11203 AS SYSDBA> select * from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   FILE_ID TABLESPACE_NAME		       BYTES	 BLOCKS STATUS	  RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE_
---------- ------------------------------ ---------- ---------- --------- ------------ --- ---------- ---------- ------------ ---------- ----------- -------
+DATA/v11203/datafile/system.260.785186841
	 1 SYSTEM			   734003200	  89600 AVAILABLE	     1 YES 3.4360E+10	 4194302	 1280  732954624       89472 SYSTEM

+DATA/v11203/datafile/sysaux.261.785186847
	 2 SYSAUX			   629145600	  76800 AVAILABLE	     2 YES 3.4360E+10	 4194302	 1280  628097024       76672 ONLINE

+DATA/v11203/datafile/undotbs1.262.785186849
	 3 UNDOTBS1			   477102080	  58240 AVAILABLE	     3 YES 3.4360E+10	 4194302	  640  476053504       58112 ONLINE

+DATA/v11203/datafile/users.264.785186857
	 4 USERS			     5242880	    640 AVAILABLE	     4 YES 3.4360E+10	 4194302	  160	 4194304	 512 ONLINE

+DATA/v11203/datafile/ts.266.785323903
	 5 TS				   419430400	  51200 AVAILABLE	  1024 YES 3.5184E+13 4294967293	12800  418381824       51072 ONLINE

+DATA/v11203/datafile/iops.267.785326883
	 6 IOPS 			  5452595200	 665600 AVAILABLE	  1024 YES 3.5184E+13 4294967293	12800 5451546624      665472 ONLINE

+DATA/v11203/datafile/test1.268.789380535
	 7 TEST2			    10485760	   1280 AVAILABLE	  1024 NO	    0	       0	    0	 9437184	1152 ONLINE

To rename the datafile in ASM, offline the tablespace, copy the datafile using RMAN, rename the datafile in the Oracle data dictionary, and online the tablespace again:

Offline the tablespace:

SYS@v11203 AS SYSDBA> alter tablespace test2 offline;

Copy the datafile using RMAN:

RMAN> copy datafile '+DATA/v11203/datafile/test1.268.789380535' to '+DATA';

Starting backup at 23-JUL-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=28 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00007 name=+DATA/v11203/datafile/test1.268.789380535
output file name=+DATA/v11203/datafile/test2.269.789380645 tag=TAG20120723T082404 RECID=1 STAMP=789380644
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 23-JUL-12

Rename the datafile in the Oracle data dictionary:

SYS@v11203 AS SYSDBA> alter database rename file '+DATA/v11203/datafile/test1.268.789380535' to '+DATA/v11203/datafile/test2.269.789380645';

Please mind the ‘old’ filename is at ‘input datafile’, and the ‘new’ filename is at ‘output file name’ with the RMAN output.

Next, and finally: online the tablespace:

SYS@v11203 AS SYSDBA> alter tablespace test2 online;

(the old datafile is gone)

Update:
The RMAN copy command and data dictionary update could also be done with RMAN backup as copy and switch datafile:

Status of the database after renaming:

SYS@v11203 AS SYSDBA> select file_id, file_name, tablespace_name from dba_data_files;

   FILE_ID FILE_NAME					      TABLESPACE_NAME
---------- -------------------------------------------------- ------------------------------
	 1 +DATA/v11203/datafile/system.260.785186841	      SYSTEM
	 2 +DATA/v11203/datafile/sysaux.261.785186847	      SYSAUX
	 3 +DATA/v11203/datafile/undotbs1.262.785186849       UNDOTBS1
	 4 +DATA/v11203/datafile/users.264.785186857	      USERS
	 5 +DATA/v11203/datafile/ts.266.785323903	      TS
	 6 +DATA/v11203/datafile/iops.267.785326883	      IOPS
	 7 +DATA/v11203/datafile/test1.269.789411511	      TEST2

Offline the tablespace (my database is in NOARCHIVELOG, online backup (as copy) can only be done in ARCHIVELOG, when doing so, the datafile needs recovery):

RMAN> sql "alter tablespace test2 offline";

sql statement: alter tablespace test2 offline

Backup the datafile as copy. It is very convenient to use file number (file_id), this makes it much simpler to do this:

RMAN> backup as copy datafile 7 format '+DATA';

Starting backup at 23-JUL-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00007 name=+DATA/v11203/datafile/test1.269.789411511
output file name=+DATA/v11203/datafile/test2.268.789411665 tag=TAG20120723T170105 RECID=2 STAMP=789411665
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 23-JUL-12

Now switch to the backup copy:

RMAN> switch datafile 7 to copy;

datafile 7 switched to datafile copy "+DATA/v11203/datafile/test2.268.789411665"

And online the tablespace again:

RMAN> sql "alter tablespace test2 online";

sql statement: alter tablespace test2 online

Please mind this leaves the old datafile in place, so it needs to be removed explicitly:

RMAN> delete noprompt copy of datafile 7;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=44 device type=DISK
List of Datafile Copies
=======================

Key     File S Completion Time Ckp SCN    Ckp Time       
------- ---- - --------------- ---------- ---------------
5       7    A 23-JUL-12       1779771    23-JUL-12      
        Name: +DATA/v11203/datafile/test1.268.789415137

deleted datafile copy
datafile copy file name=+DATA/v11203/datafile/test1.268.789415137 RECID=5 STAMP=789415216
Deleted 1 objects

An alternative is to do this with asmcmd, but it’s far less elegant:

$ asmcmd rm -f +DATA/v11203/datafile/test1.269.789411511