How to relocate datafiles using RMAN [ID 755620.1]

How to relocate datafiles using RMAN [ID 755620.1]

---

 Modified 06-DEC-2010     Type HOWTO     Status PUBLISHED 

In this Document  Goal  Solution  References

---

 

 

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.3 and later   [Release: 10.2 and later ]Information in this document applies to any platform.***Checked for relevance on 07-Dec-2010***

Goal

How to relocate all database files using RMAN. Database must be in MOUNT mode.

Solution

The examples below use /u002/oradata/ORA1020 as the new destination. You can use any destinations you like, including new ASM disk groups:

1) restart database in mount mode

SQL> shutdown immediate;SQL> startup mount;

2)copy all datafiles to the new location

a) allow RMAN to generate the new name:

RMAN> backup as copy database format '/u002/oradata/ORA1020/%U';    orRMAN> backup as copy database format '+DGROUP4';

b) if you need to rename your datafiles to specific names, you will need to copy each datafile as follows:

RMAN> backup as copy datafile 1 format '/u002/oradata/ORA1020/datafile01.dbf';    orRMAN> backup as copy datafile 6 format '+DGROUP4';

3) switch to the datafile copies

RMAN> switch database to copy;

4) relocate the online redo logs

As online redo logs are not backed up by RMAN, you will need to relocate them outside of RMAN:

a) identify the list of online redo logs:

SQL> select * from v$logfile;

b) make an o/s copy of the line redo logs to the new location:

$ cp /tmp/redo01.log /u002/oradata/ORA1020/redo01.rdo$ cp /tmp/redo02.log /u002/oradata/ORA1020/redo02.rdo$ cp /tmp/redo03.log /u002/oradata/ORA1020/redo03.rdo$ cp /tmp/redo04.log /u002/oradata/ORA1020/redo04.rdo

Note: as of 11g, you can also cp to an ASM diskgroup

c) now rename the log files, do this for each of the redo log files:

 SQL> alter database rename file '/tmp/redo01.log' to '/u002/oradata/ORA1020/redo01.rdo'; SQL> alter database rename file '/tmp/redo02.log' to '/u002/oradata/ORA1020/redo02.rdo'; SQL> alter database rename file '/tmp/redo03.log' to '/u002/oradata/ORA1020/redo03.rdo'; SQL> alter database rename file '/tmp/redo04.log' to '/u002/oradata/ORA1020/redo04.rdo';

 

5) relocate the controlfiles

a) backup current controlfile to new location:

RMAN> backup as copy current controlfile format '/u002/oradata/ORA1020/control001.ctl';

b) duplicate the controlfile copy:

RMAN> backup as copy controlfilecopy '/u002/oradata/ORA1020/control01.ctl' format '/u002/oradata/ORA1020/control02.ctl';

c) change the controlfile locations:

SQL> startup nomount;SQL> show parameter control SQL> alter system set control_files='/u002/oradata/ORA1020/control01.ctl','/u002/oradata/ORA1020/control02.ctl'scope=spfile;SQL> alter database mount;RMAN> recover database;RMAN> alter database open; 

 

6) if you need to relocate temp then simply drop and recreate it in SQL*Plus:

SQL> alter database drop temporary tablespace temp;SQL> create temporary tablespace temp datafile '/tmp/temp01.dbf' size 100m;