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.
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;
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