ORA-00392

APPLIES TO

Oracle Database - Enterprise Edition - Version 10.1.0.2 to 11.2.0.2 [Release 10.1 to 11.2]

SYMPTOMS

在 RESTORE/RECOVER 数据库后，打开数据库报如下错误：

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00392: log 20 of thread 2 is being cleared, operation not allowed
ORA-00312: online log 20 thread 2:
'/u01/app/oracle/oradata/STONE/onlinelog/o1_mf_20_%u_.log'
ORA-00312: online log 20 thread 2:
'/u01/app/oracle/fast_recovery_area/STONE/onlinelog/o1_mf_20_%u_.log'

CAUSE

因为联机重做日志存在 CLEARING/CLEARING_CURRENT 状态。

SQL> select GROUP#,THREAD#,SEQUENCE#,MEMBERS,ARCHIVED,STATUS,FIRST_CHANGE# from v$log order by first_change# ;

    GROUP#    THREAD#  SEQUENCE#    MEMBERS ARC STATUS           FIRST_CHANGE#
---------- ---------- ---------- ---------- --- ---------------- -------------
        16          2          0          2 YES UNUSED              8404986995
        17          2          0          2 YES UNUSED              8406639597
        13          1          0          2 YES UNUSED              8408061431
        14          1          0          2 YES UNUSED              8409535327
        15          1          0          2 YES UNUSED              8410993604
        18          2          0          2 YES UNUSED              8410993910
        19          2          0          2 YES CLEARING            8411750908
        11          1          0          2 YES UNUSED              8411750937
        12          1          0          2 NO  CURRENT             8411750992
        20          2          0          2 NO  CLEARING_CURRENT    8411751035

10 rows selected.

SOLUTION

清理存在问题的联机重做日志文件。

SQL> alter database clear unarchived logfile group 19;

Database altered.

SQL> alter database clear unarchived logfile group 20;

Database altered.

SQL> select GROUP#,THREAD#,SEQUENCE#,MEMBERS,ARCHIVED,STATUS,FIRST_CHANGE# from v$log order by first_change# ;

    GROUP#    THREAD#  SEQUENCE#    MEMBERS ARC STATUS           FIRST_CHANGE#
---------- ---------- ---------- ---------- --- ---------------- -------------
        16          2          0          2 YES UNUSED              8404986995
        17          2          0          2 YES UNUSED              8406639597
        13          1          0          2 YES UNUSED              8408061431
        14          1          0          2 YES UNUSED              8409535327
        15          1          0          2 YES UNUSED              8410993604
        18          2          0          2 YES UNUSED              8410993910
        19          2          0          2 YES UNUSED              8411750908
        11          1          0          2 YES UNUSED              8411750937
        12          1          0          2 NO  CURRENT             8411750992
        20          2          0          2 NO  CURRENT             8411751035

10 rows selected.

SQL> alter database open resetlogs;

Database altered.

REFERENCES

NOTE:1352133.1 - ALTER DATABASE OPEN RESETLOGS fails with ORA-00392