PROBLEM:

While dropping a redolog group, got below error.

SQL> alter database drop logfile group 3;
alter database drop logfile group 3
*
ERROR at line 1:
ORA-01623: log 3 is current log for instance SBIP18DB (thread 1) - cannot drop
ORA-00312: online log 3 thread 1: '/SIBIP16/SBIP18DB/SBIP18DB/redo03.log'

SOLUTION:

First, check the status of the redolog group.

select l.group#, l.thread#,
f.member,
l.archived,
l.status,
(bytes/1024/1024) fsize
from
v$log l, v$logfile f
where f.group# = l.group#
order by 1,2

GROUP# THREAD# MEMBER                                                                 ARCHIVED   STATUS     Size (MB)
------ ------- ---------------------------------------------------------------------- ---------- ---------- ---------
     2       1 /SIBIP16/SBIP18DB/SBIP18DB/redo02.log                                  YES        ACTIVE            50
     3       1 /SIBIP16/SBIP18DB/SBIP18DB/redo03.log                                  NO         CURRENT           50  ---- >>>>
     4       1 /SIBIP16/SBIP18DB/SBIP18DB/redo04.log                                  YES        UNUSED            50

Here the status of the redolog group, which are trying to drop is CURRENT. i.e This implies that the redo log is active. The redo log could be open or closed.

So we need to make the status of the redolog to INACTIVE. Switch logfile multiple times, till the status becomes INACTIVE.

SQL> alter system switch logfile;

SQL>  @log_member

GROUP# THREAD# MEMBER                                                                 ARCHIVED   STATUS     Size (MB)
------ ------- ---------------------------------------------------------------------- ---------- ---------- ---------
     2       1 /SIBIP16/SBIP18DB/SBIP18DB/redo02.log                                  YES        INACTIVE          50
     3       1 /SIBIP16/SBIP18DB/SBIP18DB/redo03.log                                  YES        ACTIVE            50
     4       1 /SIBIP16/SBIP18DB/SBIP18DB/redo04.log                                  NO         CURRENT           50

SQL> alter system switch logfile;

SQL>  @log_member

GROUP# THREAD# MEMBER                                                                 ARCHIVED   STATUS     Size (MB)
------ ------- ---------------------------------------------------------------------- ---------- ---------- ---------
     2       1 /SIBIP16/SBIP18DB/SBIP18DB/redo02.log                                  NO         CURRENT           50
     3       1 /SIBIP16/SBIP18DB/SBIP18DB/redo03.log                                  YES        INACTIVE          50 ---- >>>>>>>>>>>>>
     4       1 /SIBIP16/SBIP18DB/SBIP18DB/redo04.log                                  YES        INACTIVE          50

As the status is INACTIVE now, we can drop it.

SQL> alter database drop logfile group 3;

SQL>  @log_member

GROUP# THREAD# MEMBER                                                                 ARCHIVED   STATUS     Size (MB)
------ ------- ---------------------------------------------------------------------- ---------- ---------- ---------
     2       1 /SIBIP16/SBIP18DB/SBIP18DB/redo02.log                                  NO         CURRENT           50
     4       1 /SIBIP16/SBIP18DB/SBIP18DB/redo04.log                                  YES        INACTIVE          50