ORA-00392 from ALTER DATABASE OPEN RESETLOGS

After restoring the database, the following errors appear when opening resetlogs:

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: '+DATAC1/TESTDB/ONLINELOG/redo02.log'
SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME     CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------
         1          1          0   52428800        512          1 YES CLEARING               1682491 26-JUL-21      1682509 26-JUL-21          0
         3          1          0   52428800        512          1 YES CLEARING               1682488 26-JUL-21      1682491 26-JUL-21          0
         2          1          0   52428800        512          1 NO  CLEARING_CURRENT       1682509 26-JUL-21   2.8147E+14                    0

CAUSE

For some reason, “alter database open resetlogs” is abnormally abrupted leaving the redo log status as CLEARING/CLEARING_CURRENT in control file.

SOLUTION

SQL> alter database clear unarchived logfile group 1 ;
Database altered.

SQL>alter database clear unarchived logfile group 2 ;
Database altered.

SQL>alter database clear unarchived logfile group 3 ;
Database altered.
SQL>alter database open resetlogs;
Database altered.

If the above method is not working , then need recreate the control file with RESETLOGSĀ option.

SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/tmp/control_file.sql' resetlogs ;

Check the above control_file.sql to make sure all online redo logs directories exist.

SQL> STARTUP FORCE NOMOUNT
SQL> @/tmp/control_file.sql

controlfile created
SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL ;

Type <CANCEL> when prompted

Finally open database resetlogs successfully.

SQL> ALTER DATABASE OPEN RESETLOGS ;
Database altered.

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.