ORA-00392: ORA-00312: alter database open resetlogs

ORA-00392: ORA-00312: alter database open resetlogs

ORA-00392: log 3 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 3 of thread 1 is being cleared, operation not allowed

ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/OCM11G/redo03.log'

Today I seems one error while opening database in resetlogs mode after successful restoration.

Scenarios

[oracle@rac131 BACKUP]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Mar 11 03:03:37 2016

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

SQL> set lin200 pages 200

SQL> column member format a55

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME

---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------

         1          1          0   52428800        512          1 YES CLEARING               1152706 26-MAR-16      1152718 26-MAR-16

         3          1          0   52428800        512          1 NO  CLEARING_CURRENT       1152742 26-MAR-16   2.8147E+14

         2          1          0   52428800        512          1 YES CLEARING               1152718 26-MAR-16      1152742 26-MAR-16

In this scenario we are getting the error for redo log 3. As the state you can see is “CLEARING CURRENT”.

Let prompt the error one more time to verify.

SQL> alter database open resetlogs ;

alter database open resetlogs

*

ERROR at line 1:

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

ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/OCM11G/redo03.log'

Now clear the logfile 3

SQL> alter database clear logfile '/u01/app/oracle/oradata/OCM11G/redo03.log' ;

Database altered.

Great………   Now validate the logfile once more

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME

---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------

         1          1          0   52428800        512          1 YES CLEARING               1152706 26-MAR-16      1152718 26-MAR-16

         3          1          0   52428800        512          1 NO  CURRENT                1152742 26-MAR-16   2.8147E+14

         2          1          0   52428800        512          1 YES CLEARING               1152718 26-MAR-16      1152742 26-MAR-16

Now open the database in resetlogs mode

SQL> alter database open resetlogs ;

Database altered.

SQL> select * from v$logfile ;

    GROUP# STATUS  TYPE    MEMBER                                                  IS_

---------- ------- ------- ------------------------------------------------------- ---

         3         ONLINE  /u01/app/oracle/oradata/OCM11G/redo03.log               NO

         2         ONLINE  /u01/app/oracle/oradata/OCM11G/redo02.log               NO

         1         ONLINE  /u01/app/oracle/oradata/OCM11G/redo01.log               NO