Size of RMAN incremental backups

We had a situation recently where backup destination on a Linux server was 97%. Our system admin, wanted to know why? because RMAN retention has not changed, we wanted to know the db did actually grew that big to make that difference. We were taking Lavel 0 backups every Sunday followed by Level 1 and 2 backups during the week and Level 0 again next Sunday.

We were keeping 45 days worth of backups on disk. Now, we needed to find out the size of backups sets every week and then calculate which week had a enormous growth in size. Messy way of doing this is, at the UNIX level, we can issue, ls -ltr and then total the backups belonging to each week. The database was 500Gb and each backupsets had different sizes. It was a cumbersome effort.

How did we do this elegantly in RMAN? There is a view called rc_backup_datafile that you can use fir this purpose.

First Week

select
sum(blocks * block_size)/(1024*1024*1024)
from rc_backup_datafile
where completion_time between ('04-APR-2010') and ('10-APR-2010')
order by 1

SQL> /

SUM(BLOCKS*BLOCK_SIZE)/(1024*1024*1024)

---------------------------------------
255.587982

Second week

select
sum(blocks * block_size)/(1024*1024*1024)
from rc_backup_datafile
where completion_time between ('11-APR-2010') and ('17-APR-2010')
order by 1
SQL> /



SUM(BLOCKS*BLOCK_SIZE)/(1024*1024*1024)
---------------------------------------
255.705849


select
sum(blocks * block_size)/(1024*1024*1024)
from rc_backup_datafile
where completion_time between ('26-APR-2010') and ('03-MAY-2010')
order by 1

SQL> /


SUM(BLOCKS*BLOCK_SIZE)/(1024*1024*1024)
---------------------------------------
283.666035


select
sum(blocks * block_size)/(1024*1024*1024)
from rc_backup_datafile
where completion_time between ('04-MAY-2010') and ('11-MAY-2010')
order by 1

SQL> /


SUM(BLOCKS*BLOCK_SIZE)/(1024*1024*1024)
---------------------------------------
279.676537

Ok, between 26th Apr and 3rd May, the backups had grown almost 30Gb. It was admitted to me , there indeed was a major upload that happened that week, which would explain the sudden increase in backup directory usage.

Ok, demand for more disk was justified!