ORA-00314: log 102 of thread 1, expected sequence# 642317 doesn't match 642315

第一次见到这种错误，记录一下，根据网上搜的资料这个错误有可能会导致数据库挂掉，还好遇到的时候没挂。

alert日志如下：

关于报错的原因和解决方法mos搜到一篇相关文档符合遇到的情况 ORA-00314: LOG 404 OF THREAD 4, EXPECTED SEQUENCE# 33808 DOESN'T MATCH 33543 (Doc ID 1077564.1)

 

SYMPTOMS

Following errors are received when attempting to startup standby or while logs are being applied

ORA-00314: log 404 of thread 4, expected sequence# 33808 doesn't match 33543
ORA-00312: online log 404 thread 4: '+<path>/onlinelog/group_404.468.703522549'
ORA-00314: log 404 of thread 4, expected sequence# 33808 doesn't match 33543
ORA-00312: online log 404 thread 4: '+<path>/onlinelog/group_404.450.703522549'

报错原因

• Standby redo has corrupt entry.
• Instance crash while redo entry being transfer/received
• The error message with ora-314 tells you the standby redo with corruption

例如，ORA-00314: log 404 of thread 4, expected sequence# 33808 doesn't match 33543
==> redo log group (# 404) is the one with corruption.

When problem (instance crash,network problem) happened, it was receiving sequence 33808 and was corrupted in the middle. so the header has info on sequence 33808 but current archive sequence# being transferred is 33543.

You may see the another standby redo is receiving the sequence# 33543 in v$standby_log with two of them are active status.

GROUP# DBID THREAD# SEQUENCE#
---------- ---------------------------------------- ---------- ----------
BYTES USED ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- --- ---------- ------------- -------------------
LAST_CHANGE# LAST_TIME
------------ -------------------
401 1863397730 4 34381
104857600 93738496 YES ACTIVE 5.9903E+12 Jan 28 2010 05:22PM
5.9903E+12 Jan 28 2010 06:52PM

404 1863397730 4 33808
104857600 0 YES ACTIVE 5.9902E+12 Jan 24 2010 02:04AM
5.9903E+12 Jan 28 2010 06:52PM

 

解决方法

Clear standby redo with ora-314 error.

-- Stop recovery on standby side.
SQL> Alter database recover managed standby database cancel;

--clear standby redo group 404.
SQL>  alter database clear logfile group 404

-- You may have to use the 'unarchived'-Keyword to be able to clear the Standby RedoLog Group in most Cases, eg.
SQL> alter database clear unarchived logfile group 404;

 

结合mos文档和alert日志，再来整理一下问题发生的过程：

• 17:33:04 RFS[426] selected log 102 for sequence 642315
• 17:33:04 Media Recovery Waiting for sequence 642315（in transit），同时读取642315的online日志进行恢复。
• 17:33:04 MRP applyed sequence 642314
• 17:33:58 RFS[426] selected log 101 for sequence 642316
• 17:33:58 Media Recovery Waiting for sequence 642316（in transit），同时读取642316的online日志进行恢复。
• 17:34:00 MRP applyed sequence 642315
• 17:34:49 RFS[426] selected log 102 for sequence 642317。
• 17:34:49 传输的642317有损坏，导致报错ORA-314。此时standby logfile header中记录的sequence是642317，而数据库中最新applyed的sequence（current archive sequence# being transferred），即控制文件中记录的sequence是642315。所以也同时报错 ORA-00338：log 102 more recent than control file。
• 17:34:50 Media Recovery Waiting for sequence 642317（in transit）-- 这里应该是续传642317的日志到standby log。并且运气好，续传还能接的上。所以后续就直接读取这个序列的standby log，MRP进行恢复了。
• 17:34:51 MRP applyed sequence 642316
• 17:35:49 MRP applyed sequence 642317，此后恢复了正常