How to relocate database files using RMAN

How to relocate database files using RMAN

GOAL

How to relocate all database files - datafiles, online redo logs, and controlfiles. This requires the database to be in mounted mode.

Non-system datafiles can be relocate with minimal outage - see solution 2.

SOLUTION

Solution 1 - How to relocate all database files

This solution requires the database to be in mounted mode. We will relocate all database files, controlfiles and redo logs.

The examples below use /u002/oradata/ORA1020 as the new destination. You can use any destination 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';

OR

b) To keep the same names you can use db_file_name convert as follows:

RMAN> BACKUP AS COPY DB_FILE_NAME_CONVERT ('/u001/oradata/ORA1020','/u002/oradata/ORA1020') database;

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;

===============================================================

Solution 2 - How to relocate a non-system datafile

The following can be done when the database is open. You can only do this for non-system datafiles. It only needs minimal outage during the rename...

1) backup the datafile to the new location:

RMAN> report schema;
RMAN> backup as copy datafile 5 format '/opt/app/oracle/oradata/ORA11G/users01.bk';
RMAN> list copy of datafile 5;

  

2) take the datafile offline and rename it by using the SWITCH command:

SQL> alter database datafile 5 offline;
RMAN> switch datafile 5 to copy;
RMAN> recover datafile 5;

  

3) put it online and confirm its new location:

SQL> alter database datafile 5 online;
RMAN> report 

===========================================================================