参考文档：ALTER DATABASE OPEN RESETLOGS fails with ORA-00392 (Doc ID 1352133.1)

打开一个克隆数据库报以下错误：

SQL> alter database open resetlogs; 

alter database open resetlogs 

* 

ERROR at line 1: 

ORA-00392: log 1 of thread 1 is being cleared, operation not allowed 

ORA-00312: online log 1 thread 1: '/data/oradata/clone/group_1.log' 

ORA-00312: online log 1 thread 1: '/data/oradata/clone/group_12.log'

 

接着查看redo日志的状态为invaild的（ CLEARING/CLEARING_CURRENT）：

select GROUP#,THREAD#,SEQUENCE#,MEMBERS,ARCHIVED,STATUS,FIRST_CHANGE# from v$log order by first_change# ;       GROUP#     THREAD#   SEQUENCE#        MEMBERS   ARCHIVED STATUS                 FIRST_CHANGE#------------- ----------- ----------- -------------- ---------- ---------------- -------------------            2           1           0              2 YES        CLEARING              12142264718335            3           1           0              2 YES        CLEARING              12142306351551            1           1           0              2 NO         CLEARING_CURRENT      12142306351562

解决办法：

1，试着clear当前group能否执行：

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;

或者

2，重新生成控制文件，修改日志文件目录指向：

1)生成控制文件的trace备份：

SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/tmp/control.sql' resetlogs ;

 

2)修改控制文件 /tmp/control.sql 确保redo相关目录存在且oracle有写权限；

3)创建控制文件

SQL> STARTUP FORCE NOMOUNTSQL> @/tmp/control.sql controlfile created

 

4)根据当前控制文件recover database:

SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL ;Type 
      
        when prompted
      

 

5) 打开数据库使用 RESETLOGS:

SQL> ALTER DATABASE OPEN RESETLOGS ;