RMAN: All Backups Are Not Created Equal

I came across an interesting behavior exhibited by RMAN in a recent test I was conducting. I thought I will share this here. The scenario has been explained with a sample below.

The test was conducted in a Oracle 11g single instance environment running on OEL 4. But I tested the similar response to be true in 10g as well.

Here is a simple question: Will the size of a level 1 incremental be bigger than a FULL backup?

Answer: It depends. If the volume of change after the full backup is higher than the size of the full backup, then the incremental backup will be bigger in size, irrespective of the fact that the incremental being a “delta”.

But If I were to ask the question a little differently, Will a "LEVEL 1 INCREMENTAL" backup without a "prior" level 0 backup, be of equal size compared to a "FULL" backup, then the answer most probably will be: YES.

The assumption is that a FULL backup is nothing but a LEVEL 0 backup. If in the absence of a LEVEL 0 backup, an incremental LEVEL 1 backup will act as a LEVEL 0 backup. So, this will exhibit similar behavior. In other words, the size of the "FULL BACKUP" and an "INCREMENTAL LEVEL 1" will be of the same size.

I was surprised to learn that this is not the case *always*.

We all know, that based on the compatibility settings, whether its >=10.0 or less, the behavior of the incremental backups change. Essentially, A “FULL” backup is not very useful in an incremental backup strategy. So it is important that we do a Level 0 and Level 1 backups to maintain a viable incremental backup and recovery strategy. A level 1 backup without a level 0 is akin to a FULL backup with the advantage of an implementable incremental backup strategy. However, lack of understanding of the behavior of incremental backup options will have serious consequences when we reach implementation.

Check this out.

create tablespace test_5mb_tbs
datafile '/oradata2/OSI11/tbs_tbs_tbs_1.dbf' size 5M
extent management local

11:12:07 SQL> create table rman_bkup_test(col1 number)
11:12:54 2 tablespace test_5mb_tbs;

Table created.

select count(extent_id)ext_id ,segment_name
from dba_extents
where segment_name='RMAN_BKUP_TEST' group by segment_name
11:16:41 SQL> /

EXT_ID SEGMENT_NAME
------ ---------------
1 RMAN_BKUP_TEST

11:16:41 SQL> declare
11:17:00 2 begin
11:17:02 3 for x in 1..50000 loop
11:17:07 4 insert into rman_bkup_test(col1) values (x);
11:17:16 5 end loop;
11:17:18 6 end;
11:17:20 7 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:07.50
11:17:28 SQL> get afiedt.buf
1 select count(extent_id)ext_id ,segment_name
2 from dba_extents
3* where segment_name='RMAN_BKUP_TEST' group by segment_name
11:17:41 SQL> /

EXT_ID SEGMENT_NAME
------ ---------------
11 RMAN_BKUP_TEST

By running a block, we fill it up to 11 extents.

*All the RMAN output is edited for conciseness*

RMAN> backup full tablespace test_5mb_tbs tag='full_backup';

Starting backup at 24-APR-09
piece handle=/oradata3/OSI11/BACKUP/17kd94q0_1_1 tag=FULL_BACKUP

RMAN> backup incremental level 1 tablespace test_5mb_tbs tag='inc_lvl1';

piece handle=/oradata3/OSI11/BACKUP/18kd94sr_1_1 tag=INC_LVL1

[ora11rac@linux1 BACKUP]$ ls -lrt
total 1636
-rw-r--r-- 1 ora11rac dbarac11 113 Apr 16 16:47 afiedt.buf
-rw-r----- 1 ora11rac dbarac11 843776 Apr 24 11:19 17kd94q0_1_1
-rw-r----- 1 ora11rac dbarac11 819200 Apr 24 11:21 18kd94sr_1_1

As we can see, the full backup and a level 1 incremental backup without a level 0 base results in a backup piece of approximately the same size.

11:35:39 SQL> delete from rman_bkup_test where rownum <48001;
11:35:58 SQL> commit;

RMAN> backup full tablespace test_5mb_tbs tag='full_aft_del';

piece handle=/oradata3/OSI11/BACKUP/19kd96ep_1_1 tag=FULL_AFT_DEL comment=NONE

About 48K rows gets deleted and a full backup is taken again. Before I do this backup, I delete the backup sets from previous iteration. I delete the RMAN backups for every iteration, throughout this exercise.

[ora11rac@linux1 BACKUP]$ ls -lt
total 832
-rw-r----- 1 ora11rac dbarac11 843776 Apr 24 11:47 19kd96ep_1_1

12:02:31 SQL> alter table rman_bkup_test enable row movement;
12:02:43 SQL> alter table rman_bkup_test shrink space;
RMAN> backup full tablespace test_5mb_tbs tag='full_aft_shrink';

piece handle=/oradata3/OSI11/BACKUP/1akd97e9_1_1

[ora11rac@linux1 BACKUP]$ ls -lrt
total 632
-rw-r--r-- 1 ora11rac dbarac11 113 Apr 16 16:47 afiedt.buf
-rw-r----- 1 ora11rac dbarac11 638976 Apr 24 12:04 1akd97e9_1_1

After the shrink, the full backup results in a 630K backup piece instead of the 800K size from before.

RMAN> backup incremental level 1 tablespace test_5mb_tbs tag='level1_aft_shrink ';

piece handle=/oradata3/OSI11/BACKUP/1bkd97hd_1_1
[ora11rac@linux1 BACKUP]$ ls -lrt
total 1436
-rw-r--r-- 1 ora11rac dbarac11 113 Apr 16 16:47 afiedt.buf
-rw-r----- 1 ora11rac dbarac11 638976 Apr 24 12:04 1akd97e9_1_1
-rw-r----- 1 ora11rac dbarac11 819200 Apr 24 12:06 1bkd97hd_1_1

However, if I did an incremental level 1, even though this is *exactly* similar to a full backup with the advantage of qualifying to be part of an incremental backup strategy, the size of the backup does not change from the first test. If you notice, this size is similar to the size we noticed from the initial level 0 backup, before the 48K rows were deleted.

RMAN> backup incremental level 0 tablespace test_5mb_tbs tag='level0_aft_shrink';

piece handle=/oradata3/OSI11/BACKUP/1ckd9e5s_1_1 tag=LEVEL0_AFT_SHRINK comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 24-APR-09

[ora11rac@linux1 BACKUP]$ ls -l
total 632
-rw-r----- 1 ora11rac dbarac11 638976 Apr 24 13:59 1ckd9e5s_1_1

Backup incremental level 0 produces a backup size that is similar to a FULL backup.

To summarize: Though a Level 1 w/o a Level 0 is similar to a full backup and can be part of an incremental backup strategy, various factors, influence the backup size, and performance (time taken) to perform the level 1. Understanding the implications of the various backup types is critical when implementing a backup strategy.

It is important to implement an incremental backup strategy with a level 0 base backup, no matter if a level 1 could be (indirectly) used as a starting point.


As always, comments/suggestions are appreciated.