In this scenario we will restore incremental backup when all data files, control files including redo logs are lost
Step 1: take full RMAN backup (level 0)
Step 2: create two tables and insert data in these table
Step 3: take incremental level 1 backup
Step 4: delete all data, redo and control files
Step 5: bring database in nomount mode
Step 6. restore control file
Step 7: mount database
Step 8: restore database from full backup
Step 9: restore database from incremental backup
Step 10: recover archive log
Step 11: open database
Thats it !!!!!!!!!!!!!!!!!
-------------Details------------
Enter user-name: /as sysdba
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
SQL> set time on
09:50:39 SQL> alter user scott identified by tiger account unlock;
User altered.
09:51:00 SQL> conn scott/tiger
Connected.
09:51:08 SQL> create table abc(
09:51:23 2 id number,
09:51:28 3 name varchar2(30));
Table created.
09:51:39 SQL> insert into abc values (10,'sebl');
1 row created.
09:51:57 SQL> insert into abc values (20,'BB');
1 row created.
09:52:09 SQL> commit;
Commit complete.
09:52:12 SQL> select * from abc;
ID NAME
---------- ------------------------------
10 sebl
20 BB
09:52:22 SQL>
Step 1: take full RMAN backup
[oracle@said rman]$ ./full.sh
RMAN> RMAN> RMAN> RMAN> RMAN> RMAN> RMAN> RMAN> RMAN> RMAN> RMAN> [oracle@said rman]$
Step 2: create table and insert data
[oracle@said backup_log]$ sqlplus
SQL*Plus: Release 11.2.0.4.0 Production on Tue Nov 14 09:55:03 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter user-name: scott/tiger
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
SQL> set time on
09:55:26 SQL> create table bcd(
09:55:32 2 id number,
09:55:41 3 salary number);
Table created.
09:55:49 SQL> insert into bcd values(10,1000);
1 row created.
09:56:10 SQL> insert into bcd values(20,20000);
1 row created.
09:56:21 SQL> create table cde(
09:56:28 2 id number,
09:56:34 3 dept varchar2(30));
Table created.
09:56:47 SQL> insert into cde values(10,'IT');
1 row created.
09:57:07 SQL> insert into cde values(20,'HR');
1 row created.
09:57:19 SQL> commit;
Commit complete.
09:57:22 SQL> select * from bcd;
ID SALARY
---------- ----------
10
1000
20
20000
09:57:29 SQL> select * from cde;
ID DEPT
---------- ------------------------------
10 IT
20 HR
09:57:35 SQL>
Step 3: take incremental backup
[oracle@said rman]$ ./incre.sh
RMAN> RMAN> RMAN> RMAN> RMAN> RMAN> RMAN> RMAN> RMAN> RMAN> RMAN> [oracle@said rman]$
----Delete all Datafile /Redo Log/Control File--------------
Step 4: delete all data, redo and control files
[oracle@said oradata]$ cd orcl/
[oracle@said orcl]$ ls -lr
ttotal 1942628
-rw-r----- 1 oracle oinstall 5251072 Nov 14 10:08 users01.dbf
-rw-r----- 1 oracle oinstall 125837312 Nov 14 10:08 undotbs01.dbf
-rw-r----- 1 oracle oinstall 22028288 Nov 14 07:02 temp01.dbf
-rw-r----- 1 oracle oinstall 817897472 Nov 14 10:08 system01.dbf
-rw-r----- 1 oracle oinstall 744497152 Nov 14 10:08 sysaux01.dbf
-rw-r----- 1 oracle oinstall 52429312 Nov 14 10:08 redo03.log
-rw-r----- 1 oracle oinstall 52429312 Nov 14 10:11 redo02.log
-rw-r----- 1 oracle oinstall 52429312 Nov 14 10:09 redo01.log
-rw-r----- 1 oracle oinstall 104865792 Nov 14 10:09 encryptedtbs01.dbf
-rw-r----- 1 oracle oinstall 9748480 Nov 14 10:12 control02.ctl
-rw-r----- 1 oracle oinstall 9748480 Nov 14 10:12 control01.ctl
[oracle@said orcl]$ rm -rf *
[oracle@said orcl]$ ls -lrrt
total 0
[oracle@said orcl]$
-----Now Restore Full and Incremental Backup-----------
startup nomount
restore control file
mount database
restore full backup
Step 5: bring database in nomount mode.
[oracle@said ~]$ sqlplus
SQL*Plus: Release 11.2.0.4.0 Production on Tue Nov 14 10:21:19 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 764121088 bytes
Fixed Size
2257152 bytes
Variable Size
469765888 bytes
Database Buffers
285212672 bytes
Redo Buffers
6885376 bytes
SQL>
[oracle@said ~]$ rman target/
Recovery Manager: Release 11.2.0.4.0 - Production on Tue Nov 14 10:21:45 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (not mounted)
RMAN> list backup summary;
using target database control file instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of list command at 11/14/2017 10:22:04
ORA-01507: database not mounted
RMAN>
Step 6. restore control file
-----Restore Control File from full backup and mount the database---
RMAN> restore controlfile from '/fra/backup/Tuesday2017-11-1410:05:08_full/orcl_37_960026761_20171114_1.ctl';
Starting restore at 14-NOV-17
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/oradata/orcl/control02.ctl
Finished restore at 14-NOV-17
Step 7: mount database
RMAN> alter database mount
2> ;
database mounted
released channel: ORA_DISK_1
RMAN>
MAN> list backup summary;
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
20 B 0 X DISK 14-NOV-17 1 1 YES DATAFILE_BACKUP
21 B 1 X DISK 14-NOV-17 1 1 YES DATAFILE_BACKUP
22 B 1 X DISK 14-NOV-17 1 1 YES DATAFILE_BACKUP
23 B A X DISK 14-NOV-17 1 1 NO TAG20171114T100035
25 B F X DISK 14-NOV-17 1 1 NO TAG20171114T100038
26 B F A DISK 14-NOV-17 1 1 NO TAG20171114T100508
27 B F A DISK 14-NOV-17 1 1 YES DATAFILE_BACKUP
28 B F A DISK 14-NOV-17 1 1 YES DATAFILE_BACKUP
29 B A A DISK 14-NOV-17 1 1 NO TAG20171114T100558
30 B F A DISK 14-NOV-17 1 1 NO SPFILE_BACKUP
RMAN>
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
20 Incr 0 299.55M DISK 00:00:38 14-NOV-17
BP Key: 20 Status: EXPIRED Compressed: YES Tag: DATAFILE_BACKUP
Piece Name: /fra/backup/Tuesday2017-11-1409:59:39_incre/incre_orcl_0qsjhlog_26_1_20171114.DBF
List of Datafiles in backup set 20
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 0 Incr 1972437 14-NOV-17 /u01/app/oracle/oradata/orcl/system01.dbf
2 0 Incr 1972437 14-NOV-17 /u01/app/oracle/oradata/orcl/sysaux01.dbf
3 0 Incr 1972437 14-NOV-17 /u01/app/oracle/oradata/orcl/undotbs01.dbf
4 0 Incr 1972437 14-NOV-17 /u01/app/oracle/oradata/orcl/users01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
21 Incr 1 1.02M DISK 00:00:01 14-NOV-17
BP Key: 21 Status: EXPIRED Compressed: YES Tag: DATAFILE_BACKUP
Piece Name: /fra/backup/Tuesday2017-11-1409:59:39_incre/incre_orcl_0rsjhlpt_27_1_20171114.DBF
List of Datafiles in backup set 21
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
5 1 Incr 1972551 14-NOV-17 /u01/app/oracle/oradata/orcl/encryptedtbs01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
22 Incr 1 1.05M DISK 00:00:01 14-NOV-17
BP Key: 22 Status: EXPIRED Compressed: YES Tag: DATAFILE_BACKUP
Piece Name: /fra/backup/Tuesday2017-11-1409:59:39_incre/incre_orcl_0ssjhlq0_28_1_20171114.DBF
SPFILE Included: Modification time: 14-NOV-17
SPFILE db_unique_name: ORCL
Control File Included: Ckp SCN: 1972591 Ckp time: 14-NOV-17
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
23 1.79M DISK 00:00:00 14-NOV-17
BP Key: 23 Status: EXPIRED Compressed: NO Tag: TAG20171114T100035
Piece Name: /fra/backup/Tuesday2017-11-1409:59:39_incre/orcl_0tsjhlq3_29_1_20171114.arch
List of Archived Logs in backup set 23
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 61 1972025 14-NOV-17 1972068 14-NOV-17
1 62 1972068 14-NOV-17 1972363 14-NOV-17
1 63 1972363 14-NOV-17 1972367 14-NOV-17
1 64 1972367 14-NOV-17 1972428 14-NOV-17
1 65 1972428 14-NOV-17 1972603 14-NOV-17
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
25 Full 9.33M DISK 00:00:01 14-NOV-17
BP Key: 25 Status: EXPIRED Compressed: NO Tag: TAG20171114T100038
Piece Name: /fra/backup/Tuesday2017-11-1409:59:39_incre/orcl_31_960026438_20171114_1.ctl
Control File Included: Ckp SCN: 1972670 Ckp time: 14-NOV-17
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
26 Full 9.33M DISK 00:00:01 14-NOV-17
BP Key: 26 Status: AVAILABLE Compressed: NO Tag: TAG20171114T100508
Piece Name: /fra/backup/Tuesday2017-11-1410:05:08_full/orcl_10sjhm2k_32_1_20171114.ctl
Control File Included: Ckp SCN: 1972931 Ckp time: 14-NOV-17
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
27 Full 301.95M DISK 00:00:42 14-NOV-17
BP Key: 27 Status: AVAILABLE Compressed: YES Tag: DATAFILE_BACKUP
Piece Name: /fra/backup/Tuesday2017-11-1410:05:08_full/full_orcl_11sjhm2n_33_1_20171114.DBF
List of Datafiles in backup set 27
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1972952 14-NOV-17 /u01/app/oracle/oradata/orcl/system01.dbf
2 Full 1972952 14-NOV-17 /u01/app/oracle/oradata/orcl/sysaux01.dbf
3 Full 1972952 14-NOV-17 /u01/app/oracle/oradata/orcl/undotbs01.dbf
4 Full 1972952 14-NOV-17 /u01/app/oracle/oradata/orcl/users01.dbf
5 Full 1972952 14-NOV-17 /u01/app/oracle/oradata/orcl/encryptedtbs01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
28 Full 1.05M DISK 00:00:01 14-NOV-17
BP Key: 28 Status: AVAILABLE Compressed: YES Tag: DATAFILE_BACKUP
Piece Name: /fra/backup/Tuesday2017-11-1410:05:08_full/full_orcl_12sjhm44_34_1_20171114.DBF
SPFILE Included: Modification time: 14-NOV-17
SPFILE db_unique_name: ORCL
Control File Included: Ckp SCN: 1972978 Ckp time: 14-NOV-17
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
29 1.79M DISK 00:00:00 14-NOV-17
BP Key: 29 Status: AVAILABLE Compressed: NO Tag: TAG20171114T100558
Piece Name: /fra/backup/Tuesday2017-11-1410:05:08_full/orcl_13sjhm46_35_1_20171114.arch
List of Archived Logs in backup set 29
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 65 1972428 14-NOV-17 1972603 14-NOV-17
1 66 1972603 14-NOV-17 1972943 14-NOV-17
1 67 1972943 14-NOV-17 1972990 14-NOV-17
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
30 Full 80.00K DISK 00:00:00 14-NOV-17
BP Key: 30 Status: AVAILABLE Compressed: NO Tag: SPFILE_BACKUP
Piece Name: /fra/backup/Tuesday2017-11-1410:05:08_full/orcl_SPFILE_14sjhm47_36_1_20171114.ORA
SPFILE Included: Modification time: 14-NOV-17
SPFILE db_unique_name: ORCL
Step 8: restore database from full backup
-------Now restore Database----
Catalog the backup set
RMAN> restore database;
Starting restore at 14-NOV-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=134 device type=DISK
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/encryptedtbs01.dbf
channel ORA_DISK_1: reading from backup piece /fra/backup/Tuesday2017-11-1410:05:08_full/full_orcl_11sjhm2n_33_1_20171114.DBF
channel ORA_DISK_1: piece handle=/fra/backup/Tuesday2017-11-1410:05:08_full/full_orcl_11sjhm2n_33_1_20171114.DBF tag=DATAFILE_BACKUP
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 14-NOV-17
RMAN>
Step 9: restore database from incremental backup
--------Catalog Incremental Backup-------------
RMAN> CATALOG START WITH '/fra/backup/Tuesday2017-11-1410:08:52_incre';
searching for all files that match the pattern /fra/backup/Tuesday2017-11-1410:08:52_incre
List of Files Unknown to the Database
=====================================
File Name: /fra/backup/Tuesday2017-11-1410:08:52_incre/orcl_16sjhm9k_38_1_20171114.ctl
File Name: /fra/backup/Tuesday2017-11-1410:08:52_incre/incre_orcl_17sjhm9p_39_1_20171114.DBF
File Name: /fra/backup/Tuesday2017-11-1410:08:52_incre/incre_orcl_18sjhmb6_40_1_20171114.DBF
File Name: /fra/backup/Tuesday2017-11-1410:08:52_incre/incre_orcl_19sjhmb7_41_1_20171114.DBF
File Name: /fra/backup/Tuesday2017-11-1410:08:52_incre/orcl_1asjhmb9_42_1_20171114.arch
File Name: /fra/backup/Tuesday2017-11-1410:08:52_incre/orcl_SPFILE_1bsjhmbb_43_1_20171114.ORA
File Name: /fra/backup/Tuesday2017-11-1410:08:52_incre/orcl_44_960026988_20171114_1.ctl
Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /fra/backup/Tuesday2017-11-1410:08:52_incre/orcl_16sjhm9k_38_1_20171114.ctl
File Name: /fra/backup/Tuesday2017-11-1410:08:52_incre/incre_orcl_17sjhm9p_39_1_20171114.DBF
File Name: /fra/backup/Tuesday2017-11-1410:08:52_incre/incre_orcl_18sjhmb6_40_1_20171114.DBF
File Name: /fra/backup/Tuesday2017-11-1410:08:52_incre/incre_orcl_19sjhmb7_41_1_20171114.DBF
File Name: /fra/backup/Tuesday2017-11-1410:08:52_incre/orcl_1asjhmb9_42_1_20171114.arch
File Name: /fra/backup/Tuesday2017-11-1410:08:52_incre/orcl_SPFILE_1bsjhmbb_43_1_20171114.ORA
File Name: /fra/backup/Tuesday2017-11-1410:08:52_incre/orcl_44_960026988_20171114_1.ctl
RMAN> restore database;
Starting restore at 14-NOV-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 /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: reading from backup piece /fra/backup/Tuesday2017-11-1410:08:52_incre/incre_orcl_17sjhm9p_39_1_20171114.DBF
channel ORA_DISK_1: piece handle=/fra/backup/Tuesday2017-11-1410:08:52_incre/incre_orcl_17sjhm9p_39_1_20171114.DBF tag=DATAFILE_BACKUP
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
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 00005 to /u01/app/oracle/oradata/orcl/encryptedtbs01.dbf
channel ORA_DISK_1: reading from backup piece /fra/backup/Tuesday2017-11-1410:08:52_incre/incre_orcl_18sjhmb6_40_1_20171114.DBF
channel ORA_DISK_1: piece handle=/fra/backup/Tuesday2017-11-1410:08:52_incre/incre_orcl_18sjhmb6_40_1_20171114.DBF tag=DATAFILE_BACKUP
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 14-NOV-17
RMAN>
Step 10: recover archive log
---------------Recover Database--------------
RMAN> recover database;
Starting recover at 14-NOV-17
using channel ORA_DISK_1
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=68
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=69
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=70
channel ORA_DISK_1: reading from backup piece /fra/backup/Tuesday2017-11-1410:08:52_incre/orcl_1asjhmb9_42_1_20171114.arch
channel ORA_DISK_1: piece handle=/fra/backup/Tuesday2017-11-1410:08:52_incre/orcl_1asjhmb9_42_1_20171114.arch tag=TAG20171114T100945
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/fra/archivelog/1_68_957612745.dbf thread=1 sequence=68
archived log file name=/fra/archivelog/1_69_957612745.dbf thread=1 sequence=69
archived log file name=/fra/archivelog/1_70_957612745.dbf thread=1 sequence=70
unable to find archived log
archived log thread=1 sequence=71
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 11/14/2017 10:39:48
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 71 and starting SCN of 1973237
RMAN>
Step 11: open database
---------Open Database--------
RMAN> alter database open resetlogs;
database opened
RMAN>
-------------Now check the data exist or not------------
[oracle@said ~]$ sqlplus
SQL*Plus: Release 11.2.0.4.0 Production on Tue Nov 14 10:40:55 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
SQL> conn scott/tiger
Connected.
SQL> select * from tab;
TNAME
TABTYPE
CLUSTERID
------------------------------ ------- ----------
ABC
TABLE
BCD
TABLE
BONUS
TABLE
CDE
TABLE
DEPT
TABLE
EMP
TABLE
SALGRADE
TABLE
TDE_TEST
TABLE
TDE_TEST1
TABLE
9 rows selected.
SQL>
SQL> select * from abc;
ID NAME
---------- ------------------------------
10 sebl
20 BB
SQL> select * from bcd;
ID SALARY
---------- ----------
10
1000
20
20000
SQL> select * from cde;
ID DEPT
---------- ------------------------------
10 IT
20 HR
SQL>
