Skip to content
Home » Oracle Database » How RMAN Restore Database from BackupSet

How RMAN Restore Database from BackupSet

In this post, I will show you how to restore a single-instance database from a backup set by RMAN. For RAC restoration, you may check the following posts:

Suppose we have restored the whole OS to the new server including Oracle software and directory structure. Our goal is to restore the database to the target server solely by a backup set for testing purpose. The content of the backup set that we copied from the source server to the target server is listed below.

[oracle@primary ~]$ ll /tmp/rman
total 1560664
-rw-r-----. 1 oracle oinstall  355449856 Oct 26 17:17 backup_08tgl7on_1_1
-rw-r-----. 1 oracle oinstall 1232814080 Oct 26 17:17 backup_09tgl7ov_1_1
-rw-r-----. 1 oracle oinstall    9830400 Oct 26 17:17 backup_0atgl7q2_1_1
-rw-r-----. 1 oracle oinstall      15360 Oct 26 17:17 backup_0btgl7q4_1_1

In this post, I assume there's no database in the target server. So we need to restore the whole database from the ground up. Which means, I will start from SPFILE restoration, then CONTROLFILE and rests.

Restore SPFILE

We don't copy SPFILE from the source database, because the backup set already has everything we need. It's consistent and healthy to be used.

STARTUP FORCE NOMOUNT

You have to go to NOMOUNT before you restore SPFILE from a backup piece. Without a proper SPFILE, you have to force the database to NOMOUNT.

[oracle@primary ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Oct 26 17:21:23 2018

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database (not started)

RMAN> startup force nomount;

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initORCL.ora'

starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started

Total System Global Area    1068937216 bytes

Fixed Size                     2260088 bytes
Variable Size                281019272 bytes
Database Buffers             780140544 bytes
Redo Buffers                   5517312 bytes

Usually, LRM-00109 is an error against missing file. It can be ignored in this case.

Restore SPFILE from One of Backup Pieces

Now we can restore SPFILE from one of the backup pieces.

RMAN> restore spfile from '/tmp/rman/backup_0atgl7q2_1_1';

Starting restore at 26-OCT-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=171 device type=DISK

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /tmp/rman/backup_0atgl7q2_1_1
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 26-OCT-18

If you don't know which backup piece contains SPFILE, you can test each piece one by one.

Check Restored SPFILE

We have to make sure the restored SPFILE is at correct path. Conventionally it's at $ORACLE_HOME/dbs/. If the restored SPFILE is mislocated, you can copy or move it to the correct path.

[oracle@primary ~]$ ll $ORACLE_HOME/dbs/spfile*
-rw-r-----. 1 oracle oinstall 2560 Oct 26 17:36 /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileORCL.ora

Which Backup Piece Should We Pick?

Now I have a question for you: How do you know which backup piece should be used? The answer depends on the source or primary database is running or not. If it's running, you can query your database to identify it.

RMAN> list backup of spfile;


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
...

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4       Full    9.36M      DISK        00:00:01     13-FEB-18
        BP Key: 4   Status: AVAILABLE  Compressed: NO  Tag: TAG20180213T214447
        Piece Name: /u02/full_backups/backup_0atgl7q2_1_1
  SPFILE Included: Modification time: 13-FEB-18
  SPFILE db_unique_name: ORCL

If it's not running, you have to test each of every backup piece from the smallest one until the restoration succeeds. Not that hard.

Restore Control File

For restoring control file, you have to use the restored SPFILE to startup the database to NOMOUNT. This is because SPFILE knows the destinations of multiplexed control files that RMAN should restore to.

Restart Database to NOMOUNT Normally

RMAN> shutdown immediate;

Oracle instance shut down

RMAN> startup nomount;

connected to target database (not started)
Oracle instance started

Total System Global Area    1553305600 bytes

Fixed Size                     2253544 bytes
Variable Size                956304664 bytes
Database Buffers             587202560 bytes
Redo Buffers                   7544832 bytes

Restore CONTROLFILE from One of Backup Pieces

The backed up CONTROLFILE is usually in the same backup piece with SPFILE, but not always. You have to find out by yourself.

RMAN> restore controlfile from '/tmp/rman/backup_0atgl7q2_1_1';

Starting restore at 26-OCT-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=134 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/ORCL/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/ORCL/control02.ctl
Finished restore at 26-OCT-18

As you can see, there have two output files been restored in the above.

Restore Data Files

RESTORE DATABASE is actually a course of actions of restoring data files.

Restart Database to MOUNT Normally

Before restoring data files, we need to mount the newly restored control file.

RMAN> shutdown immediate;

Oracle instance shut down

RMAN> startup mount;

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area    1553305600 bytes

Fixed Size                     2253544 bytes
Variable Size                956304664 bytes
Database Buffers             587202560 bytes
Redo Buffers                   7544832 bytes

For rebuilding a crashed server from the ground, it's crucial to know that:

  1. SPFILE knows the original locations of control file.
  2. The control file knows the original locations of data files.

Catalog Backup Set

So far, the control file knows nothing about the backup set in /tmp/rman, we need to catalog them before using them.

RMAN> catalog start with '/tmp/rman';

Starting implicit crosscheck backup at 26-OCT-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=133 device type=DISK
Crosschecked 7 objects
Finished implicit crosscheck backup at 26-OCT-18

Starting implicit crosscheck copy at 26-OCT-18
using channel ORA_DISK_1
Crosschecked 2 objects
Finished implicit crosscheck copy at 26-OCT-18

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2018_10_26/o1_mf_1_36_fx5py48g_.arc

searching for all files that match the pattern /tmp/rman

List of Files Unknown to the Database
=====================================
File Name: /tmp/rman/backup_0atgl7q2_1_1
File Name: /tmp/rman/backup_0btgl7q4_1_1

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

List of Cataloged Files
=======================
File Name: /tmp/rman/backup_0atgl7q2_1_1
File Name: /tmp/rman/backup_0btgl7q4_1_1

RESTORE DATABASE

RMAN> restore database;

Starting restore at 26-OCT-18
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/ORCL/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/ORCL/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/ORCL/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/ORCL/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/ORCL/example01.dbf
channel ORA_DISK_1: reading from backup piece /tmp/rman/backup_09tgl7ov_1_1
channel ORA_DISK_1: piece handle=/tmp/rman/backup_09tgl7ov_1_1 tag=TAG20181026T171718
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 26-OCT-18

Recover Database

Restore Archived Logs

We need to get archived logs back from the backup set in order to recover the database. Which is, backup archived logs cannot be used until they are restored as online archived logs. I think you might be interested in the life cycle of archived logs.

RMAN> restore archivelog all;

Starting restore at 26-OCT-18
using channel ORA_DISK_1

channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=7
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORCL/backupset/2018_02_13/o1_mf_annnn_TAG20180213T214446_f85tyg70_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2018_02_13/o1_mf_annnn_TAG20180213T214446_f85tyg70_.bkp tag=TAG20180213T214446
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=8
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=9
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=10
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=11
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=12
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=13
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=14
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=15
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=16
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=17
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=18
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=19
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=20
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=21
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=22
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=23
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=24
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=25
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=26
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=27
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=28
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=29
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=30
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=31
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=32
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=33
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=34
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=35
channel ORA_DISK_1: reading from backup piece /tmp/rman/backup_08tgl7on_1_1
channel ORA_DISK_1: piece handle=/tmp/rman/backup_08tgl7on_1_1 tag=TAG20181026T171711
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=36
channel ORA_DISK_1: reading from backup piece /tmp/rman/backup_0btgl7q4_1_1
channel ORA_DISK_1: piece handle=/tmp/rman/backup_0btgl7q4_1_1 tag=TAG20181026T171756
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 26-OCT-18

Recover Database with Restored Archived Logs

RMAN> recover database;

Starting recover at 26-OCT-18
using channel ORA_DISK_1

starting media recovery

unable to find archived log
archived log thread=1 sequence=37
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 10/26/2018 18:33:09
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 37 and starting SCN of 1611205

The database will recover itself as far as it can. The error message can be ignored.

Open Database

We are ready to open the database. Since it's an incomplete recovery, we should open it with RESETLOGS.

RMAN> alter database open resetlogs;

database opened

SQL> select name, open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
ORCL      READ WRITE

We've done it.

Tags:

Leave a Reply

Your email address will not be published. Required fields are marked *