Steps to Move ASM DATABASE FILES from ONE DISKGROUP TO ANOTHER

Since ASM files cannot be accessed through normal operating system interfaces, RMAN is the preferred means of copying ASM file.

The steps to moving a datafile from a diskgroup to another is as below, using RMAN.

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

1) Identify the datafile to be moved.

SQL> SELECT FILE_NAME FROM DBA_DATA_FILES;
+ASMDISK2/orcl/datafile/users.256.565313879 <======= Move this to ASMDISK1.
+ASMDISK1/orcl/sysaux01.dbf
+ASMDISK1/orcl/undotbs01.dbf
+ASMDISK1/orcl/system01.dbf

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

SQL> SELECT GROUP_NUMBER, NAME FROM V$ASM_DISKGROUP;
GROUP_NUMBER NAME
------------ ---------
1 ASMDISK1
2 ASMDISK2

3) Take the datafile offline.
SQL> ALTER DATABASE DATAFILE '+ASMDISK2/orcl/datafile/users.256.565313879' OFFLINE;

4) Copy the datafile to new diskgroup using Either RMAN or DBMS_FILE_TRANSFER.

   a)   DBMS_FILE_TRANSFER package or
   b)   RMAN
     
a).Using DBMS_FILE_TRANSFER package
         
SQL> create or replace directory orcl1 as '+ASMDISK1/orcl/datafile';

SQL> Alter disgroup ASMDISK2 add directory  '+ASMDISK2/test';
     
SQL> create or replace directory orcl2 as '+ASMDISK2/test';

SQL>
BEGIN
     DBMS_FILE_TRANSFER.COPY_FILE(
     source_directory_object => 'ORCL1',
     source_file_name => 'users.259.565359071',
     destination_directory_object => 'ORCL2',
     destination_file_name => 'USERS01.DBF');
END; 
Database altered.

--------------------  OR   --------------------
 
b).Using RMAN copy the file to new diskgroup.

$ rman target /

connected to target database: ORCL (DBID=1020304050)

RMAN> COPY DATAFILE '+ASMDISK2/orcl/datafile/users.256.565313879' TO '+ASMDISK1';

Starting backup at 03-AUG-98
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=146 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=+ASMDISK2/orcl/datafile/users.256.565313879
output filename=+ASMDISK1/orcl/datafile/users.259.565359071 tag=TAG19980803T12110
9 recid=2 stamp=565359071
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 03-AUG-98

5) Rename the datafile to point to new location.

If you have used DBMS_FILE_TRANSFER (method 4 a)) use the following command to rename:
SQL> ALTER DATABASE RENAME FILE '+ASMDISK2/orcl/datafile/users.256.565313879' TO                                 

'+ASMDISK1/orcl/datafile/users.259.565359071';

Database altered.

If you have used RMAN (method 4 b) use the following option of RMAN
RMAN>
run
{
set newname for datafile '+ASMDISK2/orcl/datafile/users.256.565313879'
to '+ASMDISK1/orcl/datafile/users.259.565359071';
switch datafile all;
}

6) Recover the datafile.

SQL> RECOVER DATAFILE '+ASMDISK1/orcl/datafile/users.259.565359071';
Media recovery complete.

7) Bring the datafile online.

SQL> ALTER DATABASE DATAFILE '+ASMDISK1/orcl/datafile/users.259.565359071' ONLINE;
Database altered.

8) Verify the new datafile locations.

SQL> SELECT FILE_NAME FROM DBA_DATA_FILES;

FILE_NAME
-------------------------------------------------------------------------------
+ASMDISK1/orcl/datafile/users.259.565359071
+ASMDISK1/orcl/sysaux01.dbf
+ASMDISK1/orcl/undotbs01.dbf
+ASMDISK1/orcl/system01.dbf

9) Delete the datafile from its original location.

SQL> ALTER DISKGROUP ASMDISK2 DROP FILE users.256.565313879;
or
ASMCMD> rm -rf <filename>


Note:-
====

The steps provided above assume that the database is open and in Archivelog mode.
Besides these steps are not appropriated for system or sysaux datafiles.

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 system format '<New DG>';
RMAN> backup as copy tablespace sysaux format '<New DG>';

2. Then shutdown the database and restart to a mounted state
RMAN> shutdown immediate;
RMAN> startup mount;

3. switch the datafiles to the copy
RMAN> switch tablespace system to copy;
RMAN> switch tablespace sysaux to copy;

4. Recover the changes made to these tablespaces
RMAN> recover database;

Note:-
====

Most ASM files do not need to be manually deleted because, as Oracle managed files, they are removed automatically

when they are no longer needed.

However, if you need to drop an Oracle Managed File (OMF) manually you should use the fully qualified filename if

you reference the file. Otherwise you will get an error (e.g. ORA-15177).

ALTER DISKGROUP ASMDISK2 DROP FILE '+ASMDISK2/orcl/datafile/users.256.565313879';

Comments

Popular posts from this blog

Step by Step: How to troubleshoot a slow running query in Oracle

Register Archive log file manually in standby Database

How to check UNDO tablespace usage and who is using more undo