收到此次故障时,同事给出的描述是数据库不能归档,做过基本的检查,磁盘空间不存在问题,磁盘状态也都是正常的,并附上部分警报日志文件内容:
Wed May 17 10:43:42 2006 ARC1: Evaluating archive log 1 thread 1 sequence 202 ARC1: Archiving not possible: No primary destinations ARC1: Failed to archive log 1 thread 1 sequence 202 Wed May 17 10:43:42 2006 Errors in file /oracle/admin/jshs/bdump/jshs_arc1_17874.trc: ORA-16014: log 1 sequence# 202 not archived, no available destinations ORA-00312: online log 1 thread 1: '/u01/oradata/jshs/redo01.log'
从日志来看,的确是数据库不能归档,并且提示归档路径错误。
登陆数据库进行检查,首先查询参数设置及归档路径状态:
SQL> select dest_id,dest_name,status from v$archive_dest; DEST_ID DEST_NAME STATUS ---------- -------------------- --------- 1 LOG_ARCHIVE_DEST_1 ERROR 2 LOG_ARCHIVE_DEST_2 INACTIVE 3 LOG_ARCHIVE_DEST_3 INACTIVE 4 LOG_ARCHIVE_DEST_4 INACTIVE 5 LOG_ARCHIVE_DEST_5 INACTIVE 6 LOG_ARCHIVE_DEST_6 INACTIVE 7 LOG_ARCHIVE_DEST_7 INACTIVE 8 LOG_ARCHIVE_DEST_8 INACTIVE 9 LOG_ARCHIVE_DEST_9 INACTIVE 10 LOG_ARCHIVE_DEST_10 INACTIVE
已选择10行。
SQL> show parameter log_archive_dest NAME TYPE VALUE ------------------------------------ ----------- ---------------------------------- log_archive_dest string log_archive_dest_1 string LOCATION=/u04/oradata/jshs/archive ..... log_archive_dest_state_1 string enable
发现当前归档路径的状态的确是错误(error)的。检查警报日志文件,找到第一次出现错误的部分:
Wed May 17 10:32:31 2006 Errors in file /oracle/admin/jshs/bdump/jshs_arc1_17874.trc: ORA-00354: corrupt redo log block header ORA-00353: log corruption near block 92256 change 0 time 05/17/2006 01:57:27 ORA-00312: online log 1 thread 1: '/u01/oradata/jshs/redo01.log' ARC1: Archiving not possible: error count exceeded ARC1: Failed to archive log 1 thread 1 sequence 202 ARCH: Archival stopped, error occurred. Will continue retrying Wed May 17 10:32:31 2006 ORACLE Instance jshs - Archival Error ARCH: Connecting to console port... Wed May 17 10:32:31 2006 ORA-16038: log 1 sequence# 202 cannot be archived ORA-00354: corrupt redo log block header ORA-00312: online log 1 thread 1: '/u01/oradata/jshs/redo01.log' ARCH: Connecting to console port... ARCH: Wed May 17 10:32:31 2006 ORA-16038: log 1 sequence# 202 cannot be archived ORA-00354: corrupt redo log block header ORA-00312: online log 1 thread 1: '/u01/oradata/jshs/redo01.log' Wed May 17 10:32:31 2006 Errors in file /oracle/admin/jshs/bdump/jshs_arc1_17874.trc: ORA-16038: log 1 sequence# 202 cannot be archived ORA-00354: corrupt redo log block header ORA-00312: online log 1 thread 1: '/u01/oradata/jshs/redo01.log' 我们注意,这里发现了问题的根本原因,归档失败的原因在于日志损坏。检查跟
踪文件jshs_arc1_17874.trc ,由于多次归档不能成功,导致数据库将归档路径标记为Error,使得后续正常的日志同样无法归档:
*** 2006-05-17 10:32:31.621 kcrrfail: dest:1 err:354 force:0 ORA-00354: corrupt redo log block header ORA-00353: log corruption near block 92256 change 0 time 05/17/2006 01:57:27 ORA-00312: online log 1 thread 1: '/u01/oradata/jshs/redo01.log' *** 2006-05-17 10:32:31.662 ARC1: Archiving not possible: error count exceeded ORA-16038: log 1 sequence# 202 cannot be archived ORA-00354: corrupt redo log block header ORA-00312: online log 1 thread 1: '/u01/oradata/jshs/redo01.log' ORA-16014: log 1 sequence# 202 not archived, no available destinations
查询数据库:
SQL> select * from v$logfile; GROUP# STATUS TYPE MEMBER ---------- ------- ------- ---------------------------------------- 1 ONLINE /u01/oradata/jshs/redo01.log 2 ONLINE /u01/oradata/jshs/redo02.log 3 ONLINE /u01/oradata/jshs/redo03.log SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS ---------- ---------- ---------- ---------- ---------- --- ---------------- 1 1 202 104857600 1 NO INACTIVE 2 1 313 104857600 1 NO CURRENT 3 1 312 104857600 1 YES INACTIVE
我们看到在其他人进行的多次重起切换过程中,日志组2和组3的SEQUENCE#都已经增进,只有日志组1的SEQUENCE#仍然是202。
由于日志组1并非Current日志组,所以我们可以通过Clear方式清除该日志内容,从而使该日志恢复正常状态:
SQL> alter database clear unarchived logfile group 1;
数据库已更改。
SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS ---------- ---------- ---------- ---------- ---------- --- ---------------- 1 1 0 104857600 1 YES UNUSED 2 1 313 104857600 1 YES INACTIVE 3 1 314 104857600 1 NO CURRENT
注意,由于该日志未归档,所以之前的热备份用于恢复时将不能跨越这个缺口,Oracle建议重新进行全库备份,从警告日志中也可以看到如下提示:
Wed May 17 11:17:32 2006 alter database clear unarchived logfile group 1 Wed May 17 11:17:35 2006 WARNING! CLEARING REDO LOG WHICH HAS NOT BEEN ARCHIVED. BACKUPS TAKEN BEFORE 05/17/2006 01:58:01 (CHANGE 338217516) CANNOT BE USED FOR RECOVERY. Clearing online log 1 of thread 1 sequence number 202 Completed: alter database clear unarchived logfile group 1 Wed May 17 11:18:11 2006 Archiver process freed from errors. No longer stopped
并且注意到归档进程从错误中被释放出来,数据库恢复了正常。我们手工进行日志切换:
SQL> alter system switch logfile;
系统已更改。
SQL> /
系统已更改。
再检查日志归档情况,确认日志组1已经被成功归档:
SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS ---------- ---------- ---------- ---------- ---------- --- ---------------- 1 1 315 104857600 1 YES ACTIVE 2 1 316 104857600 1 NO CURRENT 3 1 314 104857600 1 YES INACTIVE
检查归档路径的状态,发现已经恢复正常:
SQL> select dest_name,status from v$archive_dest where rownum <2; DEST_NAME STATUS ------------------------------ --------- LOG_ARCHIVE_DEST_1 VALID
至此问题解决完毕,后续的工作是需要对数据库进行备份。
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。