Removing online redo log files from a group

To remove an existing redo logfile from its member group we use the ALTER DATABASE DROP LOGFILE MEMBER sql statement. But we have to make sure that the status of the group where the corresponding logfile exists should not be current.

To check the status of the log groups and information of the log members run the following SQL statement.

SQL>SELECTa.group# ,a.status group_stat ,b.member member ,b.status member_status FROM v$log a, v$logfile b WHERE a.group# = b.group# ORDER BY a.group#, b.member;

Example:

SQL> conn sys/orcl@to_primary as sysdba
Connected.
SQL> COL group# FORM 99999
SQL> COL group_stat FORM a10
SQL> COL member FORM a30
SQL> COL mem_stat FORM a10
 SELECTa.group# ,a.status group_stat ,b.member member ,b.status member_status FROM v$log a, v$logfile b WHERE a.group# = b.group# ORDER BY a.group#, b.member;
 GROUP# GROUP_STAT MEMBER                         MEMBER_STA
------ ---------- ------------------------------ ----------
     1 INACTIVE   D:\APP\ADMIN\VIRTUAL\ORADATA\O
                  RCL\REDO01.LOG
      1 INACTIVE   D:\APP\ADMIN\VIRTUAL\ORADATA\O INVALID
                  RCL\REDO01B.LOG
      2 CURRENT    D:\APP\ADMIN\VIRTUAL\ORADATA\O
                  RCL\REDO02.LOG
      3 INACTIVE   D:\APP\ADMIN\VIRTUAL\ORADATA\O
                  RCL\REDO03.LOG
 GROUP# GROUP_STAT MEMBER                         MEMBER_STA
------ ---------- ------------------------------ ----------
      4 INACTIVE   D:\APP\ADMIN\VIRTUAL\ORADATA\O
                  RCL\REDOA4.LOG
      4 INACTIVE   D:\APP\ADMIN\VIRTUAL\ORADATA\O
                  RCL\REDOB4.LOG
      7 UNUSED     D:\APP\ADMIN\VIRTUAL\ORADATA\O
                  RCL\REDOA7.LOG
      7 UNUSED     D:\APP\ADMIN\VIRTUAL\ORADATA\O
 GROUP# GROUP_STAT MEMBER                         MEMBER_STA
------ ---------- ------------------------------ ----------
                  RCL\REDOB7.LOG
  8 rows selected.
 
 ---- Dropping a log file 
SQL> alter database drop logfile member 'D:\APP\ADMIN\VIRTUAL\ORADATA\ORCL\REDO02.LOG';
alter database drop logfile member 'D:\APP\ADMIN\VIRTUAL\ORADATA\ORCL\REDO02.LOG'
*
ERROR at line 1:
ORA-00361: cannot remove last log member
D:\APP\ADMIN\VIRTUAL\ORADATA\ORCL\REDO02.LOG for group 2

  ---- dropping a logfile from inactive/unused group
SQL> alter database drop logfile member 'D:\APP\ADMIN\VIRTUAL\ORADATA\ORCL\REDOA7.LOG';
 Database altered.
SQL>

The error ORA-00361:cannot remove last log member.. shown above is occurred while I was trying to drop the last redo logfile from group 2. a group must contain at least one redo logfile.

And you will encounter ORA-01623: error  when you try to drop log file while its group is in ‘CURRENT’ status.