Move an ASM Datafile to Another Diskgroup

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.716245261aaaaaaaaaaaaaaaaa22

SQL> SELECT name FROM v$asm_diskgroup;

NAME
------------------------------
REC
DATA

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

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

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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/27064837/viewspace-745240/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/27064837/viewspace-745240/