又一例ORA-600 kcbzpbuf_1恢复
惜分飞发表于 2023-05-11 14:35:10
联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
数据库突然报ORA-600 kdddgb1和ORA-600 kcl_snd_cur_2错误,并且导致实例crash
Tue May 09 22:29:40 2023 Errors in file /oracle/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_ora_338012.trc (incident=962050): ORA-00600: internal error code, arguments: [kdddgb1], [0], [], [], [], [], [], [], [], [], [], [] Incident details in: /oracle/app/oracle/diag/rdbms/orcl/orcl1/incident/incdir_962050/orcl1_ora_338012_i962050.trc Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. Tue May 09 22:29:43 2023 Hex dump of (file 75, block 1154926) in trace file /oracle/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_lms3_217928.trc Corrupt block relative dba: 0x12d19f6e (file 75, block 1154926) Bad header found during preparing block for transfer Data in bad block: type: 0 format: 2 rdba: 0x1affe051 last change scn: 0x0009.a2266e65 seq: 0x2 flg: 0x10 spare1: 0x83 spare2: 0x36 spare3: 0x3700 consistency value in tail: 0x6e650002 check value in block header: 0x0 block checksum disabled Errors in file /oracle/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_lms3_217928.trc (incident=960186): ORA-00600: internal error code, arguments: [kcl_snd_cur_2], [], [], [], [], [], [], [], [], [], [], [] Incident details in: /oracle/app/oracle/diag/rdbms/orcl/orcl1/incident/incdir_960186/orcl1_lms3_217928_i960186.trc Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. Tue May 09 22:29:43 2023 Sweep [inc][962050]: completed Sweep [inc][960186]: completed Sweep [inc2][962050]: completed Errors in file /oracle/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_lms3_217928.trc: ORA-00600: internal error code, arguments: [kcl_snd_cur_2], [], [], [], [], [], [], [], [], [], [], [] LMS3 (ospid: 217928): terminating the instance due to error 484 System state dump requested by (instance=1, osid=217928 (LMS3)), summary=[abnormal instance termination]. System State dumped to trace file /oracle/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_diag_217897_20230509222949.trc Tue May 09 22:29:52 2023 ORA-1092 : opitsk aborting process Tue May 09 22:29:53 2023 ORA-1092 : opitsk aborting process Tue May 09 22:29:54 2023 Instance terminated by LMS3, pid = 217928
另外一个正在运行的实例做instance recovery,然后节点报ORA-600 kcbzpbuf_1,节点也crash,再次启动一直该错误无法正常启动.
Wed May 10 08:17:07 2023 Hex dump of (file 75, block 1154926) in trace file /oracle/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_dbw9_134621.trc Corrupt block relative dba: 0x12d19f6e (file 75, block 1154926) Bad header found during preparing block for write Data in bad block: type: 0 format: 2 rdba: 0x1affe051 last change scn: 0x0009.a2266e65 seq: 0x2 flg: 0x34 spare1: 0x83 spare2: 0x36 spare3: 0x3700 consistency value in tail: 0x6e650002 check value in block header: 0xf894 computed block checksum: 0x0 Errors in file /oracle/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_dbw9_134621.trc (incident=2240402): ORA-00600: internal error code, arguments: [kcbzpbuf_1], [4], [1], [], [], [], [], [], [], [], [], [] Incident details in: /oracle/app/oracle/diag/rdbms/orcl/orcl1/incident/incdir_2240402/orcl1_dbw9_134621_i2240402.trc Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. Errors in file /oracle/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_dbw9_134621.trc: ORA-00600: internal error code, arguments: [kcbzpbuf_1], [4], [1], [], [], [], [], [], [], [], [], [] DBW9 (ospid: 134621): terminating the instance due to error 471 Wed May 10 08:17:08 2023 System state dump requested by (instance=1, osid=134621 (DBW9)), summary=[abnormal instance termination]. System State dumped to trace file /oracle/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_diag_134555_20230510081708.trc Instance terminated by DBW9, pid = 134621
尝试直接recover datafile 75失败,报ORA-03113
SQL> recover datafile 75; ORA-03113: end-of-file on communication channel Process ID: 281304 Session ID: 14161 Serial number: 1503
dbv检查file 75,发现15个block逻辑坏块
[oracle@oradb21 ~]$ dbv userid=xxx/xxx file=+datadg/orcl/datafile/xifenfei01.377.1130539753
DBVERIFY: Release 11.2.0.4.0 - Production on Wed May 10 08:29:44 2023
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = +datadg/orcl/datafile/xifenfei01.377.1130539753
Block Checking: DBA = 314866909, Block Type = KTB-managed data block
data header at 0x7f852b573064
kdbchk: row locked by non-existent transaction
table=0 slot=13
lockid=101 ktbbhitc=2
Page 294109 failed with check code 6101
Block Checking: DBA = 314866928, Block Type = KTB-managed data block
data header at 0x7f852b599064
kdbchk: row locked by non-existent transaction
table=0 slot=18
lockid=101 ktbbhitc=2
Page 294128 failed with check code 6101
Block Checking: DBA = 315415269, Block Type = KTB-managed data block
data header at 0x7f852b583064
kdbchk: the amount of space used is not equal to block size
used=7470 fsc=0 avsp=625 dtl=8088
Page 842469 failed with check code 6110
Block Checking: DBA = 315415302, Block Type = KTB-managed data block
data header at 0x7f852b3c3064
kdbchk: row locked by non-existent transaction
table=0 slot=13
lockid=101 ktbbhitc=2
Page 842502 failed with check code 6101
Block Checking: DBA = 315415350, Block Type = KTB-managed data block
data header at 0x7f852b423064
kdbchk: row locked by non-existent transaction
table=0 slot=14
lockid=101 ktbbhitc=2
Page 842550 failed with check code 6101
Block Checking: DBA = 315415351, Block Type = KTB-managed data block
data header at 0x7f852b425064
kdbchk: row locked by non-existent transaction
table=0 slot=10
lockid=101 ktbbhitc=2
Page 842551 failed with check code 6101
Block Checking: DBA = 315415397, Block Type = KTB-managed data block
data header at 0x7f852b481064
kdbchk: row locked by non-existent transaction
table=0 slot=14
lockid=101 ktbbhitc=2
Page 842597 failed with check code 6101
Block Checking: DBA = 315415414, Block Type = KTB-managed data block
data header at 0x7f852b4a3064
kdbchk: row locked by non-existent transaction
table=0 slot=14
lockid=101 ktbbhitc=2
Page 842614 failed with check code 6101
Block Checking: DBA = 315665300, Block Type = KTB-managed data block
data header at 0x7f852b2dd0ac
kdbchk: the amount of space used is not equal to block size
used=7191 fsc=0 avsp=832 dtl=8016
Page 1092500 failed with check code 6110
Block Checking: DBA = 315665302, Block Type = KTB-managed data block
data header at 0x7f852b2e10ac
kdbchk: row locked by non-existent transaction
table=0 slot=14
lockid=101 ktbbhitc=5
Page 1092502 failed with check code 6101
Block Checking: DBA = 315665316, Block Type = KTB-managed data block
data header at 0x7f852b2fd0ac
kdbchk: the amount of space used is not equal to block size
used=7140 fsc=0 avsp=883 dtl=8016
Page 1092516 failed with check code 6110
Block Checking: DBA = 315665491, Block Type = KTB-managed data block
data header at 0x7f852f4170c4
kdbchk: row locked by non-existent transaction
table=0 slot=3
lockid=101 ktbbhitc=6
Page 1092691 failed with check code 6101
Block Checking: DBA = 315727518, Block Type = KTB-managed data block
data header at 0x7f852b4f50c4
kdbchk: row locked by non-existent transaction
table=0 slot=8
lockid=101 ktbbhitc=6
Page 1154718 failed with check code 6101
Block Checking: DBA = 315727614, Block Type = KTB-managed data block
data header at 0x7f852b5b50ac
kdbchk: row locked by non-existent transaction
table=0 slot=15
lockid=101 ktbbhitc=5
Page 1154814 failed with check code 6101
Block Checking: DBA = 315727646, Block Type = KTB-managed data block
data header at 0x7f852b3f30ac
kdbchk: row locked by non-existent transaction
table=0 slot=3
lockid=101 ktbbhitc=5
Page 1154846 failed with check code 6101
DBVERIFY - Verification complete
Total Pages Examined : 1835008
Total Pages Processed (Data) : 250749
Total Pages Failing (Data) : 15
Total Pages Processed (Index): 74532
Total Pages Failing (Index): 0
Total Pages Processed (Other): 1244181
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 265546
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 2720428335 (9.2720428335)
通过对坏块一些处理,数据库open成功,以前有过类似恢复ORA-600 kcbzpbuf_1故障恢复
SQL> alter database open; Database altered.
alert日志报事务异常
ORACLE Instance orcl1 (pid = 34) - Error 1578 encountered while recovering transaction (697, 6) on object 170692. Errors in file /oracle/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_smon_301450.trc: ORA-01578: ORACLE data block corrupted (file # 75, block # 1154926) ORA-01110: data file 75: '+DATADG/orcl/datafile/xifenfei01.377.1130539753' Archived Log entry 9299 added for thread 1 sequence 4781 ID 0x5f4a1865 dest 1: Wed May 10 08:24:03 2023 NOTE: dependency between database orcl and diskgroup resource ora.ARCHDG.dg is established ARC3: Archival started ARC0: STARTING ARCH PROCESSES COMPLETE Wed May 10 08:24:04 2023 Starting background process EMNC Wed May 10 08:24:04 2023 EMNC started with pid=49, OS id=305303 Archived Log entry 9300 added for thread 2 sequence 4530 ID 0x5f4a1865 dest 1: ARC2: Archiving disabled thread 2 sequence 4531 Archived Log entry 9301 added for thread 2 sequence 4531 ID 0x5f4a1865 dest 1: Wed May 10 08:24:13 2023 Errors in file /oracle/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_p000_305307.trc (incident=2560578): ORA-01578: ORACLE data block corrupted (file # 75, block # 1154926) ORA-01110: data file 75: '+DATADG/orcl/datafile/xifenfei01.377.1130539753' Incident details in: /oracle/app/oracle/diag/rdbms/orcl/orcl1/incident/incdir_2560578/orcl1_p000_305307_i2560578.trc Errors in file /oracle/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_p000_305307.trc (incident=2560579): ORA-01578: ORACLE data block corrupted (file # , block # ) Incident details in: /oracle/app/oracle/diag/rdbms/orcl/orcl1/incident/incdir_2560579/orcl1_p000_305307_i2560579.trc Wed May 10 08:24:15 2023 Errors in file /oracle/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_smon_301450.trc (incident=2560427): ORA-01578: ORACLE data block corrupted (file # 75, block # 1154926) ORA-01110: data file 75: '+DATADG/orcl/datafile/xifenfei01.377.1130539753' Incident details in: /oracle/app/oracle/diag/rdbms/orcl/orcl1/incident/incdir_2560427/orcl1_smon_301450_i2560427.trc Errors in file /oracle/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_smon_301450.trc (incident=2560432): ORA-01578: ORACLE data block corrupted (file # 75, block # 1154926) ORA-01110: data file 75: '+DATADG/orcl/datafile/xifenfei01.377.1130539753' ORACLE Instance orcl1 (pid = 34) - Error 1578 encountered while recovering transaction (717, 20) on object 170692. Errors in file /oracle/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_smon_301450.trc: ORA-01578: ORACLE data block corrupted (file # 75, block # 1154926) ORA-01110: data file 75: '+DATADG/orcl/datafile/xifenfei01.377.1130539753'
处理异常事务,并且定位异常对象表
SQL> select owner,object_name,object_type from dba_objects where object_id=170692; OWNER -------------------------------------------------------------------------------- OBJECT_NAME -------------------------------------------------------------------------------- OBJECT_TYPE --------------------------------------------------------- XFF T_XIFENFEI TABLE
rman检测逻辑坏块所属对象也是这个表（15个坏块均为该表）,对该表数据进行重建抛弃损坏数据,完成本次恢复