DBA Tips Archive for Oracle

Moving ASM Database Files from one Diskgroup to Another

by Jeff Hunter, Sr. Database Administrator

Contents

• Introduction
• Configuration
• Steps Required to Move ASM Files
• About the Author

Introduction

There are times when it becomes necessary to move Oracle ASM database files from one diskgroup to another. As with most tasks that involve file management in Oracle 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 Oracle ASM files from one diskgroup to another using RMAN.

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	Oracle Database 10g 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 Oracle ASM database file from one diskgroup to another.

1. The first step is to identify the Oracle ASM database file(s) that needs to be moved. In this guide, a single file (+TESTDB_DATA2/testdb/datafile/app_data.256.598127837) will be moved from one disk group to another disk group.

   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 Oracle ASM diskgroup to which the database file will be moved to. There are a total of three ASM diskgroups defined for the example database. For the purpose of this guide, the previously identified database file will be moved from the +TESTDB_DATA2 diskgroup to the +TESTDB_DATA1 diskgroup.

   SQL> SELECT name FROM v$asm_diskgroup; NAME -------------------- TESTDB_DATA1 TESTDB_DATA2 FLASH_RECOVERY_AREA

3. Take the Oracle ASM data file to be moved OFFLINE.

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

4. Make a copy the Oracle 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 - (preferred)
   • Using the COPY_FILE procedure of the DBMS_FILE_TRANSFER PL/SQL Package

   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

   After successfully executing the RMAN statement above, you will now have two copies of the Oracle ASM database file.

   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/DATAFILE/APP_DATA.264.598128765 524,296,192 526,385,152 DATAFILE 10-AUG-2006 18:52:44 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,780,845,056 4,824,498,176 +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: 5,346,479,616 5,392,826,368 16 rows selected.

   Thank you to Stephan Uzzell for the following tip. For non-OMF files, you will need to include the directory and file name when declaring the destination in the COPY DATAFILE statement. For example. RMAN> COPY DATAFILE '+TESTDB_DATA2/testdb/datafile/appdata02.dbf' TO '+TESTDB_DATA1/testdb/datafile/appdata02.dbf'; Failure to include the file name and directory for the destination while copying a non-OMF will result in the following error. RMAN> COPY DATAFILE '+TESTDB_DATA2/testdb/datafile/appdata02.dbf' TO '+TESTDB_DATA1'; RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03009: failure of backup command on ORA_DISK_1 channel at 07/14/2009 13:48:38 ORA-19504: failed to create file "+TESTDB_DATA1/testdb" ORA-17502: ksfdcre:3 Failed to create file +TESTDB_DATA1/testdb ORA-15113: alias name 'testdb' refers to a directory

5. Now that the file has been copied, update the Oracle data dictionary with the location of the new Oracle 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.

   After Oracle successfully renames the ASM database file in the data dictionary, it will remove the original ASM database file (+TESTDB_DATA2/testdb/datafile/app_data.256.598127837).

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.

    This step was not required for the example I provided in this guide. I believe (however not certain) that is related to the fact I am using Oracle Database 10g Release 2. This may be a step that is required with Oracle Database 10g Release 1, however, I did not have a spare Oracle Database 10g Release 1 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 guide, you will remember that the original ASM database file gets removed during the ALTER DATABASE RENAME FILE statement.

    If this step needs to be performed, it will need to be executed from the actual Oracle ASM instance.

    $ ORACLE_SID=+ASM; export ORACLE_SID $ sqlplus "/ as sysdba" SQL> ALTER DISKGROUP TESTDB_DATA2 DROP FILE '+TESTDB_DATA2/testdb/datafile/app_data.256.598127837';

About the Author

Jeffrey Hunter is an Oracle Certified Professional, Java Development Certified Professional, Author, and an Oracle ACE. Jeff currently works as a Senior Database Administrator for The DBA Zone, Inc. located in Pittsburgh, Pennsylvania. His work includes advanced performance tuning, Java and PL/SQL programming, developing high availability solutions, capacity planning, database security, and physical / logical database design in a UNIX, Linux, and Windows server 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. He has been a Sr. Database Administrator and Software Engineer for over 18 years and maintains his own website site at: http://www.iDevelopment.info. Jeff graduated from Stanislaus State University in Turlock, California, with a Bachelor's degree in Computer Science.