Introduction.
As part of Housekeeping job Dbas of all times have been creating tablespaces for the users and applications. During the lifecycle of an application objects will be created , deleted copy into temporary copies etc. In the end storage used of a tablespace might look like a Swiss Cheese. Even though Oracle is smart enough to reuse the “wholes” sometimes it is good housekeeping to reorganize a tablespace by moving – recreating its objects in a new tablespace. Once such scenario has completed the old tablespace ( aka swiss cheese ) will be empty so it could be tossed away. Or not if the tablespace name is mandatory since Vendor scripts use hardcodes specific tablespace name. Even though the dba might not like such ‘Fixed’ names it is good to know that Oracle in 11G( my version is 11.2.03 on Linux ) offers the option to rename a tablespace. But what will happen to the datafiles that are part of that tablespace ?
Below scenario is based on a great post I found on the web by Frits Hoogland: https://fritshoogland.wordpress.com/2012/07/23/rename-oracle-managed-file-omf-datafiles-in-asm/ .
In his Post Frits is offering two scenarios to do this action. Personally i favor the first once since it holds less risk. Oracle keeps track of the copied file and the old file and is dropping the old file during the alter database datafile rename .. action whereas the second scenario means you will have to do some afterwork in asmcmd since the original ( old file ) remains in place ( since in rman a copy and switch to is done).
And as you can see , after all these years (his post was written in 2012 ) still great input for the current scenario. Good posts are like good wine , they get better as they age.
Scenario:
Often on the web it is stated that you should not believe it just because it is printed. That is also what I did. To get a look and feel of the scenario let’s run a test scenario first.
## Creating a tablespace in an Asm diskgroup ( which will create the datafile as OMF).
SQL> create tablespace test1 datafile ‘+DATA’ size 100m;
Let’s see how the datafiles for this tablespace in the DATA diskgroup will look like:
SQL> select * from dba_data_files where tablespace_name = ‘TEST1’;
FILE_NAME FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE_
—- — ———- ———- ———— ———- ———– ——-
+DATA/mydb1/datafile/test1.736.935381317 5 TEST1 104857600 12800 AVAILABLE 5 NO 0 0 0 103809024 12672 ONLINE
Ok so we see that the datafile is +DATA/mydb1/datafile/test1.736.935381317 at the moment.
In the next step we will rename the tablespace:
SQL> alter tablespace test1 rename to test2;
Time to check if there has something changed under the hood now we have renamed the tablespace:
SQL> select * from dba_data_files where tablespace_name = ‘TEST2’;
FILE_NAME FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE_
—- — ———- ———- ———— ———- ———– ——-
+DATA/mydb1/datafile/test1.736.935381317 5 TEST2 104857600 12800 AVAILABLE 5 NO 0 0 0 103809024 12672 ONLINE
Not much of a surprise that the datafile is still +DATA/mydb1/datafile/test1.736.935381317 so if we feel that there should be a 1:1 relation between datafiles we need to take the extra mile to make it right.
In my first sqlplus session , issued this command:
SQL> alter tablespace TEST2 offline;
I am used to work in multiple sessions on my linux sessions so I opened a second window sqlplus and started RMAN and gave this command to copy ( the old file) in the Diskgroup. This will create another OMF for this datafile in the diskgroup +DATA.
rman target /
RMAN> copy datafile ‘+DATA/mydb1/datafile/test1.736.935381317’ to ‘+DATA’;
Output of this action looked like this:
Starting backup at 08.02.2017 04:13:58
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=923 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=+DATA/mydb1/datafile/test1.736.935381317
output file name=+DATA/mydb1/datafile/test2.733.935381641 tag=TAG20170208T041400 RECID=3 STAMP=935381644
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 08.02.2017 04:14:07
Starting Control File and SPFILE Autobackup at 08.02.2017 04:14:08
piece handle=/opt/oracle/product/11203_ee_64/db/dbs/MYDB1_c-2786503255-20170208-00 comment=NONE
Finished Control File and SPFILE Autobackup at 08.02.2017 04:14:15
With the output file name ( the new created file ) +DATA/mydb1/datafile/test2.733.935381641
I Moved back to my first screen and in sqlplus issued this command:
SQL> alter database rename file ‘+DATA/mydb1/datafile/test1.736.935381317’ to ‘+DATA/mydb1/datafile/test2.733.935381641’ ;
After that last step to make is to take the tablespace online again:
SQL> alter tablespace test2 online;
Let’s check the datafile for the tablespace again now.
SQL> select * from dba_data_files where tablespace_name = ‘TEST2’;
FILE_NAME FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE_
—- — ———- ———- ———— ———- ———– ——-
+DATA/mydb1/datafile/test2.733.935381641 5 TEST2 104857600 12800 AVAILABLE 5 NO 0 0 0 103809024 12672 ONLINE
Now it is time for the real deal.
First let’s find out which datafiles are part of our Tablespace POOL_DATA ( which was renamed from POOL_DATA_REORG.
SQL> select file_id,file_name from dba_data_files where tablespace_name = ‘POOL_DATA’;
FILE_ID FILE_NAME --------------------------------------------- 41 +DATA/mydb1/datafile/pool_data_reorg.780.934704787 42 +DATA/mydb1/datafile/pool_data_reorg.783.934705039 43 +DATA/mydb1/datafile/pool_data_reorg.658.934705043 44 +DATA/mydb1/datafile/pool_data_reorg.735.934705271 45 +DATA/mydb1/datafile/pool_data_reorg.734.934705525 46 +DATA/mydb1/datafile/pool_data_reorg.732.934705785 47 +DATA/mydb1/datafile/pool_data_reorg.731.934706037 48 +DATA/mydb1/datafile/pool_data_reorg.730.934706289 49 +DATA/mydb1/datafile/pool_data_reorg.729.934706505
Time to take the tablespace offline after consulting with Vendor – Dba that there will be an agreed maintenance window to do these actions:
SQL> alter tablespace POOL_DATA offline;
Opened a second screen and started an rman session.
rman target /
RMAN> copy datafile ‘+DATA/mydb1/datafile/pool_data_reorg.780.934704787’ to ‘+DATA’;
This showed this output in Rman Starting backup at 08.02.2017 04:35:57 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy input datafile file number=00041 name=+DATA/mydb1/datafile/pool_data_reorg.780.934704787 output file name=+DATA/mydb1/datafile/pool_data.736.935382959 tag=TAG20170208T043558 RECID=4 STAMP=935383461 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:08:25 Finished backup at 08.02.2017 04:44:23 Starting Control File and SPFILE Autobackup at 08.02.2017 04:44:24 piece handle=/opt/oracle/product/11203_ee_64/db/dbs/MYDB1_c-2786503255-20170208-03 comment=NONE Finished Control File and SPFILE Autobackup at 08.02.2017 04:44:31
In sqlplus I issued the rename action using the above information:
SQL> alter database rename file ‘+DATA/mydb1/datafile/pool_data_reorg.780.934704787’ to ‘+DATA/mydb1/datafile/pool_data.736.935382959’;
And with the idea checking Is believing , in the alert log I saw this entry
Alter database rename file '+DATA/mydb1/datafile/pool_data_reorg.780.934704787' to '+DATA/mydb1/datafile/pool_data.736.935382959' Deleted Oracle managed file +DATA/mydb1/datafile/pool_data_reorg.780.934704787 Completed: alter database rename file '+DATA/mydb1/datafile/pool_data_reorg.780.934704787' to '+DATA/mydb1/datafile/pool_data.736.935382959'
I like what I see! Working this way and performing the rename will automatically delete the old File in ASM for us!
If this would be the online datafile to be moved the next step would be to take the tablespace online again. ( In this case also as an extra check that we did not break anything).
SQL> alter tablespace POOL_DATA online;
Now let us check the datafile(s) for the tablespace again:
SQL> select file_id,file_name from dba_data_files where tablespace_name = ‘POOL_DATA’;
FILE_ID FILE_NAME ---------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 41 +DATA/mydb1/datafile/pool_data.736.935382959 42 +DATA/mydb1/datafile/pool_data_reorg.783.934705039 43 +DATA/mydb1/datafile/pool_data_reorg.658.934705043 44 +DATA/mydb1/datafile/pool_data_reorg.735.934705271 45 +DATA/mydb1/datafile/pool_data_reorg.734.934705525 46 +DATA/mydb1/datafile/pool_data_reorg.732.934705785 47 +DATA/mydb1/datafile/pool_data_reorg.731.934706037 48 +DATA/mydb1/datafile/pool_data_reorg.730.934706289 49 +DATA/mydb1/datafile/pool_data_reorg.729.934706505
Ah that looks like one down ( one correct 8 More to go).
alter tablespace POOL_DATA offline;
Proceeded with the remaining 8 Datafiles and when doing the rename step it was a good thing to see this below information in the Alert log:
Wed Feb 08 06:00:34 2017 alter database rename file '+DATA/mydb1/datafile/pool_data_reorg.783.934705039' to '+DATA/mydb1/datafile/pool_data.780.935384353' Deleted Oracle managed file +DATA/mydb1/datafile/pool_data_reorg.783.934705039 Completed: alter database rename file '+DATA/mydb1/datafile/pool_data_reorg.783.934705039' to '+DATA/mydb1/datafile/pool_data.780.935384353' alter database rename file '+DATA/mydb1/datafile/pool_data_reorg.658.934705043' to '+DATA/mydb1/datafile/pool_data.755.935385209' Deleted Oracle managed file +DATA/mydb1/datafile/pool_data_reorg.658.934705043 Completed: alter database rename file '+DATA/mydb1/datafile/pool_data_reorg.658.934705043' to '+DATA/mydb1/datafile/pool_data.755.935385209' alter database rename file '+DATA/mydb1/datafile/pool_data_reorg.735.934705271' to '+DATA/mydb1/datafile/pool_data.778.935384371' Wed Feb 08 06:00:48 2017 Deleted Oracle managed file +DATA/mydb1/datafile/pool_data_reorg.735.934705271 Completed: alter database rename file '+DATA/mydb1/datafile/pool_data_reorg.735.934705271' to '+DATA/mydb1/datafile/pool_data.778.935384371' alter database rename file '+DATA/mydb1/datafile/pool_data_reorg.734.934705525' to '+DATA/mydb1/datafile/pool_data.781.935385215' Deleted Oracle managed file +DATA/mydb1/datafile/pool_data_reorg.734.934705525 Completed: alter database rename file '+DATA/mydb1/datafile/pool_data_reorg.734.934705525' to '+DATA/mydb1/datafile/pool_data.781.935385215' alter database rename file '+DATA/mydb1/datafile/pool_data_reorg.730.934706289' to '+DATA/mydb1/datafile/pool_data.762.935386865' Deleted Oracle managed file +DATA/mydb1/datafile/pool_data_reorg.730.934706289 Completed: alter database rename file '+DATA/mydb1/datafile/pool_data_reorg.730.934706289' to '+DATA/mydb1/datafile/pool_data.762.935386865' alter database rename file '+DATA/mydb1/datafile/pool_data_reorg.729.934706505' to '+DATA/mydb1/datafile/pool_data.772.935386887' Deleted Oracle managed file +DATA/mydb1/datafile/pool_data_reorg.729.934706505 Completed: alter database rename file '+DATA/mydb1/datafile/pool_data_reorg.729.934706505' to '+DATA/mydb1/datafile/pool_data.772.935386887' Wed Feb 08 06:01:30 2017 alter database rename file '+DATA/mydb1/datafile/pool_data_reorg.732.934705785' to '+DATA/mydb1/datafile/pool_data.744.935386243' Deleted Oracle managed file +DATA/mydb1/datafile/pool_data_reorg.732.934705785 Completed: alter database rename file '+DATA/mydb1/datafile/pool_data_reorg.732.934705785' to '+DATA/mydb1/datafile/pool_data.744.935386243'
All looked well so time to wrap up.
SQL> alter tablespace POOL_DATA online;
SQL> alter database backup controlfile to trace;
SQL> select file_id,file_name from dba_data_files where tablespace_name = ‘POOL_DATA’;
FILE_ID FILE_NAME ---------------- 41 +DATA/mydb1/datafile/pool_data.736.935382959 42 +DATA/mydb1/datafile/pool_data.780.935384353 43 +DATA/mydb1/datafile/pool_data.755.935385209 44 +DATA/mydb1/datafile/pool_data.778.935384371 45 +DATA/mydb1/datafile/pool_data.781.935385215 46 +DATA/mydb1/datafile/pool_data.744.935386243 47 +DATA/mydb1/datafile/pool_data.732.935388425 48 +DATA/mydb1/datafile/pool_data.762.935386865 49 +DATA/mydb1/datafile/pool_data.772.935386887
Final check:
SQL> select TABLESPACE_NAME,status from dba_tablespaces order by 1 ;
TABLESPACE_NAME STATUS ------------------------------ --------- POOL_DATA ONLINE POOL_IX ONLINE SYSAUX ONLINE SYSTEM ONLINE TEMP ONLINE TEST2 ONLINE TOOLS ONLINE UNDOTBS1 ONLINE
As always hope you had some happy reading this procedure.
Mathijs.
Mathijs Bruggink 