Oracle:ORA-00392: log 3 of thread 1 is being cleared, operation not allowed
如题所示的问题发生在数据库的启动过程中,具体如下:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
[oracle@oradg2 dbs]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Sat Jun 18 08:15:57 2016 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select instance_name,status from v$instance; INSTANCE_NAME STATUS ---------------- ------------ orcl MOUNTED 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/orcl/redo03.log' SQL> |
发生这个问题的时候,REDO日志的状态可能如下所示:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
SQL> desc v$log; Name Null? Type ----------------------------------------- -------- ---------------------------- GROUP# NUMBER THREAD# NUMBER SEQUENCE# NUMBER BYTES NUMBER BLOCKSIZE NUMBER MEMBERS NUMBER ARCHIVED VARCHAR2(3) STATUS VARCHAR2(16) FIRST_CHANGE# NUMBER FIRST_TIME DATE NEXT_CHANGE# NUMBER NEXT_TIME DATE SQL> SQL> select group#,bytes/1024/1024 "MB",status from v$log; GROUP# MB STATUS ---------- ---------- ---------------- 1 50 CLEARING 3 50 CLEARING_CURRENT 2 50 CLEARING SQL> SQL> desc v$logfile; Name Null? Type ----------------------------------------- -------- ---------------------------- GROUP# NUMBER STATUS VARCHAR2(7) TYPE VARCHAR2(7) MEMBER VARCHAR2(513) IS_RECOVERY_DEST_FILE VARCHAR2(3) SQL> SQL> col member for a40 SQL> select group#,member from v$logfile; GROUP# MEMBER ---------- ---------------------------------------- 3 /oradata/orcl/redo03.log 2 /oradata/orcl/redo02.log 1 /oradata/orcl/redo01.log SQL> |
修复该问题:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
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 "MB",status from v$log; GROUP# MB STATUS ---------- ---------- ---------------- 1 50 UNUSED 3 50 CURRENT 2 50 UNUSED SQL> SQL> select instance_name,status from v$instance; INSTANCE_NAME STATUS ---------------- ------------ orcl MOUNTED SQL> alter database open resetlogs; Database altered. SQL> select instance_name,status from v$instance; INSTANCE_NAME STATUS ---------------- ------------ orcl OPEN SQL> |
————————————————————————
Done。