Reprinted with Permission by Quest Software Feb. 2007

Moving ASM Database Files from one Diskgroup to Another
by Jeff Hunter, The DBA Zone, Inc

Overview

There are times when it becomes necessary to move ASM database files from one diskgroup to another. As with most tasks that involve file management in ASM, RMAN is the easiest and preferred method as ASM files cannot be accessed through normal operating system interfaces. In this article, I will detail the steps required to move ASM files from one diskgroup to another using RMAN.

Current Configuration

The steps used in this article assume the following configuration:

 
Oracle ASM Configuration
Machine Name: linux3.idevelopment.info
Oracle SID: TESTDB
Database Name: TESTDB
ASM Disk Groups: +TESTDB_DATA1
+TESTDB_DATA2
+FLASH_RECOVERY_AREA
Operating System: Red Hat Linux 3 - (CentOS 3.4)
Oracle Release: Oracle10g Release 2 - (10.2.0.2.0)
  This article assumes the database is open and in ARCHIVELOG mode: 
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     15
Next log sequence to archive   17
Current log sequence           18

The following query lists the available ASM diskgroups and the database files they contain: 

$ ORACLE_SID=+ASM; export ORACLE_SID
$ sqlplus "/ as sysdba"

SQL> @asm_files
                                                                                                                       System
File Name                                                       Bytes          Space File Type    Creation Date        Created?
---------------------------------------------------- ---------------- -------------- ------------ -------------------- --------
+TESTDB_DATA1/TESTDB/DATAFILE/USERS.257.598066465       2,382,897,152  2,384,461,824     DATAFILE 10-AUG-2006 01:34:25    Y
+TESTDB_DATA1/TESTDB/DATAFILE/SYSTEM.258.598066917        608,182,272    610,271,232     DATAFILE 10-AUG-2006 01:41:56    Y
+TESTDB_DATA1/TESTDB/DATAFILE/SYSAUX.259.598067031        419,438,592    421,527,552     DATAFILE 10-AUG-2006 01:43:51    Y
+TESTDB_DATA1/TESTDB/DATAFILE/UNDOTBS1.260.598067119      209,723,392    211,812,352     DATAFILE 10-AUG-2006 01:45:18    Y
+TESTDB_DATA1/TESTDB/DATAFILE/EXAMPLE.261.598067163       157,294,592    159,383,552     DATAFILE 10-AUG-2006 01:46:03    Y
+TESTDB_DATA1/TESTDB/DATAFILE/APEX22.262.598067197        104,865,792    106,954,752     DATAFILE 10-AUG-2006 01:46:37    Y
+TESTDB_DATA1/TESTDB/DATAFILE/FLOW_1.263.598067223         52,502,528     53,477,376     DATAFILE 10-AUG-2006 01:47:03    Y
+TESTDB_DATA1/TESTDB/ONLINELOG/group_1.267.598068145       52,429,312     58,720,256    ONLINELOG 10-AUG-2006 02:02:25    Y
+TESTDB_DATA1/TESTDB/ONLINELOG/group_4.268.598068321       52,429,312     58,720,256    ONLINELOG 10-AUG-2006 02:05:20    Y
+TESTDB_DATA1/TESTDB/ONLINELOG/group_2.269.598068455       52,429,312     58,720,256    ONLINELOG 10-AUG-2006 02:07:34    Y
+TESTDB_DATA1/TESTDB/ONLINELOG/group_3.270.598068475       52,429,312     58,720,256    ONLINELOG 10-AUG-2006 02:07:55    Y
+TESTDB_DATA1/TESTDB/CONTROLFILE/backup.256.598066421       7,061,504      8,388,608  CONTROLFILE 10-AUG-2006 01:33:40    Y
+TESTDB_DATA1/TESTDB/TEMPFILE/TEMP.266.598067899          104,865,792    106,954,752     TEMPFILE 10-AUG-2006 01:58:18    Y
                                                     ---------------- --------------
                                                        4,256,548,864  4,298,113,024

+TESTDB_DATA2/TESTDB/DATAFILE/APP_DATA.256.598127837      524,296,192    526,385,152     DATAFILE 10-AUG-2006 18:37:17    Y
                                                     ---------------- --------------
                                                          524,296,192    526,385,152

+FLASH_RECOVERY_AREA/TESTDB/ARCHIVELOG/2006_08_10/th       41,338,368     41,943,040   ARCHIVELOG 10-AUG-2006 18:39:22    Y
read_1_seq_17.260.598127963
                                                     ---------------- --------------
                                                           41,338,368     41,943,040

                                                     ---------------- --------------
Grand Total:                                            4,822,183,424  4,866,441,216

15 rows selected.

Steps Required to Move ASM Files

Now let's take a look at the steps used to move an ASM database file from one diskgroup to another:

  1. Identify the ASM data files to move

    The first step is to identify the ASM database file that needs to be moved. For the purpose of this example, I want to move file 

    +TESTDB_DATA2/testdb/datafile/app_data.256.598127837 

SQL> SELECT file_name FROM dba_data_files;

FILE_NAME
----------------------------------------------------
+TESTDB_DATA2/testdb/datafile/app_data.256.598127837  <-- (Move this file to +TESTDB_DATA1) 
+TESTDB_DATA1/testdb/datafile/system.258.598066917
+TESTDB_DATA1/testdb/datafile/undotbs1.260.598067119
+TESTDB_DATA1/testdb/datafile/sysaux.259.598067031
+TESTDB_DATA1/testdb/datafile/example.261.598067163
+TESTDB_DATA1/testdb/datafile/users.257.598066465
+TESTDB_DATA1/testdb/datafile/apex22.262.598067197
+TESTDB_DATA1/testdb/datafile/flow_1.263.598067223

8 rows selected.

  2. Identify the ASM diskgroup to which the database file will be moved to

    I have a total of three ASM diskgroups defined:

    SQL> SELECT name FROM v$asm_diskgroup;

NAME
--------------------
TESTDB_DATA1
TESTDB_DATA2
FLASH_RECOVERY_AREA

    For the purpose of this article, I want to move the previously identified ASM database file from the +TESTDB_DATA2 diskgroup to the +TESTDB_DATA1 diskgroup.

  3. Take the ASM data file to be moved OFFLINE

    Take the file to be moved OFFLINE:

    SQL> ALTER DATABASE DATAFILE '+TESTDB_DATA2/testdb/datafile/app_data.256.598127837' OFFLINE;

Database altered.

  4. Copy the ASM database file from the source diskgroup to target diskgroup

    In this step, we need to make a copy of the ASM database file to be moved. There are two methods that can be used to perform the copy operation; however, I will only cover the RMAN method:

    RMAN Method

    $ rman target /

connected to target database: TESTDB (DBID=2370649665)

RMAN> COPY DATAFILE '+TESTDB_DATA2/testdb/datafile/app_data.256.598127837' TO '+TESTDB_DATA1';

Starting backup at 10-AUG-06
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=130 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00008 name=+TESTDB_DATA2/testdb/datafile/app_data.256.598127837
output filename=+TESTDB_DATA1/testdb/datafile/app_data.264.598128765 tag=TAG20060810T185244 recid=18 stamp=598128889
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:02:06
Finished backup at 10-AUG-06

  5. Rename the ASM database file to point to the new location

    Now that the file has been copied, we can update the Oracle data dictionary with the location of the new ASM database file to use:

    SQL> ALTER DATABASE RENAME FILE
  2     '+TESTDB_DATA2/testdb/datafile/app_data.256.598127837'
  3  TO '+TESTDB_DATA1/testdb/datafile/app_data.264.598128765';

Database altered.

  6. Use RMAN to rename the ASM database file copy

    RMAN> SWITCH DATAFILE '+TESTDB_DATA1/testdb/datafile/app_data.264.598128765' TO COPY;

datafile 8 switched to datafile copy "+TESTDB_DATA1/testdb/datafile/app_data.264.598128765"

  7. Recovery the new ASM database file 

    SQL> RECOVER DATAFILE '+TESTDB_DATA1/testdb/datafile/app_data.264.598128765';

Media recovery complete.

  8. Bring the new ASM database file ONLINE

    SQL> ALTER DATABASE DATAFILE '+TESTDB_DATA1/testdb/datafile/app_data.264.598128765' ONLINE;

Database altered.

  9. Verify the new ASM data file location

    SQL> SELECT file_name FROM dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
+TESTDB_DATA1/testdb/datafile/app_data.264.598128765
+TESTDB_DATA1/testdb/datafile/system.258.598066917
+TESTDB_DATA1/testdb/datafile/undotbs1.260.598067119
+TESTDB_DATA1/testdb/datafile/sysaux.259.598067031
+TESTDB_DATA1/testdb/datafile/example.261.598067163
+TESTDB_DATA1/testdb/datafile/users.257.598066465
+TESTDB_DATA1/testdb/datafile/apex22.262.598067197
+TESTDB_DATA1/testdb/datafile/flow_1.263.598067223

8 rows selected.

  10. Delete the old ASM database file from its original location

    NOTE: This step was not required for the example I provided in this article. I believe (however not certain) that is related to the fact I am using Oracle Oracle10g R2. This may be a step that is required with Oracleg R1; however, I did not have a spare Oracleg R1 install sitting around to test this. I saw this step documented by Oracle; however, I believe it is not required. If you followed the steps in this article, you will remember that the original ASM database file gets removed during the ALTER DATABASE RENAME FILE statement.

    Please note that if this step needs to be performed, it will need to be executed from the actual ASM instance itself:

    $ ORACLE_SID=+ASM; export ORACLE_SID

$ sqlplus "/ as sysdba"

SQL> ALTER DISKGROUP TESTDB_DATA2 DROP FILE '+TESTDB_DATA2/testdb/datafile/app_data.256.598127837';
 

Jeffrey Hunter graduated from Stanislaus State University in Turlock, California, with a Bachelor's degree in Computer Science. Jeff is an Oracle Certified Professional, Java Development Certified Professional, Author, and currently works as a Senior Database Administrator for The DBA Zone, Inc. His work includes advanced performance tuning, Java programming, capacity planning, database security, and physical / logical database design in a UNIX, Linux, and Windows NT environment. Jeff's other interests include mathematical encryption theory, programming language processors (compilers and interpreters) in Java and C, LDAP, writing web-based database administration tools, and of course Linux. Jeff has been a Sr. Database Administrator and Software Engineer for over 11 years and maintains his own website site at: http://www.iDevelopment.info.