Moving Datafiles between diskgroups in ASM

Admin — Fri, 13 Mar 2009 11:27:04 GMT

You can follow these steps to move datafiles between diskgroups in a database that is using ASM.

I'll show you the steps for the system related tablespaces: System, UNDO, sysaux etc..

The reason for this is because with the other tablespace you can put them offline and use rman to copy and then bring them online. With SYSTEM/SYSAUX etc.. it's difficult to bring them offline. So let's begin:

Here are the files that I would like to have moved to diskgroup called : +SHARED_DATA_DG03 . Currently they are on diskgroup +SHARED_DATA_DG01.

SQL> col file_name format a65
SQL> col member format a65
SQL> select file_name from dba_data_files;

FILE_NAME
-----------------------------------------------------------------
+SHARED_DATA_DG01/PRODDB/datafile/system.260.681144809
+SHARED_DATA_DG01/PRODDB/datafile/undotbs1.261.681144817
+SHARED_DATA_DG01/PRODDB/datafile/sysaux.262.681144821
+SHARED_DATA_DG01/PRODDB/datafile/tools.264.681144827
+SHARED_DATA_DG01/PRODDB/datafile/users.265.681144827

I've already brought the database down using shutdown immediate command. Now, I'm going to bring it back up using mount.

prdadminsrv01 [PRODDB]-> sqlplus '/ as sysdba'

SQL*Plus: Release 10.2.0.3.0 - Production on Wed Mar 11 16:33:49 2009

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning and Data Mining options

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1.6106E+10 bytes
Fixed Size 2402240 bytes
Variable Size 2843793472 bytes
Database Buffers 1.3237E+10 bytes
Redo Buffers 22708224 bytes
Database mounted.
SQL> exit

Now I'm going into rman to copy the datafiles.

prdadminsrv01 [PRODDB]-> rman

Recovery Manager: Release 10.2.0.3.0 - Production on Wed Mar 11 16:31:03 2009

RMAN> connect target /

connected to target database: PRODDB (DBID=3179283874, not open)

RMAN>

RMAN> copy datafile '+SHARED_DATA_DG01/PRODDB/datafile/system.260.681144809' to '+SHARED_DATA_DG03';

Starting backup at 11-MAR-2009 16:32:24
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=71 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=+SHARED_DATA_DG01/PRODDB/datafile/system.260.681144809
output filename=+SHARED_DATA_DG03/PRODDB/datafile/system.265.681237145 tag=TAG20090311T163225 recid=1 stamp=681237155
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
Finished backup at 11-MAR-2009 16:32:40

RMAN> copy datafile '+SHARED_DATA_DG01/PRODDB/datafile/undotbs1.261.681144817' to '+SHARED_DATA_DG03';

Starting backup at 11-MAR-2009 16:32:42
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=+SHARED_DATA_DG01/PRODDB/datafile/undotbs1.261.681144817
output filename=+SHARED_DATA_DG03/PRODDB/datafile/undotbs1.266.681237163 tag=TAG20090311T163243 recid=2 stamp=681237176
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
Finished backup at 11-MAR-2009 16:32:58

RMAN> copy datafile '+SHARED_DATA_DG01/PRODDB/datafile/sysaux.262.681144821' to '+SHARED_DATA_DG03';

Starting backup at 11-MAR-2009 16:33:02
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=+SHARED_DATA_DG01/PRODDB/datafile/sysaux.262.681144821
output filename=+SHARED_DATA_DG03/PRODDB/datafile/sysaux.267.681237183 tag=TAG20090311T163302 recid=3 stamp=681237193
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
Finished backup at 11-MAR-2009 16:33:18

RMAN> copy datafile '+SHARED_DATA_DG01/PRODDB/datafile/tools.264.681144827' to '+SHARED_DATA_DG03';

Starting backup at 11-MAR-2009 16:33:20
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=+SHARED_DATA_DG01/PRODDB/datafile/tools.264.681144827
output filename=+SHARED_DATA_DG03/PRODDB/datafile/tools.268.681237201 tag=TAG20090311T163320 recid=4 stamp=681237203
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 11-MAR-2009 16:33:27

RMAN> copy datafile '+SHARED_DATA_DG01/PRODDB/datafile/users.265.681144827' to '+SHARED_DATA_DG03';

Starting backup at 11-MAR-2009 16:33:30
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile fno=00005 name=+SHARED_DATA_DG01/PRODDB/datafile/users.265.681144827
output filename=+SHARED_DATA_DG03/PRODDB/datafile/users.269.681237211 tag=TAG20090311T163330 recid=5 stamp=681237211
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 11-MAR-2009 16:33:33

RMAN> exit

Recovery Manager complete.

The next step is to issue the rename command to rename the files. This step you have to becareful because if you look at the rman command above, you will notice that rman gave a new name to the file. Look at the output filename= part and you will see that each datafile is given a new name. This is because I let oracle manage my files. Here is the step to rename the file.

prdadminsrv01 [PRODDB]-> sqlplus '/ as sysdba'

SQL*Plus: Release 10.2.0.3.0 - Production on Wed Mar 11 16:33:49 2009

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning and Data Mining options

SQL> alter database rename file '+SHARED_DATA_DG01/PRODDB/datafile/system.260.681144809' to '+SHARED_DATA_DG03/PRODDB/datafile/system.265.681237145';

Database altered.

SQL> alter database rename file '+SHARED_DATA_DG01/PRODDB/datafile/undotbs1.261.681144817' to '+SHARED_DATA_DG03/PRODDB/datafile/undotbs1.266.681237163';

Database altered.

SQL> alter database rename file '+SHARED_DATA_DG01/PRODDB/datafile/sysaux.262.681144821' to '+SHARED_DATA_DG03/PRODDB/datafile/sysaux.267.681237183';

Database altered.

SQL> alter database rename file '+SHARED_DATA_DG01/PRODDB/datafile/tools.264.681144827' to '+SHARED_DATA_DG03/PRODDB/datafile/tools.268.681237201';

Database altered.

SQL> alter database rename file '+SHARED_DATA_DG01/PRODDB/datafile/users.265.681144827' to '+SHARED_DATA_DG03/PRODDB/datafile/users.269.681237211';

Database altered.

SQL>

That's pretty much it. You can open your database now.

SQL> alter database open;

Database altered.

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
+SHARED_DATA_DG03/PRODDB/datafile/system.265.681237145
+SHARED_DATA_DG03/PRODDB/datafile/undotbs1.266.681237163
+SHARED_DATA_DG03/PRODDB/datafile/sysaux.267.681237183
+SHARED_DATA_DG03/PRODDB/datafile/tools.268.681237201
+SHARED_DATA_DG03/PRODDB/datafile/users.269.681237211

Hope you found this useful. There are many other ways that can be done. If you have done it differently feel free to share it with others.

Thanks,

FranklinFaces.com - Oracle & SQL Server Database Forums for all IT Professionals / Oracle Forum / Oracle Database Administration / Moving Datafiles between diskgroups in ASM / Latest Posts

Moving Datafiles between diskgroups in ASM