Mir Sayeed Hassan – Oracle Blog

Oracle DBA – Tips & Techniques | Learn with real-time examples

  • Translate

  • It’s Me






  • My Certificates

  • Links

    My Acclaim Certification : Credly Profile
    My Oracle ACE Pro Profile

  • Achievements

    Awarded Top 100 Oracle Blogs from Worldwide - #RANK 39
  • VISITORS COUNT

  • Verified International Academic Qualification from World Education Service (WES)

    Verified International Academic Qualification from World Education Service (WES)

  • Jobs

Restore Full with Incremental Backup of Database using RMAN

Posted by Mir Sayeed Hassan on October 7th, 2017

Restore Full with Incremental Backup of Database by using RMAN

The below step by step procedure is to Restore Full Database with Incremental backup from the Production DB to Test DB Env for the verification purpose by using RMAN

Transfer the Production Full DB to Test DB for restore by using WINSCP, SCP etc

[oracle@localhost rman]$ cd friday_level_0/    ---- Level 0 (Full backup)
[oracle@localhost friday_level_0]$ ls

df_PRIM_20171006_4415.bck
arch_PRIM_20171006_4416.bck
arch_PRIM_20171006_4417.bck
ctlfileL0_PRIM_20171006_PRIM.bck
rman2017-10-06.log
[oracle@localhost rman]$ cd saturday_level_1/  --- Level 1 (Incremental backup)
[oracle@localhost saturday_level_1]$ ls

df_PRIM_20171007_4420.bck
arch_PRIM_20171007_4421.bck
rch_PRIM_20171007_4422.bck
ctlfileL1_PRIM_20171007_4423.bck
rman2017-10-07.log
[oracle@PRIM friday_level_0]$ cat rman2017-10-06

Check & verify – All the backup should be available as per requirement

[oracle@localhost saturday_level_1]$ cat rman2017-10-07

Check & verify – All the backup should be available as per requirement

On Test DB — Copy the backup from production database as shown below

[oracle@DB backup]$ ls

arch_PRIM_20171006_4416.bck  ctlfileL0_PRIM_20171006_PRIM.bck  rman2017-10-06.log
arch_PRIM_20171006_4417.bck  ctlfileL1_PRIM_20171007_4423.bck  rman2017-10-07.log
arch_PRIM_20171007_4421.bck  df_PRIM_20171006_4415.bck
arch_PRIM_20171007_4422.bck  df_PRIM_20171007_4420.bck
(The contain Full + Incremental backup)

Set the SID Name in bash profile

[oracle@DB ~]$ cat .bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin
export PATH
# Oracle Settings
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME
ORACLE_SID=prim; export ORACLE_SID
ORACLE_HOME_LISTNER=$ORACLE_HOME; export ORACLE_HOME_LISTNER
ORACLE_TERM=xterm; export ORACLE_TERM
TNS_ADMIN=$ORACLE_HOME/network/admin; export TNS_ADMIN
ORA_NLS11=$ORACLE_HOME/nls/data; export ORA_NLS11
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib:$ORACLE_HOME/network/jlib;
export CLASSPATH
if [ $USER = "oracle" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi
umask 022
[oracle@DB ~]$ . .bash_profile
[oracle@DB ~]$ echo $ORACLE_SID
PRIM

Now startup the database in nomount to restore the controlfile from the backup location

[oracle@DB ~]$ !sq

sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sat Oct 7 15:22:01 2017
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to an idle instance
 SQL> startup nomount
ORACLE instance started.
Total System Global Area 1.5734E+10 bytes
Fixed Size                  2268712 bytes
Variable Size            2415919576 bytes
Database Buffers         1.3288E+10 bytes
Redo Buffers               27828224 bytes
SQL> exit
[oracle@DB backup]$ ls
arch_PRIM_20171006_4416.bck  ctlfileL0_PRIM_20171006_PRIM.bck  rman2017-10-06.log
arch_PRIM_20171006_4417.bck  ctlfileL1_PRIM_20171007_4423.bck  rman2017-10-07.log
arch_PRIM_20171007_4421.bck  df_PRIM_20171006_4415.bck
arch_PRIM_20171007_4422.bck  df_PRIM_20171007_4420.bck
[oracle@DB backup]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Sat Oct 7 17:26:16 2017

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

connected to target database: PRIM (not mounted)
RMAN> restore controlfile from '/backup/ctlfileL1_PRIM_20171007_4423.bck';

Starting restore at 07-OCT-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=385 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/oradata/admin/prim/controlfile/control01.ctl

Finished restore at 07-OCT-17
RMAN> alter database mount;
database mounted
RMAN> crosscheck backup;
using channel ORA_DISK_1
specification does not match any backup in the repository

RMAN> catalog start with '/backup/';
searching for all files that match the pattern /backup/
List of Files Unknown to the Database

=====================================
File Name: /backup/arch_PRIM_20171006_4416.bck
File Name: /backup/arch_PRIM_20171006_4417.bck
File Name: /backup/arch_PRIM_20171007_4421.bck
File Name: /backup/arch_PRIM_20171007_4422.bck
File Name: /backup/ctlfileL0_PRIM_20171006_PRIM.bck
File Name: /backup/ctlfileL1_PRIM_20171007_4423.bck
File Name: /backup/df_PRIM_20171006_4415.bck
File Name: /backup/df_PRIM_20171007_4420.bck
File Name: /backup/rman2017-10-06.log
File Name: /backup/rman2017-10-07.log
Do you really want to catalog the above files (enter YES or NO)?
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /backup/arch_PRIM_20171006_4416.bck
File Name: /backup/arch_PRIM_20171006_4417.bck
File Name: /backup/arch_PRIM_20171007_4421.bck
File Name: /backup/arch_PRIM_20171007_4422.bck
File Name: /backup/ctlfileL0_PRIM_20171006_PRIM.bck
File Name: /backup/ctlfileL1_PRIM_20171007_4423.bck
File Name: /backup/df_PRIM_20171006_4415.bck
File Name: /backup/df_PRIM_20171007_4420.bck

List of Files Which Where Not Cataloged
=======================================
File Name: /backup/rman2017-10-06.log
RMAN-07517: Reason: The file header is corrupted
File Name: /backup/rman2017-10-07.log
RMAN-07517: Reason: The file header is corrupted
RMAN> crosscheck backup;

using channel ORA_DISK_1
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/backup/arch_PRIM_20171006_4416.bck RECID=4353 STAMP=956770102
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/backup/arch_PRIM_20171006_4417.bck RECID=4354 STAMP=956770102
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/backup/arch_PRIM_20171007_4421.bck RECID=4355 STAMP=956770102
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/backup/arch_PRIM_20171007_4422.bck RECID=4356 STAMP=956770102
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/backup/ctlfileL0_PRIM_20171006_PRIM.bck RECID=4357 STAMP=956770103
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/backup/ctlfileL1_PRIM_20171007_4423.bck RECID=4358 STAMP=956770103
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/backup/df_PRIM_20171006_4415.bck RECID=4359 STAMP=956770103
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/backup/df_PRIM_20171007_4420.bck RECID=4360 STAMP=956770103
Crosschecked 8 objects

Restore database on test db

RMAN> run {
2> set newname for database to '/oradata/datafiles/%b';
4> restore database;
5>switch datafile all;
6>}
executing command: SET NEWNAME
Starting restore at 07-OCT-17
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 /oradata/datafiles/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /oradata/datafiles/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /oradata/datafiles/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /oradata/datafiles/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /oradata/datafiles/example01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /oradata/datafiles/esb_test.dbf
channel ORA_DISK_1: restoring datafile 00008 to /oradata/datafiles/sample01.dbf
channel ORA_DISK_1: reading from backup piece /backup/df_PRIM_20171006_4415.bck
channel ORA_DISK_1: piece handle=/backup/df_PRIM_20171006_4415.bck tag=FRIDAY_LEVEL_0
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:14:47

Finished restore at 07-OCT-17
datafile 1 switched to datafile copy
input datafile copy RECID=24 STAMP=956771174 file name=/oradata/datafiles/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=25 STAMP=956771174 file name=/oradata/datafiles/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=26 STAMP=956771174 file name=/oradata/datafiles/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=27 STAMP=956771174 file name=/oradata/datafiles/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=28 STAMP=956771174 file name=/oradata/datafiles/example01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=29 STAMP=956771174 file name=/oradata/datafiles/esb_test.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=30 STAMP=956771174 file name=/oradata/datafiles/sample01.dbf

Restore complete

Now recover the database with the last updated  SCN

RMAN> list backup of archivelog all;

List of Backup Sets
===================
BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
4352    502.63M    DISK        00:00:00     06-OCT-17
BP Key: 4353   Status: AVAILABLE  Compressed: YES  Tag: TAG20171006T003715
Piece Name: /backup/arch_PRIM_20171006_4416.bck
List of Archived Logs in backup set 4352
Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
---- ------- ---------- --------- ---------- ---------
1    15515   596050443  05-OCT-17 596091563  05-OCT-17
1    15516   596091563  05-OCT-17 596130689  05-OCT-17
1    15517   596130689  05-OCT-17 596169682  05-OCT-17
1    15518   596169682  05-OCT-17 596219627  05-OCT-17
1    15761   606999903  06-OCT-17 607037229  06-OCT-17
1    15762   607037229  06-OCT-17 607072578  06-OCT-17
1    15763   607072578  06-OCT-17 607107087  06-OCT-17
1    15764   607107087  06-OCT-17 607142634  07-OCT-17
1    15765   607142634  07-OCT-17 607179013  07-OCT-17
1    15766   607179013  07-OCT-17 607188078  07-OCT-17
RMAN> run {
2> set until scn 607188078;
3> recover database;
4> }
executing command: SET until clause
Starting recover at 07-OCT-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /oradata/datafiles/system01.dbf
destination for restore of datafile 00002: /oradata/datafiles/sysaux01.dbf
destination for restore of datafile 00003: /oradata/datafiles/undotbs01.dbf
destination for restore of datafile 00004: /oradata/datafiles/users01.dbf
destination for restore of datafile 00005: /oradata/datafiles/example01.dbf
destination for restore of datafile 00006: /oradata/datafiles/esb_test.dbf
destination for restore of datafile 00008: /oradata/datafiles/sample01.dbf
channel ORA_DISK_1: reading from backup piece /backup/df_PRIM_20171007_4420.bck
channel ORA_DISK_1: piece handle=/backup/df_PRIM_20171007_4420.bck tag=SATURDAY_LEVEL_1
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:15
starting media recovery
media recovery failed
RMAN-00571:
===========================================================
RMAN-00569: ======ERROR MESSAGE STACK FOLLOWS ======
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 10/07/2017 17:49:00
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover if needed
start until change 607188078 using backup controlfile

ORA-00283: recovery session canceled due to errors

ORA-38760: This database instance failed to turn on flashback database

 (Here you can see the error – Its indicate that the database is not recover completely as the flashback is enable in the production database, Need to disable the flashback & perform the restore)

 RMAN> exit
Recovery Manager complete.

[oracle@DB backup]$ !sq

sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sat Oct 7 17:50:06 2017
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> alter database flashback off;
Database altered.
RMAN> run {
2> set until scn 607188078;
3> recover database;
4> }
executing command: SET until clause
Starting recover at 07-OCT-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=385 device type=DISK
starting media recovery
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=15765
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=15766
channel ORA_DISK_1: reading from backup piece /backup/arch_PRIM_20171007_4422.bck
channel ORA_DISK_1: piece handle=/backup/arch_PRIM_20171007_4422.bck tag=TAG20171007T003205
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:05
archived log file name=/oradata/archive_log/1_15765_905677721.dbf thread=1 sequence=15765
archived log file name=/oradata/archive_log/1_15766_905677721.dbf thread=1 sequence=15766
media recovery complete, elapsed time: 00:00:26
Finished recover at 07-OCT-17
RMAN> alter database open resetlogs;
database opened

Hence restore the production DB in Test ENV, Tested & Verified in Real time