ora-00392

ORA-00392 Solution

by

ORA-00392 log 1 of thread 1 is being cleared, operation not allowed, this ora error comes after restoration when we trying to open the database in resetlogs mode.

Cause: ORA-00392

The first command “alter database open resetlogs” is abnormally abrupted leaving the redo log status as CLEARING/CLEARING_CURRENT in controlfile :

ORA-00392

SQL> select GROUP#,THREAD#,SEQUENCE#,MEMBERS,ARCHIVED,STATUS,FIRST_CHANGE# from v$log order by first_change# ;
image

SOLUTION:

First you try to clear logfile group using the below query.
SQL> alter database clear unarchived logfile group 1;
SQL> alter database clear unarchived logfile group 2;
SQL> alter database clear unarchived logfile group 3;

alter database open resetlogs;

Else try this one:

Recreate the controlfile with resetlog option.

Step 1: create a new control file using the below command.

SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/u01/control.sql' resetlogs ;

Step 2: Modify create controlfile script and make sure all the directories should be available then start database nomount and recreate the controlfile.

SQL> STARTUP NOMOUNT

SQL> @/u01/control.sql

controlfile created

Step 3: Run fake database recovery using the below commands.

SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL ;

Type <CANCEL> when prompted

Step 4: Try to open the database with resetlogs.

SQL> ALTER DATABASE OPEN RESETLOGS ;

Leave a Comment