Blog

ORA-00392: log 2 of thread 1 is being cleared, operation not allowed

ORA-00392
While doing your DBA tasks, you may encounter this ORA-00392 error while issuing resetlogs. Today we will discuss how to resolve this error with steps in detail. Let’s discuss this :

Problem :

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00392: log 2 of thread 1 is being cleared, operation not allowed
ORA-00312: online log 2 thread 1: '/opt/oraredo01/TEST/redo/redo02a.dbf'
ORA-00312: online log 2 thread 1: '/opt/oraredo02/TEST/redo/redo02b.dbf'

Solution :

Step 1: Check the status of that redo log for which you are facing error :

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

    GROUP#    THREAD# STATUS
---------- ---------- ----------------
         1          1 CLEARING
         4          1 CLEARING
         3          1 CLEARING
         2          1 CLEARING_CURRENT

Step 2: Clear that logfile

Caution: Please be cautious while clearing the redo log, crosscheck once as redo logs are crucial for Database Recovery.

SQL> alter database clear unarchived logfile group 2;

Database altered.

Step 3: Now check the status of the redo log again

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

    GROUP#    THREAD# STATUS
---------- ---------- ----------------
         1          1 CLEARING
         4          1 CLEARING
         3          1 CLEARING
         2          1 CURRENT

SQL> select MEMBER from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/opt/oraredo01/TEST/redo/redo01a.dbf
/opt/oraredo01/TEST/redo/redo01b.dbf
/opt/oraredo01/TEST/redo/redo02a.dbf
/opt/oraredo02/TEST/redo/redo02b.dbf
/opt/oraredo01/TEST/redo/redo03a.dbf
/opt/oraredo02/TEST/redo/redo03b.dbf
/opt/oraredo01/TEST/redo/redo04a.dbf
/opt/oraredo02/TEST/redo/redo04b.dbf

Step 4: Issue reset logs command:

SQL> alter database open resetlogs;

Database altered.

Step 5: Validate the database name and open mode if everything is fine

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
TEST      READ WRITE

So, here are the detailed steps by which you can resolve ORA-00392 and ORA-00312 error. Hope this article helps !!

Also, you can take reference from Oracle Doc ID 1352133.1 also.

Our Other ORA Error-Related Articles:

Leave a Reply