Moving ASM Database Files from one Diskgroup to Another

posted Mar 16, 2011, 6:17 AM by Sachchida Ojha
1. Identify the data files to move.

$sqlplus / as sysdba

SQL*Plus: Release Production on Wed Mar 16 13:19:38 2011

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release - 64bit Production
With the Partitioning and Automatic Storage Management options

SQL> select file_name FROM dba_data_files;



21 rows selected.

2. Identify the diskgroup where you need to move the data file.

SQL>  SELECT name FROM v$asm_diskgroup;


Let's move  +DEV_DATA/stgrptdb/datafile/ts_voipdm_data.442.680445909 from DEV_DATA to QA_FLASH.

3. Take the file to be moved OFFLINE:

SQL> ALTER DATABASE DATAFILE '+DEV_DATA/stgrptdb/datafile/ts_voipdm_data.442.680445909' OFFLINE;

Database altered.

4. Copy the data file from source diskgroup to destination diskgroup using RMAN

$ rman target /

connected to target database: STGRPTDB (DBID=2370649665)

RMAN> COPY DATAFILE '+DEV_DATA/stgrptdb/datafile/ts_voipdm_data.442.680445909' TO '+QA_FLASH';
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 '+DEV_DATA/stgrptdb/datafile/ts_voipdm_data.442.680445909' TO '+QA_FLASH/stgrptdb/datafile/ts_voipdm_data.442.680445909';

5. 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 '+DEV_DATA/stgrptdb/datafile/ts_voipdm_data.442.680445909' TO '+QA_FLASH/stgrptdb/datafile/ts_voipdm_data.442.680445909';

Database altered.

Note: After Oracle successfully renames the ASM database file in the data dictionary, it will remove the original ASM database file '+DEV_DATA/stgrptdb/datafile/ts_voipdm_data.442.680445909'

# Use RMAN to rename the ASM database file copy

RMAN> SWITCH DATAFILE '+QA_FLASH/stgrptdb/datafile/ts_voipdm_data.442.680445909' TO COPY;

datafile 8 switched to datafile copy '+QA_FLASH/stgrptdb/datafile/ts_voipdm_data.442.680445909'

# Recovery the new ASM database file

SQL> RECOVER DATAFILE '+QA_FLASH/stgrptdb/datafile/ts_voipdm_data.442.680445909';

Media recovery complete.

# Bring the new ASM database file ONLINE

SQL> ALTER DATABASE DATAFILE '+QA_FLASH/stgrptdb/datafile/ts_voipdm_data.442.680445909' ONLINE;

Database altered.

# Verify the new ASM data file location

SQL> SELECT file_name FROM dba_data_files;

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


$ sqlplus "/ as sysdba"

SQL> ALTER DISKGROUP TESTDB_DATA2 DROP FILE '+DEV_DATA/stgrptdb/datafile/ts_voipdm_data.442.680445909';