If you backup your database at least weekly, you could check the datafile size from your backup history.
Without catalog :
select to_char(d,'"W"IW-IYYY') compl, sum(b)/1024/1024/1024*8192 GB
from
(
select max(DATAFILE_BLOCKS) b, trunc(completion_time,'IW') d
from v$backup_datafile
group by FILE# ,trunc(completion_time,'IW')
)
group by d
order by d;
COMPL GB
-------- ------
W30-2012 3.73
W31-2012 4.84
W32-2012 5.00
W33-2012 5.05
W34-2012 5.35
W35-2012 5.80
W36-2012 6.12
W37-2012 6.39
W38-2012 .93
W39-2012 7.02
W40-2012 7.56
W41-2012 7.72
W42-2012 7.88
W43-2012 8.08
W44-2012 8.83
W45-2012 9.03
W46-2012 9.45
W47-2012 9.61
W48-2012 10.11
W49-2012 10.29
W50-2012 10.38
The history mostly depends on control_file_record_keep_time. If you do not use an rman catalog, set it to a high value like 93 (3M) or 366 (1Y)
With the rman catalog, use the RC_ view
select DB_NAME,to_char(d,'"W"IW-IYYY') compl, sum(b)/1024/1024/1024*8192 GB
from
(
select DB_NAME,max(DATAFILE_BLOCKS) b, trunc(completion_time,'IW') d
from rc_backup_datafile
group by DB_NAME,FILE# ,trunc(completion_time,'IW')
)
group by DB_NAME,d
order by DB_NAME,d;
DB_NAME COMPL GB
-------- -------- ------
DB01 W30-2012 3.73
DB01 W31-2012 4.83
DB01 W32-2012 5.00
DB01 W33-2012 5.05
DB01 W34-2012 5.34
DB01 W35-2012 5.79
DB01 W36-2012 6.11
DB01 W37-2012 6.39
DB01 W38-2012 .93
DB01 W39-2012 7.01
DB01 W40-2012 7.56
DB01 W41-2012 7.71
DB01 W42-2012 7.87
DB01 W43-2012 8.08
DB01 W44-2012 8.82
DB01 W45-2012 9.02
DB01 W46-2012 9.44
DB01 W47-2012 9.60
DB01 W48-2012 10.10
DB01 W49-2012 10.28
DB01 W50-2012 10.37
If you need to check which table grows the most, check How big was my table yesterday. But remember, RMAN backup is free to use, AWR and the WRI$ tables require the diagnostic pack and the Enterprise edition
good idea!
Nice and useful idea. I would extend it to cover tablespace block size (not just the default one). Example (hopefully the correct extension) is below. Thanks.
select DB_NAME,to_char(d,'”W”IW-IYYY’) compl, sum(b)/1024/1024/1024 GB
from
(
select DB_NAME,max(DATAFILE_BLOCKS*BLOCK_SIZE) b, trunc(completion_time,’IW’) d
from rc_backup_datafile where DB_NAME=’&DB_NAME’
group by DB_NAME,FILE# ,trunc(completion_time,’IW’)
)
group by DB_NAME,d
order by 1;
Indeed Vaclav, that was very lazy from me! Thank you
We are often asked not for size of the database itself but rather for size of the real backupsets on the tapes. So far we used db view v$rman_backup_job_details to get this information. But with small modification of Laurent’s idea we can get the same information directly from rman catlog as well. That is maybe more practical.
Example is below:
select DB_NAME,to_char(d,'”W”WW-IYYY’) compl, sum(b)/1024/1024/1024 GB
from
(
select DB_NAME,max(BLOCKS*BLOCK_SIZE) b, trunc(completion_time,’WW’) d
from rc_backup_datafile where DB_NAME=’&DB_NAME’
group by DB_NAME,FILE# ,trunc(completion_time,’WW’)
)
group by DB_NAME,d
order by 1;
One note: I replaced ISO format by calendar one as well.