How to Determine Archived Log Size

Archived Log Size

Before determining archived log size, you have to know that archived logs are derived (retired) from online redo logs switching. That is, you are not determining archived log size, in fact you are determining online redo log size.

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

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.