Moving Datafile RMAN

Posted on 2009-05-26
Medium Priority
Last Modified: 2013-11-16
Can someone please provide steps\commands to move a datafile between disk groups using RMAN.

Question by:crishna1
  • 3
LVL 40

Expert Comment

ID: 24477983
Don't use RMAN to move datafiles.

Use standard SQLPLUS with Oracle DDL commands.

1) Take tablespace offline (if is system or undo tablespace you have to shutdown and go to mount mode)
2) Issue: alter tablespace foo name datafile '/old/path/foo.dbf' to '/new/path/foo.dbf'
3) Copy or move the filename at the OS level with appropriate command
4) Bring tablespace online

This is standard procedure, RMAN is not required
LVL 40

Expert Comment

ID: 24478112
crishna1, my apologies, I assumed you meant a normal datafile in a standard volume manager disk group, but perhaps you were asking regarding moving between ASM disk groups? You can indeed use RMAN. Is this what you were requesting?

LVL 40

Accepted Solution

mrjoltcola earned 500 total points
ID: 24478234
Since this is probably what you mean, here is a sample:

1) SQL> alter tablespace foo offline;
2) RMAN> copy datafile 'c:/oradata/ora1/foo.dbf' to 'd:/oradata/ora1/foo.dbf';
3) SQL> alter tablespace foo rename datafile 'c:/oradata/ora1/foo.dbf' to 'd:/oradata/ora1/foo.dbf';
4) SQL> alter tablespace foo online;

For ASM replace the filenames above in steps 2 and 3 with ASM paths

2) RMAN> copy datafile '+DISKGROUP1/ora1/foo.dbf.123' to '+DISKGROUP2/ora1/foo.dbf.123;
3) SQL> alter tablespace foo rename datafile '+DISKGROUP1/ora1/foo.dbf.123' to '+DISKGROUP1/ora1/foo.dbf.123';

LVL 48

Assisted Solution

schwertner earned 500 total points
ID: 24480998
Copy datafile of the tablespace to the new diskgroup
Bring tablespace to offline mode
Switch datafile to its new location
Recover the tablespace
Bring tablespace to online mode
RMAN> run{
3> copy datafile 169 to +DGRP;
4> release channel disk1;
5> }

allocated channel: disk1
channel disk1: sid=450 devtype=DISK

Starting backup at 12-JUN-08 21:10:19
channel disk1: starting datafile copy
input datafile fno=00169 name=+DGROUP1/dds/datafile/cmp_default.463.645917347
output filename=+DGRP/dds/datafile/cmp_default.1093.657234623 tag=TAG20080612T211021 recid=7533 stamp=657234623
channel disk1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 12-JUN-08 21:10:25

Starting Control File and SPFILE Autobackup at 12-JUN-08 21:10:25
piece handle=+RGRP/dds/autobackup/2008_06_12/s_657234626.7000.657234627 comment=NONE
Finished Control File and SPFILE Autobackup at 12-JUN-08 21:10:30

released channel: disk1

RMAN> sql alter tablespace cmp_default offline;

sql statement: alter tablespace cmp_default offline

RMAN> switch datafile 169 to copy;

datafile 169 switched to datafile copy +DGRP/dds/datafile/cmp_default.1093.6572346233

RMAN> recover tablespace cmp_default;

Starting recover at 12-JUN-08 21:13:20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=450 devtype=DISK

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

Finished recover at 12-JUN-08 21:13:26

RMAN> sql alter tablespace cmp_default online;

sql statement: alter tablespace cmp_default online



Featured Post

Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Messaging apps are amazing tools with the power to do a lot of good, but the truth is the process of collaborating with coworkers requires relationships established through meaningful communication - the kind of communication that only happens face-…
An introductory discussion about Oracle Analytic Functions which are used to calculate or compute Aggregate values, based on a group of rows.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question