• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

The Geek Diary

CONCEPTS | BASICS | HOWTO

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • Linux Services
    • VCS
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
    • Data Guard
  • DevOps
    • Docker
    • Shell Scripting
  • Interview Questions
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

How to relocate or move oracle database files using RMAN

By admin

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;

Filed Under: oracle

Some more articles you might also be interested in …

  1. How to Resize the Undo Tablespace in Oracle Database
  2. How to Install and configure OSWatcher Black Box (OSWbb)
  3. Oracle database : Basics about pfile and spfile
  4. How to Rename or Move Datafiles and Logfiles in Oracle Database
  5. What roles can be set as default for a user in Oracle Database
  6. Oracle Database 18c new feature – Scalable Sequences
  7. How to change the SCAN IP address (SCAN VIP resources) in 11gR2 Grid (CRS) environment
  8. How to Rename Diskgroup having OCR, Vote File and SPILE
  9. How to Roll Forward a standby database using RMAN incremental backup in 11g
  10. How to Force ASM to Scan the Multipathed Device First using ASMLIB/oracleasm

You May Also Like

Primary Sidebar

Recent Posts

  • How to disable ACPI in CentOS/RHEL 7
  • How to Use real-time query to access data on a physical standby database
  • CentOS/RHEL 8: “ACPI MEMORY OR I/O RESET_REG” Server Hung after reboot
  • How to Create a Physical Standby Database by Using SQL and RMAN Commands
  • Archives
  • Contact Us
  • Copyright

© 2021 · The Geek Diary