How To Move Datafiles From One ASM Diskgroup To Another

The preferred way of doing the file movement among ASM DISKGROUPS is using RMAN. RMAN is critical to Automatic Storage Management and is responsible for tracking the ASM filenames and for deleting obsolete ASM files. Since ASM files cannot be accessed through normal operating system interfaces, RMAN is the preferred means of copying ASM file.


Move Non SYSTEM/SYSAUX datafiles to another ASM diskgroup

The steps involved in moving a datafile from a diskgroup to another is as given below.


1) Identify the data file to be moved.
2) Identify the diskgroup on to which the file has to be moved.
3) Take the file offline.
4) Copy the file to new diskgroup using RMAN.
5) Rename the file to point to new location.
6) Recover the file.
7) Bring the file online.
8) Verify the new file locations.
9) Delete the file from its original location.


1) Identify the data file to be moved.

Run the following command on the database instance

SQL> SELECT FILE_NAME FROM DBA_DATA_FILES;

FILE_NAME
--------------------------------------------------------------------------------
+DATA/rac/datafile/users.259.824552199
+DATA/rac/datafile/undotbs1.258.824552199
+DATA/rac/datafile/sysaux.257.824552199
+DATA/rac/datafile/system.256.824552197

2) Identify the diskgroup on to which the file has to be moved.

SQL> SELECT GROUP_NUMBER, NAME FROM V$ASM_DISKGROUP;

GROUP_NUMBER NAME
------------ ------------------------------
1 DATA
2 FRA

3) Take the file offline.

SQL> ALTER DATABASE DATAFILE '+DATA/rac/datafile/users.259.824552199' OFFLINE;

Database altered.

4) Copy the file from Source diskgroup ASMDSK1 to target Diskgroup ASMDSK2.

[oracle@rac1 ~]$ rman target /

RMAN> COPY DATAFILE '+DATA/rac/datafile/users.259.824552199' TO '+FRA';

Starting backup at 29-AUG-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=49 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=+DATA/rac/datafile/users.259.824552199
output file name=+FRA/rac/datafile/users.257.824724469 tag=TAG20130829T100724 RECID=1 STAMP=824724473
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:18
Finished backup at 29-AUG-13

5) Rename the file to point to new location.

Get the copied datafile name with asmcmd

[oracle@rac1 ~]$ asmcmd
ASMCMD> cd FRA/rac/datafile
ASMCMD> pwd
+FRA/rac/datafile
ASMCMD> ls
USERS.257.824724469
ASMCMD>

So we found the new datafile name is USERS.257.824724469

Now run the rename command in RMAN

RMAN> run {
set newname for datafile '+DATA/rac/datafile/users.259.824552199' to '+FRA/rac/datafile/users.257.824724469' ;
switch datafile all;
}2> 3> 4>

executing command: SET NEWNAME

datafile 4 switched to datafile copy
input datafile copy RECID=1 STAMP=824724473 file name=+FRA/rac/datafile/users.257.824724469

6) Recover the file.

SQL> RECOVER DATAFILE '+FRA/rac/datafile/users.257.824724469';
Media recovery complete.

7) Bring the datafile online.

SQL> ALTER DATABASE DATAFILE '+FRA/rac/datafile/users.257.824724469' ONLINE;

Database altered.


8) Verify the new file location.

SQL> SELECT FILE_NAME FROM DBA_DATA_FILES;

FILE_NAME
--------------------------------------------------------------------------------
+FRA/rac/datafile/users.257.824724469
+DATA/rac/datafile/undotbs1.258.824552199
+DATA/rac/datafile/sysaux.257.824552199
+DATA/rac/datafile/system.256.824552197

9) Delete the file from its original location either per SQLPLUS or per ASMCMD

ASMCMD> cd +DATA/rac/datafile
ASMCMD> ls
SYSAUX.257.824552199
SYSTEM.256.824552197
UNDOTBS1.258.824552199
USERS.259.824552199
ASMCMD>
ASMCMD> rm -rf USERS.259.824552199
ASMCMD> ls
SYSAUX.257.824552199
SYSTEM.256.824552197
UNDOTBS1.258.824552199
ASMCMD>




Move SYSTEM and SYSAUX datafiles to another ASM diskgroup
For System and SYSAUX an approach similar to the one given below can be used

1) Create a Copy of datafile in target Diskgroup:

RMAN> backup as copy tablespace sysaux format '+FRA';

Starting backup at 29-AUG-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=+DATA/rac/datafile/sysaux.257.824552199
output file name=+FRA/rac/datafile/sysaux.258.824725695 tag=TAG20130829T102810 RECID=3 STAMP=824725742
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:56
Finished backup at 29-AUG-13

2) Then shutdown the database and restart to a mounted state

RMAN> shutdown immediate;

database closed
database dismounted
Oracle instance shut down

RMAN> startup mount;

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area 400846848 bytes

Fixed Size 2213776 bytes
Variable Size 159385712 bytes
Database Buffers 234881024 bytes
Redo Buffers 4366336 bytes

3) Switch the datafiles to the copy

RMAN> switch tablespace sysaux to copy;

datafile 2 switched to datafile copy "+FRA/rac/datafile/sysaux.258.824725695"

4) Recover the changes made to these tablespaces

RMAN> recover database;

Starting recover at 29-AUG-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK

starting media recovery
media recovery complete, elapsed time: 00:00:02

Finished recover at 29-AUG-13

RMAN>

5) Open the database

RMAN> sql 'alter database open';

sql statement: alter database open

RMAN>

6) Verify the new file location.

SQL> SELECT FILE_NAME FROM DBA_DATA_FILES;

FILE_NAME
--------------------------------------------------------------------------------
+FRA/rac/datafile/users.257.824724469
+DATA/rac/datafile/undotbs1.258.824552199
+FRA/rac/datafile/sysaux.258.824725695
+DATA/rac/datafile/system.256.824552197