oracle異機恢復 open resetlogs 報：ORA-00392

參考文檔：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              121422647183353           1           0              2 YES        CLEARING              121423063515511           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 NOMOUNT
SQL> @/tmp/control.sql

controlfile created

?

4)根據當前控制文件recover database:

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

?

5) 打開數據庫使用 RESETLOGS:

SQL> ALTER DATABASE OPEN RESETLOGS ;

?