Incremental backup size of 4th week is Huge

This is tested in production environment. There was an issue in the incremental backup of a 10.2.0.3 database server. The every months 4th week cumulative incremental backup size is huge(comes around 400 Gigs).

Database size - 335 GB.

Backup scenario:

> 1st sunday of every month - Level 0 Incremental backup

    script => Backup incremental level = 0 database tag='Complete_Backup';

> Every Sunday of the month - Level 1 Cumulative Incremental backup

   script => Backup incremental level = 1 cumulative database tag='Cumulative_Backup';

Once the backup is completed the backup's will be moved from the local drive to tape drive(ie: once in a week)

The issue was because of the Incremental backups are moved from its original location to tape drive. It was proved by the below query.

select recid, file#, to_char(creation_change#) create#, incremental_level lvl, to_char(incremental_change#) inc#,
to_char(checkpoint_change#) ckp#, datafile_blocks BLKS, block_size blksz, blocks_read READ,
round((blocks_read/datafile_blocks) * 100,2)  READpc,
blocks WRTN, round((blocks/datafile_blocks)*100,2) WRTNpc,
completion_time, used_change_tracking bct
from v$backup_datafile where
completion_time > to_date('17-Jun-11 12:32:00', 'dd-mon-rr hh24:mi:ss ')
and file# > 0;

 

 If we see the query output we can find that for several Datafiles the 'incremental_change#' is 0 or the same as the Datafile Creation SCN. This means that the RMAN Incremental Backup cannot find a corresponding Level 0 Backup and takes a full Backup of these Datafiles. Therefore the Size of the Backup is growing that much.

So to avoid this we have to ensure that Level 0 Backup from Week 1 for all Datafiles is available for all 4 Weeks (until the next Level 0 Backup is taken).Even do not move this Backup outside RMAN - RMAN must be able to access the Level 0 Backup at its cataloged Location.

We tested by not moving the Incremental Level 0 backup until the 4th weeks backup was taken from its original location. After this the 4th week backup was fine and was creating the backup size of normal.

Hope this helps !

Regards,

Navaneeth