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.
Make DBA Life Easy 