ORA-01567: dropping log

recently my dev which was a standalone got refreshed from prod which was 3 node RAC. When I try to drop the redo log groups I was getting below error:

SQL> select group#,thread#,status from v$log;

GROUP# THREAD# STATUS
---------- ---------- ----------------
2 1 INACTIVE
3 1 CURRENT
4 2 ACTIVE
5 2 UNUSED
6 2 UNUSED
7 3 ACTIVE
8 3 UNUSED
9 3 UNUSED
10 3 UNUSED


dropped couple of log groups:

SQL> select group#,thread#,status from v$log;

GROUP# THREAD# STATUS
---------- ---------- ----------------
2 1 INACTIVE
3 1 CURRENT
4 2 ACTIVE
5 2 UNUSED
7 3 ACTIVE
8 3 UNUSED

Now since this is a standalone db I don't need threads 2 & 3 so when I tried to drop group from thread 3:

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 i3
(thread 3)
ORA-00312: online log 8 thread 3:
'+ICMDEV_FRA_01/icmdev/onlinelog/group_8.261.748372139'
ORA-00312: online log 8 thread 3:
'+ICMDEV_DATA_01/icmdev/onlinelog/group_8.373.748372177'


Hmm Crazy...

I need to first disable the thread before deleting the logs.


SQL> alter database disable thread 3;

Database altered.

SQL> select group#,thread#,status from v$log;

GROUP# THREAD# STATUS
---------- ---------- ----------------
2 1 INACTIVE
3 1 CURRENT
4 2 ACTIVE
5 2 UNUSED
7 3 INACTIVE
8 3 UNUSED

6 rows selected.

SQL> alter database drop logfile group 8;

Database altered.

SQL> alter database drop logfile group 7;

Database altered.

SQL> select group#,thread#,status from v$log;

GROUP# THREAD# STATUS
---------- ---------- ----------------
2 1 INACTIVE
3 1 CURRENT
4 2 ACTIVE
5 2 UNUSED



Also if the group you want to delete is current and you disabled the thread# of that group.

say.

SQL> select thread#,group#,status from v$log;

THREAD# GROUP# STATUS
---------- ---------- ----------------
2 1 CURRENT
2 2 INACTIVE
3 3 INACTIVE
3 4 CURRENT
1 11 INACTIVE
1 12 CURRENT


Here thread 3 and group 4 is current and now I without archiving I have disabled the thread# 3.



SQL> alter database disable thread 3;

Database altered.

SQL> select thread#,group#,status from v$log;

THREAD# GROUP# STATUS
---------- ---------- ----------------
2 1 CURRENT
2 2 INACTIVE
3 3 INACTIVE
3 4 INACTIVE
1 11 INACTIVE
1 12 CURRENT

6 rows selected.

SQL> alter database drop logfile group 3;

Database altered.

SQL> alter database drop logfile group 4;
alter database drop logfile group 4
*
ERROR at line 1:
ORA-00350: log 4 of instance UNNAMED_INSTANCE_3 (thread 3) needs to be archived
ORA-00312: online log 4 thread 3:
'+DBNAME_REDO_01/dbname/onlinelog/group_4.263.762210781'
ORA-00312: online log 4 thread 3:
'+DBNAME_REDO_02/dbname/onlinelog/group_4.264.762210783'

Here it comes, since group4 was not archived I cannot just drop the group here.
So how do we overcome this.
SQL> alter database clear unarchived logfile group 4;

Database altered.

SQL> alter database drop logfile group 4;

Database altered.

SQL> select thread#,group#,status from v$log;

THREAD# GROUP# STATUS
---------- ---------- ----------------
2 1 CURRENT
2 2 INACTIVE
1 11 INACTIVE
1 12 CURRENT



All is well now the thread# 3 is dropped.