Oracle DBA Tips Corner
Return to the Oracle DBA Tips Corner.
|
Oracle DBA Tips Corner
|
Moving ASM Database Files from one Diskgroup to Another
by Jeff Hunter, Sr. Database Administrator
Contents
Overview
Current Configuration
The following query lists the available ASM diskgroups and the database files
they contain:
Steps Required to Move ASM Files
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
I have a total of three ASM diskgroups defined:
Take the file to be moved OFFLINE:
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
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:
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:
All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter
and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express,
prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.
I have made every effort and taken great care in making sure that the material included on my web site is technically accurate,
but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from
relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.
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.
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
$ 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.
Now let's take a look at the steps used to move an ASM database file from one diskgroup to another:
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.
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.
SQL> ALTER DATABASE DATAFILE '+TESTDB_DATA2/testdb/datafile/app_data.256.598127837' OFFLINE;
Database altered.
$ 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 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.
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".
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"
SQL> RECOVER DATAFILE '+TESTDB_DATA1/testdb/datafile/app_data.264.598128765';
Media recovery complete.
SQL> ALTER DATABASE DATAFILE '+TESTDB_DATA1/testdb/datafile/app_data.264.598128765' ONLINE;
Database altered.
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.
$ ORACLE_SID=+ASM; export ORACLE_SID
$ sqlplus "/ as sysdba"
SQL> ALTER DISKGROUP TESTDB_DATA2 DROP FILE '+TESTDB_DATA2/testdb/datafile/app_data.256.598127837';
Thursday, 10-Aug-2006 20:02:04 EDT
Page Count: 11124