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

228次阅读

共计 2699 个字符，预计需要花费 7 分钟才能阅读完成。

最近在还原 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; -- 执行升级脚本