Friday, June 22, 2007

ASM: Move DataFile from One DiskGroup to Another

Introduction :
We will use RMAN commands to move a ASM Based DataFile from One Disk to Another.

RMAN> report schema;
Report of database schema
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
5 100 T1 ** +DG1/odb/datafile/t1.256.617493211
...

RMAN> sql 'alter database datafile 5 offline';
sql statement: alter database datafile 5 offline

RMAN> copy datafile '+DG1/odb/datafile/t1.256.617493211' to '+DATA';
Starting backup at 20-MAR-07
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile fno=00005 name=+DG1/odb/datafile/t1.256.617493211
output filename=+DATA/odb/datafile/t1.270.617751709 tag=TAG20070320T214148 recid=5 stamp=617751751
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:57
Finished backup at 20-MAR-07

RMAN> report schema;
Report of database schema
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
5 100 T1 ** +DG1/odb/datafile/t1.256.617493211
...

RMAN> switch datafile '+DG1/odb/datafile/t1.256.617493211' to COPY;
datafile 5 switched to datafile copy "+DATA/odb/datafile/t1.270.617751709"

RMAN> report schema;

Report of database schema

List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
5 100 T1 ** +DATA/odb/datafile/t1.270.617751709

RMAN> sql 'alter database datafile 5 online';
sql statement: alter database datafile 5 online
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of sql command on default channel at 03/20/2007 21:47:02
RMAN-11003: failure during parse/execution of SQL statement: alter database datafile 5 online
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: '+DATA/odb/datafile/t1.270.617751709'

RMAN> recover datafile '+DATA/odb/datafile/t1.270.617751709';
Starting recover at 20-MAR-07
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:03

Finished recover at 20-MAR-07

RMAN> sql 'alter database datafile 5 online';
sql statement: alter database datafile 5 online

RMAN>

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+DATA/odb/datafile/system.256.617411373
+DATA/odb/datafile/undotbs1.258.617411379
+DATA/odb/datafile/sysaux.257.617411377
+DATA/odb/datafile/users.259.617411381
+DATA/odb/datafile/t1.270.617751709
+DG1/odb/datafile/test1.261.617749583
+DG1/odb/datafile/test1.262.617749589

7 rows selected.

SQL>

Summary:
Here we have taken the datafile offline;Using RMAN Copy command we have copied the datafile from its DiskGroup to the New DiskGroup. Then using SWITCH Command the DataFile is Renamed , Recovered and Onlined.

RMAN Copy Command Syntax ( Release 9i)
From Release 10g , some of the RMAN Commands are Deprecated. Like "COPY" Command is replaced by "BACKUP AS COPY"

Deprecated RMAN Commands in 10g

Let me explain how to go about the same step using BACKUP AS COPY option.

RMAN> REPORT SCHEMA;
File Size(MB) Tablespace RB segs Datafile Name
..
4 5 USERS ** +DATA/odb/datafile/users.259.617411381

+TEST is the new ASM Disk Group

RMAN> BACKUP AS COPY DATAFILE 4 FORMAT '+TEST';

Starting backup at 18-APR-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=149 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=+DATA/odb/datafile/users.259.617411381
output filename=+TEST/odb/datafile/users.256.620211179 tag=TAG20070418T085257 re
cid=16 stamp=620211184
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:08
Finished backup at 18-APR-07

Starting Control File and SPFILE Autobackup at 18-APR-07
piece handle=+CONTROL/odb/autobackup/2007_04_18/s_620211185.256.620211191 commen
t=NONE
Finished Control File and SPFILE Autobackup at 18-APR-07

RMAN>
RMAN> switch datafile 4 to COPY;

datafile 4 switched to datafile copy "+TEST/odb/datafile/users.256.620211179"

RMAN>

RMAN> REPORT SCHEMA;

Report of database schema

List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
..
4 5 USERS +TEST/odb/datafile/users.256.620211179

Summary: Here we have Backup As Copy Option to move a datafile from one Disk Group to Another.

Popular Posts