It is very dangerous when we deal with online redolog files in database which records the active transaction changes into it and needed for media ,block and crash recovery based on their states

We have four usual states of redologs in a typical oracle database

1)active 2)inactive 3)current 4) unused

active – require for instance crash recovery in case of failure and block recovery

inactive – not require for recovery and no logs written by lgwr

current – lgwr writing the logs to current group

unused – states fresh newly added group

If we want to drop an online redolog group , we have to first bring the particular group to inactive state.

SQL> select a.bytes/1024/1024,a.thread#,a.sequence#,a.thread#,a.status,b.member from v$log a,v$logfile b where a.GROUP# = b.GROUP#;

A.BYTES/1024/1024    THREAD#  SEQUENCE#    THREAD# STATUS           MEMBER
----------------- ---------- ---------- ---------- ---------------- ----------------------
               50          1        213          1 INACTIVE         /app01/oradata/orcl12x/redo03.log
               50          1        215          1 CURRENT          /app01/oradata/orcl12x/redo02.log
               50          1        214          1 INACTIVE         /app01/oradata/orcl12x/redo01.log

If the redologs are switching frequently due to heavy transactions,then issue the checkpoint to save the changes to the datafile.

SQL> alter system checkpoint;

System altered.

Oracle forces the checkpoint on the instance and saves the buffered blocks to the disk

we can drop the logfiles when checkpoint completes.

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME     CON_ID
---------------- ------------- --------- ------------ --------- ----------
         1          1        214   52428800        512          1 NO
INACTIVE               3922477 26-AUG-20      3931944 26-AUG-20          0

         2          1        215   52428800        512          1 NO
CURRENT                3931944 26-AUG-20   2.8147E+14                    0

         3          1        213   52428800        512          1 NO
INACTIVE               3811949 26-AUG-20      3922477 26-AUG-20          0

We can drop either group 1 or group 3 as we need atleast minimum two redo groups for logging and add new groups.

If there is frequent log switch happen again,we can add two more groups before dropping the existing one and drop the old groups

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database drop logfile group 3;
alter database drop logfile group 3
*
ERROR at line 1:
ORA-01567: dropping log 3 would leave less than 2 log files for instance
orcl12x (thread 1)
ORA-00312: online log 3 thread 1: '/app01/oradata/orcl12x/redo03.log'

Once we drop the logfile,we can add a logfile with more size

SQL> alter database add logfile group 1 '/app01/oradata/orcl12x/redo01.log
  2  ' size 200M;

Database altered.

Now to verify the newly added logfile

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME     CON_ID
---------------- ------------- --------- ------------ --------- ----------
         1          1          0  209715200        512          1 YES
UNUSED                       0                      0                    0

         2          1        215   52428800        512          1 NO
CURRENT                3931944 26-AUG-20   2.8147E+14                    0

         3          1        213   52428800        512          1 NO
INACTIVE               3811949 26-AUG-20      3922477 26-AUG-20          0