How to restore a database from set of incremental RMAN backups

Sometime we do have  a curiosity on how oracle RMAN utility will restore a database by applying incremental backups.

This post is a very simple /basic demonstration of how to do database restore, for details please go through the below scenario

Scenario:
======
In this example I am using a database called "sales" and working on a schema called "scott"

step 1: take a level 0 full incremental backup and tag it as "Sunday or Full backup"
step 2: create a sample table called EMP1 from EMP and add almost 10 million records and commit.
Step 3: Take a incremental backup and tag it as "Monday"
Step 4:Add some data to EMP1 table and make sure it should contain 30 million records and commit

Now shutdown the database and the delete the CRD files under oradata directory

So now you are ready for database restore that means RMAN should apply backup files of sunday,monday and Tuesday

After restoration your EMP1 table should contain 50 million records

Trust me you follow the below example its very easy


=======================================================================

Login to RMAN and create a script

RMAN > list script names
 Scripts of Target Database SALES

       Script Name

       full_cold_bkp

       full_hot_bkp

       rman_full_inc_bkp

       rman_inc_mon_bkp

       rman_inc_tue_bkp

Here I have created 3 scripts for (Sunday/Monday/Tuesday)

create script rman_inc_sun_bkp

{

allocate channel t1 device type disk;

allocate channel t2 device type disk;

allocate channel t3 device type disk;

allocate channel t4 device type disk;

sql 'alter system switch logfile';

backup incremental level 0 tag weekly_inc_database  format "c:\app\backup\weekly_%T_L0_%d-%s_%p.db" database archivelog all delete all input; 

backup archivelog all delete all input;

sql 'alter system switch logfile';

backup tag weeklybkpcontrolfile  current controlfile format 'c:\app\backup\%d_%T_%s_%p_CONTROL';

release channel t1;

release channel t2;

release channel t3;

release channel t4;

}

=========================================================

create script rman_inc_mon_bkp

{

allocate channel t1 device type disk;

allocate channel t2 device type disk;

allocate channel t3 device type disk;

allocate channel t4 device type disk;

sql 'alter system switch logfile';

backup incremental level 1 tag dailymon_inc_database  format "c:\app\backup\MonDaily_%T_L1_%d-%s_%p.db" database archivelog all delete all input; 

backup archivelog all delete all input;

sql 'alter system switch logfile';

backup tag monincbkpcontrolfile  current controlfile format 'c:\app\backup\%d_%T_%s_%p_CONTROL';

release channel t1;

release channel t2;

release channel t3;

release channel t4;

}

=====================================

create script rman_inc_tue_bkp

{

allocate channel t1 device type disk;

allocate channel t2 device type disk;

allocate channel t3 device type disk;

allocate channel t4 device type disk;

sql 'alter system switch logfile';

backup incremental level 1 tag dailytue_inc_database  format "c:\app\backup\TueDaily_%T_L1_%d-%s_%p.db" database archivelog all delete all input; 

backup archivelog all delete all input;

sql 'alter system switch logfile';

backup tag tueincbkpcontrolfile  current controlfile format 'c:\app\backup\%d_%T_%s_%p_CONTROL';

release channel t1;

release channel t2;

release channel t3;

release channel t4;

}

==================================================================

Now you are ready with the scripts 

C:\WINDOWS\system32>rman target sys/oracle123@sales catalog ruser/ruser@rmandb

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Apr 5 12:59:33 2019

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

connected to target database: SALES (DBID=877926465)

connected to recovery catalog database

RMAN> run

2> {

3> execute script rman_full_inc_bkp;

4> }

NOW login to database 

sqlplus scott/tiger@sales 

>create table emp1 as select * from emp;

>insert into emp1 select * from emp1;

>  continue to execute the above statement until it reaches to 10 million records 

>commit;

RMAN> run

2> {

3> execute script rman_inc_mon__bkp;

4> }

NOW login to database 

sqlplus scott/tiger@sales 

>insert into emp1 select * from emp1;

>  continue to execute the above statement until it reaches to 30 million records 

>commit;

>select count(*) from emp1; // result should be 50 million records 

RMAN> run

2> {

3> execute script rman_inc_tue_bkp;

4> }

Now shutdown the database and then delete the CRD files 

RMAN> shutdown immediate;

database closed

database dismounted

Oracle instance shut down

RMAN> startup nomount;

connected to target database (not started)

Oracle instance started

Total System Global Area    3173429248 bytes

Fixed Size                     2179936 bytes

Variable Size               1728056480 bytes

Database Buffers            1426063360 bytes

Redo Buffers                  17129472 bytes

RMAN> restore controlfile from tag 'tueincbkpcontrolfile';

Starting restore at 05-APR-19

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=63 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: reading from backup piece C:\APP\BACKUP\SALES_20190405_46_1_CONTROL

channel ORA_DISK_1: piece handle=C:\APP\BACKUP\SALES_20190405_46_1_CONTROL tag=TUEINCBKPCONTROLFILE

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

output file name=C:\APP\SL38\ORADATA\SALES\CONTROL01.CTL

output file name=C:\APP\SL38\ORADATA\SALES\CONTROL02.CTL

Finished restore at 05-APR-19

RMAN> alter database mount;

database mounted

released channel: ORA_DISK_1

RMAN> restore database;

RMAN> recover database until scn 247551;

Starting recover at 05-APR-19

using channel ORA_DISK_1

starting media recovery

media recovery complete, elapsed time: 00:00:00

Finished recover at 05-APR-19

RMAN> alter database open resetlogs;

database opened

new incarnation of database registered in recovery catalog

starting full resync of recovery catalog

full resync complete

IMPORTANT POINTS:

================

RMAN INCREMENTAL BACKUP

———————————————————————

In case of incremental backup, RMAN takes the backup of only modified blocks.

The different levels of incremental are:

1.Level 0 [Complete]

2.Level 1 [Incremental]

3.Level 2 [Cumulative]

In order to take incremental backup, first we need to take level 0 backup.

Without taking level 0, if we initiate level 1, by default RMAN takes level 0 backup.

In order to minimize incremental backup time, In 10g oracle has introduced block change tracking.

ALTER DATABASE  ENABLE BLOCK CHANGE TRACKING  USING FILE os_file_name;

SQL> alter database enable block change tracking using file ‘c:\app\changetrack.file’;

>From 10.2.0.4 ,Levels greater than 0 and 1 have become obsolete.

RMAN> backup incremental level 0 database;

RMAN> backup incremental level 1 database;

RMAN> backup incremental level 2 database;

(or)

RMAN> backup incremental level 1 cumulative database;

BLOCK CHANGE TRACKING:

=======================

SQL> alter database enable block change tracking;

SQL> alter database enable block change tracking using file  'c:\app\backup\BCtracking.file';

To disable this feature, you issue this command:

SQL> alter database disable block change tracking;

To monitor the status of block change tracking, you type:

select filename, status, bytes from   v$block_change_tracking

You can view the size of the CTWR dba buffer by looking at v$sgastat:

SELECT  * FROM v$sgastat WHERE name like 'CTWR%'; 

Thanks,

Satya