How to Check Archive Log Size

  • by

In this post, I will talked about the following 3 topics.

  1. How to Define Archive Log Size?
  2. Are all Archive Logs the Same Size?
  3. How to Check Archive Log Size?

A. 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 online redo logs. In other words, you are not determining archived log size, in fact you are determining online redo log size.

1. 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

2. 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.

B. Are all Archive Logs the Same Size?

Mostly, all archived logs are the same size, but they could be affected by the following factors.

1. 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.

2. 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.

3. 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.

C. 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?

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 completion_time > sysdate-1;

 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 above query looks up archive logs in the last day (SYSDATE-1). The size of archive log can be calculated from multiplying BLOCKS by BLOCK_SIZE column.

As you can see, not all archive logs are in the same size.

Leave a Reply

Your email address will not be published. Required fields are marked *