Resizing or Recreating Online Redo Log Files

Online redo logs are sized too small causing database performance problems.

I had 11MB logfiles in each group.

I decided to increase the redo log file size to 100MB

The redo logs must be dropped and recreated for changing the redo log size. It can be done without shutdown the database. Redo group status must be INACTIVE before dropping.

There is a basic requirement in Oracle database that there should always be a minimum 2 redo log groups available with the database. So we can't drop any redo groups if there  are only 2 groups.

First we will see the current redo log files status
$sqlplus / as sysdba

SQL> select group#,bytes,status from v$log;

    GROUP#      BYTES STATUS
---------- ---------- ----------------
         1   10485760 ACTIVE
         2   10485760 CURRENT
         3   10485760 ACTIVE

To convert an group status to inactive , execute the checkpoint

SQL> alter system checkpoint;

SQL> select group#,bytes,status from v$log;

    GROUP#      BYTES STATUS
---------- ---------- ----------------
         1   10485760 ACTIVE
         2   10485760 CURRENT
         3   10485760 INACTIVE

The group 3 is inactive
Drop the 3rd group file using the following command

SQL> alter database drop logfile group 3;
Database altered

Re-Create dropped online redo log group

SQL> alter database add logfile group 3 (
'/u01/app/oracle/PROD/db/app_sts/PROD/log3a.dbf','/u01/app/oracle/PROD/db/app_sts/PROD/log3b.dbf''/u01/app/oracle/PROD/db/app_sts/PROD/log3c.dbf') size 512M
SQL> select group#,bytes,status from v$log;

 GROUP#      BYTES STATUS
---------- ---------- ----------------
         1   10485760 ACTIVE
         2   10485760 ACTIVE
         3   104857600 UNUSED

After adding the new log file, it status should become current. using the following command
SQL> alter system switch logfile;

SQL> select group#,bytes,status from v$log;

    GROUP#      BYTES STATUS
---------- ---------- ----------------
         1   10485760 ACTIVE
         2   10485760 ACTIVE
         3   104857600 CURRENT



Repeat the same steps for other 2 log files.