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';
    or
RMAN> 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';
    or
RMAN> 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;