天萃荷净

运维DBA反映数据库重启后无法启动，报错：ORA-00313 ORA-00312 ORA-27037，分析原因为数据库redo日志损坏导致

一、环境模拟

删除INACTIVE状态redo日志的物理文件，然后进行大批量事务操作

二、出现现象

1、alert.log记录

Tue Aug 23 23:32:02 2011

Errors in file /u01/admin/xienfei/bdump/xff_arc1_8773.trc:

ORA-00313: Message 313 not found; No message file for product=RDBMS, facility=ORA; arguments: [3] [1]

ORA-00312: Message 312 not found; No message file for product=RDBMS, facility=ORA; arguments: [3] [1] [/u01/oradata/xienfei/redo03.log]

ORA-27037: Message 27037 not found; No message file for product=RDBMS, facility=ORA

Linux Error: 2: No such file or directory

Additional information: 3

2、xff_arc1_8773.trc文件中内容

*** 2011-07-18 18:35:32.071 59526 kcrr.c

kcrrfail: dest:2 err:12541 force:0 blast:1

ORA-00313: Message 313 not found; No message file for product=RDBMS, facility=ORA; arguments: [3] [1]

ORA-00312: Message 312 not found; No message file for product=RDBMS, facility=ORA; arguments: [3] [1] [/u01/oradata/xienfei/redo03.log]

ORA-27037: Message 27037 not found; No message file for product=RDBMS, facility=ORA

Linux Error: 2: No such file or directory

三、处理过程

1、由alert中的redo文件路径，查询group#

SQL> select group#,member from v$logfile;

GROUP# MEMBER

———- ———————————————

3 /u01/oradata/xienfei/redo03.log

2 /u01/oradata/xienfei/redo02.log

1 /u01/oradata/xienfei/redo01.log

SQL> select group#,sequence#,status from v$log;

GROUP# SEQUENCE# STATUS

———- ———- —————-

1 16 ACTIVE

2 17 CURRENT

3 15 INACTIVE

2、确定是inactive状态的redo日志，直接删除该日志组

SQL> alter database drop logfile group 3;

alter database drop logfile group 3

*

ERROR at line 1:

ORA-00350: log 3 of instance xff (thread 1) needs to be archived

ORA-00312: online log 3 thread 1: ‘/u01/oradata/xienfei/redo03.log’

3、删除失败，提示该日志需要归档，然后直接强行执行清空redo操作

SQL> alter database clear logfile group 3;

alter database clear logfile group 3

*

ERROR at line 1:

ORA-00350: log 3 of instance xff (thread 1) needs to be archived

ORA-00312: online log 3 thread 1: ‘/u01/oradata/xienfei/redo03.log’

4、还不行，加大力度，直接清空并不归档

SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3;

Database altered.

5、再删除group 3

SQL> alter database drop logfile group 3;

alter database drop logfile group 3

*

ERROR at line 1:

ORA-01623: log 3 is current log for instance xff (thread 1) – cannot drop

ORA-00312: online log 3 thread 1: ‘/u01/oradata/xienfei/redo03.log’

6、还是失败，根据提示查看该日志组当前状态

SQL> select group#,sequence#,status from v$log;

GROUP# SEQUENCE# STATUS

———- ———- —————-

1 16 INACTIVE

2 17 ACTIVE

3 18 CURRENT

7、切换日志组

SQL> alter system switch logfile;

System altered.

8、查看状态，并处理置于INACTIVE状态

SQL> select group#,sequence#,status from v$log;

GROUP# SEQUENCE# STATUS

———- ———- —————-

1 19 CURRENT

2 17 ACTIVE

3 18 ACTIVE

SQL> alter system checkpoint;

System altered.

SQL> select group#,sequence#,status from v$log;

GROUP# SEQUENCE# STATUS

———- ———- —————-

1 19 CURRENT

2 17 INACTIVE

3 18 INACTIVE

9、再次删除归档group 3

SQL> alter database drop logfile group 3;

alter database drop logfile group 3

*

ERROR at line 1:

ORA-00350: log 3 of instance xff (thread 1) needs to be archived

ORA-00312: online log 3 thread 1: ‘/u01/oradata/xienfei/redo03.log’

10、再次清空日志并不归档

SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3;

Database altered.

11、查询状态

SQL> select group#,sequence#,status from v$log;

GROUP# SEQUENCE# STATUS

———- ———- —————-

1 19 CURRENT

2 17 INACTIVE

3 0 UNUSED

12、再次删除group 3

SQL> alter database drop logfile group 3;

Database altered.

SQL> select group#,sequence#,status from v$log;

GROUP# SEQUENCE# STATUS

———- ———- —————-

1 19 CURRENT

2 17 INACTIVE

13、删除丢失的group 3成功，需要添加一组日志

SQL> alter database add logfile group 3 ‘/u01/oradata/xienfei/redo03.log’ size 50m reuse;

Database altered.

14、核实是否正常

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> select group#,sequence#,status from v$log;

GROUP# SEQUENCE# STATUS

———- ———- —————-

1 22 CURRENT

2 21 ACTIVE

3 20 INACTIVE

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【案例】Oracle报错ORA-00313 ORA-00312 ORA-27037 日志异常处理办法