To drop a logfile group, it cannot be the current logfile group.
Attempting to drop the logfile group results in the following error
when attempting to drop a logfile group that has an active status:
SQL> ALTER DATABASE DROP LOGFILE GROUP 3;
ALTER DATABASE DROP LOGFILE GROUP 3
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of instance ORA920 (thread 1)
ORA-00312: online log 3 thread 1: ‘<file_name>’
— just for fun
select * from v$logfile;
select * from v$log;
— status “CURRENT” cannot be dropped
select group#, members, bytes, status from v$log;
GROUP# MEMBERS BYTES STATUS
———- ———- ———- —————-
1 2 209715200 INACTIVE
2 2 209715200 INACTIVE
3 2 209715200 CURRENT
— Fix it by performing a checkpoint on the database:
SQL> ALTER SYSTEM CHECKPOINT GLOBAL;
System altered.
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
select group#, members, bytes, status from v$log;
GROUP# MEMBERS BYTES STATUS
———- ———- ———- —————-
1 2 209715200 INACTIVE
2 2 209715200 CURRENT
3 2 209715200 INACTIVE
SQL> ALTER DATABASE DROP LOGFILE GROUP 3;
Database altered.