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:
- How to Restore 10g RAC Database from a Backup Set.
- How to Restore 12g RAC Database from a Backup Set.
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:
- SPFILE knows the original locations of control file.
- 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.