http://www.idevelopment.info/data/Oracle/DBA_tips/Automatic_Storage_Management/ASM_36.shtml 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 Copyright (c) 1982, 2006, Oracle. All Rights Reserved. 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 Copyright (c) 1982, 2005, Oracle. All rights reserved. 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 Copyright (c) 1982, 2006, Oracle. All Rights Reserved. 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. |
|
|