How to move ASM database files from one diskgroup to another

Sometimes we need to move one or more datafile from our tablespace to one other diskgroup because of disk space issue.

In this article i try to explain how we can achive this process wiht RMAN utulity via  demo

The database version is :

11.2.0.1

The Operating System is:

AIX 6.1

Let  us check first the existing  status

[oracle@TEST] export ORACLE_SID=+ASM
[oracle@TEST]echo $ORACLE_SID
+ASM
[oracle@TEST]sqlplus "/as sysasm"
SQL*Plus: Release 11.2.0.1.0 Production on Fri Apr 8 15:21:14 2011
 Copyright (c) 1982, 2009, Oracle.  All rights reserved.
 Connected to:
 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Automatic Storage Management option

Let us see first our diskgroup :

SQL> select group_number, name, total_mb, free_mb, state, type from v$asm_diskgroup;

Group_Number	Name	Total_MB	Free_MB	State	Type
1	ORADATA	204800	128	CONNECTED	EXTERN

Let us check new candidate disk information to can add to after create new diskgroup:

SQL> SELECT group_number, disk_number, mount_status, header_status, state, path FROM   v$asm_disk;

Group_Number	DISK_NUMBER	MOUNT_STATUS	HEADER_STATUS	STATE	PATH
1	1	CACHED	MEMBER	NORMAL	/dev/rhdisk1
1	0	CACHED	MEMBER	NORMAL	/dev/rhdisk2
0	0	CLOSED	CANDIDATE	NORMAL	/dev/rhdisk4

Now we are going to create our new diskgroup which is called ORADATA2 by using below query;

SQL> CREATE DISKGROUP ORAODM2 EXTERNAL REDUNDANCY DISK '/dev/rhdisk4';
Diskgroup created.

Let us check last status of our ASM

SQL> select group_number, name, total_mb, free_mb, state, type from v$asm_diskgroup;

Group_Number	Name	Total_MB	Free_MB	State	Type
1	ORADATA	204800	128	CONNECTED	EXTERN
2	ORADATA2	10240	9163	CONNECTED	EXTERN

Now we have new diskgroup and we can start to move our datafile from ORADATA to ORADATA2

Before start be sure your database in ARCHIVELOG mode:

[oracle@TEST] export ORACLE_SID=TEST
[oracle@TEST] sqlplus “/as sysdba”
SQL> select log_mode from v$database;
LOG_MODE
 ------------
 ARCHIVELOG

Now we need to  identify which datafile or datafiles we will move. Here is the steps:

SQL> SELECT FILE_NAME FROM DBA_DATA_FILES where tablespace_name='GUNESTABLESPACE';
FILE_NAME
 --------------------------------------------------------------------------------
 +ORADATA/TEST/datafile/gunestablespace.270.747153997
 +ORADATA/TEST/datafile/gunestablespace.271.747154095
 +ORADATA/TEST/datafile/gunestablespace.272.747154201
 +ORADATA/TEST/datafile/gunestablespace.273.747239703
 +ORADATA/TEST/datafile/gunestablespace.274.747934181
 +ORADATA/TEST/datafile/gunestablespace.275.747934253

We need to take this datafile offline first

 SQL> ALTER DATABASE DATAFILE '+ORADATA/TEST/datafile/gunestablespace.275.747934253' OFFLINE;
 Database altered.

 We need to connect RMAN as:

 [oracle@TEST]> rman target /
 Recovery Manager: Release 11.2.0.1.0 - Production on Fri Apr 8 15:36:57 2011
 Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
 RMAN> COPY DATAFILE '+ORADATA/TEST/datafile/gunestablespace.275.747934253' TO '+ORADATA2';
Starting backup at 08-APR-11
 using target database control file instead of recovery catalog
 allocated channel: ORA_DISK_1
 channel ORA_DISK_1: SID=147 device type=DISK
 channel ORA_DISK_1: starting datafile copy
 input datafile file number=00014 name=+ORADATA/TEST/datafile/gunestablespace.275.747934253
 output file name=+ORADATA2/TEST/datafile/gunestablespace.256.747934675 tag=TAG20110408T153755 RECID=2 STAMP=747934679
 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
 Finished backup at 08-APR-11

Now we should rename datafile,please notice we use new datafile name which is given by rman(Check bold place in upper copy datafile command part)
Our  orginal file name is:
+ORADATA/TEST/datafile/gunestablespace.275.747934253

Now its become:
ORADATA2/TEST/datafile/gunestablespace.256.747934675

We should rename filename by using RMAN again

RMAN> run
 {
 set newname for datafile '+ORADATA/TEST/datafile/gunestablespace.275.747934253'
 to '+ORADATA2/TEST/datafile/gunestablespace.256.747934675' ;
 switch datafile all;
 }
executing command: SET NEWNAME
datafile 14 switched to datafile copy
input datafile copy RECID=2 STAMP=747934679 file name=+ORADATA2/TEST/datafile/gunestablespace.256.747934675

We should recover file now by using RMAN again

RMAN> RECOVER DATAFILE '+ORADATA2/TEST/datafile/gunestablespace.256.747934675' ;
Starting recover at 08-APR-11
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 08-APR-11

Now we can take our datafile online which we moved from +ORADATA to +ORADATA2

[oracle@TEST ]sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.1.0 Production on Fri Apr 8 15:44:42 2011
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
 Connected to:
 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
 With the Partitioning, Automatic Storage Management, OLAP, Data Mining
 and Real Application Testing options

SQL> ALTER DATABASE DATAFILE '+ORADATA2/TEST/datafile/gunestablespace.256.747934675' ONLINE;
Database altered.
SQL> SELECT file_name,online_status FROM DBA_DATA_FILES where tablespace_name='GUNESTABLESPACE';

File_Name	Online_Status
+ORAODM/odmtest/datafile/gunestablespace.270.747153997+ORAODM/odmtest/datafile/gunestablespace.271.747154095 +ORAODM/odmtest/datafile/gunestablespace.272.747154201 +ORAODM/odmtest/datafile/gunestablespace.273.747239703 +ORAODM/odmtest/datafile/gunestablespace.274.747934181 +ORAODM2/odmtest/datafile/gunestablespace.256.747934675	ONLINEONLINE ONLINE ONLINE ONLINE ONLINE

Now we can delete our old datafile from diskgroup.

[oracle@TEST] export ORACLE_SID=+ASM
SQL> ALTER DISKGROUP ORADATA DROP FILE  ‘+ORADATA/TEST/datafile/gunestablespace.275.747934253'

Or

You can use asmcmd command line utulity

ASMCMD> rm -rf  +ORADATA/TEST/datafile/gunestablespace.275.747934253

Reference:

How to move ASM database files from one diskgroup to another ? [ID 330103.1]

Advertisement