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。