Open Resetlog Failure : ORA-00392: log 1 of thread 1 is being cleared, operation not allowed

SCOPE

Restoring the database using RMAN backup.

After completion of cloning to the staging environment, OPEN RESETLOGS fails with error as below.

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

SQL> alter database open resetlogs ;
alter database open resetlogs
*
ERROR at line 1:
ORA-00392: log 1 of thread 1 is being cleared, operation not allowed
ORA-00312: online log 1 thread 1: '+DATA'
ORA-00312: online log 1 thread 1: '+RECO'

Check the status of Online redo logs

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

    GROUP#    THREAD# STATUS
---------- ---------- ----------------
         1          1 CLEARING_CURRENT    <---------
         2          1 CLEARING
         6          1 CLEARING
         4          1 CLEARING
         5          1 CLEARING
         3          1 CLEARING

6 rows selected.

 

Group 1 have status CLEARING_CURRENT.  

Lets clear it manually and check the status. Try to open the database now.

SQL> alter database clear logfile group 1 ;

Database altered.

 

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

    GROUP#    THREAD# STATUS
---------- ---------- ----------------
         1          1 CLEARING

                   

SQL>  alter database open resetlogs;

Database altered

 

Conclusion

CLEARING – Log is being re-created as an empty log after an ALTER DATABASE CLEAR LOGFILE statement. After the log is cleared, the status changes to UNUSED.

CLEARING_CURRENT – Current log is being cleared of a closed thread. The log can stay in this status if there is some failure in the switch such as an I/O error writing the new log header.

 

STATE of REDO Logs

CURRENT The log group is currently being written to by the log writer.

ACTIVE The log group is required for crash recovery and may or may not have been archived.

CLEARING The log group is being cleared out by an ALTER DATABASE CLEAR LOGFILE command.

CLEARING_CURRENT The current log group is being cleared of a closed thread.

INACTIVE The log group isn’t required for crash recovery and may or may not have been archived.

UNUSED The log group has never been written to; it was recently created.

 

Read More Here : Working with Redo Logs

Found this post interesting? Subscribe us  😊😉