ORA-01623, ORA-01624 and ORA-01567

This was tested on an Oracle 11.2.0.2.7 database. The output below shows that this database had 3 online redo log groups. You can only drop a redo log group if its STATUS is INACTIVE so when I tried to drop the CURRENT redo log group, I got an error:

 

SQL> l

  1* select group#, status from v$log

SQL> /

 

    GROUP# STATUS

---------- ----------------

         1 INACTIVE

         2 CURRENT

         3 INACTIVE

 

SQL> l

  1* alter database drop logfile group 2

SQL> /

alter database drop logfile group 2

*

ERROR at line 1:

ORA-01623: log 2 is current log for instance ORCL

(thread 1) - cannot drop

ORA-00312: online log 2 thread 1:

'/oracle/app/oracle/product/11.2.0.2.7/software_downloa

d/ORCL/redo02.log'

 

SQL>

 

I switched to a new redo log group so the previous CURRENT redo log group changed to ACTIVE but I still could not drop it:

 

SQL> alter system switch logfile

  2  /

 

System altered.

 

SQL> select group#, status from v$log

  2  /

 

    GROUP# STATUS

---------- ----------------

         1 INACTIVE

         2 ACTIVE

         3 CURRENT

 

SQL> alter database drop logfile group 2

  2  /

alter database drop logfile group 2

*

ERROR at line 1:

ORA-01624: log 2 needed for crash recovery of instance

ORCL (thread 1)

ORA-00312: online log 2 thread 1:

'/oracle/app/oracle/product/11.2.0.2.7/software_downloa

d/ORCL/redo02.log'

 

SQL>

 

Eventually it changed to INACTIVE then I was able to drop it:

 

SQL> select group#, status from v$log

  2  /

 

    GROUP# STATUS

---------- ----------------

         1 INACTIVE

         2 INACTIVE

         3 CURRENT

 

SQL> alter database drop logfile group 2

  2  /

 

Database altered.

 

SQL> select group#, status from v$log

  2  /

 

    GROUP# STATUS

---------- ----------------

         1 INACTIVE

         3 CURRENT

 

SQL>

 

You need a minimum of 2 online redo log groups at all times so that one can be archived while the other is in use. When I tried to drop another redo log group, I got a different error again:

 

SQL> select group#, status from v$log

  2  /

 

    GROUP# STATUS

---------- ----------------

         1 INACTIVE

         3 CURRENT

 

SQL> alter database drop logfile group 1

  2  /

alter database drop logfile group 1

*

ERROR at line 1:

ORA-01567: dropping log 1 would leave less than 2 log

files for instance ORCL (thread 1)

ORA-00312: online log 1 thread 1:

'/oracle/app/oracle/product/11.2.0.2.7/software_downloa

d/ORCL/redo01.log'

 

SQL>