Background & Overview
This document explains how to move an ASM datafile to another diskgroup.
Step-By-Step
1. Get the name of the datafile to be moved.
- . oraenv
- ORACLE_SID = [oracle] ? Enter DB Sid
- sqlplus '/ as sysdba'
- SQL> select tablespace_name, file_name, file_id file# from dba_data_files where tablespace_name = 'MYTSP';
2. List the available ASM disk groups.
- . oraenv
- ORACLE_SID = [oracle] ? Enter DB Sid
- sqlplus '/ as sysdba'
- SQL> SELECT name FROM v$asm_diskgroup;
3. Take the ASM data file to be moved OFFLINE
- . oraenv
- ORACLE_SID = [oracle] ? Enter DB Sid
- sqlplus '/ as sysdba'
- SQL> alter database datafile 'my_datafile_name' offline;
4. Copy the datafile to the new diskgroup using RMAN
- rman target /
- RMAN> copy datafile 'my_datafile_name' to '+my_new_disk_group';
- RMAN> exit;
5. Rename the ASM database file.
- . oraenv
- ORACLE_SID = [oracle] ? Enter DB Sid
- sqlplus '/ as sysdba'
- SQL> alter database rename file 'my_datafile_name' TO 'my_new_datafile_name';
- SQL> exit
6. Switch to the new datafile using RMAN
- rman target /
- RMAN> switch datafile 'my_new_datafile_name' to copy;
7. Recover the datafile using RMAN
- RMAN> recover datafile 'my_new_datafile_name';
- RMAN> exit
8. Put the datafile back online
- . oraenv
- ORACLE_SID = [oracle] ? Enter DB Sid
- sqlplus '/ as sysdba'
- SQL> alter database datafile 'my_new_datafile_name' online;
9. Confirm the datafile has been moved.
- . oraenv
- ORACLE_SID = [oracle] ? Enter DB Sid
- sqlplus '/ as sysdba'
- SQL> select tablespace_name, file_name, file_id file# from dba_data_files where tablespace_name = 'MYTSP';
Example Output
SQL> select tablespace_name, file_name, file_id file# from dba_data_files where tablespace_name = 'MYTSP';
TABLESPACE_NAMEaaFILE_NAMEaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaFILE#
-------------------- ------------------------------------------------------------ -----
MYTSPaaaaaaaaaaaaa+DATA/mydb/datafile/myfile.379.716245261aaaaaaaaaaaaaaaaa22SQL> SELECT name FROM v$asm_diskgroup;
NAME
------------------------------
REC
DATASQL> alter database datafile '+DATA/mydb/datafile/myfile.379.716245261' offline;
Database altered.
rman target /
Recovery Manager: Release 10.2.0.4.0 - Production on Tue May 13 10:01:27 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: MYDB (DBID=403243456)RMAN> copy datafile '+DATA/mydb/datafile/myfile.379.716245261' to '+REC';
Starting backup at 13-MAY-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=165 instance=MYDB devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00022 name=+DATA/mydb/datafile/myfile.379.716245261
output filename=+REC/mydb/datafile/myfile.2005.716245261 tag=TAG20080513T120010 recid=364 stamp=716223223
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 13-MAY-08RMAN> exit;
sqlplus / as sysdba
SQL> ALTER DATABASE RENAME FILE '+DATA/mydb/datafile/myfile.379.716245261' TO
aaaaaaaaaa'+REC/mydb/datafile/myfile.2005.716245261';Database altered.
SQL> exit
rman target /
RMAN> switch datafile '+REC/mydb/datafile/myfile.2005.716245261' to copy;
Recovery Manager: Release 10.2.0.4.0 - Production on Tue May13 10:08:27 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: MYDB (DBID=403243456)
using target database control file instead of recovery catalog
datafile 22 switched to datafile copy "+REC/mydb/datafile/myfile.2005.716245261'"RMAN> recover datafile '+REC/mydb/datafile/myfile.2005.716245261';
Starting recover at 13-MAY-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=161 instance=MYDB devtype=DISK
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 13-MAY-10RMAN> exit
SQL> alter database datafile '+REC/mydb/datafile/myfile.2005.716245261' online;
Database altered.
SQL> select tablespace_name, file_name, file_id file# from dba_data_files where tablespace_name = 'MYTSP';
TABLESPACE_NAMEaaFILE_NAMEaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaFILE#
-------------------- ------------------------------------------------------------ -----
MYTSPaaaaaaaaaaaaa+REC/mydb/datafile/myfile.2005.716245261aaaaaaaaaaaaaaaaa22