ora-742错误处理
今天打开虚拟机启动数据库时遇到ora-742,原因重做日志读时发现写保护丢失,现象重做日志不能归档
查看错误解释
[qdtais1]@ht19c01[/home/oracle]$oerr ora 742 00742, 00000, "Log read detects lost write in thread %s sequence %s block %s" // *Cause: Either a write issued by Oracle was lost by the underlying // operating system or storage system or an Oracle internal error // occurred. // *Action: The trace file shows the lost write location. Dump the problematic // log file to see whether it is a real lost write. Contact Oracle // Support Services.
查看重做日志状态
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME CON_ID ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ---------- 1 1 21 524288000 512 1 YES INACTIVE 18615295 14-JUL-22 18617678 20-JUL-22 0 4 1 20 524288000 512 1 NO INACTIVE 18612767 14-JUL-22 18615295 14-JUL-22 0 3 1 23 524288000 512 1 NO CURRENT 18632009 20-JUL-22 9.2954E+18 0 2 1 22 524288000 512 1 YES INACTIVE 18617678 20-JUL-22 18632009 20-JUL-22 0
重做日志组4状态时inactive,这个状态数据已写入到数据文件,清除重做日志组4不会导致数据丢失。
查看trc文件
*** 2022-07-20 10:54:03.380987 [krsh.c:6364] Error 742 while archiving DDE rules only execution for: ORA 312 ----- START Event Driven Actions Dump ---- ---- END Event Driven Actions Dump ---- ----- START DDE Actions Dump ----- Executing SYNC actions ----- START DDE Action: 'DB_STRUCTURE_INTEGRITY_CHECK' (Async) ----- DDE Action 'DB_STRUCTURE_INTEGRITY_CHECK' was flood controlled ----- END DDE Action: 'DB_STRUCTURE_INTEGRITY_CHECK' (FLOOD CONTROLLED, 0 csec) ----- Executing ASYNC actions ----- END DDE Actions Dump (total 0 csec) ----- <error barrier> at 0x7ffff1e36d30 placed krse.c@1890 ORA-16038: log 4 sequence# 20 cannot be archived ORA-00742: Log read detects lost write in thread sequence block ORA-00312: online log 4 thread 1: '/u01/app/db/oradata/QDTAIS1ADG/onlinelog/o1_mf_4_k8vt9yvc_.log' <error barrier> at 0x7ffff1e388a0 placed ksb.c@4174 Log read is SYNCHRONOUS though disk_asynch_io is enabled! WARNING! Lost write detected in complete log 4(thread 1 seq 20 block 27368). NAB 27656 DDE rules only execution for: ORA 312 ----- START Event Driven Actions Dump ---- ---- END Event Driven Actions Dump ---- ----- START DDE Actions Dump ----- Executing SYNC actions ----- START DDE Action: 'DB_STRUCTURE_INTEGRITY_CHECK' (Async) ----- DDE Action 'DB_STRUCTURE_INTEGRITY_CHECK' was flood controlled ----- END DDE Action: 'DB_STRUCTURE_INTEGRITY_CHECK' (FLOOD CONTROLLED, 0 csec) ----- Executing ASYNC actions ----- END DDE Actions Dump (total 0 csec) ----- <error barrier> at 0x7ffff1e322c8 placed krse.c@7389 ORA-00742: Log read detects lost write in thread 1 sequence 20 block 27368 ORA-00312: online log 4 thread 1: '/u01/app/db/oradata/QDTAIS1ADG/onlinelog/o1_mf_4_k8vt9yvc_.log' <error barrier> at 0x7ffff1e388a0 placed ksb.c@4174 *** 2022-07-20 10:54:03.863805 [krsh.c:6364] Error 742 while archiving DDE rules only execution for: ORA 312 ----- START Event Driven Actions Dump ---- ---- END Event Driven Actions Dump ---- ----- START DDE Actions Dump ----- Executing SYNC actions ----- START DDE Action: 'DB_STRUCTURE_INTEGRITY_CHECK' (Async) ----- DDE Action 'DB_STRUCTURE_INTEGRITY_CHECK' was flood controlled ----- END DDE Action: 'DB_STRUCTURE_INTEGRITY_CHECK' (FLOOD CONTROLLED, 0 csec) ----- Executing ASYNC actions ----- END DDE Actions Dump (total 0 csec) ----- <error barrier> at 0x7ffff1e36d30 placed krse.c@1890 ORA-16038: log 4 sequence# 20 cannot be archived ORA-00742: Log read detects lost write in thread sequence block ORA-00312: online log 4 thread 1: '/u01/app/db/oradata/QDTAIS1ADG/onlinelog/o1_mf_4_k8vt9yvc_.log' <error barrier> at 0x7ffff1e388a0 placed ksb.c@4174
清除重做日志组4
SQL> select open_mode from v$database; OPEN_MODE -------------------- MOUNTED SQL> alter database clear unarchived logfile group 4; Database altered. SQL> alter database open; Database altered.
查看重做日志
SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME CON_ID ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ---------- 1 1 21 524288000 512 1 YES INACTIVE 18615295 14-JUL-22 18617678 20-JUL-22 0 2 1 22 524288000 512 1 YES INACTIVE 18617678 20-JUL-22 18632009 20-JUL-22 0 3 1 23 524288000 512 1 YES INACTIVE 18632009 20-JUL-22 18633653 20-JUL-22 0 4 1 24 524288000 512 1 NO CURRENT 18633653 20-JUL-22 9.2954E+18 0
physical standby遇到ora-742错误参考ORA-00742 Log read detects lost write on standby (Doc ID 2762519.1)
生产系统上建议每组重组日志组成员2个做好冗余,不是每次重做日志组遇到状态都是INACTIVE。