ORA-00392 ORA-00312 日志正在清除故障

谁践踏了优雅 2022-08-21 05:42 184阅读 0赞

> 最近在还原Oracle数据库后open的时候碰到了ORA-00392: log 3 of thread 1 is being cleared, operation not allowed，其字面含义则是日志文件正在被清除，不允许操作。通常情况下，当我们基于不完全恢复的时候，日志文件需要被清空，而此时是正在被清空。不是很好理解啊。下面是这个问题的解决方案。

## 一、故障现象 ##

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 allowed
    ORA-00312: online log 3 thread 1: '/oradata/sincnet/redo03.log'
    
    -- 查看故障描述信息
    SQL> ho oerr ora 00392
    00392, 00000, "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.
    
    --下面的SQL语句表名3个日志文件都处于clearing状态
    SQL> select group#,bytes/1024/1024||'M',status from v$log;
    
        GROUP# BYTES/1024/1024||'M'                      STATUS
    ---------- ----------------------------------------- ----------------
             1 50M                                       CLEARING
             3 50M                                       CLEARING_CURRENT
             2 50M                                       CLEARING

# 二、故障处理 #

下面直接执行clear logfile 命令
    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.
    
    --再次查看状态，此时状态显示为常见的几种正常状态
    SQL> select group#,bytes/1024/1024||'M',status from v$log;
    
        GROUP# BYTES/1024/1024||'M'                      STATUS
    ---------- ----------------------------------------- ----------------
             1 50M                                       UNUSED
             3 50M                                       CURRENT
             2 50M                                       UNUSED
    
    --再次尝试open resetlog，依旧错误，实例终止，需要upgrade
    SQL> alter database open resetlogs;
    alter database open resetlogs
    *
    ERROR at line 1:
    ORA-01092: ORACLE instance terminated. Disconnection forced
    ORA-00704: bootstrap process failure
    ORA-39700: database must be opened with UPGRADE option
    Process ID: 30553
    Session ID: 1217 Serial number: 3
    
    --查看是否存在pmon进程
    SQL> ho ps -ef|grep pmon
    oracle   30589 30440  0 16:02 pts/1    00:00:00 /bin/bash -c ps -ef|grep pmon
    oracle   30591 30589  0 16:02 pts/1    00:00:00 grep pmon
    
    --Author : Leshami
    --Blog : http://blog.csdn.net/leshami
    --下面尝试upgrade，因为当前基于一个11.2.0.1的备份恢复到11.2.0.4，所以需要upgrade
    SQL> startup upgrade;
    ORA-24324: service handle not initialized
    ORA-01041: internal error. hostdef extension doesn't exist
    SQL> 
    SQL> exit
    Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    [oracle@10134569 DBSRV]$ sqlplus / as sysdba
    
    SQL*Plus: Release 11.2.0.4.0 Production on Wed Feb 24 16:03:15 2016
    
    Copyright (c) 1982, 2013, Oracle.  All rights reserved.
    
    Connected to an idle instance.
    
    SQL> startup upgrade;
    ORACLE instance started.
    
    Total System Global Area 1068937216 bytes
    Fixed Size                  2260088 bytes
    Variable Size             704643976 bytes
    Database Buffers          352321536 bytes
    Redo Buffers                9711616 bytes
    Database mounted.
    Database opened.
    SQL> @$ORACLE_HOME/rdbms/admin/catupgrd.sql; --执行升级脚本