ORA-01567: dropping log N would leave less than 2 log files for instance UNNAMED_INSTANCE_N (thread N)

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
Advertisements

6 thoughts on “ORA-01567: dropping log N would leave less than 2 log files for instance UNNAMED_INSTANCE_N (thread N)

  1. 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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s