How to move ASM database files from one diskgroup to another
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 Either RMAN or DBMS_FILE_TRANSFER.
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
Two methods:
A) By Using RMAN
B) By using DBMS Package
Steps for Using RMAN
Step 1 Identify the data file to be moved.
SQL> SELECT FILE_NAME FROM DBA_DATA_FILES;
FILE_NAME
——————————————————————————–
+DB_DATA01/indian/datafile/users.263.745864691< Move this to DB_DATA02 Disk>
+DB_DATA01/indian/datafile/sysaux.266.745864681
+DB_DATA01/indian/datafile/undotbs1.264.745864689
+DB_DATA01/indian/datafile/system.260.745864679
+DB_DATA01/indian/datafile/example.256.745864813
Step 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 DB_DATA01
2 DB_DATA02
Step 3 Take the file offline.
SQL> alter database datafile ‘+DB_DATA01/indian/datafile/users.263.745864691’ offline;
Step 4 Copy the datafile to new diskgroup.
bash-3.00$ rman target /
Recovery Manager: Release 10.2.0.2.0 – Production on Tue Mar 15 16:53:33 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: INDIAN (DBID=4219585603)
RMAN> copy datafile ‘+DB_DATA01/indian/datafile/users.263.745864691’ to ‘+DB_DATA02’;
Starting backup at 15-MAR-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=151 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=+DB_DATA01/indian/datafile/users.263.745864691
output filename=+DB_DATA02/indian/datafile/users.256.745865655 tag=TAG20110315T165413 recid=2 stamp=745865658
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:08
Finished backup at 15-MAR-11
Important: Note Down Output file name
(Example: =+DB_DATA02/indian/datafile/users.256.745865655)
Step 5 Rename the file to point to new location
run {
set newname for datafile ‘+DB_DATA01/indian/datafile/users.263.745864691’ to
‘+DB_DATA02/INDIAN/DATAFILE/USERS.256.745865655’;
switch datafile all;
}
executing command: SET NEWNAME
using target database control file instead of recovery catalog
datafile 4 switched to datafile copy
input datafile copy recid=2 stamp=745865658 filename=+DB_DATA02/indian/datafile/users.256.745865655
Step 6 Recover the datafile
RMAN> recover datafile ‘+DB_DATA02/indian/datafile/users.256.745865655’;
Starting recover at 15-MAR-11
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:04
Finished recover at 15-MAR-11
Step 7 Online the Data file.
SQL> conn / as sysdba
Connected.
SQL> alter database datafile ‘+DB_DATA02/indian/datafile/users.256.745865655’ online;
Database altered.
Step 8 Verify the new file location.
SQL> SELECT FILE_NAME FROM DBA_DATA_FILES;
FILE_NAME
——————————————————————————–
+DB_DATA02/indian/datafile/users.256.745865655
+DB_DATA01/indian/datafile/sysaux.266.745864681
+DB_DATA01/indian/datafile/undotbs1.264.745864689
+DB_DATA01/indian/datafile/system.260.745864679
+DB_DATA01/indian/datafile/example.256.745864813
Step 9 Delete the file from its original location either per SQLPLUS or per ASMCMD.
e.g.: SQL > ALTER DISKGROUP ASMDSK1 DROP FILE
users.263.745864691;
or: ASMCMD> rm -rf
e.g.: SQL:ASM> ALTER DISKGROUP ASMDSK2 DROP FILE ‘+ASMDSK2/orcl/datafile/ users.263.745864691’;
Most Important:
The above method are not qualified for System and Sysaux data file For System and Sysaux an approach similar to the one given below can be used:
Step 1 Create a Copy of datafile in target Diskgroup:
RMAN> backup as copy tablespace system format ‘
Step 2 Then shutdown the database and restart to a mounted state
RMAN> shutdown immediate;
RMAN> startup mount;
Step 3 switch the datafiles to the copy
RMAN> switch tablespace system to copy;
RMAN> switch tablespace sysaux to copy;
Step 4 Recover the changes made to these tablespaces;
RMAN> recover database;
Steps by Using DBMS Package
Step 1 Identify the data file to be moved.
SQL> SELECT FILE_NAME FROM DBA_DATA_FILES;
FILE_NAME
——————————————————————————–
+DB_DATA01/indian/datafile/users.257.745869937< Move this to DB_DATA02 Disk>
+DB_DATA01/indian/datafile/sysaux.259.745869925
+DB_DATA01/indian/datafile/undotbs1.258.745869935
+DB_DATA01/indian/datafile/system.262.745869923
+DB_DATA01/indian/datafile/example.260.745870065
Step 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 DB_DATA01
2 DB_DATA02
Step 3 Take the file offline.
SQL> alter database datafile ‘+DB_DATA01/indian/datafile/users.257.745869937‘ offline;
Step 4 copy Data file from one disk group to another disk group
Connect to SQL PLUS and Create Source Directory:
create or replace directory ORACLE1 as ‘+DB_DATA01/INDIAN/DATAFILE’;
Connect to ASM SQL Plus and add directory to Destination Diskgroup:
Alter diskgroup DB_DATA02 add directory ‘+DB_DATA02/INDIAN/DATAFILE’;
Connect to SQL PLUS and Destination Directory:
create or replace directory ORACLE2 as ‘+DB_DATA02/INDIAN/DATAFILE’;
Execute DBMS_FILE_TRANSFER package:
BEGIN
DBMS_FILE_TRANSFER.COPY_FILE(
source_directory_object => ‘ORACLE1’,
source_file_name => ‘ users.257.745869937‘,
destination_directory_object => ‘ORACLE2’,
destination_file_name => ‘USERS01.DBF’);
END;
DBMS_FILE_TRANSFER.COPY_FILE(
source_directory_object => ‘ORACLE1’,
source_file_name => ‘ users.257.745869937‘,
destination_directory_object => ‘ORACLE2’,
destination_file_name => ‘USERS01.DBF’);
END;
Step 5 Rename the file to point to new location.
ALTER DATABASE RENAME FILE ‘+DB_DATA01/indian/datafile/users.257.745869937‘ TO ‘+DB_DATA02/Indian/datafile/ USERS01.DBF’
Step 6 recover datafile.
recover datafile ‘+DB_DATA02/indian/datafile/users01.dbf’;
Step 7
alree database datafile ‘+DB_DATA02/indian/datafile/users01.dbf’ online;
Step 8 Verify the new file location.
SQL> SELECT FILE_NAME FROM DBA_DATA_FILES;
FILE_NAME
——————————————————————————–
+DB_DATA02/indian/datafile/users.256.745865655
+DB_DATA01/indian/datafile/sysaux.266.745864681
+DB_DATA01/indian/datafile/undotbs1.264.745864689
+DB_DATA01/indian/datafile/system.260.745864679
+DB_DATA01/indian/datafile/example.256.745864813
Responses