Moving Asm Database Files From One Diskgroup To Another

Moving ASM Database Files from one Diskgroup to Another by Jeff Hunter, Sr. Database Administrator

Contents 1. Overview 2. Current Configuration 3. Steps Required to Move ASM Files

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 Automatic archival Archive destination Oldest online log sequence Next log sequence to archive Current log sequence

Archive Mode Enabled USE_DB_RECOVERY_FILE_DEST 15 17 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 526,385,152 DATAFILE 10-AUG-2006 18:37:17 Y -------------526,385,152 +FLASH_RECOVERY_AREA/TESTDB/ARCHIVELOG/2006_08_10/th 41,943,040 ARCHIVELOG 10-AUG-2006 18:39:22 Y read_1_seq_17.260.598127963 -------------41,943,040

524,296,192 ---------------524,296,192 41,338,368 ---------------41,338,368 ----------------

-------------Grand Total: 4,866,441,216 15 rows selected.

4,822,183,424

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 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

<-- (Move this

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: o o

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 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 526,385,152 DATAFILE 10-AUG-2006 18:37:17 Y -------------526,385,152 +FLASH_RECOVERY_AREA/TESTDB/ARCHIVELOG/2006_08_10/th 41,943,040 ARCHIVELOG 10-AUG-2006 18:39:22 Y read_1_seq_17.260.598127963 -------------41,943,040

524,296,192 ---------------524,296,192 41,338,368 ---------------41,338,368 ----------------

-------------Grand Total: 5,392,826,368

5,346,479,616

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.

After Oracle successfully renames the ASM database file in the data dictionary, it will remove th original ASM database file - "+TESTDB_DATA2/testdb/datafile/app_data.256.598127837 6. Use RMAN to rename the ASM database file copy 7. RMAN> SWITCH DATAFILE

'+TESTDB_DATA1/testdb/datafile/app_data.264.598128765' TO COPY;

8.

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

9. Recovery the new ASM database file 10. SQL> RECOVER DATAFILE

'+TESTDB_DATA1/testdb/datafile/app_data.264.598128765';

11.

Media recovery complete.

12. Bring the new ASM database file ONLINE 13. SQL> ALTER DATABASE DATAFILE '+TESTDB_DATA1/testdb/datafile/app_data.264.598128765' ONLINE;

14.

Database altered.

15. Verify the new ASM data file location 16. SQL> SELECT file_name FROM dba_data_files;

17. 18.FILE_NAME 19.------------------------------------------------------------------------------20.+TESTDB_DATA1/testdb/datafile/app_data.264.598128765 21.+TESTDB_DATA1/testdb/datafile/system.258.598066917 22.+TESTDB_DATA1/testdb/datafile/undotbs1.260.598067119 23.+TESTDB_DATA1/testdb/datafile/sysaux.259.598067031 24.+TESTDB_DATA1/testdb/datafile/example.261.598067163 25.+TESTDB_DATA1/testdb/datafile/users.257.598066465 26.+TESTDB_DATA1/testdb/datafile/apex22.262.598067197 27.+TESTDB_DATA1/testdb/datafile/flow_1.263.598067223 28. 8 rows selected.

29. 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';