Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
If we are trying to drop the redo log group created for a thread that is active and if the drop operation of redo log groups would leave less than 2 redo log files for instance, the drop process of the redo log groups will give an ORA-01567 error.
The possible error will be as follows.
1 2 3 4 5 6 7 | SQL> alter database drop logfile group 8; alter database drop logfile group 8 * ERROR at line 1: ORA-01567: dropping log 8 would leave less than 2 log files for instance ORCL4 (thread 4) ORA-00312: online log 8 thread 4: '+RECO/orcl/onlinelog/group_8.21358.908503693' ORA-00312: online log 8 thread 4: '+DATA/orcl/onlinelog/group_8.1527.908497073' |
After making sure that the instance in which redo log group is used is closed, the redo log group will be dropped when this thread is disabled.
Querying threads:
1 2 3 4 5 6 7 8 | SQL> select thread#,status from v$thread; THREAD# STATUS ---------- ------------------ 1 OPEN 2 OPEN 3 OPEN 4 CLOSED |
If the instance with thread number 4 is closed but the thread is still active, it can be seen from the query above.
In this case, we have to disable the thread of the instance we closed.
1 2 3 | SQL> alter database disable thread 4; Database altered. |
After that, the drop process of the redo log created for the relevant thread will be completed successfully.
1 2 3 4 5 6 7 | SQL> alter database drop logfile group 8; Database altered. SQL> alter database drop logfile group 9; Database altered. |
