ora-742错误处理

今天打开虚拟机启动数据库时遇到ora-742，原因重做日志读时发现写保护丢失，现象重做日志不能归档
查看错误解释

?

1 2 3 4 5 6 7 8

[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.

　　查看重做日志状态

?

1 2 3 4 5 6

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文件

+ View Code?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50

*** 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

?

1 2 3 4 5 6 7 8 9 10 11 12 13

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.

　　查看重做日志

?

1 2 3 4 5 6 7 8

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。