Re: Calculating archivedlog file size per day

> I want to calculate archivedlog file size per day to estimate backup
> storage area for the database which is currently operating in
> NOARCHIVELOG MODE. My redo log filesize is 100MB and below is the
> query.
>
> select trunc(FIRST_TIME),count(*)*100 size_in_MB
> from gv$loghist
> group by trunc(FIRST_TIME);
>

You should divide by 100, not multiply by 100, to have your answer displayed in Mbytes.

This query might give a rough worst-case estimate, but the amount of redo actually written to each redo logfile sequence is not stored in this view. See below...

> If the database is in ARCHIVELOG mode, then I can use the below query
> to calculate the same (of course I can check file system)
>
> select trunc(COMPLETION_TIME),count(*)*100 size_in_MB
> from gv$archived_log
> group by trunc(COMPLETION_TIME);
>
> Just wanted to verify.
>

It is not valid to assume that every archived redo logfile will be the size defined for the online redo logfiles. Shortened files are created all the time (i.e. manual logfile switch, shutdown, etc). A more useful query would be:

    select trunc(completion_time), sum(blocks*block_size)/1048576 mb     from gv$archived_log
    group by trunc(completion_time);

Hope this helps...

--
http://www.freelists.org/webpage/oracle-l