Wednesday, June 16, 2010

RMAN Full, Full level 0, Incremental and cumalative incremental

Question:

Let me give you a brief about the scenario


first a level 0 backup-----full backup

full backup size is 126 M



Now I take a level 1 incremental backup, please notice that the size is 162 M



I again take a level 1 incremental backup, but now the size is 664k


My question at the first instance when I took the incremental backup, it should have only taken affected blocks & not the whole database backup??????????????

Second time it did take only effected blocks size shows it.



Answer is as follows:


First of all, I can only guess what RMAN backups commands are issued to take a backup as I have limited visibility in the question. Secondly, "Full backup" is different than "full level 0 backup". In Oracle words, "A level 0 incremental backup is physically identical to a full backup. The only difference is that the level 0
backup is recorded as an incremental backup in the RMAN repository, so it can be used as the parent for a level 1 backup.
"

So from the above Oracle's statement, we can say the following:

Full backup ==> A complete backup without the incremental strategy marked in RMAN. Level 0 backup ==> A complete backup WITH the incremental strategy marked in RMAN repository.


So let's try few things out. Also, don't forget to check the information on cumalative backups in section-5 of the document.

SECTION-1:
Example-1

1A) Let's take a full backup using RMAN. This instructs RMAN to take a full backup which will NOT have any incremental
backups following in the future.

RMAN> backup database;

Backupset size after the above command is done in backup location:
Backupset = 126M
controlfile = 7.2M

1B) Let's take a level 1 backup using RMAN. This instructs RMAN to take a incremental backup. If the level 0
backup is not found (which in this case is not found --remember full backup is different than level 0 bakcup), then instruction is to take a
level 0 backup first and followed by incremental backup.

RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;

Backupset size after the above command is done in backup location:
Backupset = 157M
controlfile = 7.2M


1C) Let's take another level 1 backup using RMAN. This time since the level 0 (from our previous step) is found, it is
simply backing up the changed blocks.

RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;

Backupset size after the above command is done in backup location:
Backupset = 312K
controlfile = 7.2M


Verification:
Output after backup up with all the above three steps.

production:(clonea)$ ls -ltrh
total 305M
-rw-r----- 1 oracle oinstall 126M Jun 16 11:24 full_CLONEA_20100616_721826592_0rlgccp0_1_1.bak
-rw-r----- 1 oracle oinstall 7.2M Jun 16 11:24 control_n_spfile_c-2785473847-20100616-06
-rw-r----- 1 oracle oinstall 157M Jun 16 11:31 full_CLONEA_20100616_721826784_0tlgccv0_1_1.bak
-rw-r----- 1 oracle oinstall 7.2M Jun 16 11:31 control_n_spfile_c-2785473847-20100616-07
-rw-r----- 1 oracle oinstall 312K Jun 16 11:36 full_CLONEA_20100616_721827183_0vlgcdbf_1_1.bak
-rw-r----- 1 oracle oinstall 7.2M Jun 16 11:36 control_n_spfile_c-2785473847-20100616-08


SECTION-2:
Remove the backups now. I am deleting all the previous backups before proceeding further.

RMAN> delete backup;
RMAN> exit;
production:(clonea)$ pwd
/u99/clonea/backup
production:(clonea)$ ls -ltr
total 0


SECTION-3:

3A) Here, as a first step, I am taking a level 0 backup. Remember level 0 takes a full backup and instructs RMAN to be ready
for incremental changes (level 1 backup) where as a FULL backup just takes a complete backup of the db without instructing
RMAN about any incremental backups.

RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE;

Backupset size after the above command is done in backup location:
Backupset = 126K
controlfile = 7.2M


3B) Now, I will take a level 1 backup which should only backup any changes made since last level 0 (done in 2A) backups.

RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;

Backupset size after the above command is done in backup location:
Backupset = 208M
controlfile = 7.2M


3C) Let's throw some activity against the database and take another level 1 incremental backups.

SQL> create table scott.t100 as select * from scott.emp;
SQL> insert into scott.t100 select * from scott.t100; --14 rows
SQL> insert into scott.t100 select * from scott.t100; --28 rows
SQL> insert into scott.t100 select * from scott.t100; --56 rows
SQL> insert into scott.t100 select * from scott.t100; --112 rows
SQL> insert into scott.t100 select * from scott.t100; --224 rows
SQL> insert into scott.t100 select * from scott.t100; --448 rows
SQL> insert into scott.t100 select * from scott.t100; --896 rows
SQL> commit;
SQL> alter system switch logfile;
SQL> alter system switch logfile;


RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;

Backupset size after the above command is done in backup location:
Backupset = 1.4M
controlfile = 7.2M


Verification:
Let's see the sizes of all the backups we have taken so far.

production:(clonea)$ ls -ltrh
total 149M
-rw-r----- 1 oracle oinstall 126M Jun 16 11:49 full_CLONEA_20100616_721828119_11lgce8n_1_1.bak
-rw-r----- 1 oracle oinstall 7.2M Jun 16 11:49 control_n_spfile_c-2785473847-20100616-09
-rw-r----- 1 oracle oinstall 208K Jun 16 11:54 full_CLONEA_20100616_721828292_13lgcee4_1_1.bak
-rw-r----- 1 oracle oinstall 7.2M Jun 16 11:54 control_n_spfile_c-2785473847-20100616-0a
-rw-r----- 1 oracle oinstall 1.4M Jun 16 12:08 full_CLONEA_20100616_721829154_15lgcf92_1_1.bak
-rw-r----- 1 oracle oinstall 7.2M Jun 16 12:09 control_n_spfile_c-2785473847-20100616-0b



SECTION-4:
Remove backups:
Now, I am deleting all the previous backups before proceeding to next step.
RMAN> delete backup;
RMAN> exit;
production:(clonea)$ pwd
/u99/clonea/backup
production:(clonea)$ ls -ltr
total 0



SECTION-5:
In this section, lets work on CUMALATIVE backups. Oracle define cumalative backup as "In a cumulative level 1 backup, RMAN backs
up all the blocks used since the most recent level 0 incremental backup. Cumulative incremental backups reduce the work
needed for a restore by ensuring that you only need one incremental backup from any particular level. Cumulative backups
require more space and time than differential backups, however, because they duplicate the work done by previous backups at
the same level.
"


Example-3:

5A) Let's take a level 0 backup;

RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE;

Backupset size after the above command is done in backup location:
Backupset = 126M
controlfile = 7.2M

5B) Lets throw some DML against the db.

SQL> insert into scott.t100 select * from scott.t100; --1,792 rows
SQL> insert into scott.t100 select * from scott.t100; --3,584 rows
SQL> insert into scott.t100 select * from scott.t100; --71,68 rows
SQL> insert into scott.t100 select * from scott.t100; --14,336 rows
SQL> insert into scott.t100 select * from scott.t100; --28,672 rows
SQL> commit;
SQL> alter system switch logfile;

5C) Let's take a level 1 differencial backup.

RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;

Backupset size after the above command is done in backup location:
Backupset = 1.4M
controlfile = 7.2M

5D) Let's throw some more DML against the db.

SQL> insert into scott.t100 select * from scott.t100; --57,344 rows
SQL> insert into scott.t100 select * from scott.t100; --229,376 rows
SQL> insert into scott.t100 select * from scott.t100; --229,376 rows
SQL> insert into scott.t100 select * from scott.t100; --458,752 rows
SQL> insert into scott.t100 select * from scott.t100; --917,504 rows
SQL> commit;
SQL> alter system switch logfile;



5E) Let's take a level 1 differencial backup before taking to a final cumalative backup.

RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;

Backupset size after the above command is done in backup location:
Backupset = 12M
controlfile = 7.2M




5F) Let's take a level 1 cumalative backup right away (level 2 backup).

RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;

Backupset size after the above command is done in backup location:
Backupset = 12M
controlfile = 7.2M

Now this is confusing, why is it showing 12M. I was expecting it to show me 13.4M i.e, sum of 5C+5E. Hmm, I am suspecting the
RMAN overhead.Let's try the next step.


5G) Let's throw some more DML against the db.

SQL> insert into scott.t100 select * from scott.t100; --1,835,008 rows
SQL> insert into scott.t100 select * from scott.t100; --3,670,016 rows
SQL> insert into scott.t100 select * from scott.t100; --7,340,032 rows
SQL> alter system switch logfile;


5H) Perform another incremental level 1 backup again.

RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;

Backupset size after the above command is done in backup location:
Backupset = 86M
controlfile = 7.2M


5I) Lets take a level 2 backup AGAIN!!

RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;

Backupset size after the above command is done in backup location:
Backupset = 87M
controlfile = 7.2M

This size comes from backupset from (5C+5E+5H-(RMAN overhead)) i.e, (1.4M+12M+86-(RMAN_Overhead)=87M. This shows that RMAN
Overhead=12.4M. I am still not convinced. I was hoping less over head and slightly bigger backupset size. Lets try it again.



5J) Let's throw some more DML against the db.

SQL> insert into scott.t100 select * from scott.t100; --1,468,0064 rows
SQL> insert into scott.t100 select * from scott.t100; --29,360,128 rows
SQL> commit;
SQL> alter system switch logfile;



5K) Perform another incremental level 1 backup again.

RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;

Backupset size after the above command is done in backup location:
Backupset = 298M
controlfile = 7.2M


5L) Let's take a level 2 backup AGAIN!!

RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;

Backupset size after the above command is done in backup location:
Backupset = 339M
controlfile = 7.2M


The size comes from backupset is from 5I=(5C+5E+5H+ -(RMAN_Overhead)) i.e, (1.4M+12M+86+298M-(RMAN_Overhead)=339M.So, from
this, RMAN_Overhead=58.4M. This is bit convincing that when cumalative backups are taken, rman overhead is reduced.

Verification:
production:(clonea)$ ls -ltrh
total 1016M
-rw-r----- 1 oracle oinstall 126M Jun 16 12:25 full_CLONEA_20100616_721830256_17lgcgbg_1_1.bak
-rw-r----- 1 oracle oinstall 7.2M Jun 16 12:25 control_n_spfile_c-2785473847-20100616-0c
-rw-r----- 1 oracle oinstall 1.4M Jun 16 12:40 full_CLONEA_20100616_721831057_19lgch4h_1_1.bak
-rw-r----- 1 oracle oinstall 7.2M Jun 16 12:40 control_n_spfile_c-2785473847-20100616-0d
-rw-r----- 1 oracle oinstall 12M Jun 16 12:49 full_CLONEA_20100616_721831634_1blgchmi_1_1.bak
-rw-r----- 1 oracle oinstall 7.2M Jun 16 12:50 control_n_spfile_c-2785473847-20100616-0e
-rw-r----- 1 oracle oinstall 12M Jun 16 12:57 full_CLONEA_20100616_721832054_1dlgci3m_1_1.bak
-rw-r----- 1 oracle oinstall 7.2M Jun 16 12:57 control_n_spfile_c-2785473847-20100616-0f
-rw-r----- 1 oracle oinstall 86M Jun 16 13:18 full_CLONEA_20100616_721833187_1flgcj73_1_1.bak
-rw-r----- 1 oracle oinstall 7.2M Jun 16 13:18 control_n_spfile_c-2785473847-20100616-10
-rw-r----- 1 oracle oinstall 87M Jun 16 13:23 full_CLONEA_20100616_721833620_1hlgcjkk_1_1.bak
-rw-r----- 1 oracle oinstall 7.2M Jun 16 13:23 control_n_spfile_c-2785473847-20100616-11
-rw-r----- 1 oracle oinstall 298M Jun 16 14:02 full_CLONEA_20100616_721835842_1jlgclq2_1_1.bak
-rw-r----- 1 oracle oinstall 7.2M Jun 16 14:02 control_n_spfile_c-2785473847-20100616-12
-rw-r----- 1 oracle oinstall 339M Jun 16 14:09 full_CLONEA_20100616_721836281_1llgcm7p_1_1.bak
-rw-r----- 1 oracle oinstall 7.2M Jun 16 14:09 control_n_spfile_c-2785473847-20100616-13

Archivelog verification:

production:(clonea)$ pwd
/u99/clonea/Archive
production:(clonea)$ ls -ltrh
total 2.9G
-rw-r----- 1 oracle oinstall 4.3M Jun 16 12:35 ARC_1_15_720833576.arc
-rw-r----- 1 oracle oinstall 89M Jun 16 12:46 ARC_1_16_720833576.arc
-rw-r----- 1 oracle oinstall 96M Jun 16 13:04 ARC_1_17_720833576.arc
-rw-r----- 1 oracle oinstall 96M Jun 16 13:05 ARC_1_18_720833576.arc
-rw-r----- 1 oracle oinstall 96M Jun 16 13:08 ARC_1_19_720833576.arc
-rw-r----- 1 oracle oinstall 96M Jun 16 13:09 ARC_1_20_720833576.arc
-rw-r----- 1 oracle oinstall 96M Jun 16 13:10 ARC_1_21_720833576.arc
-rw-r----- 1 oracle oinstall 96M Jun 16 13:11 ARC_1_22_720833576.arc
-rw-r----- 1 oracle oinstall 71M Jun 16 13:12 ARC_1_23_720833576.arc
-rw-r----- 1 oracle oinstall 96M Jun 16 13:39 ARC_1_24_720833576.arc
-rw-r----- 1 oracle oinstall 96M Jun 16 13:40 ARC_1_25_720833576.arc
-rw-r----- 1 oracle oinstall 96M Jun 16 13:41 ARC_1_26_720833576.arc
-rw-r----- 1 oracle oinstall 96M Jun 16 13:41 ARC_1_27_720833576.arc
-rw-r----- 1 oracle oinstall 96M Jun 16 13:42 ARC_1_28_720833576.arc
-rw-r----- 1 oracle oinstall 96M Jun 16 13:43 ARC_1_29_720833576.arc
-rw-r----- 1 oracle oinstall 96M Jun 16 13:43 ARC_1_30_720833576.arc
-rw-r----- 1 oracle oinstall 96M Jun 16 13:46 ARC_1_31_720833576.arc
-rw-r----- 1 oracle oinstall 96M Jun 16 13:46 ARC_1_32_720833576.arc
-rw-r----- 1 oracle oinstall 96M Jun 16 13:47 ARC_1_33_720833576.arc
-rw-r----- 1 oracle oinstall 96M Jun 16 13:47 ARC_1_34_720833576.arc
-rw-r----- 1 oracle oinstall 96M Jun 16 13:48 ARC_1_35_720833576.arc
-rw-r----- 1 oracle oinstall 96M Jun 16 13:49 ARC_1_36_720833576.arc
-rw-r----- 1 oracle oinstall 96M Jun 16 13:49 ARC_1_37_720833576.arc
-rw-r----- 1 oracle oinstall 96M Jun 16 13:50 ARC_1_38_720833576.arc
-rw-r----- 1 oracle oinstall 96M Jun 16 13:50 ARC_1_39_720833576.arc
-rw-r----- 1 oracle oinstall 96M Jun 16 13:52 ARC_1_40_720833576.arc
-rw-r----- 1 oracle oinstall 96M Jun 16 13:52 ARC_1_41_720833576.arc
-rw-r----- 1 oracle oinstall 96M Jun 16 13:53 ARC_1_42_720833576.arc
-rw-r----- 1 oracle oinstall 96M Jun 16 13:53 ARC_1_43_720833576.arc
-rw-r----- 1 oracle oinstall 96M Jun 16 13:54 ARC_1_44_720833576.arc
-rw-r----- 1 oracle oinstall 96M Jun 16 13:55 ARC_1_45_720833576.arc
-rw-r----- 1 oracle oinstall 96M Jun 16 13:56 ARC_1_46_720833576.arc



That's it. Hope we have little better understanding of RMAN full, full level 0, differential incremental (level 1) and cumulative incremental (as Oracle calls it level-2 in previous versions) backups after reading this article.

--Moid M

References:
http://youngcow.net/doc/oracle10g/backup.102/b14191/rcmconc1005.htm
http://download.oracle.com/docs/cd/B19306_01/backup.102/b14192/bkup004.htm
http://www.dba-oracle.com/t_incr_differential_incr_cumulative_backups.htm

1 comment:

  1. Hell Moid.

    Good one,but got confused due to typo's.
    i)Under section -3/ 3A, the size of level 0 backup shows 126k,shouldnt it be 126M
    ii) changes made since last level 0 (done in 2A) - should be (done in 3A)
    iii) RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;
    Backupset = 208M -- Shouldnt this me 208K as per the output
    controlfile = 7.2M.

    Worth sharing.

    Regards,

    Sree.

    ReplyDelete

Followers