In this post, I will talked about the following 3 topics.
How to Define Archive Log Size?
Before determining archived log size, you have to know that archived logs are derived from online redo logs switching. Which means, archived logs are retired and copied from online redo logs. In other words, you are not determining archived log size, in fact you are determining online redo log size.
Define Mean Time To Recover (MTTR)
But now the question is: How to set a proper and optimal size for online redo logs? There's a simple answer for us. If your database is Oracle 10g onward, then you can get an optimal value from current instance configuration.
First, you have to make sure FAST_START_MTTR_TARGET has been set to a value other than zero.
SQL> show parameter fast_start_mttr_target;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fast_start_mttr_target integer 1800
Check Optimal Redo Log Size
If you have set FAST_START_MTTR_TARGET, i.e. 1800 seconds in this case, then you can get an advisory value for optimizing logfile size by this query.
SQL> select target_mttr, estimated_mttr, optimal_logfile_size from v$instance_recovery;
TARGET_MTTR ESTIMATED_MTTR OPTIMAL_LOGFILE_SIZE
----------- -------------- --------------------
146 21 1469
As you can see, the third column OPTIMAL_LOGFILE_SIZE is the answer. Oracle recommends you to set the redo log size to 1469MB.
Next, we might need to resize redo logs in order to conform to the recommended size.
Are all Archive Logs the Same Size?
Mostly, archived logs are the same size, but they could be affected by the following factors.
Do all redo logs defined as the same size?
Although we should define all redo log groups and members as the same size, you may see different sizes among redo logs are defined in few databases. Usually, the larger redo logs are added after the database creation. That's why we saw different sized archived logs.
Have you ever set log switch interval?
Log switching can be done at a fixed interval by setting ARCHIVE_LAG_TARGET initialization parameter to help you to control the archival frequency.
If the parameter is set other than the default value (0, disabled), some log switching will occur before redo logs are filled. Such premature redo logs make different sized archived logs.
RMAN Backup
Beside setting log switching interval, taking a database backup with archived logs by RMAN will trigger log switching. For example, we'd like to have a self-contained full backup like this:
RMAN> backup database plus archivelog;
Starting backup at 27-JUN-16
current log archived
...
Starting backup at 27-JUN-16
current log archived
...
As you can see, 2 redo logs were archived by RMAN, before and after the full backup. They are smaller than defined size.
How to Check Archive Log Size?
Now, let's get back to our main topic, how to check the archive log size in the database?
Size of Each Archived Logs
To see the size of each archived log, we query V$ARCHIVED_LOG:
SQL> column name format a50;
SQL> column "Size (MB)" format 9999.999;
SQL> select sequence#, name, blocks*block_size/1024/1024 "Size (MB)" from v$archived_log where status = 'A' and standby_dest = 'NO' and completion_time > sysdate-1 order by name;
SEQUENCE# NAME Size (MB)
---------- -------------------------------------------------- ---------
175398 /oradata/FRA/ORCL/ARCH/ERPAPP1_1_175398.arc 1023.999
175399 /oradata/FRA/ORCL/ARCH/ERPAPP1_1_175399.arc 1023.999
175400 /oradata/FRA/ORCL/ARCH/ERPAPP1_1_175400.arc 749.232
175401 /oradata/FRA/ORCL/ARCH/ERPAPP1_1_175401.arc 43.875
175402 /oradata/FRA/ORCL/ARCH/ERPAPP1_1_175402.arc 727.369
175403 /oradata/FRA/ORCL/ARCH/ERPAPP1_1_175403.arc 6.957
175404 /oradata/FRA/ORCL/ARCH/ERPAPP1_1_175404.arc 1023.993
175405 /oradata/FRA/ORCL/ARCH/ERPAPP1_1_175405.arc 1023.999
...
The size of every single archive log can be calculated from multiplying BLOCKS by BLOCK_SIZE column. Furthermore, we calculated only archived logs which are completed in 24 hours and available in the database.
As you can see, NOT all archive logs are in the same size, most archived logs are in 1024MB (i.e. 1GB).
Size of All Archived Logs
All Availables
To see the overall size of available archived logs in the database server, we can do this:
SQL> column "Total Size (GB)" format 9999.999;
SQL> select sum(blocks*block_size)/1024/1024/1024 "Total Size (GB)" from v$archived_log where status = 'A' and standby_dest = 'NO';
Total Size (GB)
---------------
436.632
As we can see, we have over 400GB of archived logs, we'd better delete them more aggressively from now on.
Last 24 Hours
To sum up all available archived log size in the last 24 hours in the database server, we can do this:
SQL> select sum(blocks*block_size)/1024/1024/1024 "Total Size (GB)" from v$archived_log where status = 'A' and standby_dest = 'NO' and completion_time > sysdate - 1;
Total Size (GB)
---------------
101.828
We added a filter to confine the time scope of archived logs in order to calculate overall size in the latest 24 hours.
Another topic you may be interested in is about archive block size of archived log files, you may have a look.