Relocate the datafile

Relocate the datafile using RMAN backup:


How to relocate the datafile
-------------------------
step:1
---backup the database using rman
---down the database and startup mount

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1690705920 bytes
Fixed Size                  1336960 bytes
Variable Size            1426065792 bytes
Database Buffers          251658240 bytes
Redo Buffers               11644928 bytes
Database mounted.

------------


step:2
open the new terminal and connect to the rman

RMAN> run
2>  {
3>  set until logseq 99 thread 1;
4> set newname for datafile 4 to '/oracle/11gdb/oradata/new/users01.dbf';
5> restore database;
6> SWITCH DATAFILE ALL;
7>  recover database;
8> open resetlogs database;
9>  }

executing command: SET until clause

executing command: SET NEWNAME

Starting restore at 07-NOV-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=135 device type=DISK
flashing back control file to SCN 1763676

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /oracle/11gdb/oradata/TEST3/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /oracle/11gdb/oradata/TEST3/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /oracle/11gdb/oradata/TEST3/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /oracle/11gdb/oradata/new/users01.dbf
channel ORA_DISK_1: reading from backup piece /oracle/11gdb/product/11.2.0/dbhome_1/flash_back/TEST3/backupset/2011_11_07/o1_mf_nnndf_TAG20111107T164015_7chh4qmm_.bkp
channel ORA_DISK_1: piece handle=/oracle/11gdb/product/11.2.0/dbhome_1/flash_back/TEST3/backupset/2011_11_07/o1_mf_nnndf_TAG20111107T164015_7chh4qmm_.bkp tag=TAG20111107T164015
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:55
Finished restore at 07-NOV-11

datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=766602600 file name=/oracle/11gdb/oradata/new/users01.dbf

Starting recover at 07-NOV-11
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 98 is already on disk as file /oracle/11gdb/oradata/TEST3/redo03.log
archived log file name=/oracle/11gdb/oradata/TEST3/redo03.log thread=1 sequence=98
media recovery complete, elapsed time: 00:00:01
Finished recover at 07-NOV-11

database opened

RMAN>


step:3

View the new datafile location
------------------

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/oracle/11gdb/oradata/TEST3/system01.dbf
/oracle/11gdb/oradata/TEST3/sysaux01.dbf
/oracle/11gdb/oradata/TEST3/undotbs01.dbf
/oracle/11gdb/oradata/new/users01.dbf    

SQL>

--------------------