How to relocate or move oracle database files using RMAN

The requirement here is to move or relocate the database files to some other location. So, there are 2 scenarios here,
1. Relocating all the database files – datafiles, online redo logs, and controlfiles ( This requires the database to be in mounted mode )
2. Relocating Non-system datafiles – This can be relocated with minimal outage.

The examples below use /data02/oradata as the new destination. You can use any destination you like, including new ASM disk groups. So For this example, we have :

Existing location : /data01/oradata
New location      : /data02/oradata

Relocating all the database files

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

1. Restart database in mount mode.

First thing first, we need to start the database in mount mode to be able to perform the relocation.

SQL> shutdown immediate;
SQL> startup mount;

2. Copy all datafiles to the new location

Next step is to start copying the datafiles to new location. There are 2 cases here.
a. Copying datafiles with different names (database residing on OS file system)
When you specify the variable %U, RMAN auto generates new filenames while copying.

RMAN> backup as copy database format '/data02/oradata/%U';

b. Copying datafiles with same name
To keep the same names you can use db_file_name_convert option as follows:

RMAN> BACKUP AS COPY DB_FILE_NAME_CONVERT ('/data01/oradata/','/data02/oradata/') database;

3. Switch to the datafile copies

Switch the datafile copies to be able to read them from new location.

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 /data01/oradata/redo01.log /data02/oradata/redo01.log
$ cp /data01/oradata/redo02.log /data02/oradata/redo02.log
$ cp /data01/oradata/redo03.log /data02/oradata/redo03.log
$ cp /data01/oradata/redo04.log /data02/oradata/redo04.log

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

SQL> alter database rename file '/data01/oradata/redo01.log' to '/data02/oradata/redo01.log';
SQL> alter database rename file '/data01/oradata/redo02.log' to '/data02/oradata/redo02.log';
SQL> alter database rename file '/data01/oradata/redo03.log' to '/data02/oradata/redo03.log';
SQL> alter database rename file '/data01/oradata/redo04.log' to '/data02/oradata/redo04.log';

5. Relocate the controlfiles

Follwo the steps below to relocate the controlfiles.
a. backup current controlfile to new location:

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

b. duplicate the controlfile copy:

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

c. change the controlfile locations:

SQL> startup nomount;
SQL> show parameter control

SQL> alter system set control_files='/data02/oradata/control01.ctl','/data02/oradata/control02.ctl' scope=spfile;

6. Start the database

Once you have completed all the above steps, you may proceed to start the database.

SQL> alter database mount;
RMAN> recover database;
RMAN> alter database open;

7. Relocating TEMP files

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 '/data02/oradata/temp01.dbf' size 100m;

Relocating a few non-system datafiles

The below mentioned steps can be done when the database is open. You can only do this for non-system datafiles which are less in number. It only needs a minimal outage during the rename. For the purpose of this post example, we will relocate the datafile 5 to new location.

1. backup the datafile to the new location

To relocate the datafile 5, we would take a backup of the datafile first.

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

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. Online the datafile

Put it online and confirm its new location:

SQL> alter database datafile 5 online;
RMAN> report schema;