PROBLEM:

 

I was cloning a database from rman backup . After completion of cloning, when I did RESETLOG it failed with ORA-00392 error.
RMAN> alter database open resetlogs;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 01/25/2017 16:05:27
ORA-00392: log 7 of thread 1 is being cleared, operation not allowed
ORA-00312: online log 7 thread 1: ‘/archive/CLONEDB/redo7a.log’
ORA-00312: online log 7 thread 1: ‘/archive/CLONEDB/redo7b.log’

SOLUTION:

First, check the status of REDO LOGS

RMAN> select group#,thread#,status from v$log;
 
    GROUP#    THREAD# STATUS
---------- ---------- ----------------
         5          1 CLEARING
         6          1 CLEARING
         7          1 CLEARING_CURRENT   ---------->>> 
         8          1 CLEARING
         9          2 CLEARING_CURRENT   --------->>>>> 
        10          2 CLEARING
        11          2 CLEARING
        12          2 CLEARING

Group 7 and 9 have status CLEARING_CURRENT. So clear them manually.

RMAN> alter database clear logfile group 7;
 
Statement processed
 
RMAN> alter database clear logfile group 9;
 
Statement processed
 
RMAN> select group#,thread#,status from v$log;
 
    GROUP#    THREAD# STATUS
---------- ---------- ----------------
         5          1 CLEARING
         6          1 CLEARING
         7          1 CURRENT
         8          1 CLEARING
         9          2 CURRENT
        10          2 CLEARING
        11          2 CLEARING
        12          2 CLEARING
 
8 rows selected

Now no group is in clearing_current mode. Lets do resetlog again.

RMAN> alter database open resetlogs;
 
Database altered

Voilla…

Meaning of different states of redolog:

UNUSED – Online redo log has never been written to. This is the state of a redo log that was just added, or just after a RESETLOGS, when it is not the current redo log.
CURRENT – Current redo log. This implies that the redo log is active. The redo log could be open or closed.
ACTIVE – Log is active but is not the current log. It is needed for crash recovery. It may be in use for block recovery. It may or may not be archived.
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.
INACTIVE – Log is no longer needed for instance recovery. It may be in use for media recovery. It might or might not be archived.
INVALIDATED – Archived the current redo log without a log switch.

 

FOR ANY QUERIES, PLEASE POST IN FORUM.DBACLASS.COM