Renaming datafiles in Oracle 11GR2 Asm

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.

 

 

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s