ORA-00392 ORA-00312 log clearing fault

ORA-00392 ORA-00312 log clearing fault

Recently encountered a ORA-00392 after restoring the Oracle database open: log 3 of thread 1 is being cleared, operation not allowed, its literal meaning is that the log file is being cleared, operation is not allowed. Generally, when we do not completely recover the log file, it needs to be cleared, and the log file is being cleared. Not very understandable. The following is a solution to this problem.

I. Fault symptom

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 allowedORA-00312: online log 3 thread 1: '/oradata/sincnet/redo03.log' -- View fault description SQL> ho oerr ora 0039200392,000 00, "log % s of thread % s is being cleared, operation not allowed "// * Cause: An operation encountered this online log in the middle of being // cleared. the command that began the clearing may have terminated // without completing the clearing. // * Action: If the clear command is still executing then wait for its // completion. if it terminated then reissue the clear command, or // drop the log. -- the three log files in the following SQL statement table are all in the clearing state. SQL> select group #, bytes/1024/1024 | 'M', status from v $ log; GROUP # BYTES/1024/1024 | 'M' STATUS ---------- ----------------------------------------- ---------------- 1 50 m clearing 3 50 M CLEARING_CURRENT 2 50 M CLEARING

Ii. troubleshooting

Run the clear logfile command SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1; Database altered. SQL> ALTER DATABASE CLEAR LOGFILE GROUP 2; Database altered. SQL> ALTER DATABASE CLEAR LOGFILE GROUP 3; Database altered. -- check the status again. The status displays the common normal status SQL> select group #, bytes/1024/1024 | 'M', status from v $ log; GROUP # BYTES/1024/1024 | 'M' STATUS ---------- ---------------------------------------------------- 1 50 m unused 3 50 m current 2 50 m unused -- try again open resetlog, still error, instance termination, need upgradeSQL> alter database open resetlogs; alter database open resetlogs * ERROR at line 1: ORA-01092: ORACLE instance terminated. disconnection forcedORA-00704: bootstrap process failureORA-39700: database must be opened with UPGRADE optionProcess ID: 30553 Session ID: 1217 Serial number: 3 -- check whether the pmon process SQL> ho ps-ef | grep pmonoracle 30589 30440 30591 0 00:00:00 pts/1/bin/bash-c ps-ef | grep pmonoracle 30589 0 pts/1 00:00:00 grep pmon -- Author: leshami -- Blog: tables> startup upgrade; ORA-24324: service handle not initializedORA-01041: internal error. hostdef extension doesn't existSQL> SQL> exitDisconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit ProductionWith the Partitioning, OLAP, data Mining and Real Application Testing options [oracle @ 10134569 DBSRV] $ sqlplus/as sysdbaSQL * Plus: Release 11.2.0.4.0 Production on Wed Feb 24 16:03:15 2016 Copyright (c) 1982,201 3, Oracle. all rights reserved. connected to an idle instance. SQL> startup upgrade; ORACLE instance started. total System Global Area 1068937216 bytesFixed Size 2260088 bytesVariable Size 704643976 bytesDatabase Buffers 352321536 bytesRedo Buffers 9711616 bytesDatabase mounted. database opened. SQL> @ $ ORACLE_HOME/rdbms/admin/catupgrd. SQL; -- execute the upgrade script