数据库在open RESETLOGS时报错 ORA-00392、ORA-00312

数据库在open RESETLOGS时报错，如下
SQL> alter database open RESETLOGS;
alter database open RESETLOGS
*
ERROR at line 1:
ORA-00392: log 4 of thread 2 is being cleared, operation not allowed
ORA-00312: online log 4 thread 2: ‘+ARCH/uqonline/onl/redo04.log’

可能原因：
open RESETLOGS异常中，使重做日志在controlfile中的状态为clear /CLEARING_CURRENT:

处理办法：
1、检查状态：
select group#, THREAD#,bytes/1024/1024||‘M’,status from v$log;
2、清理一下redo日志

select ‘ALTER DATABASE CLEAR LOGFILE GROUP ’ ||group# ||’;’ from v$log where status like ‘CLEARING%’;
select ‘ALTER DATABASE CLEAR unarchived logfile GROUP ’ ||group# ||’;’ from v$log where status like ‘CLEARING%’;
alter database clear unarchived logfile group 1 ;
alter database clear unarchived logfile group 2 ;
alter database clear unarchived logfile group 3 ;
alter database open resetlogs;

也可以重建一下redo（本次案例中使用的是重建redo解决的）

alter database drop logfile group 1;
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 1 ‘+ARCH’ SIZE 200M;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 3 ‘+ARCH’ SIZE 200M;

mos上也提到可以通过重建控制文件解决

Get the controlfile trace from below command :
SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS ‘/tmp/control.sql’ resetlogs ;

Modify the CREATE CONTROLFILE script /tmp/control.sql and ensure that all directories for the online redo logs exist and Oracle has permission to write to it

Create the controlfile in NOMOUNT state :
SQL> STARTUP FORCE NOMOUNT
SQL> @/tmp/control.sql

controlfile created

Run a fake recovery :
SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL ;
Type <CANCEL> when prompted

Open with RESETLOGS option:
SQL> ALTER DATABASE OPEN RESETLOGS ;