Recently we had a database refresh where a standalone db got refreshed from the 4-node RAC. After the completion of the refresh, saw 10 redo log groups.As it was a single instance database and wouldn’t have much workload, thought of dropping 6 groups.
SQL> select GROUP# from v$log; GROUP# ---------- 1 2 3 4 5 6 7 8 9 10 SQL> alter database drop logfile group 10; alter database drop logfile group 10 * ERROR at line 1: ORA-01567: dropping log 10 would leave less than 2 log files for instance UNNAMED_INSTANCE_4 (thread 4) ORA-00312: online log 10 thread 4: '/u05/flashback/matrix/MATRIX/onlinelog/o1_mf_10_79t3s884_.log'
As it was single instance database, was little astonished seeing “UNNAMED_INSTANCE_4 (thread 4)”.So started checking for some more clue
SQL> show parameter thread NAME TYPE VALUE ------------------------------------ --------------------------------- ------------------------------ parallel_threads_per_cpu integer 2 thread integer 0 SQL> show parameter cluster NAME TYPE VALUE ------------------------------------ --------------------------------- ------------------------------ cluster_database boolean FALSE cluster_database_instances integer 1
Parameter settings seems fine.Lets check what v$thread has to say –
SQL> select thread#,status from v$thread; THREAD# STATUS ---------- ------------------ 1 OPEN 2 CLOSED 3 CLOSED 4 CLOSED
This is where the problem is. So, disabled the thread which in turn removed the row from v$thread
SQL> alter database disable thread 4; Database altered. SQL> alter database drop logfile group 10; Database altered. SQL> alter database drop logfile group 9; Database altered.
Similarly, disabled thread 2 and 3 and dropped the redo log group.
SQL> select thread#,status from v$thread; THREAD# STATUS ---------- ------------------ 1 OPEN
6 thoughts on “ORA-01567: dropping log N would leave less than 2 log files for instance UNNAMED_INSTANCE_N (thread N)”
I encountered the same issues where we cloned a 4 RAC database onto a 2 node rac database; when trying to drop the logfile, we saw the following errors:
ORA-01567: dropping log 11 would leave less than 2 log files for instance UNNAMED_INSTANCE_3 (thread 3)
Following your advice, we tries to disable thread 3, and it looks thread 3 row did not get removed from v$thread. Do you know why?
Please advise. Thanks.
SQL> select thread#,status from v$thread;
THREAD# STATUS
———- ——
1 OPEN
2 OPEN
3 CLOSED
4 CLOSED
SQL> alter database disable thread 3;
Database altered.
SQL> select thread#,status from v$thread;
THREAD# STATUS
———- ——
1 OPEN
2 OPEN
3 CLOSED
4 CLOSED
Hi,
After disabling the thread, you need to drop the logfile group. Also check thread parameter.
thanks,good explanation
thanks it helped me ….
Liked
Thanks dude 🙂