Restore Oracle Database 12c from an RMAN Backup

How to recovery Oracle 12 from Rman backupWhen you think about architecting your backup strategy, as part of the process you must also consider how you’re going to restore and recover Oracle Database 12c. Your backups are only as good as the last time you tested a restore and recovery. A backup can be rendered worthless without a good restore and recovery strategy. The last thing you want to happen is to experience a media failure, go to restore your database, and then find out you’re missing a file, you don’t have enough space to restore, something is corrupt, and so on.

One of the best ways to test an RMAN backup is to restore and recover it to a different Oracle database 12c server. This will exercise all your backup, restore, and recovery DBA skills. If you can restore and recover an RMAN backup on a different server, it will give you confidence when a real disaster hits. Moving a database from one server to another using an RMAN backup requires an expert-level understanding of the Oracle architecture and how backup and recovery works. The next example will do just that; it uses an RMAN backup to restore and recover a database on a different server. This scenario is depicted in Figure 1.

 Manually cloning a Oracle database using an RMAN backup

Figure 1. Manually cloning a database using an RMAN backup

Notice in Figure 1 that only step 1 occurs on the source database server. All remaining steps are performed on the destination server. For this example the source database is named TRG, and the destination database is named DUP. Also notice that the originating source server and destination server have different directory names. You’ll have to adjust these directory names to reflect the directory structures on your database servers. Let’s get started with step 1:

1. Create an RMAN backup on the source (target) database. When backing up a database, make sure you have the autobackup control file feature turned on:

$ rman target /

RMAN> configure controlfile autobackup on;

Also include the archive redo logs as part of the backup, as shown:

RMAN> backup database plus archivelog;

Verify that a backup of the control file exists:

RMAN> list backup of controlfile;

Here’s some sample output:

Piece Name: /u01/rman/TRG/TRGctl_c-1251088236-20141228-00.bk

You’ll need to reference the prior backup piece file when you restore the control file on the destination server (step 8). Also notice for this example that the backup pieces on the source server are in the /u01/rman/TRG directory.

2. On the destination server, create any required directories for data files, control files, and so on. For this example the destination server directories created are:

$ mkdir -p /u01/rman/DUP
$ mkdir -p /u01/dbfile/DUP
$ mkdir -p /u01/oraredo/DUP
$ mkdir -p /u01/arch/DUP

3. Copy the RMAN backup to the destination server. This exampl.nux/UNIX scp command to copy the backup pieces (initiated from the destination server):

$ scp oracle@shrek:/u01/rman/TRG/*.*  /u01/rman/DUP


4. On the destination server, ensure you have the same version of the Oracle binaries installed as you do on the originating database.

5. On the destination server establish the OS variables, such as ORACLE_SID, ORACLE_HOME, and PATH. The ORACLE_SID variable is initially set to match what it was on the source database (TRG in this example). The destination database name will be changed as part of the last step in this list, to DUP. Here are the settings for ORACLE_SID and ORACLE_HOME on the destination server:


$ echo $ORACLE_SID


6. Copy the init.ora file from the source server to the destination server, placing it in the ORACLE_HOME/dbs directory. Modify the init.ora file so that it matches the destination box in terms of any directory paths. Ensure that you change the parameters, such as the CONTROL_FILES, to reflect the new path directories on the destination server (/u01/dbfile/DUP, in this example). Initially, the name of the init.ora file on the destination server is initTRG.ora, and the name of the database is TRG. Both will be renamed in a later step. Here are the contents of the initTRG.ora file:


7. You should now be able to start up the destination database in nomount mode:

$ rman target /
RMAN> startup nomount;

8. Next, restore the control file from the backup that was previously copied from the source database; for example:

RMAN> restore controlfile from

The control file will be restored to all locations specified by the CONTROL_FILES initialization parameter in the destination init.ora file. Here is some sample output from the restore operation:

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
output file name=/u01/dbfile/DUP/control01.ctl
output file name=/u01/dbfile/DUP/control02.ctl

You may see an error like this:

RMAN-06172: no AUTOBACKUP found or specified handle ...

In this situation, ensure that the path and backup piece names are correctly specified.

9. You should now be able to start up your database in mount mode:

RMAN> alter database mount;

At this point, your control files exist and have been opened, but none of the data files or online redo logs exist yet.

10. Make sure the control file is aware of the location of the RMAN backups. First, use the CROSSCHECK command to let the control file know that none of the backups or archive redo logs are in the same location that they were in on the original server:

RMAN> crosscheck backup; # Crosscheck backups
RMAN> crosscheck copy;    Crosscheck image copies and archive logs

You’ll probably see output indicating that RMAN can’t validate that archive redo logs exist:

archived log file name=/u01/arch/TRG/TRG1_16_869840124.arc
RECID=765 STAMP=869842623

That’s the expected behavior because those archive redo logs do not exist on the destination server.

Next use the CATALOG command to make the control file aware of the location and names of the backup pieces that were copied to the destination server.


In this example, any RMAN files that are in the /u01/rman/DUP directory will be cataloged in the control file:

RMAN> catalog start with '/u01/rman/DUP';

Here is some sample output:

List of Files Unknown to the Database

File Name: /u01/rman/DUP/TRGctl_c-1251088236-20141228-00.bk
File Name: /u01/rman/DUP/TRGrman1_b7pr9m9q_1_1.bk
File Name: /u01/rman/DUP/TRGrman2_b6pr9m82_1_1.bk
File Name: /u01/rman/DUP/TRGrman2_b4pr9m6k_1_1.bk
File Name: /u01/rman/DUP/TRGrman1_b2pr9m4c_1_1.bk
File Name: /u01/rman/DUP/TRGrman2_b3pr9m4c_1_1.bk
File Name: /u01/rman/DUP/TRGrman1_b5pr9m82_1_1.bk

Do you really want to catalog the above files (enter YES or NO)?

Now, type YES (if everything looks okay). You should then be able to use the RMAN LIST BACKUP command to view the newly cataloged backup pieces:

RMAN> list backup;

You should see output indicating that RMAN is aware of the backups that were copied to the destination server. Here’s a small snippet of the output:

BP Key: 280   Status: AVAILABLE  Compressed: NO  Tag:

Piece Name: /u01/rman/DUP/TRGrman2_jkps7th9_1_1.bk

11. Rename and restore the data files to reflect new directory locations. If your destination server has the exact same directory structure as the original server directories, you can issue the RESTORE command directly:

RMAN> restore database;

How to recovery Oracle 12 from Rman backup

However, when restoring data files to locations that are different from the original directories, you’ll have to use the SET NEWNAME command. Create a file that uses an RMAN run{} block that contains the appropriate SET NEWNAME and RESTORE commands. I like to use a SQL script that generates SQL to give me a starting point. Here is a sample script:

set head off feed off verify off echo off pages 0 trimspool on
set lines 132 pagesize 0
spo newname.sql


select 'run{' from dual;


'set newname for datafile ' || file# || ' to ' || '''' || name || '''' || ';'
from v$datafile;


'restore database;' || chr(10) ||
'switch datafile all;' || chr(10) ||
from dual;

spo off;

Run the prior script from SQL*Plus as SYS. In this example, the prior code is placed in a file named gen.sql and executed as follows:

SQL> @gen.sql

After running the script, these are the contents of the newname.sql script that was generated:


set newname for datafile 1 to '/u01/dbfile/TRG/system01.dbf';
set newname for datafile 2 to '/u01/dbfile/TRG/sysaux01.dbf';
set newname for datafile 3 to '/u01/dbfile/TRG/undotbs01.dbf';
set newname for datafile 4 to '/u01/dbfile/TRG/users01.dbf';
set newname for datafile 5 to '/u01/dbfile/TRG/repdata.dbf';
set newname for datafile 6 to '/u01/dbfile/TRG/repidx.dbf';
restore database;
switch datafile all;


Then, modify the contents of the newname.sql script to reflect the directories on the destination database server. Here is what the final newname.sql script looks like for this example:


set newname for datafile 1 to '/u01/dbfile/DUP/system01.dbf';
set newname for datafile 2 to '/u01/dbfile/DUP/sysaux01.dbf';
set newname for datafile 3 to '/u01/dbfile/DUP/undotbs01.dbf';
set newname for datafile 4 to '/u01/dbfile/DUP/users01.dbf';
set newname for datafile 5 to '/u01/dbfile/DUP/repdata.dbf';
set newname for datafile 6 to '/u01/dbfile/DUP/repidx.dbf';

restore database;
switch datafile all;


Now, connect to RMAN and run the prior script to restore the data files to the new locations:

$ rman target /
RMAN> @newname.sql

Here’s a small sample of the output from the prior script:

executing command: SET NEWNAME
executing command: SET NEWNAME


channel ORA_DISK_1: restoring datafile 00001 to /u01/dbfile/DUP/system01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/dbfile/DUP/users01.dbf


input datafile copy RECID=16 STAMP=869854446 file

RMAN> **end-of-file**

All the data files have been restored to the new database server. You can use the RMAN REPORT SCHEMA command to verify that the files have been restored and are in the correct locations:

RMAN> report schema;

Here is some sample output:

RMAN-06139: WARNING: control file is not current for REPORT SCHEMA

Report of database schema for database with db_unique_name TRG

List of Permanent Datafiles


File Size(MB) Tablespace           RB segs Datafile Name

---- -------- -------------------- ------- ------------------------

1    500      SYSTEM               ***     /u01/dbfile/DUP/system01.dbf
2    500      SYSAUX               ***     /u01/dbfile/DUP/sysaux01.dbf
3    200      UNDOTBS1             ***     /u01/dbfile/DUP/undotbs01.dbf
4    10       USERS                ***     /u01/dbfile/DUP/users01.dbf
5    10       REPDATA              ***     /u01/dbfile/DUP/repdata.dbf
6    10       REPIDX               ***     /u01/dbfile/DUP/repidx.dbf

List of Temporary Files


File Size(MB) Tablespace           Maxsize(MB) Tempfile Name

---- -------- -------------------- ----------- --------------------

1    500      TEMP                 500         /u01/dbfile/TRG/temp01.dbf

From the prior output you can see that the database name and temporary tablespace data file still don’t reflect the destination database (DUP). These will be modified in subsequent steps.

12. Next you need to apply any archive redo files that were generated during the backup. These should be included in the backup because the ARCHIVELOG ALL clause was used to create the backup. Initiate the application of redo files via the RECOVER DATABASE command:

RMAN> recover database;

RMAN will restore and apply as many archive redo logs as it has in the backup pieces; it may throw an error when it reaches an archive redo log that doesn’t exist. For example:

RMAN-06054: media recovery requesting unknown archived log for...

That error message is fine. The recovery process will restore and recover archive redo logs contained in the backups, which should be sufficient to open the database. The recovery process doesn’t know when to stop applying archive redo logs and therefore will continue to attempt to do so until it can’t find the next log. Having said that, now is a good time to verify that your data files are online and not in a fuzzy state:

SQL> select file#, status, fuzzy, error, checkpoint_change#,
         to_char(checkpoint_time,'dd-mon-rrrr hh24:mi:ss') as checkpoint_time
         from v$datafile_header;

Here is a small sample of the output:


---------- ------- --- ---------- ------------------ ---------------------

         1 ONLINE  NO                     1.3790E+13 23-jan-2015 15:23:37
         2 ONLINE  NO                     1.3790E+13 23-jan-2015 15:23:37

If you do have a file with a fuzzy status of YES, this indicates more redo logs need to be applied to the data file (normally this should not happen in this scenario).

13. Set the new location for the online redo logs. If your source and destination servers have the exact same directory structures, then you don’t need to set a new location for the online redo logs (so you can skip this step). However, if the directory structures are different, then you’ll need to update the control file to reflect the new directory for the online redo logs. I sometimes use an SQL script that generates SQL to assist with this step:

set head off feed off verify off echo off pages 0 trimspool on
set lines 132 pagesize 0
spo renlog.sql

   'alter database rename file ' || chr(10)
    || '''' || member || '''' || ' to ' || chr(10) || '''' || member || '''' ||';'
    from v$logfile;

spo off;
set feed on verify on echo on

For this example, assume the prior code was placed in a file named genredo.sql and run it as follows:

SQL> @genredo.sql

Here is a snippet of the renlog.sql file that was generated:

alter database rename file

'/u01/oraredo/TRG/redo01a.rdo' to

alter database rename file

'/u01/oraredo/TRG/redo02a.rdo' to

The contents of renlog.sql need to be modified to reflect the directory structure on the destination server. Here is what renlog.sql looks like after being edited:

alter database rename file
'/u01/oraredo/TRG/redo01a.rdo' to

alter database rename file
'/u01/oraredo/TRG/redo02a.rdo' to

Update the control file by running the renlog.sql script:

SQL> @renlog.sql

You can select from V$LOGFILE to verify that the online redo log names are correct:

SQL> select member from v$logfile;

Here is the output for this example:


14. You must open the database with the OPEN RESETLOGS clause (because there are no online redo logs, and they must be recreated at this point):

SQL> alter database open resetlogs;

If successful, you should see this message:

Statement processed


15. Add the temporary tablespace temp file. When you start your database, Oracle will automatically try to add any missing temp files to the database. Oracle won’t be able to do this if the directory structure on the destination server is different from that of the source server. In this scenario, you will have to add any missing temp files manually. To do this, first take offline the temporary tablespace temp file. The file definition from the originating database is taken offline like so:

SQL> alter database tempfile '/u01/dbfile/TRG/temp01.dbf' offline;
SQL> alter database tempfile '/u01/dbfile/TRG/temp01.dbf' drop;

Next, add a temporary tablespace file to the TEMP tablespace that matches the directory structure of the destination database server:

SQL> alter tablespace temp add tempfile '/u01/dbfile/DUP/temp01.dbf'
     size 100m;

You can run the REPORT SCHEMA command to verify that all files are in the correct locations.

  1. Rename the database (optional). If you need to rename the database to reflect the name for a development or test database, create a trace file that contains the CREATE CONTROLFILE statement and use it to rename your database. For details on how to rename a database, see the next section “Renaming a Database.”


Also keep in mind that other steps may be required for your environment depending on your standards. For example, you might want to ensure the database SID is listed in the oratab file, or you might require the use of an SPFILE, enabling a password file, changing passwords, taking a backup, adding entries into Oracle Net files, and so on.


Вас заинтересует / Intresting for you:

Oracle Database and Security: ...
Oracle Database and Security: ... 883 views sepia Mon, 26 Nov 2018, 12:20:58
Oracle Database and Instance d...
Oracle Database and Instance d... 514 views Masha Thu, 21 Jun 2018, 18:23:39
Selecting Table Data Types App...
Selecting Table Data Types App... 707 views Ded Sun, 19 Aug 2018, 12:33:49
Recovering from Undo Tablespac...
Recovering from Undo Tablespac... 485 views Aida Thu, 26 Jul 2018, 08:00:38

Comments on Restore Oracle Database 12c from an RMAN Backup

Be the first to comment
Please login to comment