数据库open状态下日志异常处理

发表于 2011 年 8 月 24 日 由 惜分飞

联系：手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题：数据库open状态下日志异常处理

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

一、环境模拟
删除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备份恢复 分类目录。将固定链接加入收藏夹。

《数据库open状态下日志异常处理》有 2 条评论

  1. 梦想启动未来 说：
    2013 年 10 月 11 日17:38

    梦想启动未来,

    谢谢你的回复，是看你的日志https://www.xifenfei.com/1478.html中出现
    ORA-00313: Message 313 not found; No message file for product=RDBMS, facility=ORA; arguments: [3] [1]
    想知道怎么会会出现ORA-00313，如何解决。

  2. 梦想启动未来 说：
    2013 年 9 月 23 日16:57

    请问，怎么会有ORA-XXX: Message xxx not found; No message file for product=RDBMS, facility=ORA;如何解决message not found的问题？