How to Move/Restore Oracle Database to New Host and File System using RMAN

You may need to restore a database to a new server due to the following reasons:

1. confirming your disaster recovery strategy
2. cloning a database to a new server for UAT or or PRE-PRODUCTION
3. moving the database to a new server

This post will work for all file systems – ASM, OCFS, raw, cooked etc. The steps can be summarised as:

1. take a backup of the database on the existing host
2. on the new host, restore the controlfiles, datafiles and tempfiles to the new location
3. on the new host, rename the online redo logs
4. use NID to change db_name and dbid

Assumptions

1. The existing and new hosts are running on the same operating system and RDBMS patchsets. Restoring to a different platform and/or RDBMS patchset is quite complex and will not be addressed in this post.

2. All data files and tablespaces are online and accessible.

1. Backup the Production Database

Online Backup

When in ARCHIVELOG mode, you can choose to perform either an offline or online backup. For an online backup, you do not need to shutdown the database. You can just backup the database plus all archivelogs whilst the database is up and running:

$ . oraenv
ORACLE_SID = [oracle] ? ORA102

$ rman target /

RMAN> backup database format '/tmp/%U';
RMAN> backup archivelog all format '/tmp/%U';
RMAN> backup current controlfile format '/tmp/control.bks';
RMAN> backup spfile format '/tmp/spfile.bks';

Offline backup

If the database is running in NOARCHIVELOG mode then it must be restarted in MOUNT mode to take an offline RMAN backup:

$ . oraenv
ORACLE_SID = [oracle] ? ORA102

$ rman target /

RMAN> shutdown immediate;
RMAN> startup mount;
RMAN> backup database format '/tmp/%U';
RMAN> backup current controlfile format '/tmp/control.bks';
RMAN> backup spfile format '/tmp/spfile.bks';

2. Transfer Backuppieces to New Host

Once the backup is completed, transfer the backuppieces across to the new host in the same location using an operating system utility such as cp, scp, ftp etc.

You can certainly place the backuppieces into another location and use the RMAN CATALOG command to tell RMAN about their new location. In Unix you may also consider using symbolic links to the new location. Or use NFS to mount the backuppieces to the new host.

3. Restore Database at New Host

Once the backuppieces have been transferred to the new host, restore the database at the new host.

1. Set the environment to point to the ORACLE_SID that you are about to restore. For example:

$ . oraenv
ORACLE_SID = [oracle] ? ORA102

2. Restore the spfile from the backuppiece:

RMAN> startup nomount force;
RMAN> restore spfile from '/tmp/spfile.bks';
RMAN> restore spfile to pfile '/tmp/initnewdb.ora' from '/tmp/spfile.bks';

3. Check the init.ora parameters, and precreate the directories if required. Note that the AUDIT directory must pre-exist before you can NOMOUNT the restored spfile

$ grep audit /tmp/initnewdb.ora
*.audit_file_dest='/u01/app/oracle/admin/em10rep/adump'

$ mkdir -p /opt/app/oracle/admin/ORA112/adump

SQL> shutdown immediate;
SQL> startup nomount;
SQL> show parameter control_files
SQL> show parameter dump
SQL> show parameter create
SQL> show parameter recovery

Or change the parameters to reflect the new directory path if required:

4. Restore the controlfile from a known backuppiece and mount the database:

RMAN> restore controlfile from '/tmp/control.bks';
RMAN> alter database mount;
RMAN> report schema;

If the backuppieces are residing in a new location at the new host you’ll need to catalog them:

RMAN> catalog start with 'pathname for backuppiece location';

5. Restore the database, using SET NEWNAME to relocate the datafiles and tempfiles to their new locations if required.

preview the restore – this will confirm the backuppieces required for the restore operation but will not perform the actual restore.

RMAN> restore database preview;

If the preview looks valid, then go ahead and perform the actual restore to the new host:

RMAN> run {
# set newname for all datafiles to be mapped to a new path
# OR use SET NEWNAME FOR DATABASE if you wish to have all files located in the same directory
# eg. SET NEWNAME FOR DATABASE to '+DATA/inovadg/datafile/%b' 
set newname for datafile 1 to  'new file path and name';
...
set newname for tempfile 1 to 'new file path and name';
restore database;
switch datafile all;
switch tempfile all;
}

6. Confirm that all datafiles have been restored to the new location:

RMAN> report schema;

7. Recover the database.

– If recovering from an OFFLINE backup, and you do not have further archivelogs to apply from the original host, use recover with NOREDO:

RMAN> recover database noredo;

– If the original database is running in archivelog mode and there are subsequent archivelogs generated after the initial backup, you can back up these archivelogs and transfer them to the new host as well.

At the original host:

RMAN> backup archivelog all format '/tmp/rest_of_arc.bks;

At the new host, catalog this new backuppiece:

RMAN> catalog backuppiece '/tmp/rest_of_arc.bks';

Now recover the database:

RMAN> run {# change the date and time to suit
SET UNTIL TIME "to_date('01 SEP 2011 12:04:00','DD MON YYYY hh24:mi:ss')";
recover database;
}

8. Relocate all the online redo logs if required:

SQL> select * from v$logfile;
SQL> alter database rename file 'old redo log path and name' to 'new redo log path and name';

9. Once all files have been renamed, open the database with resetlogs.

– If the source database had block change tracking, you will need to either precreate the block change tracking directory, or disable and enable it before opening the database:

SQL> alter database disable block change tracking;
SQL> alter database enable block change tracking using '+DG1';

– Now open with resetlogs:

RMAN> alter database open resetlogs;

10. confirm the location of your tempfiles, recreating them at the new location if required:

SQL> select * from v$tempfile;
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE 'new tempfile path filename' REUSE;

11. If the old database is going to remain up and running on the old server, use NID to rename the DBNAME and DBID of either the old or new database to avoid confusion. For example:

$ nid target=sys/password dbname=DB102